Apache POI not able to evaluate table names in the Excel Sheet

Hi,

I am working on a XSSF excel sheet, whose values are to be read and displayed on the UI.

The cells in the sheet are evaluated using formulas and they also use "table names" in them.

POI is unable to evaluate the formulas where there occurs a table name and am getting the below error:

Formula is : IF(ValidationCheck=FALSE,IF(HAOption="Yes",Table28[[#This Row],[/ DAG]]*calcNumDAGs,TotDBDiskSpaceReq*numMBXServers),"--")

Error is: Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException: Specified named range 'Table28' does not exist in the current workbook.

Any help would be greatly appreciated.

Thanks in advance,

Saritha

Re: Apache POI not able to evaluate table names in the Excel Sheet

Does that formula work in Excel for that same sheet?

(Should be easy enough to test).

If so then it could simply be a limitation of POI.

Re: Apache POI not able to evaluate table names in the Excel Sheet

Quote:

Originally Posted by

**saritha suram** Hi,

I am working on a XSSF excel sheet, whose values are to be read and displayed on the UI.

The cells in the sheet are evaluated using formulas and they also use "table names" in them.

POI is unable to evaluate the formulas where there occurs a table name and am getting the below error:

Formula is : IF(ValidationCheck=FALSE,IF(HAOption="Yes",Table28[[#This Row],[/ DAG]]*calcNumDAGs,TotDBDiskSpaceReq*numMBXServers),"--")

Error is: Exception in thread "main" org.apache.poi.ss.formula.FormulaParseException: Specified named range 'Table28' does not exist in the current workbook.

Any help would be greatly appreciated.

Thanks in advance,

Saritha

The formulas are working in the excel sheet.

Re: Apache POI not able to evaluate table names in the Excel Sheet

Hi,

Then is there any other work around in POI to get the cell value directly, without evaluating the formula from the excel sheet?

Thanks,

Saritha

Re: Apache POI not able to evaluate table names in the Excel Sheet

Hi,

If POI does not support the evaluation of table names, then as a work around, is there any way to convert or replace the table name reference in the formula to [ row, column] combination and then evaluate the formula??

Thanks,

Saritha

Re: Apache POI not able to evaluate table names in the Excel Sheet

It's been ages since I've done anything vaguely complex with Excel formulae, so I've no idea how you would replace that lot.

Here's the Apache page covering the sort of thing they support and don't support at the moment, by the way.