Monday, November 27, 2017

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

}