Apache-POI sorting rows in excel

Question

I'd like to sort rows in a sheet by one of string column. I tried to achive that using Sheet.shiftRows method, but I cannot manage with that. It doesn't switch positions of rows in my method. What's wrong in my code? Or maybe there is better way to sort rows by any String column in excel?

/**
 * Sorts (A-Z) rows by String column
 * @param sheet - sheet to sort
 * @param column - String column to sort by
 * @param rowStart - sorting from this row down
 */
private void sortSheet(Sheet sheet, int column, int rowStart) {
    boolean sorting = true;
    int lastRow = sheet.getLastRowNum();
    while (sorting == true) {
        sorting = false;
        for (Row row : sheet) {
            // skip if this row is before first to sort
            if (row.getRowNum()<rowStart) continue;
            // end if this is last row
            if (lastRow==row.getRowNum()) break;
            Row row2 = sheet.getRow(row.getRowNum()+1);
            if (row2 == null) continue;
            String firstValue = (row.getCell(column) != null) ? row.getCell(column).getStringCellValue() : "";
            String secondValue = (row2.getCell(column) != null) ? row2.getCell(column).getStringCellValue() : "";
            //compare cell from current row and next row - and switch if secondValue should be before first
            if (secondValue.compareToIgnoreCase(firstValue)<0) {                    
                sheet.shiftRows(row2.getRowNum(), row2.getRowNum(), -1);
                sheet.shiftRows(row.getRowNum(), row.getRowNum(), 1);
                sorting = true;
            }
        }
    }
}

Any idea how to manage row sorting in a sheet?

UPDATE The method above works since Apache-POI 3.9 version.

EDIT: Added missing bracket -helvio

Answer 1

Poi has no built in sorting mechanism, though of course you are far from the first one with that need.

I think you are getting in trouble because you are moving rows that you are iterating over. I have run the code above and it seems what is happening is rows are disappearing from the sheet by the end of the code execution.

The question attempts to do an in-place modification of a read-in sheet. I believe that creating a second output sheet would be more appropriate.

So the basic approach would be read the sheet, sort in java just as you would treat any other sort problem, write to output sheet. If you did a map of the row number which is unique to the string value of the column you are interested in then you could sort the map by value. This sort of approach would work if you only foresaw the need to sort on a single column. In any event, it is not as simple as just choosing the sort menu option from within excel.