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
