wb.setSheetName exception : "not implemented yet"

[slim]

Hello,

I'm using apache poi for handling an excel file in which I have about 90
tabs with some 800 formulas on each tab.

The generation of the second half of the tabs is done automatically using
the following code:

[CODE] sheet = wb.cloneSheet(sheetIndex); [/CODE]


So far, so good. But the cloned tab contains formulas that reference cells
in external tabs (in the same workbook).
And then I have a beautiful exception:

[CODE] java.lang.RuntimeException: not implemented yet
at
org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:127)
at
org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:615)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:510)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
at
org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateName(XSSFFormulaUtils.java:142)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateSheetName(XSSFFormulaUtils.java:97)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(XSSFWorkbook.java:1252)
at mon.package.ExcelWriter.renameSheets(ExcelWriter.java:184)
at mon.package.ExcelWriter.write(ExcelWriter.java:93)
at
mon.package.ExcelWriter.DBExcelWriterTest.testWrite(DBExcelWriterTest.java:83)
[/CODE]

In fact, it happens when poi re-evaluate all workbook's formulas.

the following method is executed (it's in the FormulaParser class) :

[CODE] public static Ptg[] parse(String formula, FormulaParsingWorkbook
workbook, int formulaType, int sheetIndex) {
FormulaParser fp = new FormulaParser(formula, workbook, sheetIndex);
fp.parse();
return fp.getRPNPtg(formulaType);
}[/CODE]

Parameter values :
formula: [1] January! # REF!
formulaType: 4
sheetIndex: -1! (which I do not understand ...)


He spends two times in this method, and I have twice the value [1] January!
# REF! parameter in the formula. that throws the exception, the second
time.

Do you have an idea to solve this problem.

Thank you very much.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/wb-setSheetName-exception-not-implemented-yet-tp5711501.html
Sent from the POI - User mailing list archive at Nabble.com.

[Yegor Kozlov]

POI fails to update a defined name that references a external
workbook. We don't support external names yet.

[1]January!#REF! is a external reference. Open Name Manager in Excel
and you should see expanded formula like
'otherfile.xlsx'January!#REF!

sheetIndex: -1 means that the scope of the name is workbook. If
sheetIndex>=0 then it means 0-based index of the sheet.

you can bypass all formula and name validations and set sheet name
directly in XML as follows:

wb.getCTWorkbook().getSheets().getSheetArray(sheetIndex).setName(sheetname);

Yegor

Post by slim
Hello,
I'm using apache poi for handling an excel file in which I have about 90
tabs with some 800 formulas on each tab.
The generation of the second half of the tabs is done automatically using
[CODE] sheet = wb.cloneSheet(sheetIndex); [/CODE]
So far, so good. But the cloned tab contains formulas that reference cells
in external tabs (in the same workbook).
[CODE] java.lang.RuntimeException: not implemented yet
at
org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:127)
at
org.apache.poi.ss.formula.FormulaParser.createAreaRefParseNode(FormulaParser.java:615)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:510)
at
org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
at
org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
at
org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
at
org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
at
org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
at
org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
at
org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
at
org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447)
at
org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateName(XSSFFormulaUtils.java:142)
at
org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateSheetName(XSSFFormulaUtils.java:97)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(XSSFWorkbook.java:1252)
at mon.package.ExcelWriter.renameSheets(ExcelWriter.java:184)
at mon.package.ExcelWriter.write(ExcelWriter.java:93)
at
mon.package.ExcelWriter.DBExcelWriterTest.testWrite(DBExcelWriterTest.java:83)
[/CODE]
In fact, it happens when poi re-evaluate all workbook's formulas.
[CODE] public static Ptg[] parse(String formula, FormulaParsingWorkbook
workbook, int formulaType, int sheetIndex) {
FormulaParser fp = new FormulaParser(formula, workbook, sheetIndex);
fp.parse();
return fp.getRPNPtg(formulaType);
}[/CODE]
formula: [1] January! # REF!
formulaType: 4
sheetIndex: -1! (which I do not understand ...)
He spends two times in this method, and I have twice the value [1] January!
# REF! parameter in the formula. that throws the exception, the second
time.
Do you have an idea to solve this problem.
Thank you very much.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/wb-setSheetName-exception-not-implemented-yet-tp5711501.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------

[slim]

Thank you very much. I'll test it tomorrow.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/wb-setSheetName-exception-not-implemented-yet-tp5711501p5711508.html
Sent from the POI - User mailing list archive at Nabble.com.

[slim]

Salim
***@gmail.com


On 18 November 2012 12:10, slim [via Apache POI] <

Post by slim
Thank you very much. I'll test it tomorrow.
------------------------------
If you reply to this email, your message will be added to the discussion
http://apache-poi.1045710.n5.nabble.com/wb-setSheetName-exception-not-implemented-yet-tp5711501p5711508.html
To unsubscribe from wb.setSheetName exception : "not implemented yet", click
here<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5711501&code=c2FsaW0uY2hhbWlAZ21haWwuY29tfDU3MTE1MDF8NTEzNDc4MDQ3>
.
NAML<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/wb-setSheetName-exception-not-implemented-yet-tp5711501p5711509.html
Sent from the POI - User mailing list archive at Nabble.com.

[slim]

It works ! thank you very much



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/wb-setSheetName-exception-not-implemented-yet-tp5711501p5711513.html
Sent from the POI - User mailing list archive at Nabble.com.