Custom List

Aspose.Cells Product Family
1

Hi I’m trying to add a Custom Order List,
My Order is as below
USA ,US
ENG ,UK
DUBAI,UAE

When I trying to add it, It takes as
USA
US
ENG
UK
DUBAI
UAE

Any suggestion would be appreciated.

2

@arshad3641,

Thanks for sharing some details.

How do you add/import to Excel worksheet via Aspose.Cells APIs, could you give us sample code (runnable) to evaluate your issue? Also how do you create custom order list, do you create it in your code (as a custom class/object) or it is in some file (e.g txt, csv, etc.) which you import. Do you use Excel’s custom list data sort? Please provide us complete details, resource files (input, output etc.) and all the artifacts to evaluate your issue on our end.

Thank you.

3

package iteratew;
import com.aspose.cells.Cell;
import com.aspose.cells.CellArea;
import com.aspose.cells.Cells;
import com.aspose.cells.DataSorter;
import com.aspose.cells.SortOrder;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class Main {

public static void main(String[] args) throws Exception {
	
	Workbook workbook= new Workbook("C:\\BeforeSort.xlsx");
	Worksheet worksheet = workbook.getWorksheets().get(1);
	Cells cells= worksheet.getCells();
	DataSorter sortertopCols = workbook.getDataSorter();
	
	sortertopCols.setSortLeftToRight(true);
	sortertopCols.addKey(3, SortOrder.ASCENDING);
	sortertopCols.addKey(4, SortOrder.ASCENDING,"USA,US,ENG ,UK,DUBAI,UAE");

	CellArea cellArea2 = CellArea.createCellArea(3, 2, 305, 13);
	
	sortertopCols.sort(cells, cellArea2);
	worksheet.autoFitColumns();
	workbook.save("C:\\Excel\\after3.xlsx");
	System.out.println("done");
	System.out.println(cells.getMaxRow());
	

}

}

Yeah Here the way I’m giving the Custom List is as Normal
But As said I want the Order to be as
USA ,US
ENG ,UK
DUBAI,UAE

and not as
USA
US
ENG
UK
DUBAI
UAE

4

@arshad3641,

Thanks for the sample code.

Could you also provide us your template file and output file here. Moreover, please also provide a sample file in which you could accomplish the task in MS Excel manually via data sorting options in MS Excel and save the file. We will check if we could do the same via Aspose.Cells APIs.

Thank you.

5

Hi Amjad,

There’s no way in excel that i can create a custom Order list as
USA,US
INDIA,IN
DUBAI,UAE

It always takes as
USA
US
INDIA
IN
DUBAI
UAE

I was just excited if there’s any way we can do it in aspose .

In the mean How Can i be able to replace a comma in the whole column then sort it in a custom order than replace back again

for eg:
here since we have USA,US INDIA,IN
I want to Replace ‘,’ with ‘-’ then Custom Order as USA-US,INDIA-IN then Replace back whole column ‘-’ with ‘,’.is it possible

6

@arshad3641,

Well, you may try to use Find or Search Options provided by Aspose.Cells APIs to find the “,” in the cell’s string and replace it with “-” for your needs. See the following sample code for your reference:
e.g
Sample code:

		String filePath = "f:\\files\\Bk_customlist1.xlsx";

		Workbook workbook = new Workbook(filePath);
		String stringtofind = ",";
	    String stringtoreplace = "-";

	       
	    Worksheet worksheet = workbook.getWorksheets().get(0);
	    Cells cells = worksheet.getCells();
	    Cell cell;
	    Cell prevcell = null;
	            
	          FindOptions opts = new FindOptions();
              opts.setLookAtType(LookAtType.CONTAINS);
              opts.setLookInType(LookInType.VALUES);
              opts.setCaseSensitive(false);	                
             //Scan in D column (D1:D11) 
            CellArea ca = new CellArea();
      		ca.StartRow = 0;
      		ca.StartColumn = 3;
      		ca.EndRow = 10;
      		ca.EndColumn = 3;

      		opts.setRange(ca);	
      		

	            do {
	                              
	                cell = cells.find(stringtofind, prevcell, opts);

	                if (cell == null)
	                    break;

	                String val = cell.getStringValue();

	                val = val.replace(stringtofind, stringtoreplace);

	                cell.setValue(val);
	                prevcell = cell;

	            } while (cell != null);

	        
	        workbook.save("f:\\files\\out1.xlsx");

Hope, this helps a bit.

7

@arshad3641

We will add another method like below to support your needs and your issue will be resolved by it.

public void addKey(int key, SortOrder order, String[] customList)

8

Would be very Helpful if so. thanks alot

9

When will be this available?

10

@arshad3641

Please wait till Tuesday, 12-Sep 2017, we are hopeful, you will get a fix in this time-frame. And if you don’t get it, then ask us for an update in this thread, we will then log your comment in our database.

1 Like
12

any updates for java

13

@arshad3641,

Please find attached the Java fix/version: Aspose.Cells for Java v17.8.4

14

Thanks So much Amjad.