CODEWITHSUNDEEP
perlxlsxwriter
Robert Kubrick
Robert Kubrick

Reputation: 8713

How can I change XLSX write format dynamically?

I'm using the Excel::Writer::XLSX module and want to change right and left alignment in an Excel spreadsheet based on the column number:

while( my $row = $csv->getline($fh) ) {
    my $col_num = 0;
    my $format = $std_format;
    foreach ( @$row ) {
        if ( $col_num < 2 ) {
            print "col num $col_num, left\n";
            $format->set_align('left');
        }
        else {
            print "col num $col_num, right\n";
            $format->set_align('right');
        }

        $worksheet->write($row_num, $col_num, $_, $format);
        $col_num++;
    }
    $row_num++;
}

This doesn't work in my output spreadsheet. The alignment can only be set once in $std_format and it doesn't change.

Upvotes: 0

Views: 270

Answers (1)

Borodin
Borodin

Reputation: 126722

You can't just assign a new variable to contain the required format. Each different format must be written into the spreadsheet by using add_format

You don't show enough code for me to be able to see what $std_format is, but if you've created it correctly then you can use copy to clone new embedded formats that are available for modification

I suggest that you write something like this, which adds two variations of $std_format and picks which one to use when the cell is written

my $std_left = $std_format->copy;
$std_left->set_align('left');

my $std_right = $std_format->copy;
$std_right->set_align('right');

my $row_num = 0;
while ( my $row = $csv->getline($fh) ) {

    my $col_num = 0;
    for my $cell ( @$row ) {

        my $format = $col_num < 2 ? $std_left : $std_right;
        $worksheet->write($row_num, $col_num, $cell, $format);

        $col_num++;
    }

    $row_num++;
}

Of course you can put the whole thing into a single statement if that's a style you like

$worksheet->write($row_num, $col_num, $cell, $col_num < 2 ? $std_left : $std_right);

Upvotes: 1

Related Questions