Re: Parsing Excel Columns into arrays

Spreadsheet::ParseExcel can certainly do what you want. If you post your code, we could probably tell you what tweaks you'd need to make. Below, I have started with the code in the module's doc. Where the example traverses the cells in row-major order, I traverse them in column-major order.

    my @columns; # this is your result.
    # each element will be an array(ref) of cell values.

    for(my $iC = $oWkS->{MinCol};
        defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol};
        $iC++
    ) {
        for(my $iR = $oWkS->{MinRow}; 
            defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow};
            $iR++
        ) {
            my $oWkC = $oWkS->{Cells}[$iR][$iC];
            $columns[$iC][$iR] = $oWkC ? $oWkC->Value : undef;
        }
    }
[download]

Comment on Re: Parsing Excel Columns into arrays Download Code

Replies are listed 'Best First'.
Re: Re: Parsing Excel Columns into arrays by Sameet (Beadle) on May 03, 2004 at 07:21 UTC
Hi, I am giving my code here, but it doesnt give me the result i want. #!/usr/bin/perl use strict; use warnings; use Spreadsheet::ParseExcel; my $xls = Spreadsheet::ParseExcel->new(); my $xls_book = $xls->Parse( 'chrocordinates.xls' ); my $xls_worksheet = $xls_book->{Worksheet}[2]; my @contigs = map { $xls_worksheet->Cell( $_ , 0 )->Value} 1 .. 674; my @coordinates = map {$xls_worksheet->Cell($_, 1)->Value} 1 .. 674; #print "@contigs\n"; print "@coordinates\n"; my $new_contig; my $new_coordinate; do{ foreach my $contig(@contigs){ $new_coordinate = shift (@coordinates); $new_contig = shift (@contigs); print "$new_coordinate => (chromosome => \'\', contig +=> $new_contig)\n"; } } [download] I want to have an output like this (it should print as given ahead!) `"'first element of one array' => (chromosome '', contig => 'first element of second array');"` This lookup table i want to use in another program This program prints first 300 odd entries of the 674 expected! What more can be done?!! I hope i have given all the information. All the help is highly solicitated regards Sameet	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: Parsing Excel Columns into arrays
by Sameet (Beadle) on May 03, 2004 at 07:21 UTC

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::ParseExcel;

my $xls = Spreadsheet::ParseExcel->new();
my $xls_book = $xls->Parse( 'chrocordinates.xls' );
my $xls_worksheet = $xls_book->{Worksheet}[2];
my @contigs = map { $xls_worksheet->Cell( $_ , 0 )->Value} 1 .. 674;

my @coordinates = map {$xls_worksheet->Cell($_, 1)->Value} 1 .. 674;

#print "@contigs\n";
print "@coordinates\n";


my $new_contig;
my $new_coordinate;
do{
        foreach my $contig(@contigs){
                $new_coordinate = shift (@coordinates);
                $new_contig = shift (@contigs);
                print "$new_coordinate => (chromosome => \'\', contig 
+=> $new_contig)\n";
        }
}
[download]

"'first element of one array' => (chromosome '', contig => 'first element of second array');"

[reply]
[d/l]
[select]