Applying multiple formats to same data in excel using perl

Question

I have a situation where I am writing data to an Excel file using Excel::Writer::XLSX with Perl. I have records stored as comma delimited strings in an array. For example:

$array[0] = "col1_value,col2_value,col3_value,col4_value" # row 1
$array[1] = "col1_value,col2_value,col3_value,col4_value" # row 2
.
.
$array[$n]                                                # row n

Now I have to display these data in Excel with center alignment,border and bg_color. So I have done the below:

my $general_format = $workbook->add_format(
border => 1,
bg_color => 31,
align => 'center'
);

And then I have put a loop on the array and after splitting it, I am putting all columns of each record in excel as below:

Loop on array of records{
$worksheet->write_row( $i+2, 0, $recordRef, $general_format );
}

But I am facing issue that col1 & col2 of each record has numbers whereas col3 & col4 are percentage/date, etc. I need the numbers to be formatted in comma-separated format. If I do the below, the output is fine but when we click the col3/col4 cell in Excel, it converts the percentage/date to number format:

my $general_format = $workbook->add_format(
border => 1,
bg_color => 31,
align => 'center',
num_format => '#,##0'
);

Is there any way to apply the num_format => '#,##0' format specifically on col1 & col2 along with the rest of the formatting done on col1 & col2(the color/alignment/etc) as the data being written is row(record)-wise?

I need col1 and col2 to have below formats:

border => 1,
bg_color => 31,
align => 'center',
num_format => '#,##0'

and col3 and col4 to have below formats:

border => 1,
bg_color => 31,
align => 'center'

But I can write data record wise only(using write_row()), not column by column.

Why can you only write it per row? Is that part of the program out of your hands? — simbabque
It would be useful to have actual example data. Please edit your question and include a couple of rows of real data. — simbabque

simbabque simbabque · Accepted Answer · 2017-12-05T11:45:53

You cannot pass multiple formats to the write_row method. However, you can set a default format for each column before you write the row with set_column.

Here's a short example.

use strict;
use warnings;
use Excel::Writer::XLSX;
 
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
my $worksheet = $workbook->add_worksheet();

my $general_format = $workbook->add_format(
    border => 1,
    bg_color => 31,
    align => 'center',
);

my $num_format = $workbook->add_format();
$num_format->copy($general_format); # we have to copy the other format options
$num_format->set_num_format('#.##0');

# set column formats up front
$worksheet->set_column( 'A:B', undef, $num_format);
$worksheet->set_column( 'C:D', undef, $general_format);

my @records = (
    [qw/ 1.1 11.111 1% 2017-12-05/],
    [qw/ 2.2 22.222 2.2% 2017-12-05/],
    [qw/ 3.3 33.333 3.33% 2017-12-05/],
    [qw/ 4.4 44.444 4.444% 2017-12-05/],
);
my $i = 1;
foreach my $record ( @records) {
    $worksheet->write_row( $i, 0, $record ); # no more format here
    ++$i;
}

This is what it will look like (with the Excel Viewer).

It's important to note that set_column only writes a format if there is no other format present in a cell.

The $format parameter will be applied to any cells in the column that don't have a format.

You also need to take care of the order in which formats are applied.

If you wish to define a column format in this way you should call the method before any calls to write(). If you call it afterwards it won't have any effect.

A default row format takes precedence over a default column format

Applying multiple formats to same data in excel using perl

1 Answers