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-tp28894102p28906424.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
