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
calls.xlsx
Description: MS-Excel 2007 spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
