Read excel through X++ in D365FO

Sample code to read an excel file, all it need file stream as a parameter.

public boolean importFromExcelfiles(System.IO.Stream _stream)

{

   OfficeOpenXml.ExcelWorksheet        worksheet;

   OfficeOpenXml.ExcelRange            cells;

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

       

   int totalRows,totalCells,rowCounter,cellCounter;

   if(package)

   {

      worksheet       = package.get_Workbook().get_Worksheets().get_Item(1);

      cells           = worksheet.Cells;

      totalRows       = worksheet.Dimension.End.Row ;

      totalCells      = worksheet.Dimension.End.Column;

      for (rowCounter = 2; rowCounter<= totalRows; rowCounter++)

      {

          for (cellCounter=1; cellCounter<=totalCells; cellCounter++)

          {

              //Do something with the values

              info(cells.get_Item(rowCounter, cellCounter).value);

          }

      }

   }

   return true;

}

This method can be called like this;

FileUploadTemporaryStorageResult    fileUploadResult;

fileUploadResult = FileUploadControl.getFileUploadResult();

public void importFromFile()

{

    if(fileUploadResult)

    {

        using(System.IO.Stream stream  = fileUploadResult.openResult())

        {

            Filename    filename;

            if(stream)

            {

                try

                {

                    filename = fileUploadResult.getFileName();

                    if(this.importFromExcelfiles(stream))

                    {

                        info(strFmt("File %1 has been imported successfully", filename));

                    }

                }

                catch(Exception::Error)

                {

                    warning("File import failure");

                }

            }

        }

    }

}