Sort excel by a column using shiftRows- Apache POI - XmlValueDisconnectedException

Question

I have an XSSFWorkbook with n number of columns. And my requirement is to sort the entire sheet by the first column. I referred to this link but did not get any information about sorting.

I have also tried the code from here but it gives exception at

sheet.shiftRows(row2.getRowNum(), row2.getRowNum(), -1);

I am using Apache POI 3.17.

Anyone has any suggestion or solution?

Answer 1

There seem to be a bug in POI when shifting columns, they say it was fixed in 3.9 but I used 3.17 and still have it:

Exception in thread "main" org.apache.xmlbeans.impl.values.XmlValueDisconnectedException
at org.apache.xmlbeans.impl.values.XmlObjectBase.check_orphaned(XmlObjectBase.java:1258)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.getR(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFRow.getRowNum(XSSFRow.java:394)
...

I assume it is the same you have. So I worked out an other way:

Sort your rows, then create a new workbook and copy rows in the correct order. Then write this sorted workbook to the original file.

For simplicity, I assume all cell values are Strings. (if not, then modify accordingly)

private static final String FILE_NAME = "/home/userName/Workspace/fileToSort.xlsx";

public static void main(String[] args)  {

    Workbook originalWorkbook;

    //create a workbook from your file
    try(FileInputStream excelFile = new FileInputStream(new File(FILE_NAME))) {
        originalWorkbook = new XSSFWorkbook(excelFile);
    } catch (IOException e) {
        throw new RuntimeException("Couldn't open file: " + FILE_NAME);
    }

    Sheet originalSheet = originalWorkbook.getSheetAt(0);

    // Create a SortedMap<String, Row> where the key is the value of the first column
    // This will automatically sort the rows
    Map<String, Row> sortedRowsMap = new TreeMap<>();

    // save headerRow
    Row headerRow = originalSheet.getRow(0);

    Iterator<Row> rowIterator = originalSheet.rowIterator();
    // skip header row as we saved it already
    rowIterator.next();
    // sort the remaining rows
    while(rowIterator.hasNext()) {
        Row row = rowIterator.next();
        sortedRowsMap.put(row.getCell(0).getStringCellValue(), row);
    }

    // Create a new workbook
    try(Workbook sortedWorkbook = new XSSFWorkbook();
        FileOutputStream out = new FileOutputStream(FILE_NAME)) {
        Sheet sortedSheet = sortedWorkbook.createSheet(originalSheet.getSheetName());

        // Copy all the sorted rows to the new workbook
        // - header first
        Row newRow = sortedSheet.createRow(0);
        copyRowToRow(headerRow, newRow);
        // then other rows, from row 1 up (not row 0)
        int rowIndex = 1;
        for(Row row : sortedRowsMap.values()) {
            newRow = sortedSheet.createRow(rowIndex);
            copyRowToRow(row, newRow);
            rowIndex++;
        }

        // Write your new workbook to your file
        sortedWorkbook.write(out);
    } catch (Exception e) {
        e.printStackTrace();
    }
}


// Utility method to copy rows
private static void copyRowToRow(Row row, Row newRow) {
    Iterator<Cell> cellIterator = row.cellIterator();
    int cellIndex = 0;
    while(cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        Cell newCell = newRow.createCell(cellIndex);
        newCell.setCellValue(cell.getStringCellValue());
        cellIndex++;
    }
}

I tried it out on the following file

   A      B
---------------
Header1 Header2
a       one
c       three
d       four
b       two

and it sorts it this way:

   A      B
---------------
Header1 Header2
a       one
b       two
c       three
d       four