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