Home Applying multiple formats to same data in excel using perl
Reply: 0

Applying multiple formats to same data in excel using perl

user2043
1#
user2043 Published in May 21, 2018, 6:51 pm

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.393093 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO