Autofilter for a table containing data validation lists

Aspose.Cells Product Family
1

Dear Laurence,



When using your new autofilter functionallity we are experiencing
problems with tables containing data validation lists. Most of the data
validations lists are turned into autofilters. See attached file.



Thanks and best regards,

Ulf

2

Hi Ulf,

Could you post your template file here and tell me which validation is turned into autofilters? And your sample code to set autofilter is also helpful to figure out this problem.

Thank you very much.

3

Dear Laurence,



In this case we are not using any template file. The file is completely
generated with Aspose and therefore both the validation lists and the
autofilters are created by Aspose. The following code is used to create
the autofilters:



AutoFilter filter = sheet.getAutoFilter();

filter.setRange(new CellArea(startRow, startCol, endRow, endCol));

filter.showAll();



Where the range is the entire single bids table.



Thanks,

Ulf

4

I forgot to tell you about the changed cells…Look in the U column in
the single bids sheet. For example cell U5 and U6 contains autofilters
instead of data validations. I’m viewing the file with excel 9.



Regards,

Ulf

5

Hi,

Please try this fix.

6

Warren,



The file you sent me throws the following exception for a lot of files:



com.aspose.cells.ExcelException: Can not find Msodrawing.

at com.aspose.cells.ao.a(Unknown Source)

at com.aspose.cells.ao.b(Unknown Source)

at com.aspose.cells.ao.a(Unknown Source)

at com.aspose.cells.Excel.open(Unknown Source)

at com.aspose.cells.Excel.read(Unknown Source)



The files are possible to read with the previous version of Aspose Cells.



Best regards,

Ulf

7

Hi,

We have fixed this bug. Please try this fix.

8

Dear Warren,

The fix removed the bug and it seems like autofilters now may coexist with data validations lists. Thanks a lot. However, there is a slight problem with the creation of autofilters. The first time we use a filter is seems like the 4:th item (ie, the first alternative based on the actual values in the column) is selected, but excel does not filter on that item. If we select that item again, nothing happens. However if we instead select any of the other alternatives they work fine and after that even the 4:th item works.

The problem seems to be that the excel file thinks that the 4:th item is selected from the beginning. We have tried the showAll method of your Autofilter class without any change.

Thanks,
Ulf

9

Dear Ulf,

Could you post your codes and the template file?

The bug of the showAll method has bean found.

We still do not find why the filter item doesn't work.It seems work fine.

10

Hi,

Please try this fix. The bug of the showAll method has bean fixed.

If the Auto filter is loaded from the file,the old setting of the filters will be removed after the filter method is used.

11

The problem still exists. When opening a file that has been given an autofilter by Aspose, it does not filter if I select the fourth item. It shows all rows anyway. After selecting any other item to filter by everything works perfect.

I have attached a sample file. If you open this file in Excel and select to filter by Test1 in the first column, nothing happens. I’m using Excel 2000 (9.0.6926 SP-3). However, if I open the file the latest version of Excel it works right away. So this is an issue only for old excel versions. I have tried to write the workbook using both FileFormatType.EXCEL2000 and EXCEL2003 and the problem exists for both cases (when opening the generated file with Excel 2000).

Thanks for looking into this.

Best regards,
Ulf

12

Hi,

Thanks for your response.We have located this problem with your help.