Filter and Sort

Screenshot (37).png (86.8 KB)

I want to filter the data where IsSame=“FALSE” from Row 5 onwards ,and paste it in a different worksheet.

I also want to sort the data based on Column IsSame and Row 5 onwards and paste the data in another sheet of the same workbook.

Can anyone please help me with the code snippet? I did not find enough examples to help me in solving this.

I am using Aspose Cells for Java

@sandipanghosh111,

Thanks for the details and screenshot.

Well, you may easily do that using data filters and data sorting features provided by Aspose.Cells APIs. I have created a code segment to accomplish a part of of your task using a template file (attached). The template file has the data as per your screenshot. I apply custom data filtering to the third column on your specified range of cells. After filtering data, I created the source range. Then I created a destination range in the other sheet and instantiated Paste Options so only visible cells should be copied from source range to destination range. I finally copied the source range to destination range with respect to Paste Options and saved the file. Please refer to it and write your own code for data filtering and data sorting for your needs:
e.g
Sample code:

// Opening the Excel file through the file path
		Workbook workbook = new Workbook("f:\\files\\Bk_filtersort1.xlsx");

		// Accessing the first worksheet in the Excel file
		Worksheet worksheet = workbook.getWorksheets().get(0);
		
		// Creating AutoFilter by giving the cells range
		worksheet.getAutoFilter().setRange("A5:D19");

		// Initialize filter for rows containing string "FALSE"
		worksheet.getAutoFilter().custom(2, FilterOperatorType.EQUAL, "FALSE");

		// Call Filter function to apply the filter
		//worksheet.getAutoFilter().filter(2, "FALSE");

		// Call refresh function to update the worksheet
		worksheet.getAutoFilter().refresh();
		
		//Create the source range to be copied
		Range sourcerange = worksheet.getCells().createRange("A5:D19");
				
		// Accessing the second worksheet in the Excel file
		Worksheet worksheet1 = workbook.getWorksheets().get(1);

		//Create a destination range.
		Range destrange = worksheet1.getCells().createRange("A1:D14");
		
		//Create the PasteOptions accordingly
		PasteOptions options = new PasteOptions();
		options.setOnlyVisibleCells(true);
		options.setPasteType(PasteType.VALUES);
		//options.setSkipBlanks(true);
		
		//Copy the range
		destrange.copy(sourcerange, options);
		
		// Saving the modified Excel file
		workbook.save("f:\\files\\out1.xlsx"); 

We also recommend you to see the document on how to do data sorting for your reference:

Moreover, see the document on how to apply custom data filtering for your further reference:

Hope, this helps a bit.
files1.zip (6.6 KB)

Thank you. It works perfectly fine.

@sandipanghosh111,

Good to know that it is useful to you. Feel free to write us back if you have further comments or questions, we will be happy to assist you soon.