Applying Autofilter to entire range once placed in Excel worksheet in .NET

Aspose.Cells Product Family
1

Hi!

I have a piece of code which takes a datatable (of varying size) and puts it in my workbook like so, beginning at cell A10:

sheet2.Cells.ImportDataTable(dtfiltered, true, "A10");

I'd now like to put an autofilter over the whole dataset - the number of columns and rows will always be different.

I figure I'd need to 'select region', find that range and then apply the filter. How would I approach it?

Thanks!

2

Hi,

Thanks for your question and considering Aspose.Cells for .NET.

Please see the following sample code and let us know if it is helpful.

Also see this documentation article for your reference.
Data Filtering and Validation

C#


//Creating a file stream containing the Excel file to be opened

FileStream fstream = new FileStream(“C:\book1.xls”, FileMode.Open);


//Instantiating a Workbook object

//Opening the Excel file through the file stream

Workbook workbook = new Workbook(fstream);


//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(“C:\output.xls”);


//Closing the file stream to free all resources

fstream.Close();



3

Thanks. I see how I can apply it once I know the range of the heading row - how would I do this given there are varying numbers of columns?


If this was excel, I’d select A10 and go ‘fill to right’ (eg hold down shift while tapping end then right cursor) to highlight all cells. Or I’d say ‘select region’ which highlights the contiguous data area. How do I do this in Aspose Cells so I can use that range to apply the filter?

Thanks!
4

Hi,


I think you may use get the last column index which contains data and calculate the last column name by using CellsHelper method dynamically for your needs, see the updated code for your reference.

Sample code:

//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(“e:\test\book1.xls”, FileMode.Open);
//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Creating AutoFilter by giving the cells range of the heading row
int maxcol = worksheet.Cells.MaxDataColumn;
worksheet.AutoFilter.Range = “A1:” + CellsHelper.ColumnIndexToName(maxcol) + “1”;
//Saving the modified Excel file
workbook.Save(“e:\test2\output.xls”);
//Closing the file stream to free all resources
fstream.Close();
5

Thanks - cellshelper looks exactly like what I need. I will give it a try!



Cheers