You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Siva_Masilamani <sa...@yahoo.co.in> on 2010/06/15 19:22:09 UTC
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Read Custom format cell value
Posted by MSB <ma...@tiscali.co.uk>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Read Custom format cell value
Posted by Siva_Masilamani <sa...@yahoo.co.in>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Read Custom format cell value
Posted by MSB <ma...@tiscali.co.uk>.
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-tp28894102p28950631.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Read Custom format cell value
Posted by MSB <ma...@tiscali.co.uk>.
Thanks David
It had been my plan to experiment with evaluating the cells 'in place' so to
speak, as a separate process prior to reading their contents, as I seem to
remember this offering more 'power' and the ability to evaluate formulae
that span more than one workbook.
Hopefully, I will have some time today to play about with a bit of code.
Yours
Mark B
David Fisher wrote:
>
> I think that there has been some improvement here since 3.6. It makes
> sense to try the nightly build or the prospective 3.7Beta1
>
> Regards,
> Dave
>
> On Jun 17, 2010, at 12:16 PM, MSB wrote:
>
>>
>> I think that this could be a bit of a problem and the give away is that
>> error
>> message - 'not implemented yet'; this is usually what you see if support
>> for
>> a formula has not yet been included into the api. Give me a while and I
>> will
>> have a dig around to see what I can find out about external references as
>> in
>> the back of my mind a bell is ringing that suggests they have to be
>> treated
>> differently. Wioth luck, this is correct and I will not have to post
>> again
>> saying that external references canot be resolved.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Siva_Masilamani wrote:
>>>
>>> Thanks Mark
>>>
>>> This works for most of the files.
>>>
>>> But however the same code is not working for other excel file whose
>>> formula span across the sheet.
>>>
>>> If you want i can send other excel file to your personal mail as its
>>> file
>>> size is little bit large and i can not upload it here.
>>>
>>> i am getting the below exception:
>>>
>>> java.lang.RuntimeException: not implemented yet
>>> at
>>> org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:112)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:608)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:456)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1112)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1072)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1059)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1410)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1510)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1494)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1451)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1431)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1552)
>>> at
>>> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174)
>>> at
>>> org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook$Name.getNameDefinition(XSSFEvaluationWorkbook.java:158)
>>> at
>>> org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:528)
>>> at
>>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:439)
>>> at
>>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
>>> at
>>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:604)
>>> at
>>> org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)
>>> at
>>> org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
>>> at
>>> org.apache.poi.hssf.record.formula.functions.Sumif.accumulate(Sumif.java:95)
>>> at
>>> org.apache.poi.hssf.record.formula.functions.Sumif.sumMatchingCells(Sumif.java:83)
>>> at
>>> org.apache.poi.hssf.record.formula.functions.Sumif.eval(Sumif.java:72)
>>> at
>>> org.apache.poi.hssf.record.formula.functions.Sumif.evaluate(Sumif.java:65)
>>> at
>>> org.apache.poi.hssf.record.formula.functions.Var2or3ArgFunction.evaluate(Var2or3ArgFunction.java:36)
>>> at
>>> org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
>>> at
>>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:437)
>>> at
>>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
>>> at
>>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:206)
>>> 201003 31/03/2010 AFS To HTM 632001050 Spreadsheet at
>>> org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:252)
>>> at
>>> org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:145)
>>> at
>>> org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:483)
>>> at
>>> com.excel.reader.ExcelDataReader.processFormula(ExcelDataReader.java:183)
>>> at
>>> com.excel.reader.ExcelDataReader.getCellValue(ExcelDataReader.java:156)
>>> at
>>> com.excel.reader.ExcelDataReader.process(ExcelDataReader.java:121)
>>> at
>>> com.excel.reader.ExcelDataReader.main(ExcelDataReader.java:265)
>>> Exception in thread "main" Java Result: 1
>>>
>>> Source code im using :
>>>
>>> private DataFormatter formatter = new DataFormatter();
>>> private DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
>>>
>>> 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 = processFormula(cell);
>>> System.out.print(retVal + " ");
>>> break;
>>>
>>> default:
>>> retVal = "";
>>> }
>>> return retVal;
>>> }
>>>
>>> private String isNumberOrDate(Cell cell) {
>>> String retVal;
>>> if (HSSFDateUtil.isCellDateFormatted(cell)) {
>>> retVal = dateFormat.format(cell.getDateCellValue());
>>>
>>> } else {
>>> retVal = formatter.formatCellValue(cell);
>>> }
>>> return retVal;
>>> }
>>>
>>> private String processFormula(Cell cell) {
>>> String retVal = "";
>>> FormulaEvaluator evaluator =
>>> myWorkbook.getCreationHelper().createFormulaEvaluator();
>>> if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
>>> retVal = "#VALUE!";
>>> } else {
>>> retVal = formatter.formatCellValue(cell, evaluator);
>>> if (retVal.matches("[0-9]+")) {
>>> if (HSSFDateUtil.isCellDateFormatted(cell)) {
>>> retVal = dateFormat.format(cell.getDateCellValue());
>>> } else {
>>> retVal = formatter.formatCellValue(cell);
>>> }
>>> }
>>> }
>>> return retVal;
>>> }
>>>
>>> Basically all i want to do is:
>>>
>>> if cell type is string or blank the read the value as it is.
>>>
>>> If cell type is numeric then find if it is date or number,if it is date
>>> then format it as mm/dd/yyyy else just display the number as it is (no
>>> truncation no round up etc).
>>>
>>> If cell type is formula then if the cell value contains only number then
>>> do the above line again else just display the value as it is.
>>>
>>>
>>> MSB wrote:
>>>>
>>>> Thanks for that, and further, I just want to confirm that all you need
>>>> to
>>>> do is extract the contents of the cells as formatted Strings. You do
>>>> not
>>>> need to know the cells type so that you could, for exaqmple, recover a
>>>> date value for later processing?
>>>>
>>>> If the answer to this question is also yes, then the answer is actually
>>>> quite a straightforward one, you need to use a FormulaEvaluator so that
>>>> the DataFormatter can actually work out what the cell ought to contain
>>>> in
>>>> the first place - by parsing the formula - and then return it to you as
>>>> a
>>>> String. I ran your file through some code I wrote a while ago for
>>>> converting Excel workbooks in CSV files and all seemed to go correctly.
>>>> So, this morning, I have copied the relevant piece of code from that
>>>> class and pasted it into the message below.
>>>>
>>>> String cellContents = null;
>>>>
>>>> // Open a workbook - this code tagets the SS classes to work with .xls
>>>> and .xlsx
>>>> Workbook workbook = WorkbookFactory.create(new FileInputStream(new
>>>> File("..........")))
>>>>
>>>> DataFormatter formatter = new DataFormatter();
>>>> // There are a few ways to create FormulaEvaluators. The one I use
>>>> // in the code is this one
>>>> FormulaEvaluator evaluator =
>>>> workbook.getCreationHelper().createFormulaEvaluator();
>>>>
>>>> // Now all you need to do is get sheets from the workbook and iterate
>>>> through the cells
>>>> // one at a time storing the refernce into a variable that I have
>>>> called
>>>> cell in
>>>> // the code below and will be of type Cell. Then this bit of code will
>>>> get you that
>>>> // formatted String.
>>>>
>>>>
>>>> if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
>>>> // Protect the DataFormatter from instances where the cell
>>>> // DOES contain a formula but where that formula actually
>>>> // shows #VALUE! in the cell. In such a case, a call to the
>>>> // getCahcedFormulaResultType() method will return
>>>> // Cell.CELL_TYPE_ERROR and a message can be synthesised for
>>>> // inclusion in the CSV file. It may well be worth indicating
>>>> // to the user that a problem has been detected in the worksheet.
>>>> if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
>>>> cellContents = "#VALUE!";
>>>> } else {
>>>> cellContents = this.formatter.formatCellValue(
>>>> cell, this.evaluator);
>>>> }
>>>> }
>>>> // Finally, in all other cases ( fields that are blank, boolean,
>>>> // contain numbers or Strings) rely upon the DataFormatter to return
>>>> // a String encapsulating the cells formatted value.
>>>> else {
>>>> cellContents = this.formatter.formatCellValue(cell);
>>>> }
>>>>
>>>> As you can see, it is far from complete but gives you the idea. It
>>>> should
>>>> be a simple matter of iterating through the sheets and, for each sheet,
>>>> of iterating through the cells on the sheet's rows. If you have any
>>>> further trouble, or if I have misconstrued what you do need to achieve,
>>>> just drop a message onto this list.
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> Siva_Masilamani wrote:
>>>>>
>>>>> Yes you are right.
>>>>>
>>>>>
>>>>>
>>>>> MSB wrote:
>>>>>>
>>>>>> Can I just confirm that you are referring to the values in column E
>>>>>> please? These are the only formulae that I can find at present though
>>>>>> I
>>>>>> do have to admit I have not looked through the sheets contents
>>>>>> throughly yet.
>>>>>>
>>>>>> Yours
>>>>>>
>>>>>> Mark B
>>>>>>
>>>>>>
>>>>>> Siva_Masilamani wrote:
>>>>>>>
>>>>>>> Well
>>>>>>>
>>>>>>> I did follow the step you mentioned and it seems the cell is of
>>>>>>> string.
>>>>>>>
>>>>>>> Also when i try to get the cell style and read the format it shows
>>>>>>> the
>>>>>>> number as 49 and the format string as @ for all other cells prior to
>>>>>>> this and it still throws the same error for this cell but display
>>>>>>> the
>>>>>>> cell format as 49.
>>>>>>>
>>>>>>> So i guess the api consides once the cell type is formula it uses
>>>>>>> parse double to get the value where it fails as the data contains
>>>>>>> other characters too.
>>>>>>>
>>>>>>> For your reference i have attached the excel file and the style
>>>>>>> file.
>>>>>>>
>>>>>>> Jankiraman, Radhakrishnan wrote:
>>>>>>>>
>>>>>>>> Let me first apologize for not reading your complete email.
>>>>>>>> Something
>>>>>>>> with my outlook that gave me the impression that there isn't much
>>>>>>>> content below. There seems to be something really wrong in this
>>>>>>>> case.
>>>>>>>> From the stack trace, it looks like the cell type is reported to be
>>>>>>>> numeric by the getCellType() call, but the date format utility
>>>>>>>> breaks
>>>>>>>> while trying to extract the double value for the cell which
>>>>>>>> shouldn't
>>>>>>>> happen. This seems like a bug to me.
>>>>>>>>
>>>>>>>> Try opening the .xlsx file with an archive utility like WinZip.
>>>>>>>> Unzip
>>>>>>>> the contents. The sheet content is stored under 'xl\worksheets'
>>>>>>>> folder with sheets being numbered. Locate your sheet and inspect
>>>>>>>> its
>>>>>>>> contents. The styles are stored in 'styles.xml' in the 'xl' folder.
>>>>>>>>
>>>>>>>> You should try taking a look at what the XML looks like for this
>>>>>>>> cell
>>>>>>>> content. Check the attributes 't' and 's' for that cell and also
>>>>>>>> what
>>>>>>>> the content for the cell looks like. If the cell is indeed numeric
>>>>>>>> then the value of 't' must be 'n' and you can also see the value of
>>>>>>>> 's'. It must be a number that points to the style index in the
>>>>>>>> workbook styles section. The styles are kept in a separate xml
>>>>>>>>
>>>>>>>> But as MSB has suggested in another reply, you should get hold of
>>>>>>>> the
>>>>>>>> cell style. From the cell style you'll get the format index. From
>>>>>>>> the
>>>>>>>> format index you can get the format string. And that format string
>>>>>>>> cannot be directly used as a java format. The format conventions
>>>>>>>> are
>>>>>>>> not exactly similar between java and excel. Have you tried reading
>>>>>>>> the cell value "as a string" ? Unless you need the typed cell
>>>>>>>> value,
>>>>>>>> you might be better off just extracting the formatted string as is.
>>>>>>>>
>>>>>>>> -Rk
>>>>>>>>
>>>>>>>> __________________________
>>>>>>>> Radhakrishnan Jankiraman
>>>>>>>> (o) +91.20-30239400 extn 520 (c) +91.9822006056
>>>>>>>>
>>>>>>>>
>>>>>>>> -----Original Message-----
>>>>>>>> From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
>>>>>>>> Sent: Wednesday, June 16, 2010 8:29 AM
>>>>>>>> To: user@poi.apache.org
>>>>>>>> Subject: RE: Read Custom format cell value
>>>>>>>>
>>>>>>>>
>>>>>>>> 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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>>>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> ---------------------------------------------------------------------
>>>>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> ---------------------------------------------------------------------
>>>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>
>>>>>>>>
>>>>>>>> ---------------------------------------------------------------------
>>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>> http://old.nabble.com/file/p28904075/styles.xml styles.xml
>>>>>>> http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml
>>>>>>> http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>>>>> SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28918580.html
>> Sent from the POI - User mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28922376.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Read Custom format cell value
Posted by David Fisher <df...@jmlafferty.com>.
I think that there has been some improvement here since 3.6. It makes sense to try the nightly build or the prospective 3.7Beta1
Regards,
Dave
On Jun 17, 2010, at 12:16 PM, MSB wrote:
>
> I think that this could be a bit of a problem and the give away is that error
> message - 'not implemented yet'; this is usually what you see if support for
> a formula has not yet been included into the api. Give me a while and I will
> have a dig around to see what I can find out about external references as in
> the back of my mind a bell is ringing that suggests they have to be treated
> differently. Wioth luck, this is correct and I will not have to post again
> saying that external references canot be resolved.
>
> Yours
>
> Mark B
>
>
> Siva_Masilamani wrote:
>>
>> Thanks Mark
>>
>> This works for most of the files.
>>
>> But however the same code is not working for other excel file whose
>> formula span across the sheet.
>>
>> If you want i can send other excel file to your personal mail as its file
>> size is little bit large and i can not upload it here.
>>
>> i am getting the below exception:
>>
>> java.lang.RuntimeException: not implemented yet
>> at
>> org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:112)
>> at
>> org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:608)
>> at
>> org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:456)
>> at
>> org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
>> at
>> org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1112)
>> at
>> org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1072)
>> at
>> org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1059)
>> at
>> org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1410)
>> at
>> org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1510)
>> at
>> org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1494)
>> at
>> org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1451)
>> at
>> org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1431)
>> at
>> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1552)
>> at
>> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174)
>> at
>> org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook$Name.getNameDefinition(XSSFEvaluationWorkbook.java:158)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:528)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:439)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:604)
>> at
>> org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)
>> at
>> org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
>> at
>> org.apache.poi.hssf.record.formula.functions.Sumif.accumulate(Sumif.java:95)
>> at
>> org.apache.poi.hssf.record.formula.functions.Sumif.sumMatchingCells(Sumif.java:83)
>> at
>> org.apache.poi.hssf.record.formula.functions.Sumif.eval(Sumif.java:72)
>> at
>> org.apache.poi.hssf.record.formula.functions.Sumif.evaluate(Sumif.java:65)
>> at
>> org.apache.poi.hssf.record.formula.functions.Var2or3ArgFunction.evaluate(Var2or3ArgFunction.java:36)
>> at
>> org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:437)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
>> at
>> org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:206)
>> 201003 31/03/2010 AFS To HTM 632001050 Spreadsheet at
>> org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:252)
>> at
>> org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:145)
>> at
>> org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:483)
>> at
>> com.excel.reader.ExcelDataReader.processFormula(ExcelDataReader.java:183)
>> at
>> com.excel.reader.ExcelDataReader.getCellValue(ExcelDataReader.java:156)
>> at
>> com.excel.reader.ExcelDataReader.process(ExcelDataReader.java:121)
>> at com.excel.reader.ExcelDataReader.main(ExcelDataReader.java:265)
>> Exception in thread "main" Java Result: 1
>>
>> Source code im using :
>>
>> private DataFormatter formatter = new DataFormatter();
>> private DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
>>
>> 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 = processFormula(cell);
>> System.out.print(retVal + " ");
>> break;
>>
>> default:
>> retVal = "";
>> }
>> return retVal;
>> }
>>
>> private String isNumberOrDate(Cell cell) {
>> String retVal;
>> if (HSSFDateUtil.isCellDateFormatted(cell)) {
>> retVal = dateFormat.format(cell.getDateCellValue());
>>
>> } else {
>> retVal = formatter.formatCellValue(cell);
>> }
>> return retVal;
>> }
>>
>> private String processFormula(Cell cell) {
>> String retVal = "";
>> FormulaEvaluator evaluator =
>> myWorkbook.getCreationHelper().createFormulaEvaluator();
>> if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
>> retVal = "#VALUE!";
>> } else {
>> retVal = formatter.formatCellValue(cell, evaluator);
>> if (retVal.matches("[0-9]+")) {
>> if (HSSFDateUtil.isCellDateFormatted(cell)) {
>> retVal = dateFormat.format(cell.getDateCellValue());
>> } else {
>> retVal = formatter.formatCellValue(cell);
>> }
>> }
>> }
>> return retVal;
>> }
>>
>> Basically all i want to do is:
>>
>> if cell type is string or blank the read the value as it is.
>>
>> If cell type is numeric then find if it is date or number,if it is date
>> then format it as mm/dd/yyyy else just display the number as it is (no
>> truncation no round up etc).
>>
>> If cell type is formula then if the cell value contains only number then
>> do the above line again else just display the value as it is.
>>
>>
>> MSB wrote:
>>>
>>> Thanks for that, and further, I just want to confirm that all you need to
>>> do is extract the contents of the cells as formatted Strings. You do not
>>> need to know the cells type so that you could, for exaqmple, recover a
>>> date value for later processing?
>>>
>>> If the answer to this question is also yes, then the answer is actually
>>> quite a straightforward one, you need to use a FormulaEvaluator so that
>>> the DataFormatter can actually work out what the cell ought to contain in
>>> the first place - by parsing the formula - and then return it to you as a
>>> String. I ran your file through some code I wrote a while ago for
>>> converting Excel workbooks in CSV files and all seemed to go correctly.
>>> So, this morning, I have copied the relevant piece of code from that
>>> class and pasted it into the message below.
>>>
>>> String cellContents = null;
>>>
>>> // Open a workbook - this code tagets the SS classes to work with .xls
>>> and .xlsx
>>> Workbook workbook = WorkbookFactory.create(new FileInputStream(new
>>> File("..........")))
>>>
>>> DataFormatter formatter = new DataFormatter();
>>> // There are a few ways to create FormulaEvaluators. The one I use
>>> // in the code is this one
>>> FormulaEvaluator evaluator =
>>> workbook.getCreationHelper().createFormulaEvaluator();
>>>
>>> // Now all you need to do is get sheets from the workbook and iterate
>>> through the cells
>>> // one at a time storing the refernce into a variable that I have called
>>> cell in
>>> // the code below and will be of type Cell. Then this bit of code will
>>> get you that
>>> // formatted String.
>>>
>>>
>>> if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
>>> // Protect the DataFormatter from instances where the cell
>>> // DOES contain a formula but where that formula actually
>>> // shows #VALUE! in the cell. In such a case, a call to the
>>> // getCahcedFormulaResultType() method will return
>>> // Cell.CELL_TYPE_ERROR and a message can be synthesised for
>>> // inclusion in the CSV file. It may well be worth indicating
>>> // to the user that a problem has been detected in the worksheet.
>>> if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
>>> cellContents = "#VALUE!";
>>> } else {
>>> cellContents = this.formatter.formatCellValue(
>>> cell, this.evaluator);
>>> }
>>> }
>>> // Finally, in all other cases ( fields that are blank, boolean,
>>> // contain numbers or Strings) rely upon the DataFormatter to return
>>> // a String encapsulating the cells formatted value.
>>> else {
>>> cellContents = this.formatter.formatCellValue(cell);
>>> }
>>>
>>> As you can see, it is far from complete but gives you the idea. It should
>>> be a simple matter of iterating through the sheets and, for each sheet,
>>> of iterating through the cells on the sheet's rows. If you have any
>>> further trouble, or if I have misconstrued what you do need to achieve,
>>> just drop a message onto this list.
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> Siva_Masilamani wrote:
>>>>
>>>> Yes you are right.
>>>>
>>>>
>>>>
>>>> MSB wrote:
>>>>>
>>>>> Can I just confirm that you are referring to the values in column E
>>>>> please? These are the only formulae that I can find at present though I
>>>>> do have to admit I have not looked through the sheets contents
>>>>> throughly yet.
>>>>>
>>>>> Yours
>>>>>
>>>>> Mark B
>>>>>
>>>>>
>>>>> Siva_Masilamani wrote:
>>>>>>
>>>>>> Well
>>>>>>
>>>>>> I did follow the step you mentioned and it seems the cell is of
>>>>>> string.
>>>>>>
>>>>>> Also when i try to get the cell style and read the format it shows the
>>>>>> number as 49 and the format string as @ for all other cells prior to
>>>>>> this and it still throws the same error for this cell but display the
>>>>>> cell format as 49.
>>>>>>
>>>>>> So i guess the api consides once the cell type is formula it uses
>>>>>> parse double to get the value where it fails as the data contains
>>>>>> other characters too.
>>>>>>
>>>>>> For your reference i have attached the excel file and the style file.
>>>>>>
>>>>>> Jankiraman, Radhakrishnan wrote:
>>>>>>>
>>>>>>> Let me first apologize for not reading your complete email. Something
>>>>>>> with my outlook that gave me the impression that there isn't much
>>>>>>> content below. There seems to be something really wrong in this case.
>>>>>>> From the stack trace, it looks like the cell type is reported to be
>>>>>>> numeric by the getCellType() call, but the date format utility breaks
>>>>>>> while trying to extract the double value for the cell which shouldn't
>>>>>>> happen. This seems like a bug to me.
>>>>>>>
>>>>>>> Try opening the .xlsx file with an archive utility like WinZip. Unzip
>>>>>>> the contents. The sheet content is stored under 'xl\worksheets'
>>>>>>> folder with sheets being numbered. Locate your sheet and inspect its
>>>>>>> contents. The styles are stored in 'styles.xml' in the 'xl' folder.
>>>>>>>
>>>>>>> You should try taking a look at what the XML looks like for this cell
>>>>>>> content. Check the attributes 't' and 's' for that cell and also what
>>>>>>> the content for the cell looks like. If the cell is indeed numeric
>>>>>>> then the value of 't' must be 'n' and you can also see the value of
>>>>>>> 's'. It must be a number that points to the style index in the
>>>>>>> workbook styles section. The styles are kept in a separate xml
>>>>>>>
>>>>>>> But as MSB has suggested in another reply, you should get hold of the
>>>>>>> cell style. From the cell style you'll get the format index. From the
>>>>>>> format index you can get the format string. And that format string
>>>>>>> cannot be directly used as a java format. The format conventions are
>>>>>>> not exactly similar between java and excel. Have you tried reading
>>>>>>> the cell value "as a string" ? Unless you need the typed cell value,
>>>>>>> you might be better off just extracting the formatted string as is.
>>>>>>>
>>>>>>> -Rk
>>>>>>>
>>>>>>> __________________________
>>>>>>> Radhakrishnan Jankiraman
>>>>>>> (o) +91.20-30239400 extn 520 (c) +91.9822006056
>>>>>>>
>>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
>>>>>>> Sent: Wednesday, June 16, 2010 8:29 AM
>>>>>>> To: user@poi.apache.org
>>>>>>> Subject: RE: Read Custom format cell value
>>>>>>>
>>>>>>>
>>>>>>> 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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> ---------------------------------------------------------------------
>>>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>
>>>>>>>>
>>>>>>>> ---------------------------------------------------------------------
>>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>> ---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>> http://old.nabble.com/file/p28904075/styles.xml styles.xml
>>>>>> http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml
>>>>>> http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>>>> SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> --
> View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28918580.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by MSB <ma...@tiscali.co.uk>.
I think that this could be a bit of a problem and the give away is that error
message - 'not implemented yet'; this is usually what you see if support for
a formula has not yet been included into the api. Give me a while and I will
have a dig around to see what I can find out about external references as in
the back of my mind a bell is ringing that suggests they have to be treated
differently. Wioth luck, this is correct and I will not have to post again
saying that external references canot be resolved.
Yours
Mark B
Siva_Masilamani wrote:
>
> Thanks Mark
>
> This works for most of the files.
>
> But however the same code is not working for other excel file whose
> formula span across the sheet.
>
> If you want i can send other excel file to your personal mail as its file
> size is little bit large and i can not upload it here.
>
> i am getting the below exception:
>
> java.lang.RuntimeException: not implemented yet
> at
> org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:112)
> at
> org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:608)
> at
> org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:456)
> at
> org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
> at
> org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1112)
> at
> org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1072)
> at
> org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1059)
> at
> org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1410)
> at
> org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1510)
> at
> org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1494)
> at
> org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1451)
> at
> org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1431)
> at
> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1552)
> at
> org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174)
> at
> org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook$Name.getNameDefinition(XSSFEvaluationWorkbook.java:158)
> at
> org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:528)
> at
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:439)
> at
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
> at
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:604)
> at
> org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)
> at
> org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
> at
> org.apache.poi.hssf.record.formula.functions.Sumif.accumulate(Sumif.java:95)
> at
> org.apache.poi.hssf.record.formula.functions.Sumif.sumMatchingCells(Sumif.java:83)
> at
> org.apache.poi.hssf.record.formula.functions.Sumif.eval(Sumif.java:72)
> at
> org.apache.poi.hssf.record.formula.functions.Sumif.evaluate(Sumif.java:65)
> at
> org.apache.poi.hssf.record.formula.functions.Var2or3ArgFunction.evaluate(Var2or3ArgFunction.java:36)
> at
> org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
> at
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:437)
> at
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
> at
> org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:206)
> 201003 31/03/2010 AFS To HTM 632001050 Spreadsheet at
> org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:252)
> at
> org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:145)
> at
> org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:483)
> at
> com.excel.reader.ExcelDataReader.processFormula(ExcelDataReader.java:183)
> at
> com.excel.reader.ExcelDataReader.getCellValue(ExcelDataReader.java:156)
> at
> com.excel.reader.ExcelDataReader.process(ExcelDataReader.java:121)
> at com.excel.reader.ExcelDataReader.main(ExcelDataReader.java:265)
> Exception in thread "main" Java Result: 1
>
> Source code im using :
>
> private DataFormatter formatter = new DataFormatter();
> private DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
>
> 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 = processFormula(cell);
> System.out.print(retVal + " ");
> break;
>
> default:
> retVal = "";
> }
> return retVal;
> }
>
> private String isNumberOrDate(Cell cell) {
> String retVal;
> if (HSSFDateUtil.isCellDateFormatted(cell)) {
> retVal = dateFormat.format(cell.getDateCellValue());
>
> } else {
> retVal = formatter.formatCellValue(cell);
> }
> return retVal;
> }
>
> private String processFormula(Cell cell) {
> String retVal = "";
> FormulaEvaluator evaluator =
> myWorkbook.getCreationHelper().createFormulaEvaluator();
> if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
> retVal = "#VALUE!";
> } else {
> retVal = formatter.formatCellValue(cell, evaluator);
> if (retVal.matches("[0-9]+")) {
> if (HSSFDateUtil.isCellDateFormatted(cell)) {
> retVal = dateFormat.format(cell.getDateCellValue());
> } else {
> retVal = formatter.formatCellValue(cell);
> }
> }
> }
> return retVal;
> }
>
> Basically all i want to do is:
>
> if cell type is string or blank the read the value as it is.
>
> If cell type is numeric then find if it is date or number,if it is date
> then format it as mm/dd/yyyy else just display the number as it is (no
> truncation no round up etc).
>
> If cell type is formula then if the cell value contains only number then
> do the above line again else just display the value as it is.
>
>
> MSB wrote:
>>
>> Thanks for that, and further, I just want to confirm that all you need to
>> do is extract the contents of the cells as formatted Strings. You do not
>> need to know the cells type so that you could, for exaqmple, recover a
>> date value for later processing?
>>
>> If the answer to this question is also yes, then the answer is actually
>> quite a straightforward one, you need to use a FormulaEvaluator so that
>> the DataFormatter can actually work out what the cell ought to contain in
>> the first place - by parsing the formula - and then return it to you as a
>> String. I ran your file through some code I wrote a while ago for
>> converting Excel workbooks in CSV files and all seemed to go correctly.
>> So, this morning, I have copied the relevant piece of code from that
>> class and pasted it into the message below.
>>
>> String cellContents = null;
>>
>> // Open a workbook - this code tagets the SS classes to work with .xls
>> and .xlsx
>> Workbook workbook = WorkbookFactory.create(new FileInputStream(new
>> File("..........")))
>>
>> DataFormatter formatter = new DataFormatter();
>> // There are a few ways to create FormulaEvaluators. The one I use
>> // in the code is this one
>> FormulaEvaluator evaluator =
>> workbook.getCreationHelper().createFormulaEvaluator();
>>
>> // Now all you need to do is get sheets from the workbook and iterate
>> through the cells
>> // one at a time storing the refernce into a variable that I have called
>> cell in
>> // the code below and will be of type Cell. Then this bit of code will
>> get you that
>> // formatted String.
>>
>>
>> if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
>> // Protect the DataFormatter from instances where the cell
>> // DOES contain a formula but where that formula actually
>> // shows #VALUE! in the cell. In such a case, a call to the
>> // getCahcedFormulaResultType() method will return
>> // Cell.CELL_TYPE_ERROR and a message can be synthesised for
>> // inclusion in the CSV file. It may well be worth indicating
>> // to the user that a problem has been detected in the worksheet.
>> if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
>> cellContents = "#VALUE!";
>> } else {
>> cellContents = this.formatter.formatCellValue(
>> cell, this.evaluator);
>> }
>> }
>> // Finally, in all other cases ( fields that are blank, boolean,
>> // contain numbers or Strings) rely upon the DataFormatter to return
>> // a String encapsulating the cells formatted value.
>> else {
>> cellContents = this.formatter.formatCellValue(cell);
>> }
>>
>> As you can see, it is far from complete but gives you the idea. It should
>> be a simple matter of iterating through the sheets and, for each sheet,
>> of iterating through the cells on the sheet's rows. If you have any
>> further trouble, or if I have misconstrued what you do need to achieve,
>> just drop a message onto this list.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Siva_Masilamani wrote:
>>>
>>> Yes you are right.
>>>
>>>
>>>
>>> MSB wrote:
>>>>
>>>> Can I just confirm that you are referring to the values in column E
>>>> please? These are the only formulae that I can find at present though I
>>>> do have to admit I have not looked through the sheets contents
>>>> throughly yet.
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>>
>>>> Siva_Masilamani wrote:
>>>>>
>>>>> Well
>>>>>
>>>>> I did follow the step you mentioned and it seems the cell is of
>>>>> string.
>>>>>
>>>>> Also when i try to get the cell style and read the format it shows the
>>>>> number as 49 and the format string as @ for all other cells prior to
>>>>> this and it still throws the same error for this cell but display the
>>>>> cell format as 49.
>>>>>
>>>>> So i guess the api consides once the cell type is formula it uses
>>>>> parse double to get the value where it fails as the data contains
>>>>> other characters too.
>>>>>
>>>>> For your reference i have attached the excel file and the style file.
>>>>>
>>>>> Jankiraman, Radhakrishnan wrote:
>>>>>>
>>>>>> Let me first apologize for not reading your complete email. Something
>>>>>> with my outlook that gave me the impression that there isn't much
>>>>>> content below. There seems to be something really wrong in this case.
>>>>>> From the stack trace, it looks like the cell type is reported to be
>>>>>> numeric by the getCellType() call, but the date format utility breaks
>>>>>> while trying to extract the double value for the cell which shouldn't
>>>>>> happen. This seems like a bug to me.
>>>>>>
>>>>>> Try opening the .xlsx file with an archive utility like WinZip. Unzip
>>>>>> the contents. The sheet content is stored under 'xl\worksheets'
>>>>>> folder with sheets being numbered. Locate your sheet and inspect its
>>>>>> contents. The styles are stored in 'styles.xml' in the 'xl' folder.
>>>>>>
>>>>>> You should try taking a look at what the XML looks like for this cell
>>>>>> content. Check the attributes 't' and 's' for that cell and also what
>>>>>> the content for the cell looks like. If the cell is indeed numeric
>>>>>> then the value of 't' must be 'n' and you can also see the value of
>>>>>> 's'. It must be a number that points to the style index in the
>>>>>> workbook styles section. The styles are kept in a separate xml
>>>>>>
>>>>>> But as MSB has suggested in another reply, you should get hold of the
>>>>>> cell style. From the cell style you'll get the format index. From the
>>>>>> format index you can get the format string. And that format string
>>>>>> cannot be directly used as a java format. The format conventions are
>>>>>> not exactly similar between java and excel. Have you tried reading
>>>>>> the cell value "as a string" ? Unless you need the typed cell value,
>>>>>> you might be better off just extracting the formatted string as is.
>>>>>>
>>>>>> -Rk
>>>>>>
>>>>>> __________________________
>>>>>> Radhakrishnan Jankiraman
>>>>>> (o) +91.20-30239400 extn 520 (c) +91.9822006056
>>>>>>
>>>>>>
>>>>>> -----Original Message-----
>>>>>> From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
>>>>>> Sent: Wednesday, June 16, 2010 8:29 AM
>>>>>> To: user@poi.apache.org
>>>>>> Subject: RE: Read Custom format cell value
>>>>>>
>>>>>>
>>>>>> 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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>>
>>>>>>>>
>>>>>>>> ---------------------------------------------------------------------
>>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>> ---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>>
>>>>> http://old.nabble.com/file/p28904075/styles.xml styles.xml
>>>>> http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml
>>>>> http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>>> SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28918580.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by Siva_Masilamani <sa...@yahoo.co.in>.
Thanks Mark
This works for this excel file.
But however the same code is not working for the attached file.
i am getting the below exception:
java.lang.RuntimeException: not implemented yet
at
org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:112)
at
org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:608)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:456)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:266)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1112)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1072)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1059)
at
org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1410)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1510)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1494)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1451)
at
org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1431)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1552)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:174)
at
org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook$Name.getNameDefinition(XSSFEvaluationWorkbook.java:158)
at
org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:528)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:439)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:604)
at
org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:47)
at
org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
at
org.apache.poi.hssf.record.formula.functions.Sumif.accumulate(Sumif.java:95)
at
org.apache.poi.hssf.record.formula.functions.Sumif.sumMatchingCells(Sumif.java:83)
at
org.apache.poi.hssf.record.formula.functions.Sumif.eval(Sumif.java:72)
at
org.apache.poi.hssf.record.formula.functions.Sumif.evaluate(Sumif.java:65)
at
org.apache.poi.hssf.record.formula.functions.Var2or3ArgFunction.evaluate(Var2or3ArgFunction.java:36)
at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:437)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:206)
201003 31/03/2010 AFS To HTM 632001050 Spreadsheet at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:252)
at
org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:145)
at
org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:483)
at
com.excel.reader.ExcelDataReader.processFormula(ExcelDataReader.java:183)
at
com.excel.reader.ExcelDataReader.getCellValue(ExcelDataReader.java:156)
at
com.excel.reader.ExcelDataReader.process(ExcelDataReader.java:121)
at com.excel.reader.ExcelDataReader.main(ExcelDataReader.java:265)
Exception in thread "main" Java Result: 1
Source code im using :
private DataFormatter formatter = new DataFormatter();
private DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
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 = processFormula(cell);
System.out.print(retVal + " ");
break;
default:
retVal = "";
}
return retVal;
}
private String isNumberOrDate(Cell cell) {
String retVal;
if (HSSFDateUtil.isCellDateFormatted(cell)) {
retVal = dateFormat.format(cell.getDateCellValue());
} else {
retVal = formatter.formatCellValue(cell);
}
return retVal;
}
private String processFormula(Cell cell) {
String retVal = "";
FormulaEvaluator evaluator =
myWorkbook.getCreationHelper().createFormulaEvaluator();
if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
retVal = "#VALUE!";
} else {
retVal = formatter.formatCellValue(cell, evaluator);
if (retVal.matches("[0-9]+")) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
retVal = dateFormat.format(cell.getDateCellValue());
} else {
retVal = formatter.formatCellValue(cell);
}
}
}
return retVal;
}
Basically all i want to do is:
if cell type is string or blank the read the value as it is.
If cell type is numeric then find if it is date or number,if it is date then
format it as mm/dd/yyyy else just display the number as it is (no truncation
no round up etc).
If cell type is formula then if the cell value contains only number then do
the above line again else just display the value as it is.
MSB wrote:
>
> Thanks for that, and further, I just want to confirm that all you need to
> do is extract the contents of the cells as formatted Strings. You do not
> need to know the cells type so that you could, for exaqmple, recover a
> date value for later processing?
>
> If the answer to this question is also yes, then the answer is actually
> quite a straightforward one, you need to use a FormulaEvaluator so that
> the DataFormatter can actually work out what the cell ought to contain in
> the first place - by parsing the formula - and then return it to you as a
> String. I ran your file through some code I wrote a while ago for
> converting Excel workbooks in CSV files and all seemed to go correctly.
> So, this morning, I have copied the relevant piece of code from that class
> and pasted it into the message below.
>
> String cellContents = null;
>
> // Open a workbook - this code tagets the SS classes to work with .xls and
> .xlsx
> Workbook workbook = WorkbookFactory.create(new FileInputStream(new
> File("..........")))
>
> DataFormatter formatter = new DataFormatter();
> // There are a few ways to create FormulaEvaluators. The one I use
> // in the code is this one
> FormulaEvaluator evaluator =
> workbook.getCreationHelper().createFormulaEvaluator();
>
> // Now all you need to do is get sheets from the workbook and iterate
> through the cells
> // one at a time storing the refernce into a variable that I have called
> cell in
> // the code below and will be of type Cell. Then this bit of code will get
> you that
> // formatted String.
>
>
> if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
> // Protect the DataFormatter from instances where the cell
> // DOES contain a formula but where that formula actually
> // shows #VALUE! in the cell. In such a case, a call to the
> // getCahcedFormulaResultType() method will return
> // Cell.CELL_TYPE_ERROR and a message can be synthesised for
> // inclusion in the CSV file. It may well be worth indicating
> // to the user that a problem has been detected in the worksheet.
> if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
> cellContents = "#VALUE!";
> } else {
> cellContents = this.formatter.formatCellValue(
> cell, this.evaluator);
> }
> }
> // Finally, in all other cases ( fields that are blank, boolean,
> // contain numbers or Strings) rely upon the DataFormatter to return
> // a String encapsulating the cells formatted value.
> else {
> cellContents = this.formatter.formatCellValue(cell);
> }
>
> As you can see, it is far from complete but gives you the idea. It should
> be a simple matter of iterating through the sheets and, for each sheet, of
> iterating through the cells on the sheet's rows. If you have any further
> trouble, or if I have misconstrued what you do need to achieve, just drop
> a message onto this list.
>
> Yours
>
> Mark B
>
>
> Siva_Masilamani wrote:
>>
>> Yes you are right.
>>
>>
>>
>> MSB wrote:
>>>
>>> Can I just confirm that you are referring to the values in column E
>>> please? These are the only formulae that I can find at present though I
>>> do have to admit I have not looked through the sheets contents throughly
>>> yet.
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> Siva_Masilamani wrote:
>>>>
>>>> Well
>>>>
>>>> I did follow the step you mentioned and it seems the cell is of string.
>>>>
>>>> Also when i try to get the cell style and read the format it shows the
>>>> number as 49 and the format string as @ for all other cells prior to
>>>> this and it still throws the same error for this cell but display the
>>>> cell format as 49.
>>>>
>>>> So i guess the api consides once the cell type is formula it uses parse
>>>> double to get the value where it fails as the data contains other
>>>> characters too.
>>>>
>>>> For your reference i have attached the excel file and the style file.
>>>>
>>>> Jankiraman, Radhakrishnan wrote:
>>>>>
>>>>> Let me first apologize for not reading your complete email. Something
>>>>> with my outlook that gave me the impression that there isn't much
>>>>> content below. There seems to be something really wrong in this case.
>>>>> From the stack trace, it looks like the cell type is reported to be
>>>>> numeric by the getCellType() call, but the date format utility breaks
>>>>> while trying to extract the double value for the cell which shouldn't
>>>>> happen. This seems like a bug to me.
>>>>>
>>>>> Try opening the .xlsx file with an archive utility like WinZip. Unzip
>>>>> the contents. The sheet content is stored under 'xl\worksheets' folder
>>>>> with sheets being numbered. Locate your sheet and inspect its
>>>>> contents. The styles are stored in 'styles.xml' in the 'xl' folder.
>>>>>
>>>>> You should try taking a look at what the XML looks like for this cell
>>>>> content. Check the attributes 't' and 's' for that cell and also what
>>>>> the content for the cell looks like. If the cell is indeed numeric
>>>>> then the value of 't' must be 'n' and you can also see the value of
>>>>> 's'. It must be a number that points to the style index in the
>>>>> workbook styles section. The styles are kept in a separate xml
>>>>>
>>>>> But as MSB has suggested in another reply, you should get hold of the
>>>>> cell style. From the cell style you'll get the format index. From the
>>>>> format index you can get the format string. And that format string
>>>>> cannot be directly used as a java format. The format conventions are
>>>>> not exactly similar between java and excel. Have you tried reading the
>>>>> cell value "as a string" ? Unless you need the typed cell value, you
>>>>> might be better off just extracting the formatted string as is.
>>>>>
>>>>> -Rk
>>>>>
>>>>> __________________________
>>>>> Radhakrishnan Jankiraman
>>>>> (o) +91.20-30239400 extn 520 (c) +91.9822006056
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
>>>>> Sent: Wednesday, June 16, 2010 8:29 AM
>>>>> To: user@poi.apache.org
>>>>> Subject: RE: Read Custom format cell value
>>>>>
>>>>>
>>>>> 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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>>
>>>>>>>
>>>>>>> ---------------------------------------------------------------------
>>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>> http://old.nabble.com/file/p28904075/styles.xml styles.xml
>>>> http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml
>>>> http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>> SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>>
>>>
>>>
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28914837.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by MSB <ma...@tiscali.co.uk>.
Thanks for that, and further, I just want to confirm that all you need to do
is extract the contents of the cells as formatted Strings. You do not need
to know the cells type so that you could, for exaqmple, recover a date value
for later processing?
If the answer to this question is also yes, then the answer is actually
quite a straightforward one, you need to use a FormulaEvaluator so that the
DataFormatter can actually work out what the cell ought to contain in the
first place - by parsing the formula - and then return it to you as a
String. I ran your file through some code I wrote a while ago for converting
Excel workbooks in CSV files and all seemed to go correctly. So, this
morning, I have copied the relevant piece of code from that class and pasted
it into the message below.
String cellContents = null;
// Open a workbook - this code tagets the SS classes to work with .xls and
.xlsx
Workbook workbook = WorkbookFactory.create(new FileInputStream(new
File("..........")))
DataFormatter formatter = new DataFormatter();
// There are a few ways to create FormulaEvaluators. The one I use
// in the code is this one
FormulaEvaluator evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
// Now all you need to do is get sheets from the workbook and iterate
through the cells
// one at a time storing the refernce into a variable that I have called
cell in
// the code below and will be of type Cell. Then this bit of code will get
you that
// formatted String.
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
// Protect the DataFormatter from instances where the cell
// DOES contain a formula but where that formula actually
// shows #VALUE! in the cell. In such a case, a call to the
// getCahcedFormulaResultType() method will return
// Cell.CELL_TYPE_ERROR and a message can be synthesised for
// inclusion in the CSV file. It may well be worth indicating
// to the user that a problem has been detected in the worksheet.
if (cell.getCachedFormulaResultType() == Cell.CELL_TYPE_ERROR) {
cellContents = "#VALUE!";
} else {
cellContents = this.formatter.formatCellValue(
cell, this.evaluator);
}
}
// Finally, in all other cases ( fields that are blank, boolean,
// contain numbers or Strings) rely upon the DataFormatter to return
// a String encapsulating the cells formatted value.
else {
cellContents = this.formatter.formatCellValue(cell);
}
As you can see, it is far from complete but gives you the idea. It should be
a simple matter of iterating through the sheets and, for each sheet, of
iterating through the cells on the sheet's rows. If you have any further
trouble, or if I have misconstrued what you do need to achieve, just drop a
message onto this list.
Yours
Mark B
Siva_Masilamani wrote:
>
> Yes you are right.
>
>
>
> MSB wrote:
>>
>> Can I just confirm that you are referring to the values in column E
>> please? These are the only formulae that I can find at present though I
>> do have to admit I have not looked through the sheets contents throughly
>> yet.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Siva_Masilamani wrote:
>>>
>>> Well
>>>
>>> I did follow the step you mentioned and it seems the cell is of string.
>>>
>>> Also when i try to get the cell style and read the format it shows the
>>> number as 49 and the format string as @ for all other cells prior to
>>> this and it still throws the same error for this cell but display the
>>> cell format as 49.
>>>
>>> So i guess the api consides once the cell type is formula it uses parse
>>> double to get the value where it fails as the data contains other
>>> characters too.
>>>
>>> For your reference i have attached the excel file and the style file.
>>>
>>> Jankiraman, Radhakrishnan wrote:
>>>>
>>>> Let me first apologize for not reading your complete email. Something
>>>> with my outlook that gave me the impression that there isn't much
>>>> content below. There seems to be something really wrong in this case.
>>>> From the stack trace, it looks like the cell type is reported to be
>>>> numeric by the getCellType() call, but the date format utility breaks
>>>> while trying to extract the double value for the cell which shouldn't
>>>> happen. This seems like a bug to me.
>>>>
>>>> Try opening the .xlsx file with an archive utility like WinZip. Unzip
>>>> the contents. The sheet content is stored under 'xl\worksheets' folder
>>>> with sheets being numbered. Locate your sheet and inspect its contents.
>>>> The styles are stored in 'styles.xml' in the 'xl' folder.
>>>>
>>>> You should try taking a look at what the XML looks like for this cell
>>>> content. Check the attributes 't' and 's' for that cell and also what
>>>> the content for the cell looks like. If the cell is indeed numeric then
>>>> the value of 't' must be 'n' and you can also see the value of 's'. It
>>>> must be a number that points to the style index in the workbook styles
>>>> section. The styles are kept in a separate xml
>>>>
>>>> But as MSB has suggested in another reply, you should get hold of the
>>>> cell style. From the cell style you'll get the format index. From the
>>>> format index you can get the format string. And that format string
>>>> cannot be directly used as a java format. The format conventions are
>>>> not exactly similar between java and excel. Have you tried reading the
>>>> cell value "as a string" ? Unless you need the typed cell value, you
>>>> might be better off just extracting the formatted string as is.
>>>>
>>>> -Rk
>>>>
>>>> __________________________
>>>> Radhakrishnan Jankiraman
>>>> (o) +91.20-30239400 extn 520 (c) +91.9822006056
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
>>>> Sent: Wednesday, June 16, 2010 8:29 AM
>>>> To: user@poi.apache.org
>>>> Subject: RE: Read Custom format cell value
>>>>
>>>>
>>>> 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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>> http://old.nabble.com/file/p28904075/styles.xml styles.xml
>>> http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml
>>> http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>> SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>>
>>
>>
>
>
--
View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28910810.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by Siva_Masilamani <sa...@yahoo.co.in>.
Yes you are right.
MSB wrote:
>
> Can I just confirm that you are referring to the values in column E
> please? These are the only formulae that I can find at present though I do
> have to admit I have not looked through the sheets contents throughly yet.
>
> Yours
>
> Mark B
>
>
> Siva_Masilamani wrote:
>>
>> Well
>>
>> I did follow the step you mentioned and it seems the cell is of string.
>>
>> Also when i try to get the cell style and read the format it shows the
>> number as 49 and the format string as @ for all other cells prior to this
>> and it still throws the same error for this cell but display the cell
>> format as 49.
>>
>> So i guess the api consides once the cell type is formula it uses parse
>> double to get the value where it fails as the data contains other
>> characters too.
>>
>> For your reference i have attached the excel file and the style file.
>>
>> Jankiraman, Radhakrishnan wrote:
>>>
>>> Let me first apologize for not reading your complete email. Something
>>> with my outlook that gave me the impression that there isn't much
>>> content below. There seems to be something really wrong in this case.
>>> From the stack trace, it looks like the cell type is reported to be
>>> numeric by the getCellType() call, but the date format utility breaks
>>> while trying to extract the double value for the cell which shouldn't
>>> happen. This seems like a bug to me.
>>>
>>> Try opening the .xlsx file with an archive utility like WinZip. Unzip
>>> the contents. The sheet content is stored under 'xl\worksheets' folder
>>> with sheets being numbered. Locate your sheet and inspect its contents.
>>> The styles are stored in 'styles.xml' in the 'xl' folder.
>>>
>>> You should try taking a look at what the XML looks like for this cell
>>> content. Check the attributes 't' and 's' for that cell and also what
>>> the content for the cell looks like. If the cell is indeed numeric then
>>> the value of 't' must be 'n' and you can also see the value of 's'. It
>>> must be a number that points to the style index in the workbook styles
>>> section. The styles are kept in a separate xml
>>>
>>> But as MSB has suggested in another reply, you should get hold of the
>>> cell style. From the cell style you'll get the format index. From the
>>> format index you can get the format string. And that format string
>>> cannot be directly used as a java format. The format conventions are not
>>> exactly similar between java and excel. Have you tried reading the cell
>>> value "as a string" ? Unless you need the typed cell value, you might be
>>> better off just extracting the formatted string as is.
>>>
>>> -Rk
>>>
>>> __________________________
>>> Radhakrishnan Jankiraman
>>> (o) +91.20-30239400 extn 520 (c) +91.9822006056
>>>
>>>
>>> -----Original Message-----
>>> From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
>>> Sent: Wednesday, June 16, 2010 8:29 AM
>>> To: user@poi.apache.org
>>> Subject: RE: Read Custom format cell value
>>>
>>>
>>> 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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>> http://old.nabble.com/file/p28904075/styles.xml styles.xml
>> http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml
>> http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>> SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>>
>
>
--
View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28907017.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by MSB <ma...@tiscali.co.uk>.
Can I just confirm that you are referring to the values in column E please?
These are the only formulae that I can find at present though I do have to
admit I have not looked through the sheets contents throughly yet.
Yours
Mark B
Siva_Masilamani wrote:
>
> Well
>
> I did follow the step you mentioned and it seems the cell is of string.
>
> Also when i try to get the cell style and read the format it shows the
> number as 49 and the format string as @ for all other cells prior to this
> and it still throws the same error for this cell but display the cell
> format as 49.
>
> So i guess the api consides once the cell type is formula it uses parse
> double to get the value where it fails as the data contains other
> characters too.
>
> For your reference i have attached the excel file and the style file.
>
> Jankiraman, Radhakrishnan wrote:
>>
>> Let me first apologize for not reading your complete email. Something
>> with my outlook that gave me the impression that there isn't much content
>> below. There seems to be something really wrong in this case. From the
>> stack trace, it looks like the cell type is reported to be numeric by the
>> getCellType() call, but the date format utility breaks while trying to
>> extract the double value for the cell which shouldn't happen. This seems
>> like a bug to me.
>>
>> Try opening the .xlsx file with an archive utility like WinZip. Unzip the
>> contents. The sheet content is stored under 'xl\worksheets' folder with
>> sheets being numbered. Locate your sheet and inspect its contents. The
>> styles are stored in 'styles.xml' in the 'xl' folder.
>>
>> You should try taking a look at what the XML looks like for this cell
>> content. Check the attributes 't' and 's' for that cell and also what the
>> content for the cell looks like. If the cell is indeed numeric then the
>> value of 't' must be 'n' and you can also see the value of 's'. It must
>> be a number that points to the style index in the workbook styles
>> section. The styles are kept in a separate xml
>>
>> But as MSB has suggested in another reply, you should get hold of the
>> cell style. From the cell style you'll get the format index. From the
>> format index you can get the format string. And that format string cannot
>> be directly used as a java format. The format conventions are not exactly
>> similar between java and excel. Have you tried reading the cell value "as
>> a string" ? Unless you need the typed cell value, you might be better off
>> just extracting the formatted string as is.
>>
>> -Rk
>>
>> __________________________
>> Radhakrishnan Jankiraman
>> (o) +91.20-30239400 extn 520 (c) +91.9822006056
>>
>>
>> -----Original Message-----
>> From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
>> Sent: Wednesday, June 16, 2010 8:29 AM
>> To: user@poi.apache.org
>> Subject: RE: Read Custom format cell value
>>
>>
>> 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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
> http://old.nabble.com/file/p28904075/styles.xml styles.xml
> http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml
> http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx
> SPD_REF_QRM_PLANNING_RC_20100531.xlsx
>
--
View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28906424.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by Siva_Masilamani <sa...@yahoo.co.in>.
Well
I did follow the step you mentioned and it seems the cell is of string.
Also when i try to get the cell style and read the format it shows the
number as 49 and the format string as @ for all other cells prior to this
and it still throws the same error for this cell but display the cell format
as 49.
So i guess the api consides once the cell type is formula it uses parse
double to get the value where it fails as the data contains other characters
too.
For your reference i have attached the excel file and the style file.
Jankiraman, Radhakrishnan wrote:
>
> Let me first apologize for not reading your complete email. Something with
> my outlook that gave me the impression that there isn't much content
> below. There seems to be something really wrong in this case. From the
> stack trace, it looks like the cell type is reported to be numeric by the
> getCellType() call, but the date format utility breaks while trying to
> extract the double value for the cell which shouldn't happen. This seems
> like a bug to me.
>
> Try opening the .xlsx file with an archive utility like WinZip. Unzip the
> contents. The sheet content is stored under 'xl\worksheets' folder with
> sheets being numbered. Locate your sheet and inspect its contents. The
> styles are stored in 'styles.xml' in the 'xl' folder.
>
> You should try taking a look at what the XML looks like for this cell
> content. Check the attributes 't' and 's' for that cell and also what the
> content for the cell looks like. If the cell is indeed numeric then the
> value of 't' must be 'n' and you can also see the value of 's'. It must be
> a number that points to the style index in the workbook styles section.
> The styles are kept in a separate xml
>
> But as MSB has suggested in another reply, you should get hold of the cell
> style. From the cell style you'll get the format index. From the format
> index you can get the format string. And that format string cannot be
> directly used as a java format. The format conventions are not exactly
> similar between java and excel. Have you tried reading the cell value "as
> a string" ? Unless you need the typed cell value, you might be better off
> just extracting the formatted string as is.
>
> -Rk
>
> __________________________
> Radhakrishnan Jankiraman
> (o) +91.20-30239400 extn 520 (c) +91.9822006056
>
>
> -----Original Message-----
> From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
> Sent: Wednesday, June 16, 2010 8:29 AM
> To: user@poi.apache.org
> Subject: RE: Read Custom format cell value
>
>
> 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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
http://old.nabble.com/file/p28904075/styles.xml styles.xml
http://old.nabble.com/file/p28904075/sheet1.xml sheet1.xml
http://old.nabble.com/file/p28904075/SPD_REF_QRM_PLANNING_RC_20100531.xlsx
SPD_REF_QRM_PLANNING_RC_20100531.xlsx
--
View this message in context: http://old.nabble.com/Read-Custom-format-cell-value-tp28894102p28904075.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by "Jankiraman, Radhakrishnan" <rj...@emptoris.com>.
Let me first apologize for not reading your complete email. Something with my outlook that gave me the impression that there isn't much content below. There seems to be something really wrong in this case. From the stack trace, it looks like the cell type is reported to be numeric by the getCellType() call, but the date format utility breaks while trying to extract the double value for the cell which shouldn't happen. This seems like a bug to me.
Try opening the .xlsx file with an archive utility like WinZip. Unzip the contents. The sheet content is stored under 'xl\worksheets' folder with sheets being numbered. Locate your sheet and inspect its contents. The styles are stored in 'styles.xml' in the 'xl' folder.
You should try taking a look at what the XML looks like for this cell content. Check the attributes 't' and 's' for that cell and also what the content for the cell looks like. If the cell is indeed numeric then the value of 't' must be 'n' and you can also see the value of 's'. It must be a number that points to the style index in the workbook styles section. The styles are kept in a separate xml
But as MSB has suggested in another reply, you should get hold of the cell style. From the cell style you'll get the format index. From the format index you can get the format string. And that format string cannot be directly used as a java format. The format conventions are not exactly similar between java and excel. Have you tried reading the cell value "as a string" ? Unless you need the typed cell value, you might be better off just extracting the formatted string as is.
-Rk
__________________________
Radhakrishnan Jankiraman
(o) +91.20-30239400 extn 520 (c) +91.9822006056
-----Original Message-----
From: Siva_Masilamani [mailto:saachinsiva@yahoo.co.in]
Sent: Wednesday, June 16, 2010 8:29 AM
To: user@poi.apache.org
Subject: RE: Read Custom format cell value
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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by Siva_Masilamani <sa...@yahoo.co.in>.
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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by "Jankiraman, Radhakrishnan" <rj...@emptoris.com>.
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:saachinsiva@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:saachinsiva@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by Siva_Masilamani <sa...@yahoo.co.in>.
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:saachinsiva@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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by "Jankiraman, Radhakrishnan" <rj...@emptoris.com>.
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:saachinsiva@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Read Custom format cell value
Posted by "Jankiraman, Radhakrishnan" <rj...@emptoris.com>.
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:saachinsiva@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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Read Custom format cell value
Posted by MSB <ma...@tiscali.co.uk>.
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Read Custom format cell value
Posted by MSB <ma...@tiscali.co.uk>.
In one way, I remembered correctly. There is a class called ForkedEvaluator
that does allow you to work with formulae that reference cells in external
workbooks and so I began to put together some test code. Unfortuantely, I
found that it will only work - at least the version that I have from a
recent snapshot of version 3.7 - with HSSFWorkbooks. If I remember
correctly, you are working with the newer OOXML based workbook files and so
this option may well be out of the question. All is not lost however as I
have a plan going forward.
Firstly, I am going to get the very latest release of version 3.7 and try
running that against the small test files I have put together. My intention
is to test the basic code that simply uses an evaluator to see if that will
work and then to re-test the code that uses the ForkedEvaluator to see if
the restriction on the workbooks type has been removed, allowing it to
process both HSF and XSSFWorkbooks.
Secondly, should that fail, I think that I will look at writing some code to
parse the 'cross workbook' part of the formula. The format of that part of
the formula will be fixed - 'Path[Workbook]Sheet'!Cell Address - and it
should be possible to parse this 'manually', much as I am guessing Josh did
in the ForkedEvaluator class. As I am typing this, I am loath to modify
anything that is a part of the API and so do not want to make any changes to
the ForkedEvaluator class but I am quite happy to rip out of it any code
that I think might help us or even to sub-class ForkedEvaluator if that is
the better option. That is why I am looking at writing some code that will;
Get the formula from the cell.
Test to see if it contains anything that references another cell on a
separtae worksheet.
If it does, fetch the contents of that cell and substitute that value into
the formula.
Evaluate the formula.
Obviously, there are going to be problems along the way - what if the
external cell contains another formula that itself references a third
external cell for example - but it should be possible to put some code
together fairly quickly. You will have to test it throughly however to
ensure it will work successfully in your environment.
Will post again if I make any progress.
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-tp28894102p28940911.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org