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-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
