Bug 62759

Summary: removeColumn corrupts XSSFTable
Product: POI Reporter: David Gauntt <dmgauntt>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: major    
Priority: P2    
Version: 4.0.0-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: JUnit test case to illustrate bug 62759
Patch to XSSFTable.removeColumn()

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.