java - XSSFSheet (Apache POI) sorting and filtering

Question

I'm creating an autofilter on an XSSFSheet as follows:

sheet.setAutoFilter(new CellRangeAddress(1, sheet.getLastRowNum() + 1,
                0, 14));

It works just fine, but I'd also like it to default to sorting by ascending values on a particular column (column 1 as indexed from zero). Anybody know how to do that?

Thanks! Sam

Answer 1

1. Sorting with ASPOSE library

Currently, there is no sorting available for Apache POI. I suggest ASPOSE Java for Apache POI. With that library, your class would look like:

    //Obtain the DataSorter object in the workbook
    DataSorter sorter = workbook.getDataSorter();

    //Set the first order
    sorter.setOrder1(SortOrder.ASCENDING);

    //Define the first key.
    sorter.setKey1(0);

    //Set the second order
    sorter.setOrder2(SortOrder.ASCENDING);

    //Define the second key
    sorter.setKey2(1);

    //Create a cells area (range).
    CellArea ca = new CellArea();

    //Specify the start row index.
    ca.StartRow = 1;
    //Specify the start column index.
    ca.StartColumn = 0;
    //Specify the last row index.
    ca.EndRow = 9;
    //Specify the last column index.
    ca.EndColumn = 2;

    //Sort data in the specified data range (A2:C10)
    sorter.sort(cells, ca);

Reference: : https://asposeapachepoi.codeplex.com/wikipage?title=Sort%20Data.

2. With Java Collections.sort()

If you don't want to or can't use ASPOSE, like me, you could create a class that represents a data row in your source file, which implements java.lang.Comparable. Override the method compareTo with your criteria and use Collections.sort() to sort your list ascendingly. Something like this:

//imports omitted
public class MyRow implements Comparable<MyRow>{

    private String column1;
    private int column2;

    @Override
    public int compareTo(MyRow o) {
        return this.column1.compareTo(o.column2);
    }
}

Then, in your main class (or the class that you want to sort you list of MyRow in), you would code:

Collections.sort(yourList<MyRow>);