You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Christian Castelli <vo...@gmail.com> on 2014/09/23 17:48:26 UTC
FormulaParseException on setting a sheet name
Hi everybody,
I have to read and populate an Excel 2007+ file (a template create by
someone in my company), saved in this format from its counterpart in
Excel 97/2007- with LibreOffice.
I've used SS Usermodel API (both for XSSF and HSSF) with Apache POI
3.10.1: I've no problems with HSSF with the same file, but when I use
XSSFWorkbook, I receive an exception when I try to rename the second
sheet.
Here it is the snippet of code:
FileInputStream file = new FileInputStream(new File(templateName));
Workbook workbook = format.equals("xls") ? new HSSFWorkbook(file) :
new XSSFWorkbook(file);
workbook.setSheetName(0, "a name..."); // no problem here
workbook.setSheetName(1, "Report per reparto");
Stacktrace:
org.apache.poi.ss.formula.FormulaParseException: Unused input [!#ref!]
after attempting to parse the formula [#ref!!#ref!]
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1573)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
at org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateName(XSSFFormulaUtils.java:144)
at org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateSheetName(XSSFFormulaUtils.java:99)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(XSSFWorkbook.java:1248)
I've debugged my code, following Apache POI source code, so I tried to
explore a little more the second sheet:
Sheet sheet = workbook.getSheetAt(1);
System.out.println("Last row on sheet " +
sheet.getSheetName()
+ ": " + sheet.getLastRowNum());
System.out.println("Protection enabled? " +
sheet.getProtect());
System.out.println("Are formulas displayed? " +
sheet.isDisplayFormulas());
System.out.println("Workbook's names: " +
workbook.getNumberOfNames());
for (int i = 0; i < workbook.getNumberOfNames(); i++) {
XSSFName nm = (XSSFName) workbook.getNameAt(i);
if (nm.getSheetIndex() == -1 ||
nm.getSheetIndex() == 1) {
System.out.println("Name: " +
nm.getNameName() + ", " +
"formula: " + nm.getRefersToFormula());
}
}
And the output is:
Last row on sheet REP_REP: 11
Protection enabled? false
Are formulas displayed? false
Workbook's names: 6
Name: _xlnm.Print_Titles, formula: REP_REP!$1:$13
Name: Excel_BuiltIn_Print_Titles, formula: #ref!!#ref!
Name: _xlnm.Print_Titles, formula: REP_REP!$1:$13
What kind of name is Excel_BuiltIn_Print_Titles and why there is a
reference to that unknown formula? I attach the XLSX file.
Thanks for any advice.
--
Christian Castelli
skype: christrack
Re: FormulaParseException on setting a sheet name
Posted by Nick Burch <ap...@gagravarr.org>.
On Tue, 23 Sep 2014, Christian Castelli wrote:
> FileInputStream file = new FileInputStream(new File(templateName));
> Workbook workbook = format.equals("xls") ? new HSSFWorkbook(file) :
> new XSSFWorkbook(file);
Use WorkbookFactory and a File (not inputstream) here, it'll be quicker
and lower memory!
> Stacktrace:
> org.apache.poi.ss.formula.FormulaParseException: Unused input [!#ref!]
> after attempting to parse the formula [#ref!!#ref!]
This looks a pretty messed up formula... I think it might be one where
there was a reference to a named range in another sheet, where both the
sheet and the named range have been deleted
First up, can you try with 3.11 beta 2? There have been some formula fixes
recently that're in there. If that doesn't help, please open a bug in
bugzilla, and upload a small file that shows the issue. We might need a
special case for this kind of orphaned reference
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: FormulaParseException on setting a sheet name
Posted by Christian Castelli <vo...@gmail.com>.
Hi again,
I've tried not to cut and paste the first 12 rows on each sheet and I
switched to SXSSFWorkbook (new SXSSFWorkbook(new XSSFWorkbook(file),
10000)).
Now when I try to get the first row of the third sheet, I get a null
pointer Exception and if I try to create it, I get a different weird
error:
java.lang.IllegalArgumentException: Attempting to write a row[0] in
the range [0,11] that is already written to disk.
at org.apache.poi.xssf.streaming.SXSSFSheet.createRow(SXSSFSheet.java:118)
at it.habble.report.designers.CallDesigner.setTitle(CallDesigner.java:886)
I attach the modified report. I cannot understand if it's something
related to this particular document or if it's library's bug. Should I
post this problem on the dev mailing list?
Bye.
2014-09-23 17:48 GMT+02:00 Christian Castelli <vo...@gmail.com>:
> Hi everybody,
> I have to read and populate an Excel 2007+ file (a template create by
> someone in my company), saved in this format from its counterpart in
> Excel 97/2007- with LibreOffice.
> I've used SS Usermodel API (both for XSSF and HSSF) with Apache POI
> 3.10.1: I've no problems with HSSF with the same file, but when I use
> XSSFWorkbook, I receive an exception when I try to rename the second
> sheet.
> Here it is the snippet of code:
>
> FileInputStream file = new FileInputStream(new File(templateName));
> Workbook workbook = format.equals("xls") ? new HSSFWorkbook(file) :
> new XSSFWorkbook(file);
> workbook.setSheetName(0, "a name..."); // no problem here
> workbook.setSheetName(1, "Report per reparto");
>
> Stacktrace:
> org.apache.poi.ss.formula.FormulaParseException: Unused input [!#ref!]
> after attempting to parse the formula [#ref!!#ref!]
> at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1573)
> at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
> at org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateName(XSSFFormulaUtils.java:144)
> at org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils.updateSheetName(XSSFFormulaUtils.java:99)
> at org.apache.poi.xssf.usermodel.XSSFWorkbook.setSheetName(XSSFWorkbook.java:1248)
>
> I've debugged my code, following Apache POI source code, so I tried to
> explore a little more the second sheet:
>
> Sheet sheet = workbook.getSheetAt(1);
> System.out.println("Last row on sheet " +
> sheet.getSheetName()
> + ": " + sheet.getLastRowNum());
> System.out.println("Protection enabled? " +
> sheet.getProtect());
> System.out.println("Are formulas displayed? " +
> sheet.isDisplayFormulas());
> System.out.println("Workbook's names: " +
> workbook.getNumberOfNames());
>
> for (int i = 0; i < workbook.getNumberOfNames(); i++) {
> XSSFName nm = (XSSFName) workbook.getNameAt(i);
> if (nm.getSheetIndex() == -1 ||
> nm.getSheetIndex() == 1) {
> System.out.println("Name: " +
> nm.getNameName() + ", " +
> "formula: " + nm.getRefersToFormula());
> }
> }
>
> And the output is:
>
> Last row on sheet REP_REP: 11
> Protection enabled? false
> Are formulas displayed? false
> Workbook's names: 6
> Name: _xlnm.Print_Titles, formula: REP_REP!$1:$13
> Name: Excel_BuiltIn_Print_Titles, formula: #ref!!#ref!
> Name: _xlnm.Print_Titles, formula: REP_REP!$1:$13
>
> What kind of name is Excel_BuiltIn_Print_Titles and why there is a
> reference to that unknown formula? I attach the XLSX file.
> Thanks for any advice.
>
> --
> Christian Castelli
> skype: christrack
--
Christian Castelli
skype: christrack