Yes you are right.
MSB wrote: > > Can I just confirm that you are referring to the values in column E > please? These are the only formulae that I can find at present though I do > have to admit I have not looked through the sheets contents throughly yet. > > Yours > > Mark B > > > Siva_Masilamani wrote: >> >> Well >> >> I did follow the step you mentioned and it seems the cell is of string. >> >> Also when i try to get the cell style and read the format it shows the >> number as 49 and the format string as @ for all other cells prior to this >> and it still throws the same error for this cell but display the cell >> format as 49. >> >> So i guess the api consides once the cell type is formula it uses parse >> double to get the value where it fails as the data contains other >> characters too. >> >> For your reference i have attached the excel file and the style file. >> >> Jankiraman, Radhakrishnan wrote: >>> >>> Let me first apologize for not reading your complete email. Something >>> with my outlook that gave me the impression that there isn't much >>> content below. There seems to be something really wrong in this case. >>> From the stack trace, it looks like the cell type is reported to be >>> numeric by the getCellType() call, but the date format utility breaks >>> while trying to extract the double value for the cell which shouldn't >>> happen. This seems like a bug to me. >>> >>> Try opening the .xlsx file with an archive utility like WinZip. Unzip >>> the contents. The sheet content is stored under 'xl\worksheets' folder >>> with sheets being numbered. Locate your sheet and inspect its contents. >>> The styles are stored in 'styles.xml' in the 'xl' folder. >>> >>> You should try taking a look at what the XML looks like for this cell >>> content. Check the attributes 't' and 's' for that cell and also what >>> the content for the cell looks like. If the cell is indeed numeric then >>> the value of 't' must be 'n' and you can also see the value of 's'. It >>> must be a number that points to the style index in the workbook styles >>> section. The styles are kept in a separate xml >>> >>> But as MSB has suggested in another reply, you should get hold of the >>> cell style. From the cell style you'll get the format index. From the >>> format index you can get the format string. And that format string >>> cannot be directly used as a java format. The format conventions are not >>> exactly similar between java and excel. Have you tried reading the cell >>> value "as a string" ? Unless you need the typed cell value, you might be >>> better off just extracting the formatted string as is. >>> >>> -Rk >>> >>> __________________________ >>> Radhakrishnan Jankiraman >>> (o) +91.20-30239400 extn 520 (c) +91.9822006056 >>> >>> >>> -----Original Message----- >>> From: Siva_Masilamani [mailto:saachins...@yahoo.co.in] >>> Sent: Wednesday, June 16, 2010 8:29 AM >>> To: user@poi.apache.org >>> Subject: RE: Read Custom format cell value >>> >>> >>> Thanks again. >>> >>> But i was reading the api and came to know that we can define our own >>> format >>> using DataFormatter class like the pre defined format like ssn,us phone >>> number,zip code etc. >>> >>> But i just don't know how to do that. >>> >>> Just incase will that help me...if so could you please provide some help >>> on >>> that. >>> >>> >>> Jankiraman, Radhakrishnan wrote: >>>> >>>> Ok. I should have clarified that my suggestion was to use the 'ss' user >>>> model APIs introduced in 3.6. These are set of interfaces that provide >>>> a >>>> way to write code that will work for both xlsx and xls formats ( XSSF >>>> and >>>> HSSF in the POI world ). So that leads to another question which >>>> version >>>> of POI are you using ? >>>> >>>> If you don't have control over the format, then no luck. >>>> >>>> -Rk >>>> >>>> >>>> -----Original Message----- >>>> From: Siva_Masilamani [mailto:saachins...@yahoo.co.in] >>>> Sent: Wednesday, June 16, 2010 4:57 AM >>>> To: user@poi.apache.org >>>> Subject: RE: Read Custom format cell value >>>> >>>> >>>> Hi >>>> >>>> Thanks for your reply. >>>> >>>> The file is of type xlsx. >>>> We are receiving this file from our client and we are in no control of >>>> alter >>>> the file except reading the data and processing it and storing it in >>>> the >>>> database. >>>> >>>> Also another problem is the same file contains other cells with numeric >>>> value and i do not want to alter those values and want to read them as >>>> it >>>> is. >>>> >>>> There are only two fields that are in this format that i need to read. >>>> >>>> Im pretty new to this api and learning it at my best and are out of >>>> ideas >>>> right now. >>>> >>>> i am not sure where should i write the code you have given. >>>> >>>> Please help. >>>> >>>> >>>> >>>> >>>> Jankiraman, Radhakrishnan wrote: >>>>> >>>>> Btw, you didn't mention the format of your spreadsheet ( i.e. xls or >>>>> xlsx >>>>> ). I believe the reference should be useful irrespective of that. >>>>> >>>>> -----Original Message----- >>>>> From: Jankiraman, Radhakrishnan >>>>> Sent: Wednesday, June 16, 2010 2:32 AM >>>>> To: 'POI Users List' >>>>> Subject: RE: Read Custom format cell value >>>>> >>>>> This is how you try to get a format >>>>> >>>>> CreationHelper creationHelper = workbook.getCreationHelper(); >>>>> >>>>> //If this format is one of the default formats, will return the format >>>>> index else will create a new format and return its index. >>>>> short formatIndex = >>>>> creationHelper.createDataFormat().getFormat(format); >>>>> CellStyle cellStyle = workbook.createCellStyle(); >>>>> cellStyle.setDataFormat(formatIndex); >>>>> Cell someCell.setCellStyle(cellStyle); >>>>> >>>>> >>>>> Not sure why the cell is of formula type. You should refer to the Open >>>>> XML >>>>> format reference to understand how to include text in a number format. >>>>> Refer to section 3.8.30 in the Spreadsheet Markup Reference [1] >>>>> >>>>> -Rk >>>>> >>>>> [1] >>>>> http://www.ecma-international.org/publications/standards/Ecma-376.htm >>>>> ( Download Part 4 which contains the complete OOXML reference ) >>>>> >>>>> >>>>> -----Original Message----- >>>>> From: Siva_Masilamani [mailto:saachins...@yahoo.co.in] >>>>> Sent: Tuesday, June 15, 2010 10:52 PM >>>>> To: user@poi.apache.org >>>>> Subject: Read Custom format cell value >>>>> >>>>> >>>>> Hi >>>>> >>>>> I am using apache poi api to read data from the excel file and having >>>>> problem with particular data format. >>>>> >>>>> One of the coumns in the excel file had formula type whose value will >>>>> be >>>>> in >>>>> the below format >>>>> >>>>> "2001-160101100-22110-0000-000" >>>>> >>>>> Note : the no of digits between each dash and the number of dash >>>>> itself >>>>> may >>>>> vary but the format will always be numbers followed by - and end with >>>>> numbers. >>>>> >>>>> The api successfully find the field type as formula and consider it as >>>>> numeric type but it throws NumberFormatException as it contains -. >>>>> >>>>> I am using DataFormat class to read all the numeric cell value as we >>>>> need >>>>> to >>>>> read the data as it is (no round up or truncate) in the cell value. >>>>> >>>>> I was going through the genral format available in the api but could >>>>> not >>>>> understand how to add custom format to the list of available formats. >>>>> I was trying to use DataFormat.addFormat(String,Format) from the api >>>>> but >>>>> did >>>>> not understand what to pass in the String argument and how to define >>>>> the >>>>> Format for this custom format. >>>>> >>>>> Your help is appreciated. >>>>> >>>>> >>>>> public String getCellValue(Cell cell) { >>>>> String retVal; >>>>> if (cell == null) { >>>>> return ""; >>>>> } >>>>> switch (cell.getCellType()) { >>>>> >>>>> case Cell.CELL_TYPE_BLANK: >>>>> retVal = ""; >>>>> break; >>>>> >>>>> case Cell.CELL_TYPE_BOOLEAN: >>>>> retVal = "" + cell.getBooleanCellValue(); >>>>> break; >>>>> >>>>> case Cell.CELL_TYPE_STRING: >>>>> retVal = cell.getStringCellValue(); >>>>> System.out.print(retVal + " "); >>>>> break; >>>>> >>>>> case Cell.CELL_TYPE_NUMERIC: >>>>> retVal = isNumberOrDate(cell); >>>>> System.out.print(retVal + " "); >>>>> break; >>>>> >>>>> case Cell.CELL_TYPE_FORMULA: >>>>> retVal = isNumberOrDate(cell); >>>>> System.out.print(retVal + " "); >>>>> break; >>>>> >>>>> default: >>>>> retVal = ""; >>>>> } >>>>> return retVal; >>>>> } >>>>> >>>>> private String isNumberOrDate(Cell cell) { >>>>> String retVal; >>>>> if (HSSFDateUtil.isCellDateFormatted(cell)) { >>>>> DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); >>>>> retVal = formatter.format(cell.getDateCellValue()); >>>>> >>>>> } else { >>>>> DataFormatter df = new DataFormatter(); >>>>> //This is where i am trying to add the custom format to >>>>> the >>>>> list >>>>> to already available formats. >>>>> //DecimalFormat def=new DecimalFormat("#-#"); >>>>> //df.addFormat("", def); >>>>> retVal = df.formatCellValue(cell); >>>>> } >>>>> return retVal; >>>>> } >>>>> public String getCellValue(Cell cell) { >>>>> String retVal; >>>>> if (cell == null) { >>>>> return ""; >>>>> } >>>>> switch (cell.getCellType()) { >>>>> >>>>> case Cell.CELL_TYPE_BLANK: >>>>> retVal = ""; >>>>> break; >>>>> >>>>> case Cell.CELL_TYPE_BOOLEAN: >>>>> retVal = "" + cell.getBooleanCellValue(); >>>>> break; >>>>> >>>>> case Cell.CELL_TYPE_STRING: >>>>> retVal = cell.getStringCellValue(); >>>>> System.out.print(retVal + " "); >>>>> break; >>>>> >>>>> case Cell.CELL_TYPE_NUMERIC: >>>>> retVal = isNumberOrDate(cell); >>>>> System.out.print(retVal + " "); >>>>> break; >>>>> >>>>> case Cell.CELL_TYPE_FORMULA: >>>>> retVal = isNumberOrDate(cell); >>>>> System.out.print(retVal + " "); >>>>> break; >>>>> >>>>> default: >>>>> retVal = ""; >>>>> } >>>>> return retVal; >>>>> } >>>>> >>>>> private String isNumberOrDate(Cell cell) { >>>>> String retVal; >>>>> if (HSSFDateUtil.isCellDateFormatted(cell)) { >>>>> DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); >>>>> retVal = formatter.format(cell.getDateCellValue()); >>>>> >>>>> } else { >>>>> DataFormatter df = new DataFormatter(); >>>>> //This is where i am trying to add the custom format to >>>>> the >>>>> list >>>>> to already available formats. >>>>> //DecimalFormat def=new DecimalFormat("#-#"); >>>>> //df.addFormat("", def); >>>>> retVal = df.formatCellValue(cell); >>>>> } >>>>> return retVal; >>>>> } >>>>> >>>>> >>>>> java.lang.NumberFormatException: For input string: >>>>> "2001-160101100-22110-0000-000" >>>>> at >>>>> java.lang.NumberFormatException.forInputString(NumberFormatException.java:48) >>>>> >>>>> at >>>>> java.lang.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1207) >>>>> at java.lang.Double.parseDouble(Double.java:220) >>>>> at >>>>> org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:197) >>>>> >>>>> at >>>>> org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(DateUtil.java:278) >>>>> >>>>> at >>>>> com.excel.reader.ExcelDataReader.isNumberOrDate(ExcelDataReader.java:166) >>>>> at >>>>> com.excel.reader.ExcelDataReader.getCellValue(ExcelDataReader.java:154) >>>>> at com.excel.reader.ExcelDataReader.process(ExcelDataReader.java:118) >>>>> at com.excel.reader.ExcelDataReader.main(ExcelDataReader.java:249) >>>>> Exception in thread "main" ACBS 2001 7528080 Java Result: 1 >>>>> >>>>> >>>>> -- >>>>> View this message in context: >>>>> http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28894102.html >>>>> Sent from the POI - User mailing list archive at Nabble.com. >>>>> >>>>> >>>>> --------------------------------------------------------------------- >>>>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >>>>> For additional commands, e-mail: user-h...@poi.apache.org >>>>> >>>>> >>>>> --------------------------------------------------------------------- >>>>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >>>>> For additional commands, e-mail: user-h...@poi.apache.org >>>>> >>>>> >>>>> >>>> >>>> -- >>>> View this message in context: >>>> http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28897577.html >>>> Sent from the POI - User mailing list archive at Nabble.com. >>>> >>>> >>>> --------------------------------------------------------------------- >>>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >>>> For additional commands, e-mail: user-h...@poi.apache.org >>>> >>>> >>>> --------------------------------------------------------------------- >>>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >>>> For additional commands, e-mail: user-h...@poi.apache.org >>>> >>>> >>>> >>> >>> -- >>> View this message in context: >>> http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28898543.html >>> Sent from the POI - User mailing list archive at Nabble.com. >>> >>> >>> --------------------------------------------------------------------- >>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >>> For additional commands, e-mail: user-h...@poi.apache.org >>> >>> >>> --------------------------------------------------------------------- >>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >>> For additional commands, e-mail: user-h...@poi.apache.org >>> >>> >>> >> http://old.nabble.com/file/p28904075/styles.xml styles.xml >> http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml >> http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx >> SPD_REF_QRM_PLANNING_RC_20100531.xlsx >> > > -- View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28907017.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
