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 );
}
[download]

Regards...Ed