Export to Excel with Performance

XypherXypher Member Posts: 297
edited 2014-04-24 in NAV Tips & Tricks
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. :D

(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. :wink:

Comments

  • SavatageSavatage Member Posts: 7,142
    I can't wait to give it a try :D
  • XypherXypher Member Posts: 297
    Let me know what you think :wink:
  • TimoBoTimoBo Member Posts: 30
    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);
    
  • alnaalna Member Posts: 5
    BTW: Can you compare the speed of your solution to "usual" solution?

    Do you think we can use the same technique to IMPORT data?
    Established at Y1989 Alna Business Solutions is a leading Microsoft Certified Gold Partner in Baltic States and Poland with over 250 finished projects in 30 countries.
    Ref: alnawapl
  • XypherXypher Member Posts: 297
    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.)
  • XypherXypher Member Posts: 297
    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.
  • MagnetoMagneto Member Posts: 18
    Importing/export of data - if you have 5.00, use RIM. :D
  • Alex_ChowAlex_Chow Member Posts: 5,063
    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. :)
  • XypherXypher Member Posts: 297
    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_ChowAlex_Chow Member Posts: 5,063
    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. :D
  • Ashoka-NAVAshoka-NAV Member Posts: 9
    Can anyone tell me how to use Xypher's code with dynamics nav.

    THank you
Sign In or Register to comment.