You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by John Francis <jf...@his.co.uk> on 2011/02/08 12:17:44 UTC
Reading Cells formated with General as text data
I am using the SS user model org.apache.poi.ss.usermodel.* to read an
XLS file.
One column is formatted as "General" in Excel 2007 and contains data
which is a series of user defined codes which can be purely alphabetic
'mycode' or numeric '123'.
The problem comes with the numeric values. Here POI reads the values and
sets the cell type to be CELL_TYPE_NUMERIC. This returns the value as a
double. The trouble is the code '366.0' is not the same code as '366' so
I wish to get the value 'as entered'. I appreciate that Excel may have
decided to store the value internally as a double, and there is no way
to get the value back 'as it appears'. Is this the case, or does POI
have some access to a lower level value?
If I set the CellType before reading the Cell contents I can achieve the
desired aim.
private String getGeneralCellValue(Cell cell)
{
if(cell != null)
{
cell.setCellType(Cell.CELL_TYPE_STRING);
String generalValue = cell.getStringCellValue();
...
Having read throw a previous discussion "Reading numeric values as
strings from a cell" it would seem that this is deprecated. Is this so?
Is there any way to achieve this goal without forcing users to format
the column as text ("@")? If I use a formatter can I distinguish between
a user entering "366" and "366.0"?
Thanks.
BTW Thanks generaly for the POI it certainly makes life a lot easier.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Reading Cells formated with General as text data
Posted by Nick Burch <ni...@alfresco.com>.
On Tue, 8 Feb 2011, John Francis wrote:
> The problem comes with the numeric values. Here POI reads the values and
> sets the cell type to be CELL_TYPE_NUMERIC. This returns the value as a
> double. The trouble is the code '366.0' is not the same code as '366' so
> I wish to get the value 'as entered'. I appreciate that Excel may have
> decided to store the value internally as a double, and there is no way
> to get the value back 'as it appears'. Is this the case, or does POI
> have some access to a lower level value?
Generally if you type a number into a cell in excel, excel will store it
as a double.
The DataFormatter class will probably do what you want though, as it tries
to replicate the formatting rules in excel to be able to format numbers as
strings in the same way excel does
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Problem with OpenOffice General/GENERAL format
Posted by John Francis <jf...@his.co.uk>.
I have created bug 50756 in ASF bugzilla with the offending file....
----------------------------------------------------------------------------
--------------
This message is private and confidential. If you have received this message
in error, please notify postmaster@his.co.uk and remove it from your system.
Please carry out your own virus check before opening attachments.
HISL Limited is a limited company registered in England and Wales.
Registered Number: 3202995. VAT number: 729-6256-05.
Registered Office: Chestnut Farm, Jill Lane, Sambourne, Redditch B96 6ES
----------------------------------------------------------------------------
--------------
-----Original Message-----
From: Nick Burch [mailto:nick.burch@alfresco.com]
Sent: 10 February 2011 18:24
To: POI Users List
Subject: Re: Problem with OpenOffice General/GENERAL format
On Thu, 10 Feb 2011, John Francis wrote:
> When I read numbers with org.apache.poi.ss.usermodel.DataFormatter (from
> poi-src-3.7-20101029.zip) which are shown as formatted with Format code
> General I get them returned in decimal format, even when they are shown
> as Integers. i.e. the old 12 being passed back as 12.0. I tracked the
> problem down to what is returned as the
> cell.getCellStyle().getDataFormatString() in the private Format
> getFormat(Cell cell) of the DataFormatter class. This returns "GENERAL"
> not "General" for some reason (OpenOffice seems to have changed it)
Any chance you could create a new bug in bugzilla, and upload one of these
problematic files? We'll want to use a file like that in any future bugfix
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem with OpenOffice General/GENERAL format
Posted by Nick Burch <ni...@alfresco.com>.
On Thu, 10 Feb 2011, John Francis wrote:
> When I read numbers with org.apache.poi.ss.usermodel.DataFormatter (from
> poi-src-3.7-20101029.zip) which are shown as formatted with Format code
> General I get them returned in decimal format, even when they are shown
> as Integers. i.e. the old 12 being passed back as 12.0. I tracked the
> problem down to what is returned as the
> cell.getCellStyle().getDataFormatString() in the private Format
> getFormat(Cell cell) of the DataFormatter class. This returns "GENERAL"
> not "General" for some reason (OpenOffice seems to have changed it)
Any chance you could create a new bug in bugzilla, and upload one of these
problematic files? We'll want to use a file like that in any future bugfix
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Problem with OpenOffice General/GENERAL format
Posted by John Francis <jf...@his.co.uk>.
I have a spreadsheet created and edited with a mix or Excel 2007 and
OpenOffice 3.2.1 build 9502
When I read numbers with org.apache.poi.ss.usermodel.DataFormatter (from
poi-src-3.7-20101029.zip) which are shown as formatted with Format code
General I get them returned in decimal format, even when they are shown
as Integers. i.e. the old 12 being passed back as 12.0. I tracked the
problem down to what is returned as the
cell.getCellStyle().getDataFormatString() in the private Format
getFormat(Cell cell) of the DataFormatter class. This returns "GENERAL"
not "General" for some reason (OpenOffice seems to have changed it) so
the part of the code in private Format getFormat(double cellValue, int
formatIndex, String formatStr) which handles the General style nicely;
if ("General".equals(formatStr) || "@".equals(formatStr)) {
if (DataFormatter.isWholeNumber(cellValue)) {
return generalWholeNumFormat;
}
return generalDecimalNumFormat;
}
is never called.
I posted this in the hope it might help others.
If anyone knows about this problem and is planning a POI fix for it I
would also be interested but I can work round for now.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org