Document Solutions for Excel, Java Edition | Document Solutions
Features / Sort
In This Topic
    Sort
    In This Topic

    For efficient data organization and quickly locate relevant information, DsExcel Java enables users to execute sorting operation on the data in the worksheets.

    The sort method can be used to perform sorting based on a cell range, range by value, color or icon in a spreadsheet. The apply method is used to apply the selected sort state and display the sorted results.

    Note: Sort operation can also be performed on merged cells, provided the cells to be merged are of the same size.

    Different types of sorting available in DsExcel Java are explained below:

    Sort by value

    Sort by value refers to the sorting operation that arranges the data in a particular order on the basis of the cell values. The setOrientation method is used to specify the orientation category for sorting, i.e, columns or rows.

    In order to execute sort by value operation, refer to the following example code.

    Java
    Copy Code 
    // Assigning values to the range
worksheet.getRange("A1").setValue(2);
worksheet.getRange("A2").setValue(1);
worksheet.getRange("A3").setValue(1);
worksheet.getRange("A4").setValue(3);
worksheet.getRange("B1").setValue("g");
worksheet.getRange("B2").setValue("z");
worksheet.getRange("B3").setValue("z1");
worksheet.getRange("B4").setValue("a");

// Apply Sort by value using IRange.Sort() method
worksheet.getRange("A1:B4").sort(worksheet.getRange("A1:A4"), SortOrder.Ascending, SortOrientation.Columns);

    Sort by value for multiple columns

    Sort by value refers to the sorting operation that is performed on multiple columns via a single line of code. You can also specify the orientation of columns in either ascending order or descending order.

    In order to sort by value for multiple columns, refer to the following example code.

    Java
    Copy Code 
    // Assigning values to the range
worksheet.getRange("A1").setValue(2);
worksheet.getRange("A2").setValue(1);
worksheet.getRange("A3").setValue(1);
worksheet.getRange("A4").setValue(1);
worksheet.getRange("B1").setValue("g");
worksheet.getRange("B2").setValue("z");
worksheet.getRange("B3").setValue("a");
worksheet.getRange("B4").setValue("b");

// Sort by value, multiple column sort uses IRange.Sort() method
worksheet.getRange("A1:B4").sort(SortOrientation.Columns, false,
new ValueSortField[] { new ValueSortField(worksheet.getRange("A1:A2"), SortOrder.Ascending),
new ValueSortField(worksheet.getRange("B1:B2"), SortOrder.Descending) });

    Custom sort

    Sorting is a common task, but not all data types are meant to follow the common ascending and descending sort order rule. For instance, months cannot be sorted in a meaningful way when sorted alphabetically. In such a scenario, DsExcel Java allows users to execute a custom sort.

    In order to implement custom sorting, refer to the following example code.

    Java
    Copy Code 
    // Assigning values to the range
worksheet.getRange("A1").setValue("test");
worksheet.getRange("A2").setValue(3);
worksheet.getRange("A3").setValue(5);
worksheet.getRange("A4").setValue(1);
worksheet.getRange("A5").setValue(2);
worksheet.getRange("A6").setValue(4);

// Define a custom sort value string
ValueSortField sortkey = new ValueSortField(worksheet.getRange("A1:A2"), "1,2,3");
worksheet.getRange("A2:A6").sort(SortOrientation.Columns, false, sortkey);

    Sort by interior

    Sort by interior refers to the sorting operation which is performed on the basis of the interior color, pattern, pattern color, gradient color and gradient angle. However, interior sort cannot be executed on the basis of cell color.

    In order to perform sort by interior operation, refer to the following example code.

    Java
    Copy Code 
    // Create a new workbook and add data 
 Workbook workbook = new Workbook();
 Object data = new Object[][]{
          {"Name", "City", "Birthday", "Eye color", "Weight", "Height"},
          {"Richard", "New York", new GregorianCalendar(1968, 5, 8), "Blue", 67, 165},
          {"Nia", "New York", new GregorianCalendar(1972, 6, 3), "Brown", 62, 134},
          {"Jared", "New York", new GregorianCalendar(1964, 2, 2), "Hazel", 72, 180},
          {"Natalie", "Washington", new GregorianCalendar(1972, 7, 8), "Blue", 66, 163},
          {"Damon", "Washington", new GregorianCalendar(1986, 1, 2), "Hazel", 76, 176},
          {"Angela", "Washington", new GregorianCalendar(1993, 1, 15), "Brown", 68, 145}
  };
  IWorksheet worksheet = workbook.getWorksheets().get(0);
  worksheet.getRange("A1:F7").setValue(data);
  worksheet.getRange("A:F").setColumnWidth(15);
  
  // Set color to the range 
  worksheet.getRange("F2").getInterior().setColor(Color.GetLightPink());
  worksheet.getRange("F3").getInterior().setColor(Color.GetLightGreen());
  worksheet.getRange("F4").getInterior().setColor(Color.GetLightPink());
  worksheet.getRange("F5").getInterior().setColor(Color.GetLightGreen());
  worksheet.getRange("F6").getInterior().setColor(Color.GetLightBlue());
  worksheet.getRange("F7").getInterior().setColor(Color.GetLightPink());

  // "F4" will at the top.
  worksheet.getSort().getSortFields().add(new CellColorSortField(worksheet.getRange("F2:F7"), worksheet.getRange("F4").getDisplayFormat().getInterior(), SortOrder.Ascending));
  worksheet.getSort().setRange(worksheet.getRange("A2:F7"));
  worksheet.getSort().setOrientation(SortOrientation.Columns);
  // Apply sort
  worksheet.getSort().apply();

    Sort by font color

    Sort by font color refers to the sorting operation which is executed on the basis of the cell's display format and font color. However, sorting operation is not performed on the basis of cell color.

    In order to execute sort by font color, refer to the following example code.

    Java
    Copy Code 
    // Assigning values to the range
worksheet.getRange("A1").setValue(2);
worksheet.getRange("A2").setValue(1);
worksheet.getRange("A3").setValue(1);
worksheet.getRange("A4").setValue(3);
worksheet.getRange("B1").setValue(2);
worksheet.getRange("B2").setValue(1);
worksheet.getRange("B3").setValue(1);
worksheet.getRange("B4").setValue(3);
        
// Assigning color to the range
worksheet.getRange("B1").getFont().setColor(Color.FromArgb(0, 128, 0));
worksheet.getRange("B2").getFont().setColor(Color.FromArgb(128, 0, 0));
worksheet.getRange("B3").getFont().setColor(Color.FromArgb(0, 0, 128));
worksheet.getRange("B4").getFont().setColor(Color.FromArgb(128, 128, 0));
        
// Defining sort by color
worksheet.getSort().getSortFields().add(new FontColorSortField(worksheet.getRange("B1:B4"),
worksheet.getRange("B1").getDisplayFormat().getFont().getColor(), SortOrder.Descending));
worksheet.getSort().setRange(worksheet.getRange("A1:B4"));
worksheet.getSort().setOrientation(SortOrientation.Columns);
worksheet.getSort().apply();

    Sort by Icon

    Sort by icon refers to the sorting operation that is performed on the basis of the cell's conditional format icons.

    In order to execute sort by icon operation, refer to the following example code.

    Java
    Copy Code 
    // Assigning values to the range
worksheet.getRange("A1").setValue(2);
worksheet.getRange("A2").setValue(1);
worksheet.getRange("A3").setValue(1);
worksheet.getRange("A4").setValue(3);
worksheet.getRange("B1").setValue(2);
worksheet.getRange("B2").setValue(1);
worksheet.getRange("B3").setValue(1);
worksheet.getRange("B4").setValue(3);
        
// Defining sort by icon
IIconSetCondition iconset = worksheet.getRange("B1:B4").getFormatConditions().addIconSetCondition();
iconset.setIconSet(workbook.getIconSets().get(IconSetType.Icon3TrafficLights1));
worksheet.getSort().getSortFields().add(new IconSortField(worksheet.getRange("B1:B4"),
workbook.getIconSets().get(IconSetType.Icon3TrafficLights1).get(0), SortOrder.Ascending));
worksheet.getSort().setRange(worksheet.getRange("A1:B4"));
worksheet.getSort().setOrientation(SortOrientation.Columns);
worksheet.getSort().apply();

    See Also
    Table Sort