AutoFilterMode Exception

AutoFilterMode Exception

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

• 

  mth

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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…

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  mth

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  mth

  • Post Options:
  • Link
    Copy

  Posted 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!

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  mth

  • Post Options:
  • Link
    Copy

  Posted 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!)

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  mth

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 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.