Problem with AutoFilter Multi-Range

Aspose.Cells Product Family
1

Hi,

we have a problem with the AutoFilter and setting a Multi-Range (I’d call it that way):

Say, we want to set AutoFilters for the following Ranges:

A1:C256
F1:H256
K1:L256

Usually this is represented through a string like "A1:C256;F1:H256;K1:L256"

When I set AutoFilter.Range to this string, I get an exception telling me the string was invalid.

Is there a way to achieve the required range definition for an AutoFilter, or is there a different way to specify a non-contiguous range of cells for an AutoFilter?

Regards

Kai Iske

2

Hi Kai,

Could you set autofilter to multi-range(as you called) in MS Excel? I found it was impossible. So you cannot set autofilter to multi-range in Aspose.Excel.

In your case, you can try “A1:K1”.

3

Laurence,

never mind, this does not seem to be my week Smile

Of course you can’t set a non-contiguous AutoFilter in Excel. I should have done the final step and select AutoFilter for my multi column selection. I have done now and see it.

Thanks

Kai

4

@Kai_Iske,
Aspose.Cells has replaced Aspose.Excel which is no more under development now. Aspose.Cells contains all the features in Aspose.Excel as well as supports latest features in different versions of MS Excel. We can filter data in a variety of ways using this new product as shown in the following sample code:

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Creating AutoFilter by giving the cells range of the heading row
worksheet.AutoFilter.Range = "A1:B1";

// Saving the modified Excel file
workbook.Save(dataDir + "output.out.xls");// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Creating AutoFilter by giving the cells range of the heading row
worksheet.AutoFilter.Range = "A1:B1";

// Saving the modified Excel file
workbook.Save(dataDir + "output.out.xls");

Here you will find detailed description of this feature and example codes to apply filter based on fill color, date, number, text etc.
Data Filtering

Download the latest version of this product from the following link for trials:
Aspose.Cells for .NET (Latest Version)

A detailed solution is available here which can be used for testing different features of this product.