Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do Excel display Cell values and stores internally?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do Excel display Cell values and stores internally?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do Excel display Cell values and stores internally?
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" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do Excel display Cell values and stores internally?
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do Excel display Cell values and stores internally?
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do Excel display Cell values and stores internally?
you need to decide that. you already mentioned that the numbers are formatted to accounting with 2 decimals ...so use that. "Anand Nichkaode" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel template to compare grocery prices at two or more stores | Setting up and Configuration of Excel | |||
display negative values as a blank cell in Excel | Excel Discussion (Misc queries) | |||
display negative values as a blank cell in Excel | Excel Discussion (Misc queries) | |||
Shortcut key to display change the display from displaying cell values to cell formulae | Excel Programming | |||
Simple stores control using Excel | Excel Programming |