I have somehow manage to read all the workbook required for this application
using the below piece of code.This may not be the right way to do but this
works for all of our excel files.
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.getRichStringCellValue().getString();
System.out.print(retVal + " ");
break;
case Cell.CELL_TYPE_NUMERIC:
retVal = isNumberOrDate(cell);
System.out.print(retVal + " ");
break;
case Cell.CELL_TYPE_FORMULA:
retVal = processFormula(cell);
System.out.print(retVal + " ");
break;
default:
retVal = "";
}
return retVal;
}
private String isNumberOrDate(Cell cell) {
String retVal;
String formatStr = cell.getCellStyle().getDataFormatString();
short formatNbr = cell.getCellStyle().getDataFormat();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
retVal = dateFormat.format(cell.getDateCellValue());
} else {
retVal =
formatter.formatRawCellContents(cell.getNumericCellValue(), formatNbr,
formatStr);
}
return retVal;
}
private String processFormula(Cell cell) {
String retVal = "";
//if cell type is text,then its content may have string or number.
//Assume the content is of type number and try to get the value,if
not number then it has to be string type.
if (cell.getCellStyle().getDataFormatString().equals("@")) {
try {
retVal = isNumberOrDate(cell);
} catch (IllegalStateException nfe) {
retVal = cell.getRichStringCellValue().getString();
}
} else {
retVal = isNumberOrDate(cell);
}
return retVal;
}
MSB wrote:
>
> No joy at all I am afraid; on numerours fromts.
>
> Firstly, I did as David suggested and downloaded a more recent version of
> the api - one of the 3.7 beta2 nightly builds - and can report that this
> did not solve any of the problems. The basic FormulaEvaluator still did
> not work with formulae that span multpile workbooks and the
> ForkedEvaluator was limited just to using HSSFWorkbooks and, therefore,
> the older binary file format. I even dug into the code this time and saw
> that Josh has placed into the code the basics for XSSFWorkbook support but
> also indicated that to complete this willrequire some work restructuring
> the api.
>
> My other idea of capturing the formula myself, parsing it to open the
> additional workbook, etc will not work well either. If we had been using
> the older binary file format, then all would be well as the name of the
> workbook does appear in the formula. Sadly, the same cannot be said when
> it comes to the OOXML file format as it refers to the workbook using a
> number which refers to an entry in another file of xml markup. I am sure
> that by naviagting through the various relationships it would then be
> possible to get at this information but this is not a task I am confident
> in. Having said that, I will continue to dig around but cannot promise to
> get any further with this problem as it is really well beyond my
> capabilities now.
>
> 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-tp28894102p28974915.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
