Looks like I would still have to know in advance what the format of the number in the cell is. I don't. Sometimes it's a multi-position decimal number; sometimes it's a whole number. I just want Whatever is there without having it adjusted in anyway.
When I save the xls as a txt file, the "number" is just fine, which is why we've been handling it that way. The problem is that it's difficult to automate the series of tasks on an ever-widening number of modules when there's a manual step in there to save the xls as a txt file. I'll play around with it, though, and see if I can come up with a general purpose format. Thanks for trying! Maffy -----Original Message----- From: Anthony Andrews [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 12, 2006 10:51 AM To: POI Users List Subject: RE: AW: Reading numeric values as strings from a cell 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. --------------------------------------------------------------------- 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/
