Sort Data in Excel in Java

In this article, I’ll show you how to sort data by column in Excel worksheets using Java codes programmatically. It’s worth mentioning that I used a third-party library called Spire.XLSfor Java and it is a professional Java API that enables developers to create, manage, manipulate, convert and print Excel worksheets without using Microsoft Office.

The code samples in this tutorial will be demonstrated from three aspects as below.

l  Sort Data Based on Cell Values

l  Sort Data Based on Cell Color

l  Sort Data Based on Font Color

Add Spire.Xls.jar to your Project

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.12.3</version>
</dependency>
</dependencies>

Using the code
Sort Data Based on Cell Values
import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.sorting.SortColumn;

public class CellValues {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.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:A9"));

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


Sort Data Based on Cell Colors
import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.sorting.SortColumn;
import java.awt.*;

public class CellColors {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.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.BackgroundColor, OrderBy.Top);

        //Specify the color to sort the data
        column.setColor(Color.yellow);

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

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

Sort Data Based on Font Colors
import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.sorting.SortColumn;
import java.awt.*;

public class FontColors {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load the Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.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.FontColor, OrderBy.Bottom);
        //Specify the color to sort the data
        column.setColor(Color.red);

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

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