excel format problem

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

Hi Monks, I have an issue where I am writing a hex value to an Excel spreadsheet and it looks like when there's an "e" in the number the Excel is changing it to Scientific Notation. So this number:83834e82 gets changed in Excel to:8.3834E+82. As I understand it Excel should default to General format, even still I added some code:

 my $right  = $workbook->add_format(align => 'right');
   my $num_format = $workbook->add_format();
   $num_format->set_num_format('general');
[download]

And when I do the actual write:

$worksheet1->write($row_num, $col_num, $hex_data, $right, $num_format)
+;
<
[download]

Has anyone else encountered this?

Comment on excel format problem Select or Download Code

Replies are listed 'Best First'.
Re: excel format problem by choroba (Cardinal) on Sep 03, 2014 at 17:13 UTC
How should Excel know the value is a hex number? Use hex: `$worksheet1->write($row_num, $col_num, hex $hex_data, $right, $num_for +mat);` [download] لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ	[reply] [d/l]
Re: excel format problem by AppleFritter (Vicar) on Sep 03, 2014 at 17:14 UTC
Can't say I've ever worked with Excel, so this is just a random stab in the dark, but - have you tried formatting your number as `0x83834e82` or `83834e82h`? Maybe that'll help.	[reply]
Re^2: excel format problem by Laurent_R (Canon) on Sep 03, 2014 at 17:37 UTC
I don't think this will work, but, on the other hand, Excel has a HEXDEC conversion function which might work. But I really think that converting the hex number into decimal notation in Perl prior to loading it to Excel, as suggested by choroba will be much easier.	[reply]
Re^3: excel format problem by davies (Monsignor) on Sep 03, 2014 at 17:43 UTC
Excel's hex converter certainly used to be limited to numbers less then 512. I also believe that the OP is using Spreadsheet::WriteExcel or something from that stable rather than Win32::OLE, otherwise I would rush my BigInt xla out & suggest he used that, but I understand that it's a problem importing modules into Excel files using S:WE or its derivatives. Regards, John Davies	[reply]
Re^4: excel format problem by Laurent_R (Canon) on Sep 03, 2014 at 17:50 UTC
Re^4: excel format problem by mark4444az (Sexton) on Sep 03, 2014 at 21:05 UTC
Re^5: Excel format problem by Athanasius (Archbishop) on Sep 04, 2014 at 03:04 UTC
Re^4: excel format problem by mark4444az (Sexton) on Sep 03, 2014 at 20:26 UTC
Re: excel format problem by bulrush (Scribe) on Sep 04, 2014 at 19:23 UTC
Spreadsheet::WriteExcel write() function makes a guess as to what kind of data you are writing. For the hex data, do not use write(), and instead write it as a string with: write_string($row, $column, $string, $format). Perl 5.8.8 on Redhat Linux RHEL 5.5.56 (64-bit)	[reply]