Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Merging worksheets in .xls in one Excel sheet

by machirajun (Initiate)
on Sep 20, 2017 at 12:28 UTC ( #1199730=perlquestion: print w/replies, xml ) Need Help??

machirajun has asked for the wisdom of the Perl Monks concerning the following question:

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

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: $!";

Please help

Replies are listed 'Best First'.
Re: Merging worksheets in .xls in one Excel sheet
by toolic (Bishop) on Sep 20, 2017 at 13:12 UTC
    It looks like you are trying to write all rows from all sheets from all input files into one sheet in one output file. The reason you are overwriting data is that you don't increase the row counter between sheets. You need to create a row_count variable outside of all your foreach loops. Then you need to increment it after each write. Maybe something like this UNTESTED:
    use Spreadsheet::XLSX; use Excel::Writer::XLSX; (@file_list) = glob "*xlsx"; my $workbook = Excel::Writer::XLSX->new('Target.xlsx'); $worksheet = $workbook->add_worksheet(); my $row_count = 0; foreach my $setoffiles (@file_list) { my $excel = Spreadsheet::XLSX->new("$setoffiles"); 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_count, $col, $cell->{Val} +); $row_count++; } } } } }
      no luck and the output is abnormal.
        different .xlsx to one .xlsx

        If you want the Target.xlsx workbook to have a worksheet for each of the different workbooks/worksheets try this

        #!/usr/bin/perl use strict; use Spreadsheet::XLSX; use Excel::Writer::XLSX; my $target = 'Target.xlsx'; my @file_list = glob "*xlsx"; my $workbook = Excel::Writer::XLSX->new( $target ) or die "Could not create $target : $!"; for my $file (@file_list){ next if ($file eq $target); print "Scaning $file\n"; my $excel = Spreadsheet::XLSX->new($file); for my $sheet (@{$excel->{Worksheet}}) { my $name = $file ."_".$sheet->{Name}; my $worksheet = $workbook->add_worksheet( $name ); print "Copying $name\n"; $sheet->{MaxRow} ||= $sheet->{MinRow}; $sheet->{MaxCol} ||= $sheet->{MinCol}; for my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) { for my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) { my $cell = $sheet->{Cells}[$row][$col] ; if ($cell){ $worksheet->write( $row, $col, $cell->{Val} ); } } } } }
Re: Merging worksheets in .xls in one Excel sheet
by Tux (Canon) on Sep 20, 2017 at 16:41 UTC

    Please stop using Spreadsheet::XLSX and move on to active support in Spreadsheet::ParseXLSX. The first is buggy and considered dead. No active maint. The latter is reliable and actively maintained.

    Enjoy, Have FUN! H.Merijn
Re: Merging worksheets in .xls in one Excel sheet
by thanos1983 (Parson) on Sep 20, 2017 at 14:19 UTC

    Hello machirajun,

    Welcome to the Monastery. It was a few days ago a recent question was asked and I wrote a script for it. Take a look here Re: Merging 2 xlsx files (UPDATED), maybe this shouts your needs, since is able to handle multiple sheets and multiple documents.

    Hope this helps, BR.

    Seeking for Perl wisdom...on the process of learning...not there...yet!
Re: Merging worksheets in .xls in one Excel sheet
by poj (Abbot) on Sep 21, 2017 at 14:51 UTC

    It maybe that your spreadsheets have cells with spaces in which are overwriting earlier spreadsheets. Run this to check

    #!/usr/bin/perl use strict; use Spreadsheet::ParseXLSX; my $target = 'Target.xlsx'; my @file_list = glob "*xlsx"; my @target=(); for my $file (@file_list){ next if ($file eq $target); print "Scaning $file\n"; my $parser = Spreadsheet::ParseXLSX->new(); my $excel = $parser->parse($file); for my $sheet ( $excel->worksheets ) { my $name = $file ."_".$sheet->get_name; my ( $row_min, $row_max ) = $sheet->row_range(); my ( $col_min, $col_max ) = $sheet->col_range(); for my $row ( $row_min .. $row_max ) { for my $col ( $col_min .. $col_max ) { my $cell = $sheet->get_cell($row,$col) ; if ( $cell ){ my $val = $cell->value; if ($val =~ /\S/){ if ($target[$row][$col]){ print "WARN: Overwriting row: $row col: $col val: '$val' +\n"; } else { $target[$row][$col] = $val; } } else { print "INFO: Skipped blank $name row: $row col: $col value +: '$val'\n"; } } } } } }
      Don't bother. This is a case of "i don't understand or bother to read the answers i got and so post the same again". Such people are not worth your tie.


      You can lead your users to water, but alas, you cannot drown them.

        "You can lead your users to water, but alas, you cannot drown them."

        Please don't consider a career developing software for self driving cars :p

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2022-05-28 16:41 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (99 votes). Check out past polls.