Parsing Excel Columns into arrays

Sameet has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I have an excel file, I need to parse each column into an array so that i can make a statement taking on element from each column with one element from another column and adding preformatted text to it. I have tried Spreadsheet::ParseExcel, but that gave me a mapping of each cell as coordinate of row and column to its value.

. Is there a simple way of doing this?

Regards
Sameet

UPDATE
I am pasting the following code that does the job, but the output is incomplete!

#!/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"; # When print there are 674 entires


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";
        }
} # prints only 300+ odd entires
[download]

How do i get around the probem

Regards
Sameet

Comment on Parsing Excel Columns into arrays Download Code

Replies are listed 'Best First'.
Re: Parsing Excel Columns into arrays by jdporter (Paladin) on May 02, 2004 at 19:36 UTC
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]	[reply] [d/l]
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]


good chemistry is complicated, and a little bit messy -LW
	PerlMonks