Read excel file using x++ in Dynamics 365 F&O

 How to read excel file using x++ in Dynamics 365 Finance and operation

Below is the Runnable class we can use to read the excel file. 

internal final class TestReadExcelFile

{

    FileUploadTemporaryStorageResult        fileUploadResult;

    int                                     totalRows;

 

    /// <summary>

    /// Parm method for the file upload storage

    /// </summary>

    /// <param name = "_fileUplaodResult">File upload storage</param>

    /// <returns>File upload storage</returns>

    public FileUploadTemporaryStorageResult parmFileUploadResult(FileUploadTemporaryStorageResult   _fileUplaodResult = fileUploadResult)

    {

        fileUploadResult = _fileUplaodResult;

 

        return fileUploadResult;

    }

 

    /// <summary>

    /// Class entry point. The system will call this method when a designated menu

    /// is selected or when execution starts and this class is set as the startup class.

    /// </summary>

    /// <param name = "_args">The specified arguments.</param>

    public static void main(Args _args)

    {

        TestReadExcelFile       readExcelFile = new TestReadExcelFile();

        try

        {

            FileUploadTemporaryStorageResult    tempStorageResult = File::GetFileFromUser() as FileUploadTemporaryStorageResult;

 

            if (tempStorageResult && tempStorageResult.getUploadStatus())

            {

                readExcelFile.parmFileUploadResult(tempStorageResult);

                readExcelFile.readExcelFile();

            }

        }

        catch

        {

            Error("Error");

        }

    }

 

    /// <summary>

    /// Get the excel ranges to read the excel

    /// </summary>

    /// <returns>Excel range</returns>

    private OfficeOpenXml.ExcelRange getRange()

    {

        int                             firstRow, lastRow;

        System.IO.Stream                stream;

        OfficeOpenXml.ExcelPackage      excelPackage;

        OfficeOpenXml.ExcelRange        range;

        OfficeOpenXml.ExcelWorksheet    excelWorksheet;

 

 

        stream = fileUploadResult.openResult();

 

        excelPackage = new OfficeOpenXml.ExcelPackage(stream);

        excelPackage.Load(stream);

        //get the first worksheet. here you can specify the sheet name as well

        excelWorksheet = excelPackage.get_Workbook().get_Worksheets().get_Item(1);

        firstRow  = excelWorksheet.Dimension.Start.Row;

        lastRow   = excelWorksheet.Dimension.End.Row;

        totalRows = lastRow - firstRow + 1;

        range     = excelWorksheet.Cells;

        return range;

    }

 

    /// <summary>

    /// Read excel file

    /// </summary>

    public void readExcelFile()

    {

        int                     recordsUpdated;

        CustGroup               custGroup;

        OfficeOpenXml.ExcelRange range = this.getRange();

 

        //First row is header so we we reading the values from second row.

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

        {

           

            CustGroupId                custGroupLocal           = range.get_Item(i,1).Value;

            Description                custGroupName            = range.get_Item(i,2).Value;

 

            custGroup.clear();

            custGroup.CUstGroup = custGroupLocal;

            custGroup.Name      = custGroupName;

            custGroup.insert();           

        }

        Info(strFmt("Record %1 created", totalRows-1));

    }

 

}