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

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]
      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]

Back to Seekers of Perl Wisdom