Export to Excel with Performance

Xypher · 2008-06-24

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.

Savatage · 2008-06-24

I can't wait to give it a try

Xypher · 2008-06-26

Let me know what you think

TimoBo · 2008-07-02

Does this work with 'Microsoft Excel 11.0 Object Library'?

I'm trying to use your code in an Export report that exports data from several tables to Excel.

The problem is that the Excel file is empty after running the report.

This is an abbreviated sample of what i'm doing:

OnPreReport

IF ISCLEAR(Excel) THEN
  CREATE(Excel);

Excel.Visible(FALSE);
Book  := Excel.Workbooks.Add;
Sheet := Book.ActiveSheet;

Excel.ActiveCell.EntireColumn.EntireRow.NumberFormat := '@';
Excel.ErrorCheckingOptions.NumberAsText := FALSE;
Excel.ErrorCheckingOptions.TextDate     := FALSE;

IF ISCLEAR(ScriptCtrl) THEN 
    CREATE(ScriptCtrl); 

ScriptCtrl.Language('VBScript');

CrLf[1] := 13; 
CrLf[2] := 10;

OnAfterGetRecord Table Shop

CLEAR(ScriptCode); 
ScriptCtrl.Reset;
ScriptCtrl.AddObject('xl',Excel);
ScriptCode  += 'dat(0,' + FORMAT((0)) + ') = "' + FORMAT(Shop."No.") + '"' + CrLf;
.
.
.
ScriptCode  += 'dat(0,' + FORMAT((15)) + ') = "' + FORMAT(v_Month) + '"' + CrLf;

ScriptCtrl.AddCode('SUB Insert()' + CrLf + 'DIM dat(0,' + FORMAT(14) + ')' + CrLf + ScriptCode +
                   'xl.ActiveCell.Offset(' + FORMAT(i) + ',0).' + 'Resize(1,' + FORMAT(15) + ').Value = dat' + CrLf + 'END SUB');

i := i + 1;

OnAfterGetRecord Table PLU

CLEAR(ScriptCode); 
ScriptCtrl.Reset;
ScriptCtrl.AddObject('xl',Excel);
ScriptCode  += 'dat(0,' + FORMAT((0)) + ') = "' + FORMAT(PLU."No.") + '"' + CrLf;
.
.
.
ScriptCode  += 'dat(0,' + FORMAT((15)) + ') = "' + FORMAT(v_Month) + '"' + CrLf;

ScriptCtrl.AddCode('SUB Insert()' + CrLf + 'DIM dat(0,' + FORMAT(14) + ')' + CrLf + ScriptCode +
                   'xl.ActiveCell.Offset(' + FORMAT(i) + ',0).' + 'Resize(1,' + FORMAT(15) + ').Value = dat' + CrLf + 'END SUB');

i := i + 1;

OnPostReport

IF NOT Excel.Visible THEN
    Excel.Visible(TRUE);

alna · 2008-07-03

BTW: Can you compare the speed of your solution to "usual" solution?

Do you think we can use the same technique to IMPORT data?

Xypher · 2008-07-03

Oh geeze. I wrote my code from memory and not from example. It appears I had completely forgot to include a very important line of code. #-o

ScriptCtrl.ExecuteStatement('Insert');

(Edited & included in original post so as to see where to place the above line of code.)

Xypher · 2008-07-03

alna wrote:

BTW: Can you compare the speed of your solution to "usual" solution?

Do you think we can use the same technique to IMPORT data?

I created a test situation for the below data:

2,219 Records Each record contained 15 fields (Columns) of data to be Exported.
Passing an Array (Performance Method):

11:54:30.970 AM - Start Time 11:54:42.900 AM - Stop Time (12~ second difference.)

Cell by Cell (Conventional Method):

12:00:46.576 PM - Start Time 12:01:38.062 PM - Stop Time (52~ second difference.)

As far as importing data goes, I have unfortunately not looked into this as of yet.

Magneto · 2008-07-23

Importing/export of data - if you have 5.00, use RIM.

Alex_Chow · 2008-07-23

Would it be the fastest if you exported the file to .csv format then SHELL the output with Excel?

Just thinking out of the box here.

Xypher · 2008-07-23

I feel depending on another external source other than the MSXML automation is just increasing the chances of an accident/issue.

If I can create a solution without using any file manipulation, that is the route I take. Unless I absolutely need to.

From what I have seen, a lot of people tend to sway toward using files when a solution could benefit from such an action (Rather than spending a little more time to find a more local/controlled method). So from this I believe it is more of a trend than not; in reference to "thinking out of the box".

Aside from opinions, if you open a csv document in Excel you have quite an ugly document.

The code I have provided is also quite generic and could easily be incorporated into a Codeunit to be used for virtually all future Excel exports.

Alex_Chow · 2008-07-24

Xypher wrote:

I feel depending on another external source other than the MSXML automation is just increasing the chances of an accident/issue.

If I can create a solution without using any file manipulation, that is the route I take. Unless I absolutely need to.

From what I have seen, a lot of people tend to sway toward using files when a solution could benefit from such an action (Rather than spending a little more time to find a more local/controlled method). So from this I believe it is more of a trend than not; in reference to "thinking out of the box".

Aside from opinions, if you open a csv document in Excel you have quite an ugly document.

The code I have provided is also quite generic and could easily be incorporated into a Codeunit to be used for virtually all future Excel exports.

I see your point.

I was just referencing to the title "Export to Excel with Performance", you can't get any faster than export as text than SHELL to Excel. It's quick and dirty, but it's gets the work done.

Maybe the title should be "Export to Excel that is faster than how out-of-the-box Navision exports to Excel".

No harm intended.

Ashoka-NAV · 2014-04-24

Can anyone tell me how to use Xypher's code with dynamics nav.

THank you

Howdy, Stranger!

Quick Links

Categories

Export to Excel with Performance

Comments