You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by saritha suram <sa...@netapp.com> on 2012/08/02 08:44:34 UTC
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
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-not-able-to-evaluate-table-names-in-the-Excel-Sheet-tp5710583.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: Apache POI not able to evaluate table names in the Excel Sheet
Posted by Stephen More <st...@gmail.com>.
Was a bug ever filed to support table names ?
I am trying to access a named table much like:
http://poi.apache.org/spreadsheet/quick-guide.html#NamedRanges
But it does not seem to work.
-Thanks
On Fri, Aug 3, 2012 at 2:18 AM, saritha suram <sa...@netapp.com> wrote:
> Hi Yegor,
>
> Thanks for the response and the code.
> Will try the same and let you know the results.
>
> Regards,
> Saritha
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-not-able-to-evaluate-table-names-in-the-Excel-Sheet-tp5710583p5710606.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: Apache POI not able to evaluate table names in the Excel Sheet
Posted by saritha suram <sa...@netapp.com>.
Hi Yegor,
Thanks for the response and the code.
Will try the same and let you know the results.
Regards,
Saritha
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-not-able-to-evaluate-table-names-in-the-Excel-Sheet-tp5710583p5710606.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: Apache POI not able to evaluate table names in the Excel Sheet
Posted by Yegor Kozlov <ye...@dinom.ru>.
Iterate over cell, grab raw formula string, modify and write back.
Something like this:
for(Cell cell : row){
XSSFCell xcell = (XSSFCell)cell;
CTCellFormula f = xcell.getCTCell().getF();
if(f != null){
String sval = f.getStringValue();
sval = sval.replace("Table28[[#ThisRow]",
"[row, column]");
f.setStringValue(sval);
}
Yegor
}
On Thu, Aug 2, 2012 at 1:47 PM, saritha suram <sa...@netapp.com> wrote:
> 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
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-not-able-to-evaluate-table-names-in-the-Excel-Sheet-tp5710583p5710585.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: Apache POI not able to evaluate table names in the Excel Sheet
Posted by saritha suram <sa...@netapp.com>.
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
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-not-able-to-evaluate-table-names-in-the-Excel-Sheet-tp5710583p5710585.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: Apache POI not able to evaluate table names in the Excel Sheet
Posted by Yegor Kozlov <ye...@dinom.ru>.
it looks like evaluation of tables names is not yet supported. Can you
create a new bug in Bugzilla and attach sample workbook and Java code
that results in exception?
Yegor
On Thu, Aug 2, 2012 at 10:44 AM, saritha suram <sa...@netapp.com> wrote:
> 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
>
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-not-able-to-evaluate-table-names-in-the-Excel-Sheet-tp5710583.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: Apache POI not able to evaluate table names in the Excel Sheet
Posted by noobie1111 <lo...@gmx.de>.
hello :)
i know, this thread is very old, but i have exacty the same problems.
i'm using a HSSFSheet and after reading the solution, i tried it for hours
and navigated through the classes, which seems to influence the formulas,
but i dont understand much of it and my head is exploding right now :(
could anyone please tell me, how i could realise the following?
workbook.getSheet("Sheet1").getRow(0).getCell(0).setCellFormula("Sheet2.A1");
i would like to override the automatic evaluation of the formular and force
it to evaluate it when it is loaded via excel, but i have no idea, how it
works :'(
can anyone give me the neccessary source code for it? :D
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-not-able-to-evaluate-table-names-in-the-Excel-Sheet-tp5710583p5714476.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