I’m using the AutoFilter API as follows:
Dim filterRange As Atring = “A2:” & sheet.Cells.GetAt(1, dt.Columns.Count - 1).Name
sheet.AutoFilter.Range = filterRange
The resulting Excel output shows what looks like an Excel AutoFilter, but it does not filter anything from the rows below the filter based on the selection I make in any of the filter drops downs (values from the rows below do show in their respective column filter drop downs). If I manually remove the AutoFilter and put a new one in its place via the Excel GUI, then the filter works appropriately. So, I know the problem is not with the Excel application itself, but with the dynamic file I’ve generated with Aspose.Excel.
Does anyone have any ideas about what is causing the AutoFilter I’m inserting server-side to not work on the client-side?
Any constructive feedback would be highly appreciated.
Could you email me the problem file? And which edition are you using? The latest hotfix of Aspose.Excel is 1.9.1.2.
Thanks
OK. I will send you the problem file via email. As best as I can determine, I’m running version 1.9.0.0, which I downloaded yesterday. Also, an update on the problem. Apparently the leftmost column (i.e.: Column A, or Column 0, depending on your cell referencing scheme of choice) does filter, but all other columns of the AutoFilter do not filter/work.
I got your file and will check this problem right now.
Thanks for your patience.
I’ve downloaded the latest hotfix, and have done some cursory testing. The autofilter is now working as I would expect it to. Thank you, once again, for your diligence and prompt turn-around. You guys are great!
Hi,
After some testing, I found a small bug for autofilter issue in previous hotfix and fixed it. Please re-download the hotfix again. Sorry for the inconvenience.
OK. I got the new hotfix and applied it to my current project. Seems to be working fine. Just out of curiosity, what was the “small bug” in the previous hotfix? Obviously, I didn’t catch it in my testing. I’d like to know so I can be more aware of the kinds of things I need to be looking out for in my own testing practices.
Thank you, once again.
In previous fix, if you set autofilter on A2 to P2, all worked fine. But if the start cell is not on the first column, you will find last cell of the autofilter may not take effect. That’s a misprint in my program which causes this bug. When I re-checked my code, I found this problem.
To best support our customers, sometimes we release fix just for the specific user. But to assure the quality, before releasing a public hotfix, we will do more testing and reviews on our code.
Ah, yes. I definitely would not have caught that problem in my testing because my standard practice with spreadsheets is to always start filtering on the absolute left-most column (Column zero, or A), and stop at the right-most column that I know has data in it. In other words, my identifier info is to the left (which are the attributes the user is most likely to filter on) , and aggragable values to the right (which users are less likely to filter).
@BIXIHJ,
Aspose.Excel is discontinued and no more development is done for this product. It is replaced with a new product Aspose.Cells which is far better than the previous product. This new product also supports auto filtering the data 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");
You may get more details on this topic here:
Data Filtering
Download the latest product here:
Aspose.Cells for .NET (Latest Version)
A ready to run solution containing working examples can be downloaded here.