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

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" <(E-Mail Removed)> wrote in
message news:BF727BD2-D2D0-40E8-A9FF-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in
> message news:BF727BD2-D2D0-40E8-A9FF-(E-Mail Removed)...
> > 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
>
>

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" <(E-Mail Removed)> wrote in
message news:0A0C0F76-46C0-4A05-837B-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message news:BF727BD2-D2D0-40E8-A9FF-(E-Mail Removed)...
>> > 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
>>
>>

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" <(E-Mail Removed)> wrote in
> message news:0A0C0F76-46C0-4A05-837B-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> >> message news:BF727BD2-D2D0-40E8-A9FF-(E-Mail Removed)...
> >> > 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
> >>
> >>
>

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

"Anand Nichkaode" <(E-Mail Removed)> wrote in
message news:CB7E42F8-21D2-44AD-8773-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message news:0A0C0F76-46C0-4A05-837B-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in
>> >> message news:BF727BD2-D2D0-40E8-A9FF-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>>