Add multiple Excel worksheets to single Excel workbook – Dynamics NAV 2013

In Microsoft Dynamics NAV 2013 Excell Buffer table (370) changed from using Excel Automation variable to Open XML 2.0 DotNet interoperability classes. More information about that can be found on NAV Team blog in article Excel Buffer Using Open XML Instead of Excel Automation.

The one function that is needed, in real life, when exporting data to Excel workbook is to export data to the same Excel workbook but to different worksheets.

Let say you want to export Item and Item Ledger Entry table to single Excel workbook. I haven’t found any function in ExcelBuffer that does that – correct me if I am wrong.

So I decided to write function that enables just that, open single Excel workbook and just adds new worksheet one after another. In Excel Buffer table (370) I have created AddNewSheet function that receives only one parameter (SheetName).

AddNewSheet(SheetName : Text[250])
IF SheetName = '' THEN
  ERROR(Text002);

IF ISNULL(XlWrkBkWriter) THEN BEGIN
  CreateBook(SheetName);
  ActiveSheetName := SheetName; //first sheet activated
END ELSE BEGIN
  WHILE XlWrkBkWriter.HasWorksheet(SheetName) DO BEGIN
    IF NOT FirstIncrement THEN BEGIN
      SheetName := SheetName + '-01';
      FirstIncrement := TRUE;
    END ELSE BEGIN
      SheetName := INCSTR(SheetName);
    END;
  END;
  XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(SheetName);
  FirstIncrement := FALSE;
  //ActiveSheetName := SheetName; //last sheet activated
END;
WriteSheet('',COMPANYNAME,USERID);
DELETEALL;
ClearNewRow;

Additional variable that I have used is FirstIncrement (Boolean) – all other are global variables in Excel Buffer table.

Let me explain few key points in this code. First in line 5 we check if the WorkSheetWriter is initialized. If it is not initialized we call standard CreateBook function and set the ActiveSheetName to that “first” excel sheet.

In line 9 we check if there is sheet in workbook that has the same name as the one we have passed as parameter – which can happen in subsequent calls. If this is true then we append ‘-01’ so we can use INCSTR(SheetName) until we find unused SheetName – so we don’t overwrite data on existing sheet.

Then the main task of this function is executed  in line 17 when we append new worksheet to currently open workbook.

In line 19 I have commented out line of code that sets the last sheet that we call to be initially shown when we open Excel workbook. I really want the first worksheet to be shown but you might want it the other way.

Finally, we WriteSheet and delete all data currently in Excel Buffer.

When the code, that populates data in Excel Buffer for single sheet, is finished we have to call this function to write sheet and flush all the data so it is free for another sheet.

Also, there is a difference that we cannot use standard function CreateBookAndOpenExcel – because it is used for single worksheet case scenario – but we must “close” Excel manually by calling standard functions CloseBook, OpenExcel, GiveUserControl on Excel Buffer.

Test codeunit that simulates usage of this function follows.

OBJECT Codeunit 50000 Test AddNewSheet
{
  OBJECT-PROPERTIES
  {
    Date=27.08.14;
    Time=10:58:00;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemSheet;
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemLedgerEntrySheet;
            CreateItemSheet;
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemLedgerEntrySheet;

            ExcelBuff.CloseBook;
            ExcelBuff.OpenExcel;
            ExcelBuff.GiveUserControl;
          END;

  }
  CODE
  {
    VAR
      Item@1002 : Record 27;
      ItemLedgerEntry@1001 : Record 32;
      ExcelBuff@1000 : TEMPORARY Record 370;

    PROCEDURE CreateItemSheet@1();
    BEGIN
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION("No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION(Description)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION("Base Unit of Measure")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION(Inventory)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.NewRow;

      Item.RESET;
      IF Item.FINDSET THEN REPEAT
        Item.CALCFIELDS(Inventory);
        ExcelBuff.AddColumn(FORMAT(Item."No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item.Description),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item."Base Unit of Measure"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item.Inventory),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.NewRow;
      UNTIL Item.NEXT = 0;

      ExcelBuff.AddNewSheet(Item.TABLECAPTION);
    END;

    PROCEDURE CreateItemLedgerEntrySheet@2();
    BEGIN
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Item No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Posting Date")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Document No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION(Quantity)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Remaining Quantity")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Entry No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.NewRow;

      ItemLedgerEntry.RESET;
      ItemLedgerEntry.SETCURRENTKEY("Item No.","Posting Date");
      IF ItemLedgerEntry.FINDSET THEN REPEAT
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Item No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Posting Date"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Date);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Document No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.Quantity),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Remaining Quantity"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Entry No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.NewRow;
      UNTIL ItemLedgerEntry.NEXT = 0;

      ExcelBuff.AddNewSheet(ItemLedgerEntry.TABLECAPTION);
    END;

    BEGIN
    {
      ZORAN - Excel buffer AddNewSheet Test
    }
    END.
  }
}

I hope this helps someone and please feel free to comment.

 

Leave a Reply

Your email address will not be published. Required fields are marked *