Can't get sort to sort

Aspose.Cells Product Family
1

Good day.

I’m trying to get Aspose.Cells 17.8 to sort data in an xls spreadsheet - the first worksheet of a workbook. Desired output is sorted by column B, ascending.

The files are attached. xlsFiles.zip (21.1 KB)

Following is the code (it is in ColdFusion):

<!--- the workbook --->
<cfset wb=createObject("java","com.aspose.cells.Workbook").init(fileInLocation)>
<!--- worksheet collection --->
<cfset wsc=wb.getWorksheets()>

<cfset ws=wsc.get(javacast("int",0))>
<!--- the cells on the worksheet --->
<cfset cells=ws.getCells()>

<cfset sorter=wb.getDataSorter()>
<cfset sortOrder=createObject("java","com.aspose.cells.SortOrder")>
<cfset sorter.setOrder1(sortOrder.ASCENDING)>
<cfset sorter.setKey1(javacast("int",1))>
<cfset cellArea=createObject("java","com.aspose.cells.CellArea").init()>
<cfset cellArea.createCellArea(javacast("int",0),javacast("int",0),cells.getMaxDataRow(),cells.getMaxDataColumn())>
<cfset sorter.sort(wb.getWorksheets().get(javacast("int",0)).getCells(),cellArea)>

<cfset wb.save(fileOutLocation)>

It doesn’t seem like it’s sorting at all. Can you provide any guidance? Thank you.

2

@backprop

Your code seems to be fine. We are not sure, why it is not working, may be this is because you have not translated it properly.

We have tested your issue with this code and it works fine. I have attached the output Excel file generated by the code for a reference.

Download Link:
output Excel file.zip (6.8 KB)

Java

Workbook wb = new Workbook(dirPath + "pcetINPUT.xls");

Worksheet ws = wb.getWorksheets().get(0);
Cells cells = ws.getCells();

//There are two ways to create cell area
//CellArea ca = CellArea.createCellArea("A1", "B68");
CellArea ca = CellArea.createCellArea(0, 0, cells.getMaxDataRow(), cells.getMaxDataColumn());

DataSorter sorter = wb.getDataSorter();
sorter.setKey1(1);
sorter.setOrder1(SortOrder.ASCENDING);

wb.getDataSorter().sort(ws.getCells(), ca);

wb.save(dirPath + "output.xls");
3

Thank you. You were right it was in the translation. We instantiated a CellArea and then used the setCellArea() method on it. However, setCellArea() returns a new CellArea object and does not modify the original object.

This is relevant mainly to ColdFusion because of how constructors are invoked, but in case anyone has an issue:

This from the above code:
<cfset cellArea=createObject("java","com.aspose.cells.CellArea").init()> <cfset cellArea.createCellArea(javacast("int",0),javacast("int",0),cells.getMaxDataRow(),cells.getMaxDataColumn())>

should have been

<cfset cellArea=createObject("java","com.aspose.cells.CellArea").createCellArea(javacast("int",0),javacast("int",0),cells.getMaxDataRow(),cells.getMaxDataColumn()>

4

@backprop

It is good to know that you were able to sort out this issue and helped the Aspose.Cells community. Let us know if you encounter any other issue, we will be glad to look into it and help you further.