Thanks for this, I will hve a better look through a little later today as
this problem had me banging my head against the desk and I want to see a
workaround to it.
Yours
Mark B
Siva_Masilamani wrote:
>
> 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-tp28894102p28979465.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
