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

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

Rate This
  • Comments 3

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.

-Lars-Bo Christensen

Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post
  • Very interesting!

    However I wonder how it deals with different Excel versions.

    The problem that exists with excel automation is that sometimes users have different versions of excel which you have make code around so the user uses the correct automation depending on which excel version it uses.

    How does this version work if the server and the user has different versions of excel?

  • Great question! One of the main reasons that we moved the document generation to the server was precisely the automation difficulties. The Open XML file format allows us to be independent of Excel versions and provides a way to avoid Excel automation when the document is created. On the server, the Excel Buffer writes to a file based on a set of rules that are defined by the Open XML file format for spreadsheets. When complete, the file is downloaded to the client and opened using the version of Excel that is present on the client computer.

    As long as you have a version of Excel that supports the Open XML file format, you can open any server-generated Open XML spreadsheet.

    If you want to know more about the Open XML file format, here’s the link to more information on the ECMA-376 standard: msdn.microsoft.com/.../gg607163(v=office.14).aspx.

    -Steffen Balslev

  • Thanks for this guide.

    The first part about creating an excel works perfectly.

    However when running the 2nd codeunit "Reading from Excel sheet" it gives this error:

    quote

    This message is for C/AL programmers: A call to

    Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookReader.O

    pen failed with this message:

    C:\TEMP\ExcelBufferReadBookScenario.xlsx

    /quote

    I'm using nav 2013.

    Any thoughts? I've also tried using other file paths, resulting in the same error message.

Page 1 of 1 (3 items)