I think people are a little confused about this post because you have referred to DataFormat.addFormat() when I think that you really mean DataFormatter.addFormat(). The DataFormatter class is the one that you would usually use to convert the contents of a cell into a formatted String - which it looks like you are after doing - and the first step for you in this case seems to be getting at the formatting String that the user has applied to the cell.
The way to do this is firstly to recover a reference to the CellStyle object that has been applied to the cell and then to call the getDataFormatString() on that object. This should return a String that encapsulates the format the user has applied to the cell. Now, you have the first parameter to pass to the addFormat(String, format) method; like you though, I do not yet understand how to specify an instance of the java.text.Format class to handle the actual formatting of the value http://java.sun.com/j2se/1.4.2/docs/api/java/text/Format.html. I will take the time later today to have a look at that and post the results here, work permitting. Yours Mark B Siva_Masilamani wrote: > > 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-tp28894102p28899381.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
