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.