Tag: OfficeOpenXml

How to read excel file in Dynamics 365 F&O

Using OfficeOpenXml library you can read excel files. here is a runnable class to load the file using FileUploadBuild control, then read its content:

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class AZFileUpload
{
/// /// Runs the class with the specified arguments. ///
/// The specified arguments.
public static void main(Args _args)
{
System.IO.Stream stream;
ExcelSpreadsheetName sheeet;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
Dialog dialog = new Dialog("Import the data from Excel");    
    dlgUploadGroup          = dialog.addGroup("@SYS54759");
    formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.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 Package = new ExcelPackage(stream))
            {
                int                         rowCount, i;
                Package.Load(stream);
                ExcelWorksheet  worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);
                OfficeOpenXml.ExcelRange    range       = worksheet.Cells;
                rowCount                  = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;

                for (i = 2; i<= rowCount; i++)
                {
                    info(range.get_Item(i, 1).value);
                    info(range.get_Item(i, 2).value);
                }
            }
        }
        else
        {
            error("Error here");
        }

    }
}
}