Excel Buffer Using Open XML Instead of Excel Automation (Part 1 of 2)

In Microsoft Dynamics NAV, there are several areas that enable the user to perform analysis in Microsoft Excel. Areas such as importing and exporting budgets, analysis by dimensions, and a number of selected reports all use Excel Buffer to export data to Microsoft Excel. In Microsoft Dynamics NAV 2013, the exporting technology has changed to gain better performance in the export as well as to enable more advanced customization capabilities. In Microsoft Dynamics NAV 2013, Excel Buffer has changed from being a chatty client automation solution to one that uses the Open XML SDK 2.0 for Microsoft Office and renders the Excel workbook on the server side.

This article explains some of the changes that were made to the Excel Buffer table (table 370) and gives examples of how to use and extend the table with the new Open XML functionality from C/AL.

Excel Buffer works as a temporary table which is used when you import or export data between Microsoft Dynamics NAV and Microsoft Excel. For the export part, you have an additional option to add formulas and formatting of the Excel cells.

When a Excel workbook is rendered, you use the File Management codeunit (codeunit 419) to download the file from the server to the client and then use Office .NET Interop to open it in Excel and do final formatting, such as Auto Fit Columns.

A couple of objects that use Excel Buffer are the reports Import Budget from Excel (report 81) and Export Budget to Excel (report 82). These objects are a good way of getting inspiration on how to use it. But in this article, I’m extracting some of this logic to give a simple and clear way of using import and export.

Example 1: Formatting and summarization

1. In the Microsoft Dynamics NAV Development Environment, create a new codeunit.

2. Add a new temporary record variable for table 370 called ExcelBuffer.

3. Add the following lines of code, which include formulas and simple formatting to the Excel Buffer table.

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.

// Add values to the Excel Buffer table.
ExcelBuffer.NewRow;
ExcelBuffer.AddColumn(‘Header’,FALSE,”,FALSE,FALSE,FALSE,”,ExcelBuffer.”Cell Type”::Text);

ExcelBuffer.NewRow;
ExcelBuffer.AddColumn(123.45,FALSE,”,FALSE,FALSE,FALSE,”,ExcelBuffer.”Cell Type”::Number);

ExcelBuffer.NewRow;
ExcelBuffer.AddColumn(-223.45,FALSE,”,FALSE,FALSE,FALSE,”,ExcelBuffer.”Cell Type”::Number);

// Add formula, second parameter TRUE.
ExcelBuffer.NewRow;
ExcelBuffer.AddColumn(‘SUM(A2:A3)’,TRUE,”,FALSE,FALSE,FALSE,”,ExcelBuffer.”Cell Type”::Number);

// Include custom format for the cell.
ExcelBuffer.NewRow;
ExcelBuffer.AddColumn(‘SUM(A2:A4)’,TRUE,”,FALSE,FALSE,FALSE,’#,#0.0;[blue](#,#0.0)’,ExcelBuffer.”Cell Type”::Number);

// Create and write the content from the Excel buffer table to Open XML Excel server file.
ExcelBuffer.CreateBookAndOpenExcel(‘Sheet ABC’,’Header’,COMPANYNAME,USERID);

4. After adding the lines, compile and run the codeunit from the Microsoft Dynamics NAV Development Environment. The Microsoft Dynamics NAV Windows client will open and execute the codeunit and Excel with the data from the codeunit. The formatting capabilities are shown in the Excel sheet, including summarization and coloring.

Example 2: Reading from Excel sheet

Before trying the following example, you need to save the Excel file from Example 1 in the following location: C:\TEMP\ExcelBufferReadBookScenario.xlsx.

This example will illustrate the reading capabilities that are possible.

1. Create a new codeunit.
2. Add a new temporary record variable for table 370 called ExcelBuffer.
3. Add a new text variable called MessageValue.
4. Add the following lines of code.

// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.ExcelBuffer.DELETEALL;

ExcelBuffer.OpenBook(‘C:\TEMP\ExcelBufferReadBookScenario.xlsx’,’Sheet ABC’);
ExcelBuffer.ReadSheet();

IF ExcelBuffer.FINDFIRST THEN
  REPEAT
    MessageValue := MessageValue + ExcelBuffer.”Cell Value as Text” + ‘\’;
  UNTIL ExcelBuffer.NEXT = 0;

MESSAGE(MessageValue);

5. Run the codeunit from the Microsoft Dynamics NAV Development Environment. The Microsoft Dynamics NAV Windows client opens and the content of the Excel workbook is now read into the Excel Buffer table and presented to the user in a message box for each row.