| Subject: | Excel-Writer-XLSX not formatting text that looks like numbers |
| Date: | Wed, 27 Mar 2013 16:18:58 +0000 |
| To: | "bug-Excel-Writer-XLSX [...] rt.cpan.org" <bug-Excel-Writer-XLSX [...] rt.cpan.org> |
| From: | "Ferrell, Robert J" <Robert.Ferrell [...] bnymellon.com> |
Hello,
I have been experimenting with the perl module Excel-Writer-XLSX and I came across an issue. I need to write Cusips, which are 9 digit alphanumeric strings, into cells. Sometimes a Cusip is all numeric with a leading zero and the leading zero gets dropped when I write it in a cell. Sometimes a Cusip will be all numeric, except for an "E" in the middle, which will look like an exponent. In order to have these Cusips display correctly in Excel, I need to format the cell as text first, then write the value. However, I follow that procedure using the module, but it still displays them as numbers.
I am using perl (v5.16.2) under Windows 7.
The following code will demonstrate the issue:
use Excel::Writer::XLSX;
my $filename = "test.xlsx";
my $workbook = Excel::Writer::XLSX->new( $filename );
my $worksheet = $workbook->add_worksheet( 'Results' );
my $format_string = $workbook->add_format( num_format => '@');
$worksheet->set_column( 0, 0, undef, $format_string);
my @cusips = qw/ 123000E14 ABCDEFGHI 012345678 123456789 /;
my $row = 0;
foreach (@cusips) {
$worksheet->write($row++, 0, $_, $format_string); }
exit(0);
The expected output is:
123000E14
ABCDEFGHI
012345678
123456789
However I get this:
1.23E+19
ABCDEFGHI
12345678
123456789
Thank you.
The information contained in this e-mail, and any attachment, is confidential and is intended solely for the use of the intended recipient. Access, copying or re-use of the e-mail or any attachment, or any information contained therein, by any other person is not authorized. If you are not the intended recipient please return the e-mail to the sender and delete it from your computer. Although we attempt to sweep e-mail and attachments for viruses, we do not guarantee that either are virus-free and accept no liability for any damage sustained as a result of viruses.
Please refer to http://disclaimer.bnymellon.com/eu.htm for certain disclosures relating to European legal entities.