Apache-POI sorting rows in excel

The Problem (Q-score 8, ranked #241st of 303 in the Excel VBA archive)

The scenario as originally posted in 2012

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

Why community consensus is tight on this one

Across 303 Excel VBA entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.

The Verified Solution — niche answer (below median) (+4)

Verbal answer — walkthrough without a code block

Note: the verified answer is a prose walkthrough. If you need a runnable sample, check Excel VBA entries ranked in the top 10 of the same archive.

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.

When to Use It — vintage (14+ years old, pre-2013)

Ranked #241st in its category — specialized fit

This pattern sits in the 99% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.

What changed between 2012 and 2026

The answer is 14 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.