How do Excel display Cell values and stores internally?

[Anand Nichkaode]

Hi,

I have a workbook, (Excel 97-2003) format, with values in three cells. All
of the three cells display the same value i.e. $74,523.38. And the same value
is displayed in the formula bar upon clicking the cells.

But when my java program tries to read the workbook and the cell values,
with JXL API, the value read for the cell A1 and A2 is 74532.374999999971 and
for A3 is 74532.375.

Now I press F2 in the cells and save the workbook without changing anything.
When java reads this workbook the values read for all the cells is 74532.375.

There seems to be something happening when the cell is edited. Also there
seems to be some mistery around the way the cell values are displayed and
they are stored internally by Excel.

Also I converted this file to Excel 2007 and checked the XML for the
worksheet and found out that Excel stored 74532.374999999971 as value for A1
and A2 cells and 74532.375 as value for A3 cell.

Does anybody has faced anything like this? Any help is appreciated.

-Thx
Anand

 

[Patrick Molloy]

no mystery. what is displayed depends on the formatting used. so a date is
saved as a long number , like 39902 and you can format it to show "DDD" or
"dd-mmm-yyyy"
numbers are the same. 123.456 can be formatted "#.0" and you'll see 123.5

as for editing, if you select all three cells hit F2 and then ctrl-Enter,
the same value will go into all three cells.

all your java app is doing is reading the cell content ...and if its a date,
you'll need to make the conversion.
Always ise CSV files is the recommended way - WYSIWYG - so dates look like
dates and not numbers

 

[Anand Nichkaode]

Thanks for the quick reply.

All my cells are formatted as "Accounting" with 2 decimal places.

The thing is we receive this Excel file as data from the Client and we
cannot do any updation on it except just reading it.

-Thx
Anand

 

[Patrick Molloy]

formatting is what you "see" in a cell not actually what is in the cell.
Java fetches the cell content. if the content is 123.456 , what you see is
123.45 but what java fetches is 123.456
the strange ._____999999971 is simply that numbers are saved internally as
binary and there's sometimes an issue when the application reads it back

so your java app needs to be adjusted to that its numbers are rounded.

 

[Anand Nichkaode]

If the java application have to have the adjustment, then what would be the
base of the adjustment.

I mean how do the java application decide what and how needs to be rounded
off?

Thanks for the response.
-Thx
Anand

 

[Patrick Molloy]

you need to decide that. you already mentioned that the numbers are
formatted to accounting with 2 decimals ...so use that.