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
