http://qs321.pair.com?node_id=1148820

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

Dear Monks,

I wrote this subroutine some time ago (like years), and it has worked every since. With the number of rows less than 100, everything looks fine. However if you have a couple hundred or a couple of thousand rows, the size of the the first row ( header row ) takes up pages of blank space. I now have a report that is 5K rows and it's very annoying to get to the actual data. I searched on this, but all I found were examples of limited size.

Am I doing something wrong?

use strict; use warnings; use Excel::Writer::XLSX; our %WORK = (); our $sep = chr(254); my $fields = ""; my $ID = + ""; # . . . # Below is example of how this subroutine is called # my $xls = &Gen_XLS_Report( $no, $xls_file ); sub Gen_XLS_Report { our @AuditReport; # Created before call to this report g +enerator my $seqno = shift; my $xlsx = shift; my $workbook = Excel::Writer::XLSX->new( $xlsx ); my $sheetname = substr( $xlsx, -22, 17 ); my $worksheet = $workbook->add_worksheet( $sheetname ); my $bold = $workbook->add_format( bold => 1 ); ############################################################## +######### # # Set column widths # $worksheet->set_column( 'A:A', 18, $bold ); $worksheet->set_column( 'B:B', 15 ); $worksheet->set_column( 'C:C', 13 ); $worksheet->set_column( 'D:D', 13 ); $worksheet->set_column( 'E:E', 22 ); $worksheet->set_column( 'F:F', 22 ); $worksheet->set_column( 'G:G', 20 ); $worksheet->set_column( 'H:H', 20 ); $worksheet->set_column( 'I:I', 14 ); $worksheet->set_column( 'J:J', 6 ); my $no_rows = $#AuditReport + 20; $worksheet->set_row( 0, $no_rows ); my $heading = $workbook->add_format( bold => 1, color => 'blue', size => 12, merge => 1, align => 'vcenter', ); my $hyperlink_format = $workbook->add_format( color => 'blue', underline => 1, ); # my @headings = ( 'Features of Excel::Writer::XLSX', '' ); my @headings = ( 'Confirmation Date', 'ID#', 'Date', 'Status', + 'Location', 'Number', 'Name', 'Transaction no.', 'Amount', ' ', '' +); $worksheet->write_row( 'A1', \@headings, $heading ); ###################################################################### +# # my $text_format = $workbook->add_format( bold => 1, color => 'black', size => 11, merge => 1, align => 'left', ); my $fields_format = $workbook->add_format( bold => 1, color => 'black', size => 11, merge => 1, align => 'vcenter', ); my $num1_format = $workbook->add_format( num_format => '$ #,## +0.00' ); my $num2_format = $workbook->add_format( num_format => '$ #,##0.00', bold => 1, color => 'blue', size => 12, merge => 1, align => 'right', ); my $totalamount = 0; my $row = 0; for my $no ( 0..$#AuditReport ) { $row = $no + 2; my ( $cnty,$cdate,$ourno,$edate,$status,$location,$nam +e,$cname,$tranum,$amount,undef ) = split($sep, $AuditReport[$no] ); # print "S:$status F:$FeeWaiver A:$amount\n"; $worksheet->write( "A$row", $cdate, $fields_format ); $worksheet->write( "B$row", $ourno, $fields_format ); $worksheet->write( "C$row", $edate, $fields_format ); $worksheet->write( "D$row", $status, $fields_format ); $worksheet->write( "E$row", $location, $fields_format +); $worksheet->write( "F$row", $name ); $worksheet->write( "G$row", $cname ); $worksheet->write( "H$row", $tranum, $fields_format ); $worksheet->write( "I$row", $amount, $num1_format ); $worksheet->write( "J$row", '' ); $totalamount += $amount; } $row += 2; $worksheet->write( "H$row", "No of Transactions:", $heading ); $worksheet->write( "I$row", scalar @AuditReport, $heading ); $row += 2; $worksheet->write( "H$row", "Total Amount:", $heading ); $worksheet->write( "I$row", $totalamount, $num2_format ); }

Regards...Ed

"Well done is better than well said." - Benjamin Franklin