Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Using cell_handler spreadsheetparse excel for multiple files

by reaper9187 (Scribe)
on Jan 15, 2013 at 10:00 UTC ( #1013341=perlquestion: print w/ replies, xml ) Need Help??
reaper9187 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Everyone,

I'm currently developing an app that will be used to parse large excel files. I have developed code without using cell handler and as expected , it takes a long time to load these files into memory before parsing it . I have read basic codes regarding the use of cell_handler .. but somehow m not able to get it completely... here's a part of my code that i was writing using the cell handler:
#!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel; my $parser = Spreadsheet::ParseExcel->new( CellHandler => \&cell_handler, NotSetCell => 1 ); my $workbook1 = $parser->parse('A.xls'); my $workbook2 = $parser->parse('B.xls'); my $sheetone = $workbook1->Worksheet(0); my $sheettwo = $workbook2->Worksheet(1) ; sub cell_handler { my $workbook = $_[0]; my $sheet_index = $_[1]; my $row = $_[2]; my $col = $_[3]; my $cell = $_[4]; # Do something useful with the formatted cell value print $sheetone->get_cell($row,$col)->value(),"\n"; print $sheettwo->get_cell($row,$col)->value(),"\n"; }
It isnt working obviously .. please help ...

Comment on Using cell_handler spreadsheetparse excel for multiple files
Download Code
Re: Using cell_handler spreadsheetparse excel for multiple files
by tobyink (Abbot) on Jan 15, 2013 at 10:08 UTC
    print $sheetone->get_cell($row,$col)->value(),"\n"; print $sheettwo->get_cell($row,$col)->value(),"\n";

    While $sheetone is being processed, $sheettwo is apparently not yet defined. So what exactly would you expect these two lines to be doing?

    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'
Re: Using cell_handler spreadsheetparse excel for multiple files
by reaper9187 (Scribe) on Jan 15, 2013 at 10:17 UTC
    Hi .. thanks for replying .. I'm actually trying to parse them simultaneously(for comparing)... how do i get around this problem ??

      reaper9187:

      That depends on your (unstated) requirements: If you truly need to have the values in sheet1 and sheet2 handled at the same time, then you'll have to make a copy of the data while you process sheet 1 so you can have the data when you get around to sheet 2. Something like:

      my @TEMP; sub cell_handler { my $workbook = $_[0]; my $sheet_index = $_[1]; my $row = $_[2]; my $col = $_[3]; my $cell = $_[4]; if ($sheet_index == 1) { $TEMP[$row][$col] = $cell; } elsif ($sheet_index == 2) { print $TEMP[$row][$col]->value(),"\n"; print $cell->value(),"\n"; } }

      *However*, this relies on the parser seeing all the cells in sheet 1 before seeing sheet 2. (I don't know if that's always going to happen or not.) You could expand this code to store away *all* values and process them afterwards, but then you wouldn't need a cell_handler function, either, as you could loop over the data after the sheets are parsed.

      If the parsing order isn't guaranteed, you'll have to figure out how you would do it manually if someone were handing you cells at random, and write the code to do it that way. (You might have noticed a tagline by one of our monks to that effect.)

      ...roboticus

      When your only tool is a hammer, all problems look like your thumb.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1013341]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (8)
As of 2014-12-17 22:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (40 votes), past polls