Get IndexOutOfBoundsException while creating Excel with data grouping Hello! I'm trying to create Excel with data grouping. Could you please clarify why I get IndexOutOfBoundsException? There is a simple test attached to create Excel with data grouping. When it groups rows in reverse order (starting from the last row) it works ok When I change it to natural order (starting from the first row) - it throws IndexOutOfBoundsException. Stacktrace:Exception in thread "main" java.lang.IndexOutOfBoundsException at org.apache.xmlbeans.impl.store.Xobj.remove_attribute(Xobj.java:2287) at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.unsetCollapsed(Unknown Source) at org.apache.poi.xssf.usermodel.XSSFSheet.expandRow(XSSFSheet.java:1870) at org.apache.poi.xssf.usermodel.XSSFSheet.setRowGroupCollapsed(XSSFSheet.java:1780) at CreateExcel.fillData(CreateExcel.java:49) at CreateExcel.create(CreateExcel.java:22) I'm using: jdk1.6.0_25, 32 bit poi 3.5 final (but I tried the latest release too) xmlbeans-2.3.0 See test below. There are several lines marked with 'natural order' or 'reverse order', you can switch grouping order by commenting/uncommenting them import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.xssf.usermodel.*; import org.apache.poi.ss.usermodel.*; public class CreateExcel { private static final int ROWS_NUMBER = 200; private static final int GROUP_SIZE = 5; private final String o_filename; private int o_groupsNumber = 0; public CreateExcel(String p_filename) { o_filename = p_filename; } public void create() { long startTime = System.currentTimeMillis(); Workbook wb = new XSSFWorkbook(); fillData(wb); writeToFile(wb); System.out.println("Number of groups: " + o_groupsNumber); System.out.println("Execution time: " + (System.currentTimeMillis()-startTime) + " ms"); } private void fillData(Workbook p_wb) { Sheet sheet = p_wb.createSheet("sheet123"); sheet.setRowSumsBelow(false); for (int i = 0; i < ROWS_NUMBER; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); cell.setCellValue(i+1); } int i = 1; while (i < ROWS_NUMBER) { int end = i+(GROUP_SIZE-2); int start = i; // natural order // int start = end - 1; // reverse order while (start < end) { // natural order // while (start >= i) { // reverse order sheet.groupRow(start, end); o_groupsNumber++; sheet.setRowGroupCollapsed(start, isCollapsed()); start++; // natural order // start--; // reverse order } i += GROUP_SIZE; } } private boolean isCollapsed() { return Math.random() > 0.05d; } private void writeToFile(Workbook p_wb) { FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(o_filename); } catch (FileNotFoundException e) { e.printStackTrace(); } try { p_wb.write(fileOut); } catch (IOException e) { e.printStackTrace(); } try { if (fileOut != null) { fileOut.close(); } } catch (IOException e) { e.printStackTrace(); } } public static void main(String[] args) { CreateExcel createExcel = new CreateExcel("K://workbook.xlsx"); createExcel.create(); } }
I tried to work on this and found one smaller issue in XSSFSheet.writeHidden(), it will start at row 0 instead of skipping to the start of the group, however this does not fix all broken cases, the issue seems to be that we write to the additional row multiple times if there are nested groups with the same end-row used and unsetting the collapsed state on the internal structures twice is not possible. I think we need to take another look at how the spec defines how groups are stored, there seems to be some difference between what POI writes and what other applications write, at least some sample files did behave strange in LibreOffice (no MS Office available right now...).
Thank you for reply. Could you please make estimations on how long may it take? Is it possible to speed up the process by sponsoring from our side? We use the library in our project and fixing data grouping is critical for us at the moment.
I have committed two things in r1547153: * Fix invalid calculation of start-row when setting rows hidden * Avoid IndexOutOfBoundsException when nested groups which end on the same row are all set to collapsed This at least makes your test run without throwing Exceptions. I fear there might be additional cases where the resulting Excel file is not completely correct with collapsed/hidden state, but I just could not get my head around the spec far enough to fix it, especially as LibreOffice seems to behave a bit different to POI and Excel so I am not sure what the best way to handle things are. I have at least added unit tests to record the current behavior and to allow us to see which cases are affected by future fixes. Please report separate bugs with description how to create them or sample Excel files and the expected results so we can continue to work on this.