Sort Data in Excel in Java

#java #excel #sortdata

In this article, I am going to introduce how to sort data in an Excel worksheet programmatically in Java using Spire.XLS for Java API.

Spire.XLS for Java is a multifunctional API that supports to create, read, manipulate, convert and print Excel files in Java applications. It allows you to perform data sorting in Excel based on the following:

Sort Data Based on Cell Values
Sort Data Based on Cell Color
Sort Data Based on Font Color

In the following code examples, you will see how to sort data using Spire.XLS for Java API.

Get JAR

You can download the API’s JAR file from this link or install using the following Maven configurations.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue </groupId>
        <artifactId>spire.xls</artifactId>
        <version>3.11.2</version>
    </dependency>
</dependencies>

Sort Data Based on Cell Values

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.sorting.SortColumn;

public class SortByValues {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("SortData.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Create a sort column with the column index, the sort based on and order by attributes
        SortColumn column = workbook.getDataSorter().getSortColumns().add(0, SortComparsionType.Values, OrderBy.Descending);

        //Sort specified cell range
        workbook.getDataSorter().sort(sheet.getCellRange("A1:A8"));

        //Save the result file
        workbook.saveToFile("SortByValues.xlsx", ExcelVersion.Version2013);
    }
}

The output Excel file:

Sort Data Based on Cell Color

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.sorting.SortColumn;

import java.awt.*;

public class SortByCellColor {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("SortData.xlsx");

        //Get the second worksheet
        Worksheet sheet = workbook.getWorksheets().get(1);

        //Create a sort column with the column index, the sort based on and order by attributes
        SortColumn column = workbook.getDataSorter().getSortColumns().add(0, SortComparsionType.BackgroundColor, OrderBy.Top);
        //Specify the color to sort the data
        column.setColor(Color.red);

        //Sort specified cell range
        workbook.getDataSorter().sort(sheet.getCellRange("A1:A8"));

        //Save the result file
        workbook.saveToFile("SortByCellColor.xlsx", ExcelVersion.Version2013);
    }
}

The output Excel file:

Sort Data Based on Font Color

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.sorting.SortColumn;

import java.awt.*;

public class SortByFontColor {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("SortData.xlsx");

        //Get the third worksheet
        Worksheet sheet = workbook.getWorksheets().get(2);

        //Create a sort column with the column index, the sort based on and order by attributes
        SortColumn column = workbook.getDataSorter().getSortColumns().add(0, SortComparsionType.FontColor, OrderBy.Bottom);
        //Specify the color to sort the data
        column.setColor(Color.red);

        //Sort specified cell range
        workbook.getDataSorter().sort(sheet.getCellRange("A1:A8"));

        //Save the result file
        workbook.saveToFile("SortByFontColor.xlsx", ExcelVersion.Version2013);
    }
}