Sunday, February 11, 2018

How to import Data From Excel into an AX Table using x++ in D365 FO

A very basic functionality needed nowadays to import data from Microsoft Excel but the way X++ has been redesigned including all the new functions for immense operations.
Now the SysExcelApplication and all other classes for Excel with "Sys" prefix is not used now.

Let's see how it gets worked out now :-



Step 1 :-  In your Dynamics 365 Operations Project. Add a new Class :-





Step 2 :-  Name your class























Step 3 :- Write the following code :-


class ImportExcelData extends RunBase
{
    str fileUrl;
    MyTable     myTb;
 
    public Object dialog()
    {
     
        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(ImportExcelData , uploadClickedEvent),

                                        this);

        return dialog;
    }

    private void uploadClickedEvent(FormButtonControl _formButtonControl)

    {
     
        FileUploadTemporaryStorageResult result = File::GetFileFromUser() as FileUploadTemporaryStorageResult;

        if (result && result.getUploadStatus())

        {

            result.getFileContentType();

            fileUrl = result.getDownloadUrl();

        }

    }

    public container  readExcelData(System.IO.Stream      _stream)

    {
        container conRow,conData;
        OfficeOpenXml.ExcelWorksheet _worksheet;

        OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(_stream);

        int iRowCount,iCellCount;

        anytype        anyData;

     

        try

        {

            if(package)

            {

                _worksheet = package.get_Workbook().get_Worksheets().Copy("Sheet1","ABC");

                var cells = _worksheet.get_Cells();

                iRowCount = _worksheet.get_Dimension().get_End().get_Row();



                iCellCount = _worksheet.get_Dimension().get_End().get_Column();



                for (int i=2;i<=iRowCount;i++)

                {

                    conRow = conNull();

                    for (int j=1;j<=iCellCount;j++)

                    {

                        anyData= cells.get_Item(i, j).get_Value();

                        if(!anyData && j ==1)

                           break;



                        if(anyData)

                           conRow += anyData;

                        else

                           conRow += "";

                     

                    }

                    if(conRow)

                    {

                        conRow += iRowCount;

                        conData = conIns(conData,i,conRow);

                    }

                 
                    myTb.Field1=cells.get_Item(i, 1).get_Value();
                    myTb.Field2=cells.get_Item(i, 2).get_Value();
                    myTb.Field3=cells.get_Item(i, 3).get_Value();
                    myTb.Field4=cells.get_Item(i, 4).get_Value();
                    myTb.Field5=cells.get_Item(i, 5).get_Value();
                    myTb.insert();
                 
                 
                     

                }
             
         
             

            }

         

        }

        catch (Exception::CLRError)

        {

            throw error("@SYS135884");

        }

        return conData;

    }

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

        }

    }

    public static void main(Args args)
    {
        ImportExcelData objimport=new ImportExcelData();

        if(objimport.prompt())
        {
            objimport.run();
        }
    }

}

2 comments:

  1. Thank you sharing this kind of noteworthy information. Nice Post.

    businessexceltemplates
    Technology

    ReplyDelete
  2. What is an Excel Workbook? An Excel workbook is a file used by Microsoft Excel to collectively organize worksheets, charts, graphs and other related Excel objects into one single location. There are many types of workbooks throughout the many different versions of Microsoft Excel. word translator

    ReplyDelete

How to reverse Free Text Invoice Voucher entries without Dialog

 Hey Folks ,  This blog post is in continuation of the previous post for Reversing Free Text Invoice Voucher entries with Dialog. Only diffe...