Aspose.cells AutoFilter : Ignore case during column filter

Aspose.Cells Product Family
1

Hi,
The data filter on excel works flawlessly but is there a way to ignore case during filter?.
The code that I am using to filter excel data is as below:

// completeBook is my workbook

AutoFilter autoFilter = completeBook.getWorksheets().get(0).getAutoFilter();
int maxColRange = completeBook.getWorksheets().get(0).getCells().getMaxDataColumn();
autoFilter.setRange("A1:" + CellsHelper.columnIndexToName(maxColRange) + "1");

int columnIndex = //some column index
String pattern= //is some filter string.

**autoFilter.filter(columnIndex, pattern);**  // I wish to make this filter case insensitive.

autoFilter.refresh();
workbook.save("someFileName");

The highlighted line above filters the data based on the pattern string, but this is case sensitive filter. I need something to make it ignore the case and filter the column.

Any pointers is much appreciated.

Regards
Vick

2

@vick_4444

Thanks for considering Aspose APIs.

Are you facing issue relating to filters because of some bug in Aspose.Cells? Filters are already case insensitive in Microsoft Excel. Please see this screenshot. As you can see, there are number of values but if we ignore their case, then there are only two values. I have attached the sample Excel file for your reference as well

Download Link:
Sample Excel File Having Filter.zip (5.9 KB)

Screenshot:

3

Hi Shakeel,
Thanks for the reply. My observation is as below:

1> When I match the case of string in filter with the value in cell, the column is filtered.
2> When I do not match the case of string in filter with the value in cell, the excel will have empty rows with the filter applied (as in no rows matched). BUT when I click the column header filter drop down in the excel file, the checkbox will be checked for the filter which was applied. And if I click “OK” on this filter window, the filter gets applied. Let me upload a test file, which will show no rows in the output excel file on filter if I give same string but in different case. Please open MyWorkSheetModified.xlsx and you will see no rows, however if you click the filter drop down on the column header , a checkbox is already checked and if you just click OK, the filter gets applied then.

Attached: ExcelFiles.zip (13.3 KB)

Refer: Source: MyWorkSheetOriginal.xlsx
Output: MyWorkSheetModified.xlsx

Output is filtered with “present” but the cells have “Present” so it will not show any rows,

Code used:

Workbook workbook = new Workbook("C:/2016/MyWorkSheetOriginal.xlsx");
	
AutoFilter autoFilter = workbook.getWorksheets().get(0).getAutoFilter();
int maxColRange = workbook.getWorksheets().get(0).getCells().getMaxDataColumn();
autoFilter.setRange("A1:" + CellsHelper.columnIndexToName(maxColRange) + "1");

autoFilter.filter(2, "present");
autoFilter.refresh();
workbook.save("C:/2016/MyWorkSheetModified.xlsx");

–vick

4

@vick_4444

Thanks for using Aspose APIs.

We were able to observe this issue and found the bug. We have logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-42502 - Aspose.Cells is not filtering data correctly instead it hides all the rows
5

Thank you for the update and for checking this issue. Much appreciated.

–vick

6

@vick_4444

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSJAVA-42502 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

7

The issues you have found earlier (filed as CELLSJAVA-42502) have been fixed in this Aspose.Cells for Java 18.2 update.

Please also check the following document/blog for your reference: