Writing percentages

[daxue1]

Excel has “native” percentages (eg. 5.5%) format. But Excel::Writer::XLSX cannot do it well. It either writes as string or misses the percentage mark (%) when writing as number. Any other way to do it natively?

[jmcnamara]

As in Excel, to format a number you must apply a number format. Like this:

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook   = Excel::Writer::XLSX->new( 'test.xlsx' );
my $worksheet  = $workbook->add_worksheet();

my $percent_format1 = $workbook->add_format( num_format => '0%' );
my $percent_format2 = $workbook->add_format( num_format => '0.0%' );

$worksheet->write( 'A1', 0.055, $percent_format1 );
$worksheet->write( 'A2', 0.055, $percent_format2 );

$workbook->close();

__END__

Output:

See the [num_format section of the docs](https://metacpan.org/pod/distribution/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm#set_num_format()).

[daxue1]

Thanks for your kind reply. To make it clear, first I briefly introduce the whole story. I need to read some text files and combine them into an Excel file. There are some percentages, not decimals, in the text files. To follow your way, it looks I have to convert them into decimals first, like the code. use Excel::Writer::XLSX; my $workbook=Excel::Writer::XLSX->new('test.xlsx'); my $worksheet = $workbook->add_worksheet('test'); my $percentage=$workbook->add_format(num_format =>'0.000%'); $worksheet->write(0,0,'5.535%',$percentage); $worksheet->write(0,0,0.05535,$percentage); This will produce the results in the figure above. The 1st line uses what I actually have from the text files, i.e. percentages, which was shown as a string, not a proper percentage. Conversion from percentages to decimals is not direct and does not look like a proper way (?). Is there any better way? Thanks.