Well, it's a bodge but it will work - and it may even do what you need;
HSSFWorkbook wb = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
FileInputStream fis = null;
double value = 0.0;
java.text.DecimalFormat formatter = null;
java.text.FieldPosition fPosition = null;
String formattingString = null;
String resultString = null;
StringBuffer buffer = null;
try {
// This is the String that will perform the formatting of the result
// It is best to look at the java.text.DecimalFormat class for an
// explanation of how the String works. It is not necessary to
// split this into two operations I do it here for clarity only.
formattingString = "#,##0.#####";
formatter = new java.text.DecimalFormat(formattingString);
fPosition = new java.text.FieldPosition(0);
buffer = new StringBuffer();
// Open a workbook
fis = new FileInputStream(new java.io.File("C:\\temp\\Book1.xls"));
wb = new HSSFWorkbook(fis);
// Get a sheet
sheet = wb.getSheetAt(0);
// Get a row
row = sheet.getRow((short)0);
// Get a cell
cell = row.getCell((short)0);
// Check the type of the cell and if numeric format for display
if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
// The StringBuffer is used to hold the result of formatting
// the number into a String. A new instance will be required
// each time.
buffer = new StringBuffer();
// Recover the numeric value from the cell
value = cell.getNumericCellValue();
// Format that number for display
formatter.format(value, buffer, fPosition);
// Not strictly necessary but I copy the result from the
// StringBuffer into a String - leave this out for performance
// reasons in production code
resultString = buffer.toString();
// Simply display the result to screen
System.out.println(resultString);
}
}
catch(Exception ex) {
System.out.println("Caught: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stack Trace Follows......");
ex.printStackTrace(System.out);
}
Of course, you will need to modify the code - take out the bit that does the
formatting and create a method that you can call each time you read a cell from
the sheet, take out the formatting string and place it into a properties file
so that you can play around with it and not need to re-compile the code, etc,
etc.
Hope this helps.
Anthony Andrews <[EMAIL PROTECTED]> wrote: Have just had a play around and
answered my own question - that approach is not supported by HSSF.
However, I think that the answer could be found in another 'pattern' I use in
JEXcel. It is possible to get at the formatting object that is used to
determine how a value appears in the cell. That formatter - that is an instance
of the java.text.NumberFormat class - can be applied to values read from cells
to determine how they appear. I reckon you will need to do something similar
for your application; get at the format of the cell, create a NumberFormat or
similar using the formatting String and then apply it to the value recovered
from the cell. If I get the chance today - that is if I can take another
'quiet' coffee break - I will have a look at this.
"Finnerty, Mary Ann (GE Healthcare)" wrote:
Not sure. I posted an email about this, and got one response that
seemed to indicate that I'd need to write code to apply a format. I'll
see what I can find out about HSSFDataFormat.
Thanks! I'd love to be able to read the xls directly.
Maffy
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 06, 2006 2:54 PM
To: POI Users List
Subject: RE: AW: Reading numeric values as strings from a cell
Is this something that cannot be handled using HSSFDataFormat?
~ amol
--- "Finnerty, Mary Ann (GE Healthcare)"
wrote:
> This is similar to what we're trying to do.
>
> When I read the spreadsheet, it has numbers like:
> 100, 10000, 0.967,
> etc.
> When I read them using POI, I get 100.0, 10000.0, 0.97, etc.
>
> We're doing a medical application, and we need the numbers eXACTLY as
> entered into the spreadsheet to create our JUNIT tests for
> verification and validation before we get to the next stage in the
> delivery of our products.
>
> Right now, I am just saving the xls as text, and parsing the text into
> XML. I was hoping to get rid of a step by using POI, but I only seem
> to be able to get the double value and not the value that was entered
> into the spreadsheet (which represents the output produced by the
> module being tested).
>
> Maffy
>
> -----Original Message-----
> From: Alex Mayorga Adame
> [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 06, 2006 1:21 PM
> To: [email protected]
> Subject: Re: AW: Reading numeric values as strings from a cell
>
>
> Maybe I didn't stated my question correctly. But by
> doing that on a Cell
> formatted as Date you'd probably end up with
> something like: 38966 even
> if in your Worksheet you see: 6-Sep-06
>
> I would like to store the latter, the data as seen
> in Excel on a String.
>
> Hope this clears up the confusion.
>
> Thanks in advance,
> Alex
>
>
> Daniel Noll wrote:
> >
> > Alex Mayorga Adame wrote:
> >> In short, there's a need to extract the contents
> of the cells as they
>
> >> show on the worksheet, extracted as a string no
> matter what the Cell
> >> number format is.
> >>
> >> Any help would be highly appreciated.
> >
> > We do this the trivial way, i.e.
> String.valueOf(doubleValue);
> >
> > Daniel
> >
> >
> > --
> > Daniel Noll
> > ...
> >
>
> --
> View this message in context:
>
http://www.nabble.com/Reading-numeric-values-as-strings-from-a-cell-tf11
> 26130.html#a6179056
> Sent from the POI - User forum at Nabble.com.
>
>
>
---------------------------------------------------------------------
> 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/
>
>
>
---------------------------------------------------------------------
> 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/
>
>
---------------------------------------------------------------------
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/
---------------------------------------------------------------------
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/
---------------------------------
Do you Yahoo!?
Get on board. You're invited to try the new Yahoo! Mail.
---------------------------------
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail.
