Use Open XML to Extend the Excel Buffer Functionality (Part 2 of 2)

Use Open XML to Extend the Excel Buffer Functionality (Part 2 of 2)

  • Comments 0

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.

In the blog post Excel Buffer Using Open XML Instead of Excel Automation - (Part 1 of 2), I demonstrated simple write and read scenarios. This blog post will go into details on how you can use the Open XML API in C/AL to extend the Excel Buffer functionality.
 
I will show what can be done with some additional cell formatting. The following areas will be covered:

  • Exposing the Microsoft Dynamics NAV Open XML worksheet writer object in table 370.
  • Using the decorator parameter to provide additional formatting.
  • Creating an OpenXml Helper class to interact with the lack of support for generics in C/AL.
  • Showing the file management capabilities by download file to client from server, without opening it directly into Excel.

Exposing the Microsoft Dynamics NAV Open XML worksheet Writer Object in Table 370

You need to expose the workbook writer object from table 370 by adding the following method. In this way, you open for applying functionality from the extensive and verbose Open XML API. The easiest way is to export the Excel Buffer table (table 370), add the following procedure, and then reimport and compile the object.

PROCEDURE GetWorkbookWriter@21(VAR WrkBookWriter@1000 : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookWriter");
BEGIN
  WrkBookWriter := XlWrkBkWriter;
END;
 
Using the Decorator Parameter to Provide Additional Formatting

Due to a limitation in C/AL, you cannot use generics as part of the C/AL DotNet object type. Therefore, you need to create a helper class that can handle the places where the Open XML SDK only uses generics. This helps avoid using unnecessary, complex looping of collections in C/AL code. Often tasks like this are much easier to implement in a DotNet helper class. An example of this is described below where I have a simple method that can append an Open XML element to a collection.
 
To create the OpenXmlHelper class, do the following steps:
1. Open Visual Studio.
2. Create new C# class library project called OpenXmlHelper.
3. Ensure that signing is enabled on the project, as the assembly needs to have a strong name key.
4. Reference the DocumentFormat.OpenXml.dll from the Open XML SDK. The default installation is typically in the following location: C:\Program Files (x86)\Open XML SDK\V2.0\lib\DocumentFormat.OpenXml.dll.
5. Rename Class1 to OpenXmlHelper.
6. Add a using statement DocumentFormat.OpenXml to the file.
7. Create the method: AppendChild by copying the following code.

public static bool AppendChild(OpenXmlElement parent, OpenXmlElement child)
{
  if (parent.HasChildren && parent.Contains(child))
  {
    return false;
  }
  parent.AppendChild(child);
  return true;
}

8. Compile and place the OpenXmlHelper in an OpenXML Add-ins folder for the client and server. The default installation will point to the following locations: C:\Program Files (x86)\Microsoft Dynamics NAV\70\RoleTailored Client\Add-ins\OpenXML and C:\Program Files\Microsoft Dynamics NAV\70\Service\Add-ins\OpenXML.
9. Import the ExcelBuffer Extensibility codeunit (attached) and compile. This codeunit opens up to add decorations as well as download the file to the client.

The first part of the codeunit is the same as in Excel buffer that we used in the blog post Excel Buffer Using Open XML Instead of Excel Automation - (Part 1 of 2), but I have added the workbookwriter. In addition, instead of opening the file in Excel I use the FileMangement codeunit functionality to download the file from the server to the client, to show a different way of working with the generated file.
 
// Create new method on ExcelBuffer table to expose the WorkbookWriter.
ExcelBuffer.GetWorkbookWriter(WrkbookWriter);
 
// Call method that adds new font with a set of new characters.
AddingFont(FontDecorator);
 
// Add a new cell Formula using the new fontdecorator.
            WrkbookWriter.FirstWorksheet.SetCellFormula(4,'A','SUM(A2:A3)','#,#0.0;#,#0.0',FontDecorator);
 
ExcelBuffer.CloseBook;

// Download the Excel file from the server to client.
ExcelBuffer.UTgetGlobalValue('ExcelFile',FileServerName);
FileClientName := 'C:\Temp\ClientFile.xlsx';
FileMgt.DownloadToFile(FileServerName,FileClientName);
MESSAGE(FileClientName);
 
In the codeunit, there is also an example of how the AddingFont method can be used:

// Create new Font, cloned from existing Font.
Font := WrkbookWriter.FirstWorksheet.DefaultCellDecorator.Font.CloneNode(TRUE);
 
// Create a new Font Color.
FontColor := FontColor.Color;
FontColor.Rgb := FontHexValue.HexBinaryValue('0000EEEE');
Font.Color := FontColor;
 
// Create a new Font Size.
FontSize := FontSize.FontSize;
FontSize.Val := FontSizeDoubleValue.DoubleValue(30);
Font.FontSize := FontSize;
 
// Get the collection of Fonts that already exists.
Fonts := WrkbookWriter.Workbook.WorkbookPart.WorkbookStylesPart.Stylesheet.Fonts;
 
// Add the new font to the collection of fonts and increase the number of fonts by one.
IF OpenXmlHelper.AppendChild(Fonts,Font) THEN
Fonts.Count.Value := Fonts.Count.Value + 1;
 
// Add the Font to a decorator.
Decorator := WrkbookWriter.FirstWorksheet.DefaultCellDecorator;
Decorator.Font := Font;
 
Running the codeunit will save the file on the client in the following file: C:\temp\clientfile.xlsx. In the spreadsheet, the number is now formatted to a larger font and color.

From here, it's only up to your own imagination and needs for how you want to extend with more methods, and so forth.

The capability of the Open XML SDK API is your only limitation.

-Lars-Bo Christensen

Attachment: ExcelBuffer Extensibility.txt
Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post