Read Excel file in AX 2012 with open XML

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;
    }
}

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

*

code