In AX 2012 it is often necessary to read Excel files.
There are multiple ways to do this. The most widely used method is to use the Microsoft Excel COM objects. But if the file is to be read at the Application server or if the client is running on a terminal server it will not work.
In such cases you can install Excel on the AOS or the terminal server. But the most administrators will not be happy with this.
But there is a way to read XLSX-Files without Excel objects just by using open XML.
What is open XML?
Open XML is the file format which is used to save files (e. g. Word, Excel …) in XML format. Basically it is just a zipped XML file. You can try to rename the file into .ZIP and decompress it. It would work …
https://de.wikipedia.org/wiki/Office_Open_XML
Use open XML API
Microsoft includes all needed objects in the .NET Framework. In AX we can use this objects to work with open XML files.
If you’re using .NET objects in AX be careful. Create a variable for each object you’re using:
Wrong:
DocumentFormat.OpenXML.Packaging.WorksheetPart[] worksheetParts;
worksheetParts = spreadSheetDocument.get_WorkbookPart().get_WorksheetParts();
Correct:
DocumentFormat.OpenXML.Packaging.WorkbookPart workbookPart;
DocumentFormat.OpenXML.Packaging.WorksheetPart[] worksheetParts;
workbookPart = spreadSheetDocument.get_WorkbookPart();
worksheetParts = workbookPart.get_WorksheetParts();
There is one more special thing which is called shared strings. Strings (or text) which are written in a cell are not stored in the cell itself. These strings are stored in a separate place and in the cell is just a reference to the shared string. This function should save additional space but it’s a bit tricky to read the strings.
If you use the first example, you will get errors while compiling the application with AXBUILD
The following example will read the first sheet in an Excel file. The lines are stored as container into a list. This list will be returned.
/// <summary>
/// Reads first sheet of an XLXS-file by using OpenXML
/// </summary>
/// <param name="_fileName">
/// Name of the file to read
/// </param>
/// <returns>
/// A list type of container
/// </returns>
/// <remarks>
/// The values in the container are all strings!
/// </remarks>
public static List readXLSX(str _fileName)
{
System.Exception ex;
str firstEx;
str fileName = _fileName;
str cellValueText;
System.String reference;
str ref;
str enumValueStr;
Map sharedStringMap;
List returnList;
container conLine;
CLRObject enumerator, enum, sstEnum;
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadSheetDocument;
DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart[] worksheetParts;
DocumentFormat.OpenXml.Spreadsheet.SheetData[] sheetDatas;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData;
DocumentFormat.OpenXml.Spreadsheet.Worksheet workSheet;
DocumentFormat.OpenXml.OpenXmlElementList elements, cells, sst;
DocumentFormat.OpenXml.OpenXmlElement element, cellelement, sstelement;
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.CellValue cellValue;
DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable;
DocumentFormat.OpenXml.Packaging.SharedStringTablePart sharedStringTablePart;
DocumentFormat.OpenXml.Spreadsheet.CellValues enumValue;
DocumentFormat.OpenXml.Spreadsheet.SharedStringItem sharedStringItem;
try
{
spreadSheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument::Open(fileName, false);
workbookPart = spreadSheetDocument.get_WorkbookPart();
worksheetParts = workbookPart.get_WorksheetParts();
sharedStringTablePart = workbookPart.get_SharedStringTablePart();
sharedStringTable = sharedStringTablePart.get_SharedStringTable();
sharedStringMap = new Map(Types::String, Types::String);
sst = sharedStringTable.get_ChildElements();
sstEnum = sst.GetEnumerator();
while(sstEnum.MoveNext())
{
sstelement = sstEnum.get_Current();
if(sstelement is DocumentFormat.OpenXml.Spreadsheet.SharedStringItem)
{
sharedStringItem = sstelement as DocumentFormat.OpenXml.Spreadsheet.SharedStringItem;
cellValueText = sharedStringItem.get_InnerText();
sharedStringMap.insert(int2str(sharedStringMap.elements()), cellValueText);
}
}
enumerator = worksheetParts.GetEnumerator();
if(enumerator.MoveNext())
{
worksheetPart = enumerator.get_Current();
}
if(worksheetPart == null)
{
error("no worksheetPart");
return null;
}
workSheet = worksheetPart.get_Worksheet();
elements = workSheet.get_ChildElements();
enumerator = elements.GetEnumerator();
while(enumerator.MoveNext())
{
element = enumerator.get_Current();
if(element is DocumentFormat.OpenXml.Spreadsheet.SheetData)
{
sheetData = element as DocumentFormat.OpenXml.Spreadsheet.SheetData;
break;
}
}
if(sheetData == null)
{
error("no sheet data");
return null;
}
elements = sheetData.get_ChildElements();
enumerator = elements.GetEnumerator();
while(enumerator.MoveNext())
{
element = enumerator.get_Current();
if(element is DocumentFormat.OpenXml.Spreadsheet.Row)
{
row = element as DocumentFormat.OpenXml.Spreadsheet.Row;
cells = row.get_ChildElements();
enum = cells.GetEnumerator();
cellValueText = "";
conLine = conNull();
while(enum.MoveNext())
{
cellelement = enum.get_Current();
if(cellelement is DocumentFormat.OpenXml.Spreadsheet.Cell)
{
cell = cellelement as DocumentFormat.OpenXml.Spreadsheet.Cell;
cellValue = cell.get_CellValue();
reference = cell.get_CellReference();
ref = reference.ToString();
enumValue = cell.get_DataType();
if(enumValue)
{
enumValueStr = enumValue.ToString();
}
else
{
enumValueStr = "";
}
if(enumValueStr == "s")
{
cellValueText = cellValue.get_InnerText();
if(sharedStringMap.exists(cellValueText))
{
cellValueText = sharedStringMap.lookup(cellValueText);
}
}
else
{
cellValueText = cellValue.get_InnerText();
}
conLine += cellValueText;
}
}
if(!returnList)
{
returnList = new List(Types::Container);
}
returnList.addEnd(conLine);
}
}
return returnList;
}
catch
{
ex = CLRInterop::getLastException();
if(ex != null)
{
firstEx = ex.ToString();
ex = ex.get_InnerException();
if(ex != null)
{
error(ex.ToString());
}
else
{
error(firstEx);
}
}
return null;
}
}
