Filtering on multiple columns

Aspose.Cells Product Family
1

Hello
I have the following situation: I want to auto filter a sheet on two columns using the API, the ColumnA by value A and ColumnB by value B. I didn’t found a solution to do this, but only on a column, as shown in Data Filter in the example with the Bananas.

Thanks for any help
Calin

2

Hi,

Thanks for considering Aspose.

Did AutoFilter.Filter() method not perform your task. Could you elaborate your requirement. You mean you want to perfrom autofilter separatedly on both columns. I don't think it is possible using any direct way in MS Excel (97-2003) to do that, If you feel possible, could you create a sample spreadsheet and post it here, we will try to mimic the output and implement it.

Thank you.

3

Hi,

Thanks for the quick answer. I attacehd a sample spreadsheet, with the feature I’m looking for.
As you can see in the sample I want to filter the letters column by value A and the numbers column by value 2.

Thanks for help
Calin

4

Hi Clin,

Thanks for the template file.

Now we undestand your demand. We will support this feature for Autofiltering soon.

Thank you.

5

Hi

Thank you for the support.
Do you know a timeframe when to expect this feature?

Regards
Calin

6

Hi Calin,

Please try the attached fix as we have added the feature for your need.

And kindly consult the following sample code which works fine:

Aspose.Cells.Workbook wb=new Aspose.Cells.Workbook();
wb.Open("d:\\test\\muticolfilterbook.xls");
Worksheet sheet = wb.Worksheets[0];
//Create an AutoFilter object.
AutoFilter autofilter1 = sheet.AutoFilter;
autofilter1.Range = "A1:B1";
autofilter1.Filter(0,"A");
autofilter1.Filter(1,"2");
wb.Save("d:\\test\\filtereddata.xls");
Thank you.
7

Hi

Works like a charm.
Thank you.


I see that this version is not released yet. Do know when this fix will be in a release version, because we want to deliver an application in production in 2 months and we have the Aspose.Total license, and we want to use this fix.

Thank you again
Best regards
Calin

8

Hi Calin,

Well, you may use this fix without any problem. Our next official release for public is expected within 1-2 weeks time.

Thank you.

9

Hello



I have two additional questions:



1. after applying an autofilter, how can I access the resulted filtered cells?

2. how can I get the DISTINCT values of a column?



Thank you

Calin

10

Hi,

Thanks for considering Aspose.

We will get back to you soon.

Thank you.

11

Hi Calin,

1, We do not support this feature. We will look into it soon.

2, Please try the following codes:

int columnIndex = 0;
int maxDataRow = cells.MaxDataRowInColumn(columnIndex);
Hashtable hashTable = new Hashtable();
Cell cell = null;
object cellValue = null;
for (int i = 0; i <= maxDataRow; i++)
{
int checkIndex = cells.CheckExistence(i, columnIndex);
if (checkIndex != -1)
{
cell = cells[checkIndex];
cellValue = cell.Value;
if (cellValue != null && hashTable[cellValue] != null)
{
hashTable.Add(cell.Value, cell.Value);
}

}
}

12

Hello Warren

Thanks for the code from point 2. I already was in that direction but your code gave me some tips.
Regarding the first issue, I managed to make a workaround like this
1. Create a range with the data to be filtered
2. Export the range to a DataTable object
3. Filter the DataTable using the DafaultView.RowFilter feature of this class

It works fine, but of course it would be nicer to have access to the auto filtered rows directly with Aspose.Cells with a feature.

Thank you guys for the nice job you are doing there
Calin

13

Hi Calin,

We will add a method to support this feature soon.

In the current version, we only do it when we save the file.So you will see some rows are hidden in the saved file.