How does the Export to Excel feature work under the hood?


One of the nice features of Dynamics AX 2009 is the Export to Excel that is available throughout the product and allows to content of a Grid to be exported to Microsoft Office Excel for further analysis. Sometimes however the feature does not work as expected. So for example Microsoft Office Excel is starting but the data from the Grid is not showing up.

The Export to Excel is not completely happening in the Dynamics AX Kernel but parts of the export logic are coded in X++:

  1. If you click at the button Export to Excel in any Form, the Dynamics AX Kernel is generating the list of data to be exported. The Dynamics AX Kernel takes here the formatted data as it is visible in the Grid.
     
  2. Next the Dynamics AX Kernel places this data in the Windows Clipboard, while the currently existing content of the Windows Clipboard is backed up.
     
  3. Then the Dynamics AX Kernel calls the X++ method performPushAndFormatting of the class SysGridExportToExcel. This method is instantiating Microsoft Office Excel using COM, pasting the data from the Windows Clipboard and applies some formatting to the inserted data.
     
  4. When the X++ part is finished the Dynamics AX Kernel places the original backed up content of the Windows Clipboard back.
     

The Windows Clipboard is playing a central role in the Export to Excel. Between the placing of the data in the Windows Clipboard and the retrieving of the data by Microsoft Office Excel several steps are happening. If for any reason the content of the Windows Clipboard is altered or replaced during this time the export will fail.

One cause of problems can be Add-Ins for Microsoft Office Excel. Add-Ins are loaded when Microsoft Office Excel starts and can manipulate the content of the Windows Clipboard before Dynamics AX had any chance to paste the content at a worksheet.

In order to find out what is happening during the export of data the Dynamics AX Debugger can be of a great help. Simply by putting a breakpoint at the beginning of the method performPushAndFormatting and checking the content of the Windows Clipboard while executing line after line.

–author: Alexander Lachner
–editor: Alexander Lachner
–date: 07/09/2009
Comments (2)

  1. Alex says:

    How would one use this functionality in X++?  I'd like to pass a temp table to this to export to excel with the lovely formatting.

  2. EMEAAXTec says:

    This feature hasn't been designed as a framework for people to use in their own customizations – the limitation is the kernel part that puts the data onto the clipboard in the first place, this isn't exposed for you to use in X++, so if you can manage that part with some of your own code (you might need to use managed code in X++ to do that) then you can use ClassesSysGridExportToExcel.performPushAndFormatting() to open excel and paste the data in and it'll format it nicely.

    If you're trying to do this take care when copying stuff to the clipboard – as we mention in the article above, what we do is first copy anything that's already on the clipboard off somewhere safe, so that we don't get that pasted in to Excel with our data – when you're doing this you may need to use different functions depending on what kind of data is already on the clipboard (in C++ this is true – we had a kernel issue that was caused by this if there was a bitmap on the clipboard when you try to export).

    Happy coding!

    /Tariq