How to Use Multiple DataSorter for same WorkBook

Aspose.Cells Product Family
1

Suppose I have a WorkBook , where for Few Columns I need top to Bottom Sort
and few Columns we need left To Right Sort.

How to reset the Data Sorter, since we will be using the same workbook.getDataSorter method to inialize the Data Sorter. so probably the End Columns will be same as before right?

import java.util.Calendar;

import com.aspose.cells.*;

public class Main {

	public static void main(String[] args) {
		Workbook workbook;
		try {
			workbook = new Workbook("Sample.ods");
			DataSorter sorter=workbook.getDataSorter();
			String stringtofind = ",";
			String stringtoreplace = "-";
			int weekTopRow = 0;
			boolean weekTopFlag = false;
			Worksheet worksheet = workbook.getWorksheets().get("Report");
			Cells cells=worksheet.getCells();
			/*CellArea ca1 = CellArea.createCellArea("E4", "H4");
			optsTop.setRange(ca1);
			
			Cell cell;
			Cell prevcell = null;
			do {
				cell = cells.find(stringtofind, prevcell, optsTop);
				if (cell == null)
					break;
				String val = cell.getStringValue();
				val = val.replace(stringtofind, stringtoreplace);
				cell.setValue(val);
				prevcell = cell;
			}while (cell != null);*/
			sorter.addKey(0, SortOrder.ASCENDING);
			sorter.addKey(1, SortOrder.ASCENDING);
			CellArea ca1= CellArea.createCellArea("A3", "B7");
			sorter.sort(cells, ca1);
			
			DataSorter sorterLft = workbook.getDataSorter();
                    sorterLft.setSortLeftToRight(true);
                    sorterLft.addKey(1,SortOrder.ASCENDING,"ABA,AAA");
			sorterLft.sort(cells, 0, 3, cells.getMaxRow(), cells.getMaxColumn());
			sorter.sort(cells, 0, 2, cells.getMaxRow(), cells.getMaxColumn());
			
			workbook.save("Result.xlsx");
				System.out.println("DONE");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	}

How to Reset the workBook.getDataSorter() props when using Second time.
The Custom Sort in the second Sorter is not working

2

Sample File Sample (2).zip (12.0 KB)

3

@aymarzapak

Thanks for using Aspose APIs.

Please call Workbook.calculateFormula() method, once you open your ODS file as shown below.

workbook = new Workbook("Sample.ods");
workbook.calculateFormula();

If it does not fix the issue, then please also provide us sorted ODS file. Please share screenshots how do you sort it manually using Microsoft Excel or OpenOffice. Besides, there are merged cells in your file. Could you try your code without merged cells. May be this issue is occurring because of merged cells. Try your code with other Excel or ODS files and check if the problem occurs with this file alone or it occurs with all types of files. Let us know your feedback.

4

HI Shakeel, Thanks for replying.

I have tried with the above suggestion but its gives me the same failed result.

Below is my code and my xlsx format file sample1.zip (6.6 KB)

import com.aspose.cells.*;

public class Main {

	public static void main(String[] args) {
		Workbook workbook;
		try {
			workbook = new Workbook("D:\\Sample.xlsx");
			workbook.calculateFormula();
			DataSorter sorter=workbook.getDataSorter();
			Worksheet worksheet = workbook.getWorksheets().get("Sheet1");
			Cells cells=worksheet.getCells();
			
			sorter.addKey(0, SortOrder.ASCENDING);
			sorter.addKey(1, SortOrder.ASCENDING);
			sorter.addKey(2, SortOrder.ASCENDING);
			CellArea ca1= CellArea.createCellArea("A3", "C7");
			sorter.sort(cells, ca1);
			
			DataSorter sorterLft = workbook.getDataSorter();
                    sorterLft.setSortLeftToRight(true);
                    sorterLft.addKey(1,SortOrder.ASCENDING,"ABA,AAA");
			sorterLft.sort(cells, 0, 3, cells.getMaxRow(), cells.getMaxColumn());
			
			workbook.save("D:\\Result.xlsx");
				System.out.println("DONE");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	}
5

@aymarzapak

Thanks for using Aspose APIs.

Please use the following code and read its comments. It generates the correct results.

Java

Workbook workbook = new Workbook(dirPath + "Sample1.xlsx");
workbook.calculateFormula();
DataSorter sorter=workbook.getDataSorter();
Worksheet worksheet = workbook.getWorksheets().get("Sheet1");
Cells cells=worksheet.getCells();

sorter.addKey(0, SortOrder.ASCENDING);
sorter.addKey(1, SortOrder.ASCENDING);
sorter.addKey(2, SortOrder.ASCENDING);
CellArea ca1= CellArea.createCellArea("A3", "C7");
sorter.sort(cells, ca1);

//Clear alll settings
workbook.getDataSorter().clear();

DataSorter sorterLft = workbook.getDataSorter();
sorterLft.setSortLeftToRight(true);
sorterLft.addKey(0,SortOrder.ASCENDING,"ABA,AAA"); //For Row 1, use 0
ca1 = CellArea.createCellArea("D1", "O7");
sorterLft.sort(cells, ca1);

workbook.save(dirPath + "Result.xlsx");
6

Worked like Charm. Thanks Shakeel

7

@aymarzapak

Thanks for your feedback. It is good to know that code worked for you. Let us know if you face any other issue, we will be glad to look into it and help you further.