Andreas Hartmann wrote:
Hello!
I have to read datas from cells in excel sheets. The goal is, to get the
data as they are formated by the user, who wrote the sheet.
Examples:
A cell is formated as text. The user wrote in this cell the number 13.
Excel shows this number as the user wrote it (as 13), but POI recognizes
this cell as a number cell (which is wrong), though the user defined it as
text. If I'm Reading the cell with POI as number, the value I get is 13.0,
which is wrong again (must be 13).
Actually, I think you might be mis-understanding Excel.
To actually *define* a cell as text, you need to type a single quote
before you enter the number. HSSF correctly identifies this type of
cell as a string typed cell (I just checked, FWIW.)
What you've probably done, is created a number cell, and merely
*formatted* it as text. This cell is still stored as a number, and
therefore HSSF will always see it as a number. As you can see,
formatting as text and defining as text are two different things.
Another example:
Somewhere in the sheet, there is a cell, which does a calculation. The
value of the calculation may be 15. Another cell in excel references this
value and shows 15 too, as expected. This cell is formated as number in
excel. When I'm reading the cell with the referenced value with POI, I'm
getting 15.0, which is wrong again.
The problem as it seems is that you're just using Double.toString() or
String.valueOf() on a numeric value, and expecting it to be the right
format automatically, which is never going to happen.
In short: how can I read with POI a cell just like they is shown in excel?
What you probably want to do is find the data format of the cell. You
can get it by calling getCellStyle().getDataFormat() on the cell, and
the mappings to formats can be obtained via HSSFDataFormat.
HSSFWorkbook workbook = createWorkbook();
HSSFCell cell = workbook.getSheetAt(0).getRow(0).getCell((short) 0);
HSSFDataFormat formats = workbook.createDataFormat();
String formatString =
formats.getFormat(cell.getCellStyle().getDataFormat());
The format string you'll now have is formatted in Excel's language...
where you go from here is your own choice.
In your case, the cell has been explicitly formatted as text, so the
format string should be "@", which is simple to handle.
To handle this completely, however, you will need to handle formats as
simple as "0.00" (which would work in Java's built-in number format) and
as complex as "_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)" (which
would not work in Java's built-in format.)
I do agree with the underlying sentiment of your post, though. It would
be very convenient if HSSF had a convenient getFormattedString() method
on HSSFCell which automatically outputted the cell in the same format
Excel would have displayed. I can imagine it being a fair bit of work
though.
I got this far myself and then decided that formatting the values was
ultimately more trouble than it was worth, and opted to display
everything in a standard format.
Daniel
--
Daniel Noll
Nuix Australia Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia
Phone: (02) 9280 0699
Fax: (02) 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
