Limitations of the Export to Excel functionality in AX 2012
Exporting data to Excel files is a very useful feature in AX 2012. It allows an easy way to export data from list pages into XLSX files.
When using this feature, some limitations need to be considered, as detailed below.
1. Excel has stricter limitations than data in a database, as mentioned in the following article: https://support.office.com/en-us/article/Excel-specifications-and-limits-CA36E2DC-1F09-4620-B726-67C00B05040F
Two hard limits are particularly relevant for AX, the maximum number of rows, 1,048,576, and the maximum number of columns, 16,384.
List pages exceeding these limitations will not be exported to XLSX.
2. In addition to the limitations of the output file format, AX 2012 is also bounded by the memory available to the AX Client process, AX32.exe, which is between 2GB and 4GB depending on the operating system.
The current implementation of AX 2012 requires all data to be cached in memory before the export in order to enable some business scenarios such as Remote Desktop connections.
The consequence of this implementation is that the maximum amount of data that can be exported to XLSX depends on the number of columns, the size of the values in each cell and the number of rows. Because of that variability we have never issued any guidance on the maximum number of rows supported.
To conclude, although the XLSX file format supports up to 1,048,576 rows, AX 2012 might only be able to export a smaller number depending on the actual contents.
In order to overcome this limitation, custom solutions based on the OpenXML SDK can be developed to write table data without caching it in memory. More information about the OpenXML SDK is available here: https://msdn.microsoft.com/en-us/library/office/bb448854.aspx
