perlquestion
machirajun
<p>Hi I used below code to merger different .xlsx in on excel as separate sheets and trying to put the data in worksheets to one work sheet but I am able to write only one sheet data other sheets data I am able to read but not able write in Final.xls</p>
<c>
use Spreadsheet::XLSX;
use Excel::Writer::XLSX;
use Spreadsheet::WriteExcel;
use Spreadsheet::BasicRead;
(@file_list) = glob "*xlsx";
my $workbook = Excel::Writer::XLSX->new( 'Target.xlsx' );
foreach my $setoffiles (@file_list){
my $excel = Spreadsheet::XLSX -> new ("$setoffiles");
$worksheet = $workbook->add_worksheet();
foreach my $sheet (@{$excel -> {Worksheet}}) {
$sheet -> {MaxRow} ||= $sheet -> {MinRow};
$sheet -> {MaxCol} ||= $sheet -> {MinCol};
foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol}) {
my $cell = $sheet -> {Cells} [$row] [$col] ;
if($cell){
$worksheet->write( $row, $col, $cell -> {Val});
}
}
}
}
}
$workbook->close() or die "Error closing file: $!";
my $tlfile= "Target.xlsx";
my $WorkBook1 = Spreadsheet::WriteExcel->new('Final.xls');
my $Sheet1 = $WorkBook1->add_worksheet();
my $TL = new Spreadsheet::BasicRead($tlfile) || die "Could not open '$+tlfile': $!";
do {
$TL->setRow(0);
my $firstrow = $TL->getFirstRow();
$Sheet1->write_row(0, 0, $firstrow);
while (my $nextrow = $TL->getNextRow()) {
my $row = $TL->getRowNumber();
print "$row\n";
$Sheet1->write($row, 0, $nextrow);
}
}while ($TL->getNextSheet());
$WorkBook1 ->close() or die "Error closing file: $!";
</c>
<p>Please help</p>