Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

How to limit the header row height with Excel::Writer::XLSX

by flexvault (Monsignor)
on Nov 29, 2015 at 16:11 UTC ( #1148820=perlquestion: print w/replies, xml ) Need Help??

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

Replies are listed 'Best First'.
Re: How to limit the header row height with Excel::Writer::XLSX
by poj (Abbot) on Nov 29, 2015 at 18:30 UTC
    Am I doing something wrong?
    $worksheet->set_row( 0, $no_rows );
    poj

      Thanks poj and ++

      In the example I used to get me started, the comment for the 'set_row' command was to set the number of rows allowed in the spreadsheet. The current documentation for Excel::Writer::XLSX is excellent, and I corrected my script from the description for 'set_row'.

      And the good news, It Works!

      Regards...Ed

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

Re: How to limit the header row height with Excel::Writer::XLSX
by kcott (Bishop) on Nov 29, 2015 at 23:39 UTC

    G'day flexvault,

    "I searched on this, but all I found were examples of limited size."

    I went straight to the documentation (Excel::Writer::XLSX) and found the set_row() method which has:

    The most common use for this method is to change the height of a row:

    $worksheet->set_row( 0, 20 ); # Row 1 height set to 20

    From your description and code, it appears you have:

    $worksheet->set_row( 0, 5020 ); # Row 1 height set to 5,020

    — Ken

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1148820]
Approved by Athanasius
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2020-09-19 03:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If at first I donít succeed, I Ö










    Results (114 votes). Check out past polls.

    Notices?