Import data from excel file D365 FO - AX 7

Nghia Song -  Microsoft Dynamics 365 Technical Consultant

Nghia Song

Tel - WhatsApp: +84967324794

Email: songnghia.uit@gmail.com

Now Dynamics AX 365 is running on Web browser so import the data in AX using Excel, CSV, text etc. has been changed. FileName, FilenameOpen extended data type is no more supported to browse the excel file.

First step to import the excel file in AX , create a new class (TestExcelImport) and extend with RunBase and add a new button in dialog with upload caption (Same as AX 2012)

Object Dialog()    {        FormBuildButtonControl buttonControl;        DialogGroup            dlgGroup;        FormBuildGroupControl  buttonGroup;        dialog = super();                      dlgGroup       = dialog.addGroup('');        buttonGroup    = dialog.formBuildDesign().control(dlgGroup.formBuildGroup().id());        buttonControl  = buttonGroup.addControl(FormControlType::Button, 'Upload');        buttonControl.text("Upload file");        buttonControl.registerOverrideMethod(methodStr(FormButtonControl, clicked),                                         methodStr(TestExcelImport, uploadClickedEvent),                                         this);        return dialog;    }

And below is the upload button click event (click event already registered in above dialog method)

private void uploadClickedEvent(FormButtonControl _formButtonControl)    {        FileUploadTemporaryStorageResult result = File::GetFileFromUser() as FileUploadTemporaryStorageResult;        If (result && result.getUploadStatus())        {            result.getFileContentType();            //result.            fileUrl = result.getDownloadUrl();            info(fileUrl);        }    }

FileUploadTemporaryStorageResult is the class has been introduced in AX and this class is responsible to browse and upload the file on server (On File server, SharePoint or Database). And from here store the file path (for me fileUrl) in a variable to read the file later in run method.

Now next step to read the data from uploaded excel file:-

Public  void   run()    {        System.Byte[] byteArray;        System.IO.Stream     stream;        try        {            stream = File::UseFileFromURL(fileUrl);            this. readExcelData(stream);            //info("Done");         }        catch(Exception::Error)        {            info(strFmt("%1 %2",Exception::Error,fileUrl));        }    }

File class has been used to read excel data from the url (file location from server) and will return the data in stream. Now the task is to read excel Stream data in AX.

So to read stream data of excel file Microsoft have used ExcelPackage (EPPlus ), we can manipulate the file using this package (Create excel,create new worksheet , pivot table , design ,formatting etc.). Developer can access the classes of  EPPlus using OfficeOpenXml namespace. the below classes is responsible to read the data from stream.

       OfficeOpenXml.ExcelWorksheet;

       OfficeOpenXml.ExcelPackage;

ExcelPackage class is responsible to create  excel package from stream (can be file also in place of Stream), ExcelWorksheet class where the worksheet has been copied with data. And then looping the data of rows from the excel cells.

conData container variable declared in class declaration of class. So now enjoy the excel import in AX.

Note:-Import the data via using CSV or text file still easy and we can use CommaIo or Comma7Io.
Copied and edited: http://daxtechies.blogspot.com/2017/05/dynamics-ax7-d3fo-code-for-excel.html