Spreadsheet shows scientific notation occasionally with some cells,but it is wrong

[mike yue]

I am not sure if this is related to the perl script(uses WriteExcel
module), or related to my MS office excel settings.

e.g. Here are six cells showing in a spreadsheet:

10b1e4 146914
10b818 14db80
8.054E+33 808c940

The "8.054E+33" is actually from a string "8054e30" representing a
memory address, which is definitely not 8.054E+33.
But all other cells are show correct - that is weird.

My perl script uses WriteExcel, and the cells are with format which
only set_align('right').

Anyone got ideas?

Thank you guys for your attention.

 

[Peter J. Holzer]

I am not sure if this is related to the perl script(uses WriteExcel
module), or related to my MS office excel settings.

e.g. Here are six cells showing in a spreadsheet:

10b1e4 146914
10b818 14db80
8.054E+33 808c940

The "8.054E+33" is actually from a string "8054e30" representing a
memory address, which is definitely not 8.054E+33.

% perl -le 'print 8054e30'
8.054e+33

But all other cells are show correct - that is weird.

My perl script uses WriteExcel, and the cells are with format which
only set_align('right').

Which method do you use to write the cell? "write" or "write_string"?

If you use write, it needs to guess whether the thing you want to write
is a string or a number (a perl scalar can be both), and since 8054e30
looks like a number it guesses that it is one. Use write_string if you
want to write a string. If you are already using write_string, it's
probably a bug.

hp

 

[mike yue]

I didn't use either write or write_string, I used write_col() instead.
$worksheet->write_col( 14, 1, \@start_addrs, $RIGHT_ALIGN );
So the data comes from an array.
Do I need to set the format RIGHT_ALIGN to a particular string or
something? how?

 

[Jim Gibson]

I didn't use either write or write_string, I used write_col() instead.
$worksheet->write_col( 14, 1, \@start_addrs, $RIGHT_ALIGN );
So the data comes from an array.
Do I need to set the format RIGHT_ALIGN to a particular string or
something? how?

$RIGHT_ALIGN should be a valid Format object reference as returned by
the add_format() method called on a workbook object:

my $RIGHT_ALIGN = $workbook->add_format();

The format should not change the interpretation of the object type as
determined by the write_col method.

Try using write_string() to store the data instead of write_col(). You
will have to store one cell at a time in a loop.

 

[mike yue]

$RIGHT_ALIGN should be a valid Format object reference as returned by
the add_format() method called on a workbook object:

  my $RIGHT_ALIGN = $workbook->add_format();

The format should not change the interpretation of the object type as
determined by the write_col method.

Try using write_string() to store the data instead of write_col(). You
will have to store one cell at a time in a loop.

I've done that but don't feel good with it - Still wonder if there is
a better solution.
Thanks Jim.

 

[mike yue]

Also Thanks to Sherm and Peter!