Perl - Spreadsheet::WriteExcel function explanation

Question

I'd like to use the function "autofit_columns" as found here:CPAN

Here's my program so far(I skipped the DB connect and query part)

my $workbook = Spreadsheet::WriteExcel->new("TEST.xls");

my $bold = $workbook->add_format();
$bold->set_bold();
my $number = $workbook->add_format();
$number->set_num_format(0x01);
$worksheet = $workbook->add_worksheet('Sheet1');

my @headings = ('Blabla...');

foreach $i (@headings){
$worksheet->write(0, $col++, $i, $bold);
};

$col=0;
$lrow=1;
while (@row = $sth->fetchrow_array()) {
        $worksheet->write($lrow,$col,\@row);
        $lrow++;

};
$sth->finish;
$dbh->disconnect;

autofit_columns($worksheet);
$workbook->close();

sub autofit_columns {

        my $worksheet = shift;
        my $col       = 0;

        for my $width (@{$worksheet->{__col_widths}}) {

            $worksheet->set_column($col, $col, $width) if $width;
            $col++;
        }
    }

PROBLEM: My columns are not autofitted in the xls file... Any idea why?

I don't get the peice of code:

for my $width (@{$worksheet->{__col_widths}}) {

                $worksheet->set_column($col, $col, $width) if $width;
                $col++;
            }

yko · Accepted Answer · 2011-10-17 11:25:10Z

2

You need to look at that example again and implement add_write_handler part too before you write anything to your worksheet.

Please take a look at

$worksheet->add_write_handler(qr[\w], \&store_string_widths);

line and then at store_string_widths subroutine implementation.

Answer is that you need to store absolute width of the string at each write. Then, after you wrote all data to your worksheet, you need to walk through rows and find the biggest string's 'length' for each column - that would be desired column width.

Wish you luck.

answered Oct 17, 2011 at 11:25

yko

2,71014 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jmcnamara · Accepted Answer · 2011-10-17 12:15:31Z

2

You are missing the part of the example code that adds the callback function:

$worksheet->add_write_handler(qr[\w], \&store_string_widths);

You are also missing the store_string_widths() function.

In relation to your second question, the callback stores the maximum string length used for each column. The code snippet is using these lengths to set the column width for each column from the first to the last column that has a length stored. If a column hasn't an autfit width stored then its width isn't adjusted.

This is all a little hacky in Spreadsheet::WriteExcel. It will be more integrated into the module in Excel::Writer::XLSX which is the replacement for WriteExcel.

edited Oct 17, 2011 at 12:15

answered Oct 17, 2011 at 12:09

jmcnamara

42.1k7 gold badges98 silver badges114 bronze badges

2 Comments

Joel Over a year ago

+1 for Excel::Writer::XLSX. I like the work that you're putting into it, please keep it up.

Rgonomike Over a year ago

Hi, Yes thank you... I later figured out that I was missing half of the code. It was a very interesting day and I struggled a lot but eventually came out with a nice report. I thought it would be safer to start with Spreadsheet::Excel first but I'll have a look at the XLSX module. Some customers are still using old versions of Excel so it remains the best choice for the moment. Thanks.

Collectives™ on Stack Overflow

Perl - Spreadsheet::WriteExcel function explanation

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related