DEV Community

loading...

Sort Data in Excel in Java

eiceblue profile image E-iceblue Product Family ・3 min read

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:

  1. Sort Data Based on Cell Values
  2. Sort Data Based on Cell Color
  3. 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>
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

The output Excel file:
Alt Text

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

The output Excel file:
Alt Text

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

The output Excel file:
Alt Text

Discussion

pic
Editor guide
Collapse
snorri1986 profile image
Denys Shabelnyk

Is this solution useful? What are you think about?