Hi Perlmonks,
I am interested in merging multiple (3) excel files sheet-wise i.e. cereal.xls, pulse.xls and
fruit.xls,each having two sheets i.e. sheet1 and sheet2, into a single outputfile like result.xls.
The sheet1 of result file must contain the contents of sheet1 of 3 input files in sequential rows. Similarly,
the sheet2 of result file must contain the contents of sheet2 of 3 input files. While searching the web
I have come across a program, given below, to merge excel files but the result file obtained from this
program contains the values of sheet1 of 3 input files as sheet1, sheet2 and sheet3; rather the contents of
sheet1, sheet2 and sheet3 should be in sheet1 of result.xls. I am using a Windows 7 64-bit pc and comfortable
with .xls and not .xlsx. I have tried to get the desired output but failed. I request Perlmonks to suggest
me how to sort out this problem.
#!/usr/bin/perl
use warnings;
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
my $parser = Spreadsheet::ParseExcel->new(); #Creating Object to Read
+Worksheet
my @files = qw/ cereal.xls pulse.xls fruit.xls /;
my $write_workbook = new Spreadsheet::WriteExcel('Result.xls');
my $n = 1;
foreach my $file (@files) {
my $parse_workbook = $parser->parse($file);
my $write_worksheet = $write_workbook->add_worksheet("Sheet$n");
if ( !defined $parse_workbook) { die $parser->error(), ".\n"; }
for my $parse_worksheet ( $parse_workbook->worksheets() ) {
my ( $row_min, $row_max ) = $parse_worksheet->row_range();
my ( $col_min, $col_max ) = $parse_worksheet->col_range();
for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {
my $cell = $parse_worksheet->get_cell( $row, $col );
next unless $cell;
my $cell_value = $cell->value();
my $cell_format = $cell->get_format();
$write_worksheet->write($row, $col, $cell_value);
}
}
}
$n++;
}
print "\n\n
#######################################
Result produced\n
#######################################\n";
exit;
The contents of cereal.xls are as follows:
Sheet1
No. Name Sugar (g/100g)
1 Wheat 0.3
2 Maize 7.7
3 Rice 0.1
Sheet2
No. Name Protein (g/100g)
1 Wheat 10
2 Maize 7
3 Rice 2.6
The contents of pulse.xls are as follows:
Sheet1
No. Name Sugar (g/100g)
1 Pea 6
2 Chickpea 11
3 Pigeonpea 1.8
Sheet2
No. Name Protein (g/100g)
1 Pea 5
2 Chickpea 8.4
3 Pigeonpea 7.6
The contents of fruit.xls are as follows:
Sheet1
No. Name Sugar (g/100g)
1 Apple 10
2 Pear 10
3 Pineapple 10
Sheet2
No. Name Protein (g/100g)
1 Apple 0.3
2 Pear 0.4
3 Pineapple 0.5
The desired result.xls file should look like:
Sheet1
No. Name Sugar (g/100g)
1 Wheat 0.3
2 Maize 7.7
3 Rice 0.1
1 Pea 6
2 Chickpea 11
3 Pigeonpea 1.8
1 Apple 10
2 Pear 10
3 Pineapple 10
Sheet2
No. Name Protein (g/100g)
1 Wheat 10
2 Maize 7
3 Rice 2.6
1 Pea 5
2 Chickpea 8.4
3 Pigeonpea 7.6
1 Apple 0.3
2 Pear 0.4
3 Pineapple 0.5
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.