Bug 55640 - Get IndexOutOfBoundsException while creating Excel with data grouping
Summary: Get IndexOutOfBoundsException while creating Excel with data grouping
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2013-10-08 09:12 UTC by Michael Chadov
Modified: 2013-12-02 19:19 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Michael Chadov 2013-10-08 09:12:01 UTC
Get IndexOutOfBoundsException while creating Excel with data grouping

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
When I change it to natural order (starting from the first row) - it throws

Stacktrace:Exception in thread "main" java.lang.IndexOutOfBoundsException
    at org.apache.xmlbeans.impl.store.Xobj.remove_attribute(Xobj.java:2287)
    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)

See test below. There are several lines marked with 'natural order' or
'reverse order', you can switch grouping order by commenting/uncommenting

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();

        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");

        for (int i = 0; i < ROWS_NUMBER; i++) {
            Row row = sheet.createRow(i);
            Cell cell = row.createCell(0);

        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);
                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) {
        try {
        } catch (IOException e) {
        try {
            if (fileOut != null) {
        } catch (IOException e) {

    public static void main(String[] args) {
        CreateExcel createExcel = new CreateExcel("K://workbook.xlsx");
Comment 1 Dominik Stadler 2013-11-22 22:11:42 UTC
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...).
Comment 2 Michael Chadov 2013-11-25 13:13:38 UTC
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.
Comment 3 Dominik Stadler 2013-12-02 19:19:21 UTC
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.