Long integers in Spreadsheet::WriteExcel

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

Hi monks
I am writing a program that sends an xls file as an attachment
I create this file using Spreadsheet::WriteExcel
(code below):

use Spreadsheet::WriteExcel;
use strict;
    # Create a new Excel workbook
    my $workbook = Spreadsheet::WriteExcel->new('perl.xls');

    # Add a worksheet
    my $worksheet = $workbook->add_worksheet();

    # Write a number and a formula using A1 notation
    $worksheet->write_number('A1', 1000000000002651);
[download]

Problem is that I use database IDs that look like this: 1000000000002651. The xls file shows them in this format: 1E+15. I tried playing with the format options but without any success. Obviously I want to show the full number without any shortcuts.
Any ideas?

Thanks, mrguy
Real programmers don't comment their code. It was hard to write, it should be hard to understand and even harder to modify.

Comment on Long integers in Spreadsheet::WriteExcel Download Code

Replies are listed 'Best First'.
Re: Long integers in Spreadsheet::WriteExcel by moritz (Cardinal) on Aug 09, 2011 at 09:02 UTC
If it's a database id, write it as a string, not a number. So use write_string instead of write_number. Perl 6 - second systems done right	[reply]
Re^2: Long integers in Spreadsheet::WriteExcel by mrguy123 (Hermit) on Aug 09, 2011 at 09:09 UTC
Problem is I get 1.00000000000265e+15 as output which is still not good for me.	[reply]
Re^3: Long integers in Spreadsheet::WriteExcel by Corion (Patriarch) on Aug 09, 2011 at 09:10 UTC
When needing to write looong identifiers that look like numbers into Excel spreadsheets, I usually prepend a space to them, so that Excel "knows" that they are strings.	[reply]
Re^4: Long integers in Spreadsheet::WriteExcel by mrguy123 (Hermit) on Aug 09, 2011 at 09:17 UTC
Re: Long integers in Spreadsheet::WriteExcel by jmcnamara (Monsignor) on Aug 09, 2011 at 13:26 UTC
Numbers in that range are at the limit of what can be stored in the IEEE 754 doubles used by Excel. You can verify this yourself by copying and pasting that ID number into Excel. It will appear as 1E+15 in the cell and as 1000000000002650 in the formula bar (note 50 not 51 at the end). So, you need to use the `write_string()` method to write the ID as a string to the Excel file. See the output from the following program: `use warnings; use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new( 'perl.xls' ); my $worksheet = $workbook->add_worksheet(); my $id_format = $workbook->add_format( num_format => '@' ); my $id = '1000000000002651'; $worksheet->set_column( 'A:B', 30 ); $worksheet->write_string( 'A1', $id ); $worksheet->write_string( 'B1', $id, $id_format ); __END__` [download] I've added a number format to the second example. It isn't strictly needed but it will prevent the string reverting to a number if it is edited. If your tried `write_string()` and it didn't work then perhaps the ID had already been converted to a number, and as a result lost precision, in another part of your program. -- John.	[reply] [d/l]