When I want to delete a sheet that's not the last one in a workbook with several sheets, I get an exception. Exception in thread "main" java.lang.NullPointerException at org.apache.poi.hssf.model.InternalWorkbook.removeSheet(InternalWorkbook.java:780) at org.apache.poi.hssf.usermodel.HSSFWorkbook.removeSheetAt(HSSFWorkbook.java:924) I open the file with: try (InputStream in = new BufferedInputStream(new FileInputStream(new File(fileName)))) { return WorkbookFactory.create(in); } then I call workbook.removeSheetAt(0); Apparently, the linkTable field is null. My use case is to load a template with several sheets, delete them all but one to clone it to the number of sheets needed. As a workaround, I move the wanted sheet at first position with: workbook.setSheetOrder(workbook.getSheetName(sheetIndex), 0); then I delete all the others in reverse order...
Can you attach a small file that shows this problem?
Created attachment 32172 [details] The Excel file used in the test
Created attachment 32173 [details] Show bugs
Added a standalone class that loads the sample Excel file and reproduce the bugs (three bugs are shown, I numbered them with the Bugzilla id). You might need to adjust the path to the sample.
Created attachment 32198 [details] Proposed fix which adjusts the active sheet in setSheetOrder() and removeSheet() FYI, I have a proposed fix for this and bug 57171, but would like to postpone checkin until after release 3.11 as it runs a slight risk of breaking stuff.
Fixed this and Bug 57163 via r1647264
This bug is still occurs for XLS (HSSFWorkbook) in release POI 3.12 beta 1. Problem in org.apache.poi.hssf.model.InternalWorkbook:780 public void removeSheet(int sheetIndex) { .... // also tell the LinkTable about the removed sheet // +1 because we already removed it from the count of sheets! for(int i = sheetIndex+1;i < getNumSheets()+1;i++) { // also update the link-table as otherwise references might point at invalid sheets linkTable.removeSheet(i); } } It is allowed linkTable field to be null!!!
Created attachment 32633 [details] Java file to test
Created attachment 32634 [details] Excel sheet to test
Proposed fix (add check for linkTable): public void removeSheet(int sheetIndex) { .... if (linkTable != null) { // also tell the LinkTable about the removed sheet // +1 because we already removed it from the count of sheets! for(int i = sheetIndex+1;i < getNumSheets()+1;i++) { // also update the link-table as otherwise references might point at invalid sheets linkTable.removeSheet(i); } } }
This is actually fixed already since some time via r1673168, it seems we just missed to close this issue.