Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

how use Spreadsheet::ParseExcel::Stream;?

by cibien (Novice)
on Jan 24, 2013 at 07:26 UTC ( #1015095=perlquestion: print w/ replies, xml ) Need Help??
cibien has asked for the wisdom of the Perl Monks concerning the following question:

Hi Perl Monks, I use Spreadsheet::ParseExce (interface to Excel data) but I have a memory overhead. I can solve the problem using Spreadsheet::ParseExcel::Stream does not parse the entire document to memory with no memory overhead. But I'am novice in perl and I not know how apply this module. can you help me? here is the first part of the code:
use Encode; use utf8; use XML::LibXML; use Spreadsheet::ParseExcel; system("cls"); my $materialmapping_file = shift; my $data_folder = shift; unless (defined $materialmapping_file and defined $data_folder) { print "Usage: $0 <materialmapping_file> <data_folder>\n"; exit; } opendir ( DIR, $data_folder ) || die "Error in opening dir $data_f +older\n"; my $materialmapping_table_xml = XML::LibXML->createDocument( "1.0" +, "UTF-8"); my $materialmapping_table_xml_root = $materialmapping_table_xml->c +reateElement("masterdata"); $materialmapping_table_xml_root->setAttribute('version',getSQLTime +Stamp()); $materialmapping_table_xml->setDocumentElement($materialmapping_ta +ble_xml_root); print "loading...\n"; while(($filename = readdir(DIR))){ next unless $filename =~ /\.xls$/i; print " - $filename \n"; my $parser = Spreadsheet::ParseExcel->new(); my $oBook = $parser->parse($data_folder.$filename); next unless defined $oBook; for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iS +heet++) { $oWkS = $oBook->{Worksheet}[$iSheet];
please help me, here is the site "" thank you very much Andrea

Comment on how use Spreadsheet::ParseExcel::Stream;?
Download Code
Replies are listed 'Best First'.
Re: how use Spreadsheet::ParseExcel::Stream;?
by Corion (Pope) on Jan 24, 2013 at 07:48 UTC

    Have you looked at the documentation of Spreadsheet::ParseExcel::Stream? It shows how to use the module.

    If you want more help, you will need to show us what exactly you tried and where you have problems applying the documentation to your code, or understanding the concepts.

Re: how use Spreadsheet::ParseExcel::Stream;?
by roboticus (Chancellor) on Jan 24, 2013 at 12:03 UTC


    Spreadsheet::ParseExcel::Stream doesn't keep the entire spreadsheet in memory at once. Instead, it reads the spreadsheet row-by-row. So to use it, you have to arrange your logic where you don't dare about any data but the current row. Then, as you call each row, use all the data you need, then proceed to the next row.

    # Don't do this! my @defeat_stream; my $xls = Spreadsheet::ParseExcel::Stream->new($xls_file, \%options); while ( my $sheet = $xls->sheet() ) { while ( my $row = $sheet->row ) { # current row now in memory. my @data = @$row; # after this, current row will *disappear*, unless... # you keep the data. push @defeat_stream, $row; } } # Now you either have the entire spreadsheet in memory (defeating the +purpose # of the module), or have run out of memory.

    Note: Don't keep all the data around for future use, as I do above, as then you're throwing away the advantage of the module. But since you don't read everything into RAM at once, you could extract what you need, and then iterate over it.


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

      Thankyou very much! I understand now :) but I try with my code and nothing... the xml output is null.
      my $xls = Spreadsheet::ParseExcel::Stream->new($data_folder.$filenam +e, \%options); while ( my $sheet = $xls->sheet() ) { while ( my $row = $sheet->row ) { my @data = @$row; } } for(my $iSheet=0; $iSheet < $xls->{SheetCount} ; $iSheet++) { my $oWkS = $xls->{Worksheet}[$iSheet];
      I'am too much novice in perl to try.. anyway thankyou for your help :)
        the xml output is null.
        What XML output? You're not printing anything. And why do you have this code:
        for(my $iSheet=0; $iSheet < $xls->{SheetCount} ; $iSheet++) { my $oWkS = $xls->{Worksheet}[$iSheet];
        That code is for Spreadsheet::ParseExcel, not Spreadsheet::ParseExcel::Stream.
Re: how use Spreadsheet::ParseExcel::Stream;?
by runrig (Abbot) on Jan 24, 2013 at 07:43 UTC
    Write a minimal amount of code similar to what's in the Synopsis of the module, tell us what doesn't work or what you're having trouble with, and it will be easier to help you. Including irrelevant code that creates XML documents doesn't really help focus on the problem you're having.

    The row() method returns rows of data from the spreadsheet, you can also check the test directory of the distribution for more examples, and the module is meant to be functionally similar to Spreadsheet::ParseExcel::Simple

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (6)
As of 2016-05-28 02:13 GMT
Find Nodes?
    Voting Booth?