poi 4 Shift rows giving errror but working for poi 3

Question

Hi I have recently migrated to poi 4 from poi 3. I am shifting some rows in a table and inserting new rows. In my sheet there are some tables below the table into which I am inserting rows and I am updating the references for those tables. This code was working for poi 3. I recently shifted to poi 4 and this code has started to corrupt the file. The trouble maker I suspect is the update reference part. Please let me know what I am missing.

sheet.shiftRows(firstEmptyRow, sheet.getLastRowNum(), iRowsToBeInserted, true, true);

    Row destrow = null;
    int finalRow = iRowsToBeInserted + firstEmptyRow - 1;

    for (int i = firstEmptyRow; i <= finalRow; i++) {
        destrow = sheet.getRow(i);
        if (destrow == null) {
            System.out.println("row at " + i + " is null : creating new");
            destrow = sheet.createRow(i);
        }
        for (int j = startColumn; j <= endColumn; j++) {
            Cell oldCell = sourceRow.getCell(j);
            Cell NewCell = destrow.createCell(j);
            if (oldCell == null) {
                System.out.println("source cell is null!!!");
                oldCell = sourceRow.createCell(j);
            } else {
                System.out.println("old cell value " + parseCell(oldCell));
            }
            CellStyle newCellStyle = iWorkbook.createCellStyle();

            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            if (oldCell.getCellStyle() != null && oldCell.getCellStyle().getFillBackgroundColorColor() != null
                    && oldCell.getCellStyle().getFillForegroundColorColor() != null) {
                System.out.println("fill background color " + oldCell.getCellStyle().getFillBackgroundColor()
                        + " fillb color color " + oldCell.getCellStyle().getFillBackgroundColorColor().toString()
                        + " fill foreground color " + oldCell.getCellStyle().getFillForegroundColor()
                        + " fillc color color " + oldCell.getCellStyle().getFillForegroundColorColor().toString());
            } else {
                System.out.println("either old cell cellstyle is null or its fill colors are null");
            }
            NewCell.setCellStyle(newCellStyle);
            // If there is a cell comment, copy
            if (oldCell.getCellComment() != null) {
                NewCell.setCellComment(oldCell.getCellComment());
            }

            // If there is a cell hyperlink, copy
            if (oldCell.getHyperlink() != null) {
                NewCell.setHyperlink(oldCell.getHyperlink());
            }
            NewCell.setCellType(oldCell.getCellType());
        }
    }
    crEnd = new CellReference(crEnd.getSheetName(), finalRow, endColumn, crEnd.isRowAbsolute(), crEnd.isColAbsolute());
    final AreaReference newArea = new AreaReference(crStart, crEnd, iWorkbook.getSpreadsheetVersion());
    iTable.setArea(newArea);
    iTable.updateReferences();
    for (XSSFTable t : sheet.getTables()) {
        t.updateReferences();
        crStart = t.getStartCellReference();
        crEnd = t.getEndCellReference();
        AreaReference tableArea;
        System.out.println("Co-ordinates of table: " + t.getName() + " start x:y " + crStart.getRow() + ":"
                + crStart.getCol() + " end x:y " + crEnd.getRow() + ":" + crEnd.getCol());
        if (t.getName().equals(iTable.getName()) || crEnd.getRow() < newArea.getFirstCell().getRow()) {
            System.out.println("table is/before expanded table - continuing");
            continue;
        } else {
            System.out.println("table needs to be updated");
            CellReference crStartNew = new CellReference(crStart.getSheetName(), crStart.getRow() + iRowsToBeInserted, crStart.getCol(),crStart.isRowAbsolute(), crStart.isColAbsolute());
            CellReference crEndNew = new CellReference(crEnd.getSheetName(), crEnd.getRow() + iRowsToBeInserted, crEnd.getCol(), crEnd.isRowAbsolute(), crEnd.isColAbsolute());
            tableArea = new AreaReference(crStartNew, crEndNew, iWorkbook.getSpreadsheetVersion());
            AreaReference  arefArea = t.getArea();
            System.out.println(arefArea.toString());
            System.out.println(tableArea.toString());
            t.setArea(tableArea);
            t.updateReferences();
        }
    }

Answer 1

It is "known" bug. Unfortunately it is not resolved. There is a workaround to solve this problem

The most interesting snapshot is:

for (int nRow = nFirstDstRow; nRow <= nLastDstRow; ++nRow) {
            final XSSFRow row = sheet.getRow(nRow);
            if (row != null) {
                String msg = "Row[rownum=" + row.getRowNum()
                        + "] contains cell(s) included in a multi-cell array formula. "
                        + "You cannot change part of an array.";
                for (Cell c : row) {
                    ((XSSFCell) c).updateCellReferencesForShifting(msg);
                }
            }
        }