Apache-POI在excel中对行进行排序。

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?

我想用一列字符串来对表中的行进行排序。我试着用表格来完成。shiftRows方法，但我无法使用它。它不会在我的方法中改变行位置。我的代码有什么问题?或者也许有更好的方法来用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.

从Apache-POI 3.9版本开始，更新上面的方法。

EDIT: Added missing bracket -helvio

编辑:添加缺少括号-helvio

2 个解决方案

#1

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

Poi没有内置的排序机制，当然，您远不是第一个需要排序的。

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.

所以基本的方法是，在java中，像对待任何其他排序问题一样，在java中进行排序，写入输出表。如果你做了行号的映射它是你感兴趣的列的字符串值唯一的那么你可以按值对映射进行排序。如果您只预见到需要对单个列进行排序，那么这种方法就可以工作。无论如何，从excel中选择sort菜单选项并不简单。

#2

Now I now why it's not working. There is a bug in shiftRows method. When third argument (number of rows to shift) is negative, it causes troubles.

现在我为什么不工作了。移位方法中有一个错误。当第三个参数(要移动的行数)是负数时，它会引起麻烦。

This is described here: https://issues.apache.org/bugzilla/show_bug.cgi?id=53798

这里描述如下:https://issues es.apache.org/bugzilla/show_bug.cgi?id=53798

UPDATE This bug has been fixed from version 3.9

更新此错误已从3.9版修复

#1