Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

Hi Perlmonks

This has reference to my earlier question on merging of multiple excel files sheet-wise. Since the use of hash is difficult for me, I have written a script wx.pl to produce the result.xls file. The script runs well in cmd and shows all cell values for sheet 1 from three input excel files. But the output file i.e. result.xls contains only the cell values of sheet 1 from cereal.xls file and not others. I need your suggestions and guidance to solve this problem so that result.xls contains the cell values of all three files sheet-wise.

I have given the code of wx.pl in the following:

# Name: wx.pl #!/usr/bin/perl use warnings; use strict; use OLE::Storage_Lite; use Spreadsheet::ParseExcel; use Spreadsheet::WriteExcel; use Digest::MD5; # Create a new Excel workbook my $workbookx = Spreadsheet::WriteExcel->new('result.xls'); # Add a worksheet my $worksheet1x = $workbookx->add_worksheet(); ############################################# my @arr=qw/cereal.xls pulse.xls fruit.xls/; my $row_min=0; my $row_max=11000; # out of 65536 rows in xls my $col_min=0; my $col_max=255; # out of 256 columns in xls print "\n row_min: $row_min; row_max: $row_max col_min: $col_min; col_max: $col_max\n"; ######################################### foreach my $item (@arr) { # foreach LOOP starts my $parser = Spreadsheet::ParseExcel->new(); my $workbook = $parser->parse($item); if ( !defined $workbook ) { die $parser->error(), ".\n"; } my $worksheet1=$workbook->worksheet('Sheet1'); # say from sheet +1 for my $row ( $row_min .. $row_max ) { # for LOOP2 starts for my $col ( $col_min .. $col_max ) { # for LOOP3 starts my $cell = $worksheet1->get_cell( $row, $col ); next unless $cell; print "\n"; print "### $item\n"; print " Row, Column = ($row, $col)\n"; my $value=$cell->value(); print " value: $value\n"; $worksheet1x->write($row,$col,$value); } # for LOOP3 ends } # for LOOP2 ends } # foreach LOOP ends ################### print "\n ###### Over ##########\n"; exit;

Here goes the result in cmd for wx.pl:

row_min: 0; row_max: 11000 col_min: 0; col_max: 255 ### cereal.xls Row, Column = (0, 0) value: No. ### cereal.xls Row, Column = (0, 1) value: Name ### cereal.xls Row, Column = (0, 2) value: Sugar (g /100g) ### cereal.xls Row, Column = (1, 0) value: 1 ### cereal.xls Row, Column = (1, 1) value: Wheat ### cereal.xls Row, Column = (1, 2) value: 0.3 ### cereal.xls Row, Column = (2, 0) value: 2 ### cereal.xls Row, Column = (2, 1) value: Maize ### cereal.xls Row, Column = (2, 2) value: 7.7 ### cereal.xls Row, Column = (3, 0) value: 3 ### cereal.xls Row, Column = (3, 1) value: Rice ### cereal.xls Row, Column = (3, 2) value: 0.1 ### pulse.xls Row, Column = (0, 0) value: No. ### pulse.xls Row, Column = (0, 1) value: Food ### pulse.xls Row, Column = (0, 2) value: Sugar (g /100g) ### pulse.xls Row, Column = (1, 0) value: 1 ### pulse.xls Row, Column = (1, 1) value: Pea ### pulse.xls Row, Column = (1, 2) value: 6 ### pulse.xls Row, Column = (2, 0) value: 2 ### pulse.xls Row, Column = (2, 1) value: Chickpea ### pulse.xls Row, Column = (2, 2) value: 11 ### pulse.xls Row, Column = (3, 0) value: 3 ### pulse.xls Row, Column = (3, 1) value: Pigeonpea ### pulse.xls Row, Column = (3, 2) value: 1.8 ### fruit.xls Row, Column = (0, 0) value: No. ### fruit.xls Row, Column = (0, 1) value: Name ### fruit.xls Row, Column = (0, 2) value: Sugar (g /100g) ### fruit.xls Row, Column = (1, 0) value: 1 ### fruit.xls Row, Column = (1, 1) value: Apple ### fruit.xls Row, Column = (1, 2) value: 10.12 ### fruit.xls Row, Column = (2, 0) value: 2 ### fruit.xls Row, Column = (2, 1) value: Pear ### fruit.xls Row, Column = (2, 2) value: 10 ### fruit.xls Row, Column = (3, 0) value: 3 ### fruit.xls Row, Column = (3, 1) value: Pineapple ### fruit.xls Row, Column = (3, 2) value: 10.0009 ###### Over ##########

In reply to Re^3: How can one merge multiple excel files sheet-wise? by supriyoch_2008
in thread How can one merge multiple excel files sheet-wise? by supriyoch_2008

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others imbibing at the Monastery: (2)
    As of 2020-07-05 01:31 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found

      Notices?