AutoFilterMode Exception

Posted by: mth on 8 July 2020, 9:24 am EST

  • Posted 8 July 2020, 9:24 am EST

    I would like to add an automatic filter on a generated file where the first row is the header and the rest is data.

    This seems to be supported but when I try this:

    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];
    worksheet.AutoFilterMode = true;


    I get this exception:

    System.NotSupportedException: Can't set the value to true.
    at at GrapeCity.Documents.Excel.Worksheet.set_AutoFilterMode(Boolean value)

    How can I accomplish this?

    I do not want to apply filters I just want the autofilter dropdowns to be there when I open the file...
  • Replied 8 July 2020, 7:33 pm EST

    Hello Mathieu,

    The AutoFilter dropdown is shown when the table is used in the Excel. For this you need to use the code snippet given below:

    Workbook wb = new Workbook();
    IWorksheet worksheet = wb.Worksheets[0];
    worksheet.Tables.Add(worksheet.Range["A1:E5"], true);


    And you can use the ShowAutoFilter property of the ITables class to handle the display of AutoFilter
    worksheet.Tables[0].ShowAutoFilter = false;

    If you need any other help, please let us know.

    Regards,
    Prabhat Sharma.
  • Replied 9 July 2020, 6:09 am EST

    Is there a method that do this automatically with the data currently in the worksheet?

    It would be convenient for cases where it a simple table with header and rows.
  • Replied 9 July 2020, 7:34 am EST

    Also do you have a method that allows to freeze the first row so it always stays on top when a user scroll down the file? This is available in Excel but I can't seem to find a method in the API to do this.

    Thanks!
  • Replied 9 July 2020, 3:05 pm EST

    Hello,

    You can use the Add method of ITables interface to add the table in the worksheet and specify the range of the table. It will put all the data under that specified range into the table.
    https://www.grapecity.com/documents-api-excel/docs/online/CreateDeleteTables.html

    You can use the FreezePanes method of the IWorkSheet interface to freeze the desired range:
    https://www.grapecity.com/documents-api-excel/docs/online/FreezeThePaneInTheWorksheet.html

    If you need any other help, please let us know.

    Regards,
    Prabhat Sharma.
  • Replied 10 July 2020, 3:52 am EST

    Awesome.
    Last question is it possible to specify which version of excel we are saving to?
    (best support I've seen so far. Thank you for your help!)
  • Replied 12 July 2020, 4:14 pm EST

    Hello Mathieu,

    Thank you for your appreciation.
    >>Last question is it possible to specify which version of excel we are saving to?
    If you meant to save the excel file to be able to open only in specified Excel versions like 2003,2007, 2013, and so on, then it is not possible.

    Regards,
    Prabhat Sharma.
  • Replied 13 July 2020, 3:56 am EST

    I guess that the current output is backward compatible with the older Excel versions...?
    When we create a file with your library which versions of Excel can open it?

    Thank you.
  • Replied 14 July 2020, 7:13 pm EST

    Hello Mathieu,

    We are asking the developers for the information and will let you know soon.

    Regards,
    Prabhat Sharma.
  • Marked as Answer

    Replied 16 July 2020, 4:53 pm EST

    Hi Mathieu,

    As per the developers: GcExcel's saved file can be opened with Excel 2007 and all the later versions.

    Regards,
    Prabhat Sharma.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels