Tuesday 22 December 2015

Read/Write Excel files from AOS using OpenXML

Read/write of Excel files on the client tier is well documented and it often requires Office to be installed on the machine. There are wrappers available in AX as well. However if you want to do the same operations on the server tier, options are limited. And as most administrators don't want to install office on the AOS, this further limits the options available.

One solution it to use Office OpenXML SDK. Microsoft has already added the required DLLto AX and can be found in References node in the AOT as shown below.


There are wrapper classes around this DLL as well.


As you can see from the name suffix these classes were written for Russian localisation. They work very well on the client tier. However on the server tier these classes have an issue as these uses methods from another class "ComExcelDocument_RU" which is marked to run on Client. Hence any use of these classes on server tier will result in error.

An easy work around is to copy the methods to the class OXMLSpreadsheetDocument_RU and replace ComExcelDocument_RU with OXMLSpreadsheetDocument_RU in the rest of classes. Below is screenshot of modified class.


Once changed, these classes can be used on the server tier.

Note: In AX 2012 R2 (and possibly in other versions) the class OXMLSpreadsheetDocument_RU only opens an excel file, It does not create one. There are two possible solutions to this.

1. Modify the class to create a new excel document.
2. Save an empty excel file as a "Resource" in AOT. And every time you need to write data to excel file, save the resource as file and use OXMLSpreadsheetDocument_RU for writing data in it.

This posting is provided "AS IS" with no warranties. Use code at your own risk.

1 comment:

  1. Thanks for this post,

    I'm struggling to create a simple working example of this.

    Would it be possible to post a small X++ example that creates a simple Excel file with one sheet and data in the first cell ?

    Thanks

    ReplyDelete