Read Data from Excel file – Dynamics 365 for Finance and Operations – Excel Operations X++

Scenario : upload/Import the data using excel

Solution: Below is the code snippet to use for the data upload using excel

Note: SysExcel classes has been depreciated in the dynamics 365

Microsoft office interop reference is used in the dynamics 365. You can find that in the reference node of solution explorer.

 

Code :

 

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

class BEUploadExcel

{

    /// <summary>

    /// Runs the class with the specified arguments.

    /// </summary>

    /// <param name = “_args”>The specified arguments.</param>

    public static void main(Args _args)

    {

        

        System.IO.Stream            stream;

        

        FileUploadBuild             fileUploadBuild;

        DialogGroup                 dialogUploadGroup;

        FormBuildControl            formBuildControl;

        Dialog                      dialog = new Dialog(‘Import the data from the Excel’);

        dialogUploadGroup          = dialog.addGroup(‘@SYS54759’);

        formBuildControl        = dialog.formBuildDesign().control(dialogUploadGroup.name());

        fileUploadBuild         = formBuildControl.addControlEx(classstr(FileUpload), ‘Upload’);

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        fileUploadBuild.fileTypesAccepted(‘.xlsx’);

        if (dialog.run() && dialog.closedOk())

        {

            Fileupload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId(‘Upload’));

            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())

            {

                stream = fileUploadResult.openResult();

                using (ExcelPackage ePackage = new ExcelPackage(stream))

                {

                    int                         rowCount, i;

                    ePackage.Load(stream);

                    ExcelWorksheet  eWorksheet   = ePackage.get_Workbook().get_Worksheets().get_Item(1);

                    OfficeOpenXml.ExcelRange    eRange       = eWorksheet.Cells;

                    rowCount                  = eWorksheet.Dimension.End.Row – eWorksheet.Dimension.Start.Row + 1;

                    for (i = 2; i<= rowCount; i++)

                    {

                        info(eRange.get_Item(i, 1).value);

                        info(eRange.get_Item(i, 2).value);

                    }

                }

            }

            else

            {

               throw error(‘Error here’);

            }

        }

    }

}