Aspose.cells for java - Using DATASORTER

Aspose.Cells Product Family
1

Hello,

I'm trying to use the datasorter in aspose.cells for java.

My progam runs without errors, but the result is not as expected !

I'm trying to set the 3 keys, but only the first key works. I also don't know how to specify the sorting order ! Key1 is always sorted descending !?

In the procedure I see I have to pass a character field to specify the sort order
I tried 'A' and 'D' and '0' and '1', but without success.

Can you please help ?

Thanks

2

Hi,

If you want to set sorting order ,please use setOrder1(SortOrderType.ASCENDING
)/setOrder2(SortOrderType.DESCENDING
) /setOrder3(SortOrderType.DESCENDING). We will update the Java doc soon.

Could you post your sample codes and template file? We will check this issue soon.

3

Which constants do match the sortordertype.descending / sortordertype.ascending ?

I am using setOrder1 / setOrder2 / setOrder3 !

My code is in RPGLE !


O_CellArea = newCellArea(piR.RwFr:piR.ClFr:
piR.RwTo:piR.ClTo);
O_DataSorter = Workbook$getDataSorter(O_WB);
If %Parms>3 and %Addr(piHasHeader)<>*Null;
lHasHeader = piHasheader;
EndIf;
If %Parms>4 and %Addr(piCaseSens)<>*Null;
lCaseSens = piCaseSens;
EndIf;
DataSorter$setHasHeders(O_DataSorter:lhasHeader);
DataSorter$setCaseSensitive(O_DataSorter:lCaseSens);
DataSorter$setKey1(O_DataSorter:piKey1);
DataSorter$setOrder1(O_DataSorter:piOrder1); --> piOrder1 = ?????
If %Parms>5 and %Addr(piKey2)<>*Null;
DataSorter$setKey2(O_DataSorter:piKey2);
DataSorter$setOrder2(O_DataSorter:piOrder2);
EndIf;
If %Parms>7 and %Addr(piKey3)<>*Null;
DataSorter$setKey3(O_DataSorter:piKey3);
DataSorter$setOrder3(O_DataSorter:piOrder3);
EndIf;
DataSorter$Sort(O_DataSorter:O_Cells:O_CellArea);

4

Hi,

The sort order type we defined is one of following constants:

SortOrderType.ASCENDING = 1;

SortOrderType.DESCENDING = 2;

I am not familiar with RPG, if it can’t access those constants defined in Java Interface, I think you should use value 1 or 2 in your code, for you case, O_DataSorter:piOrder1 should be replaced by 1 or 2.


And we will provide a patch soon, it will validate the input SortOrderType value when invoke setOrder*() methods, I think that maybe help to avoid some misapplication and confusion.

5

Hi,

Please try the attached verion.

This fix applies validation and verifies for the input SortOrderType value when calling setOrder*() methods. If you input illegal value, that is, neither SortOrderType.ASCENDING nor SortOrderType.DESCENDING, will cause an Exception.

Sample code:

Workbook workbook = new Workbook();
Integer[] arrN = new Integer[]{new Integer(1), new Integer(15), new Integer(3),};
Cells cells = workbook.getWorksheets().getSheet(0).getCells();
cells.importArray(new int[]{1, 1, 15, 3, 15, 3}, 0, 0, true);
cells.importObjectArray(arrN, 0, 1, true);
cells.importObjectArray(arrN, 3, 1, true);
cells.importArray(new String[]{"1", "3", "15", "1", "15", "3"}, 0, 2, true);
cells.copyCellRange(cells, 0, 0, 8, 0, 6, 3);
cells.copyCellRange(cells, 0, 0, 16, 0, 6, 3);
DataSorter ds = workbook.getDataSorter();
ds.setKey1(0);
try
{
ds.setOrder1((byte)123);
//ds.setOrder1(SortOrderType.DESCENDING);
}
catch(IllegalArgumentException e)
{
System.out.println("As expected, check illegal value of ordertype: " + e.getMessage());
}
ds.sort(cells, new CellArea(0, 0, 5, 0));
ds.setKey1(1);
ds.setOrder1(SortOrderType.ASCENDING);
ds.sort(cells, new CellArea(0, 1, 5, 1));
ds.setKey1(2);
ds.setOrder1(SortOrderType.DESCENDING);
ds.sort(cells, new CellArea(0, 2, 5, 2));

ds.setKey1(0);
ds.setOrder1(SortOrderType.ASCENDING);
ds.setKey2(1);
ds.setOrder2(SortOrderType.ASCENDING);
ds.setKey3(2);
ds.setOrder3(SortOrderType.DESCENDING);
ds.sort(cells, new CellArea(16, 0, 21, 2));
workbook.save("E:\\Files\\sort_output.xls");

Thank you.

6

Could you please have a look at this functionality in aspose.cells for java (jdk5.0).

Second and third key sorting does not work !

Thanks

7

Hi,

Thanks for the info,

We will check it soon.

Thank you.

8

Hi,

After tested it again, I can't find the issue you mentioned. For the testing code posted by Amjad, The sorted result is ok and the Key2 took effect.

For confirmation, please see following test code, and the attachment contains the template file and the result file generated by Aspose.Cells for Java(JDK15 version). In the template, C1:C9 is the original data, C11:E19 and C21:E29 of sheet2 are sorted data by MS Excel with different Key and Order. And C11:E19 and C21:E29 of sheet1 is original data to be sorted by Aspose.Cells. By the result file sorters.xls, you can see that the sort result of Aspose.Cells is just same with what can got by MS Excel. If my code and template can't simulate your case, please give us your sample template and code, we will check it soon. Thank you.

Workbook workbook = new Workbook();
workbook.open("sort.xls");
DataSorter ds = workbook.getDataSorter();
Cells cells = workbook.getWorksheets().getSheet(0).getCells();

ds.setKey1(2);
ds.setOrder1(SortOrderType.ASCENDING);
ds.setKey2(3);
ds.setOrder2(SortOrderType.DESCENDING);
ds.setKey3(4);
ds.setOrder3(SortOrderType.ASCENDING);
ds.sort(cells, new CellArea("C11", "E19"));

ds.setKey1(3);
ds.setOrder1(SortOrderType.ASCENDING);
ds.setKey2(4);
ds.setOrder2(SortOrderType.ASCENDING);
ds.setKey3(2);
ds.setOrder3(SortOrderType.DESCENDING);
ds.sort(cells, new CellArea("C21", "E29"));
workbook.save("sortres.xls");

By the way, please make sure you have set the key correctly, the int argument for method setKey*() is not the offset of the sorted range, but the column index, such as column A is 0, B is 1, and so on. And we will update our javadoc for that soon to make less confusion.