Problem with whitespace in num_format using Spreadsheet::WriteExcel

Stormr has asked for the wisdom of the Perl Monks concerning the following question:

I have a problem using WriteExcel. I add a format for currency and write the data using it correctly (?)

my %format_kr = ( num_format => "# ##0 kr" );
...
my $format_kr = $excel->add_format(%format_kr);
...
$sheet->write($i,2,$data[$i],$format_kr);
[download]

but for some reason when I open the excel document the formatted cells end up like this

12345 678 kr

And when I check the number format set in Excel it is

#\ ##0 kr

There's only a preceding backslash in front of the first blank between the #'s, not in front of the blank before 'kr'.

I'm confused... and not even sure this is WriteExcel's fault.

Edit: Trying to find some way to work around here, I found the num_format_enc property of a format. The result was the same though.

use Unicode::String;
Unicode::String->stringify_as('utf16');
my $format_latin_str = "# ##0 kr";
my $format_utf_str = Unicode::String::latin1( $format_latin_str );
my %format_kr = ( 'num_format' => $format_utf_str, 'num_format_enc' =>
+ 1 );
[download]

/ Stormr

Comment on Problem with whitespace in num_format using Spreadsheet::WriteExcel Select or Download Code

Replies are listed 'Best First'.

Re: Problem with whitespace in num_format using Spreadsheet::WriteExcel
by jmcnamara (Monsignor) on Oct 06, 2008 at 21:22 UTC

Could you:

Give an example of a number that you are trying to format,
Give an example of how the number should look when it is formatted,
Show what the format in Excel is,
Show what the last format in the Custom part of the cell Number format dialog is,
State which Kr currency it is,
State what the decimal separator is,
State what the thousands separtor is.

After that I should be able to figure out what is required although item 4 will probably give you the best clue how to fix this.

Thanks,

--
John.

[reply]


Pathologically Eclectic Rubbish Lister
	PerlMonks