Bug 62759 - removeColumn corrupts XSSFTable
Summary: removeColumn corrupts XSSFTable
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.0.0-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2018-09-25 19:22 UTC by David Gauntt
Modified: 2019-05-04 19:15 UTC (History)
0 users

JUnit test case to illustrate bug 62759 (4.77 KB, text/plain)
2019-05-04 19:10 UTC, David Gauntt
Patch to XSSFTable.removeColumn() (3.08 KB, patch)
2019-05-04 19:15 UTC, David Gauntt
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description David Gauntt 2018-09-25 19:22:56 UTC
removeColumn does not update the ref field of ctTable.  Excel reports a corrupted file when he file is opened, and repairs it by deleting the table.  Unit test code is presented below.  When bApplyBugFix is false, the table is corrupted.  When bApplyBugFix is true, the table is not corrupted.

- David Gauntt

    public static void doUnitTest(File file) {
        final XSSFWorkbook workbook = new XSSFWorkbook();
        final XSSFSheet sheet = workbook.createSheet();


        try (OutputStream fileOut = new FileOutputStream(file)) {
        } catch (Exception e) {
        } finally {
            try {
            } catch (IOException e) {
        System.out.println("doUnitTest: done");
        final Desktop desktop = Desktop.getDesktop();
        try {
        } catch (Exception e) {


    private static void removeColumnUnitTest(XSSFSheet sheet) {
        final boolean bApplyBugFix = true;
        final String procName = "removeColumnUnitTest";
        final int NUMCOLS = 3, NUMROWS = 4;

        System.out.println(String.format("\r\n%s: bFixBug=%s", procName, bApplyBugFix));

        /* Fill a range with data */
        for (int i = 0; i < NUMROWS; i++) {
            XSSFRow row = sheet.createRow(i);
            for (int j = 0; j < NUMCOLS; j++) {
                XSSFCell localXSSFCell = row.createCell(j);
                if (i == 0) {
                    localXSSFCell.setCellValue(String.format("Col%d", j + 1));
                } else {
                    localXSSFCell.setCellValue(String.format("(%d,%d)", i + 1, j + 1));

        /* Define a single column data range including headers */
        AreaReference my_data_range = new AreaReference(new CellReference(0, 0),
                new CellReference(NUMROWS - 1, NUMCOLS - 1), SpreadsheetVersion.EXCEL2007);

        /* Create an object of type XSSFTable */
        final XSSFTable my_table = sheet.createTable(my_data_range);

        /* Apply bug fix for creatTable/createColumn */
        final CTTable ctTable = my_table.getCTTable();
        final List<CTTableColumn> ctTableColumns = ctTable.getTableColumns().getTableColumnList();
        final long numCols = ctTableColumns.size();
        for (int n = 0; n < numCols; ++n) {
            ctTableColumns.get(n).setId(n + 1);
        /* Remove the last column */
        my_table.removeColumn(NUMCOLS - 1);
        System.out.println(String.format("%s: after removing last column from table", procName));

        if (bApplyBugFix) {
            my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(NUMROWS - 1, NUMCOLS - 2),
            System.out.println("After applying bug fix");

Comment 1 David Gauntt 2019-05-04 19:10:48 UTC
Created attachment 36566 [details]
JUnit test case to illustrate bug 62759

This JUnit test class will write 3 Excel workbooks to build/custom-reports-test. 

Each workbook is created by adding a 4 column, 3 row table starting at cell $C$3 of the first worksheet.  Then one column is removed from the table.  In one of the workbooks, it is the first column, in one of the workbooks it is the last column, and in one it is the penultimate (second to the last) column.

JUnit passes the tests but the workbooks are corrupted.  When the workbooks are opened in Excel 2010 it repairs the workbooks by deleting the table.

I will post a patch shortly.
Comment 2 David Gauntt 2019-05-04 19:15:53 UTC
Created attachment 36567 [details]
Patch to XSSFTable.removeColumn()

This patch updates the area reference of the table after removing the column, and shifts the cells in the table appropriately.

If the column to be removed is the last column, then the cells in the last column are deleted.  Otherwise, the cells in the column to be removed are deleted, and the cells to their right are shifted one cell to the left.  In either case, cells outside the table are unaffected.

Note that this does not update any references to the cells.