Microsoft Dynamics NAV 2017: ExcelBuffer => How can I add an AutoFilter function?

LievenD

I try to add a new function AutoFilter to ExcelBuffer (370).

attempt (without succes):

XlWrkBk := XlApp.ActiveWorkbook;
XlWrkSht := XlWrkBk.ActiveSheet;
XlWrkSht.AutoFilter;


Anyone ever written something similar and can help me out?
Thanks in advance!!!

Explore

To add an AutoFilter to Excel through Microsoft Dynamics NAV 2017, you need to be aware that the Excel COM objects and methods might not function exactly as they do in VBA.

Here is an example to help you add an AutoFilter to Excel. This is for the first column (A) of your Excel sheet:

PROCEDURE AutoFilter();
VAR
  XlApp: Automation "{000208D5-0000-0000-C000-000000000046}"; // Excel.Application
  XlWrkBk: Automation "{000208DB-0000-0000-C000-000000000046}"; // Excel.Workbook
  XlWrkSht: Automation "{000208D8-0000-0000-C000-000000000046}"; // Excel.Worksheet
  XlRange: Automation "{00020826-0000-0000-C000-000000000046}"; // Excel.Range
BEGIN
  CREATE(XlApp, FALSE, TRUE); 
  XlApp.Visible := TRUE;
  XlWrkBk := XlApp.Workbooks.Add;
  XlWrkSht := XlWrkBk.Sheets.Item(1);
  
  XlRange := XlWrkSht.Range('A1:A100');  // Or you can use XlWrkSht.UsedRange to apply to all used cells.
  XlRange.AutoFilter(1, 'Criteria', 0, 'Value', TRUE); // Modify 'Criteria' and 'Value' as per your needs.

  //The arguments of the AutoFilter function are:
  //Field: The offset of the first column is 1.
  //Criteria1: The criteria (a string). For example, "=".
  //Operator: The operator to be used (xlAnd, xlOr, etc). The default is xlAnd.
  //Criteria2: The second criteria (a string). For example, "100".
  //VisibleDropDown: TRUE displays the dropdown arrow for the AutoFilter. FALSE hides the dropdown arrow.
END;

Please replace "Criteria" and "Value" as per your requirements.

This script creates a new Excel instance, adds a Workbook to it, and then applies an AutoFilter to a range on the first Sheet of that Workbook. Please adjust the range and sheet according to your needs.

AlexDen

Hi,

You can add two new functions to Excel Buffer:

PROCEDURE AddAutoFilter(Value : Text);
BEGIN
    AutoFilterRange := Value;  // AutoFilterRange is a new global variable
END;

LOCAL PROCEDURE WriteAutoFilter();
VAR
    WrkShtHelper : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=10.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetHelper";
    XlAutoFilter : DotNet "'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.AutoFilter";
    StringValue : DotNet "'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.StringValue";
BEGIN
    IF AutoFilterRange = '' THEN
        EXIT;

    XlAutoFilter := XlAutoFilter.AutoFilter;
    XlAutoFilter.Reference := StringValue.StringValue(AutoFilterRange);
    WrkShtHelper.AppendElementToOpenXmlElement(XlWrkShtWriter.Worksheet, XlAutoFilter);

    AutoFilterRange := '';
END;

And modify WriteSheet function - call WriteAutoFilter from there:

      ...
      CRLF := 10;
      RecNo := 1;
      TotalRecNo := COUNT + TempInfoExcelBuf.COUNT;
      RecNo := 0;

      WriteAutoFilter();  // Add this line
      XlWrkShtWriter.AddPageSetup(OrientationValues.Landscape,9); // 9 - default value for Paper Size - A4
      ...

And then you can create autofilter like this:

xlBuf.NewRow;
xlBuf.AddColumn('Col1',FALSE,'',TRUE,FALSE,FALSE,'',xlBuf."Cell Type"::Text);
xlBuf.AddColumn('Col2',FALSE,'',TRUE,FALSE,FALSE,'',xlBuf."Cell Type"::Text);

xlBuf.AddAutoFilter('A1:B1');

xlBuf.NewRow;
xlBuf.AddColumn('Row1',FALSE,'',FALSE,FALSE,FALSE,'',xlBuf."Cell Type"::Text);
xlBuf.AddColumn(12,FALSE,'',FALSE,FALSE,FALSE,'',xlBuf."Cell Type"::Number);

xlBuf.NewRow;
xlBuf.AddColumn('Row2',FALSE,'',FALSE,FALSE,FALSE,'',xlBuf."Cell Type"::Text);
xlBuf.AddColumn(22,FALSE,'',FALSE,FALSE,FALSE,'',xlBuf."Cell Type"::Number);

xlBuf.CreateBookAndOpenExcel('','Sheet1','','','');