Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Re^2: How can one merge multiple excel files sheet-wise?

by supriyoch_2008 (Monk)
on Apr 01, 2020 at 05:46 UTC ( #11114878=note: print w/replies, xml ) Need Help??


in reply to Re: How can one merge multiple excel files sheet-wise?
in thread How can one merge multiple excel files sheet-wise?

Hi roboticus,

Thank you for your suggestions. I shall learn how to use hash and try to sort out the problem.

With regards,

  • Comment on Re^2: How can one merge multiple excel files sheet-wise?

Replies are listed 'Best First'.
Re^3: How can one merge multiple excel files sheet-wise?
by supriyoch_2008 (Monk) on Apr 02, 2020 at 10:12 UTC

    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 ##########
Re^3: How can one merge multiple excel files sheet-wise?
by supriyoch_2008 (Monk) on Apr 11, 2020 at 06:43 UTC

    Hi roboticus,

    This has reference to my earlier post. I have not been successful in bringing several excel files in a single excel file sheet-wise. I need your help to solve this problem.

    supriyoch_2008

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (6)
As of 2020-07-09 11:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?