Export to Excel with Performance

So I'm not too sure what all is capable in NAV 5.0 regarding Excel. But this is a little something I managed to do with 4.0, so I thought I'd share.

(If there is a better way, or simply if there is a way to pass an array to the excel Automation that I missed, please let me know.)


The idea is to pass an array of data containing a single rows' worth of data (rather than inserting each individual cell which will cause severe performance degradation). But as I see it you can't pass a 'SAFEARRAY' to the Automation through Navision (even if you could I'm not sure how messy it would be since Navision doesn't create 0 index arrays). So I went ahead and used 'Microsoft Script Control 1.0' to assist me with this. (And of course you are limited to 1024 data transfer.)

Here is the code (Using 'Microsoft Excel 12.0 Object Library'):

IF ISCLEAR(ExcelApp) THEN
  CREATE(ExcelApp);

{
  ExcelApp    Excel Application
  ExcelBook   Excel Workbook
  ExcelSheet  Excel Worksheet
}

ExcelApp.Visible(FALSE);
ExcelBook  := ExcelApp.Workbooks.Add;
ExcelSheet := ExcelBook.ActiveSheet;

valAlignLeft  := -4131;
valAlignRight := -4152;

//Header & Column settings all wrapped into an array
//[x,1] = Field No.
//[x,2] = Column Width
//[x,3] = Alignment
//[x,X] = Add whatever else you like if you wanted to.

{
  xlField    Integer  (Dimension: x;3)
}

xlField[1,1] := Table.FIELDNO("Store Number");
xlField[1,2] := 9;
xlField[1,3] := valAlignLeft;
xlField[2,1] := Table.FIELDNO("Store Name");
xlField[2,1] := 11;
xlField[2,3] := valAlignLeft;
xlField[3,1] := Table.FIELDNO("Total Sales");
xlField[3,2] := 8;
xlField[3,3] := valAlignRight;
xlField[4,1] := Table.FIELDNO("Sales Amount");
xlField[4,2] := 9;
xlField[4,3] := valAlignRight;
//etc...

varRecRef.GETTABLE(Table);

IF varRecRef.FINDFIRST THEN BEGIN

  //Sets entire document to Text format
  ExcelApp.ActiveCell.EntireColumn.EntireRow.NumberFormat := '@';

  //Just some settings to make it look better
  ExcelApp.ErrorCheckingOptions.NumberAsText := FALSE;
  ExcelApp.ErrorCheckingOptions.TextDate     := FALSE;

  //Create Header for each Column
  FOR i := 1 TO ARRAYLEN(xlField,1) DO BEGIN
    varFieldRef := varRecRef.FIELD(xlField[i,1]);
    ExcelApp.ActiveCell.Offset(0,(i-1)).Interior.ColorIndex              := 15;  //Gray
    ExcelApp.ActiveCell.Offset(0,(i-1)).Value                            := FORMAT(varFieldRef.NAME);
    ExcelApp.ActiveCell.Offset(0,(i-1)).ColumnWidth                      := xlField[i,2];
    ExcelApp.ActiveCell.Offset(0,(i-1)).EntireColumn.HorizontalAlignment := xlField[i,3];
  END;

  IF ISCLEAR(ScriptCtrl) THEN
    CREATE(ScriptCtrl);

  ScriptCtrl.Language('VBScript');

  CLEAR(varCellRow);
  CrLf[1] := 13;
  CrLf[2] := 10;

  REPEAT
    varCellRow += 1;
    CLEAR(scriptCode);
    ScriptCtrl.Reset;
    //Share Excel Object with the Scripting Object
    ScriptCtrl.AddObject('xl',ExcelApp);

    FOR i := 1 TO ARRAYLEN(xlField,1) DO BEGIN
      varFieldRef := varRecRef.FIELD(xlField[i,1]);
      scriptCode  += 'dat(0,' + FORMAT((i-1)) + ') = "' + FORMAT(varFieldRef.VALUE) + '"' + CrLf;
    END;

    ScriptCtrl.AddCode('SUB Insert()' + CrLf +
                       'DIM dat(0,' + FORMAT((ARRAYLEN(xlField,1)-1)) + ')' + CrLf +

                       scriptCode +

                       'xl.ActiveCell.Offset(' + FORMAT(varCellRow) + ',0).' +
                         'Resize(1,' + FORMAT(ARRAYLEN(xlField,1)) + ').Value = dat' + CrLf +

                       'END SUB');

    ScriptCtrl.ExecuteStatement('Insert'); //Execute the procedure!
  UNTIL varRecRef.NEXT = 0;

  IF NOT ExcelApp.Visible THEN
    ExcelApp.Visible(TRUE);
END ELSE
  ExcelApp.Quit;

Within the eyesore of code you should be able to locate, and hopefully understand, the simple concept of using the external script language to aid with this process.