The setCellType I think should be deprecated, and should not be used in newer versions of poi (2.0 onwards). the setCellValue methods automatically set the correct cell type. You cannot change the type of a cell without changing its value. Anyone has seen any valid use case for the setCellType method?? I think we should mark the method as deprecated.
In general, all this dicussion about formatted numbers is because excel stores the numeric cell values as a IEEE floating point number. This is irrespective of how the number is formatted on screen. POI tries to provide a close mapping of the excel file format (and not excel the application), and therefore returns the value as a double. Excel stores the format of the cell separately, and the applicaton applies the format to the number before displaying it on screen. That is exactly what you need to do replicate yourself, as discussed in this thread and elsewhere... That said, given that this is seems to be a common request, if someone can provide an useful implementation, we can stick in into HSSFCellUtil Regards - Avik On Wednesday 13 September 2006 23:09, Finnerty, Mary Ann (GE Healthcare) wrote: > I was surprised about the NFE, too. We already know it's a number, but > I tried it because I also knew that we declared that column in the xls > file as text format, so I thought I could easily just set the cell type, > but I guess excel is just being "helpful". :) > > I've been pulled off to work on something else, but will get back to > this either tomorrow, or on my own this weekend. Thanks for helping so > much!! > > Maffy > > > > -----Original Message----- > From: Anthony Andrews [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 12, 2006 11:43 PM > To: POI Users List > Subject: RE: AW: Reading numeric values as strings from a cell > > Not quite, with the hack, you should get exactly what was entered into > the cell in the first instance. I may have missed something when reading > the API documentation but I could not find anyway to get at the > formatting that was applied to the cell. So, when the contents of the > cell are read into a primitive double value Java makes certain > assumptions - adding decimal points and trailing zeros etc. The > DecimalFormat object goes some way to reverting the number back to the > value the user entered into the sheets cells. I tested the code very > quickly with values like '100', '0.987' and some ridiculous values just > to see what happened and it did reflect back what I entered into the > cells. > > Have you experiemented with the formatting String? The hashs and zeros > have special meanings and you may be able to come up with something that > will work for you. > > Just as an aside, I did try converting the numeric cells to String type > celss using the setCellType() method. Interesting, I saw a > NumberFormatException thrown when I tried to perform this operation! Not > what I expected or believe should happen. > > "Finnerty, Mary Ann (GE Healthcare)" <[EMAIL PROTECTED]> wrote: > 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 > 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/ > > > > > > --------------------------------- > Do you Yahoo!? > Get on board. You're invited to try the 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/ --------------------------------------------------------------------- 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/
