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

}

1 comment:

  1. Hi, How to make an excel as read only after exporting it through this code?

    ReplyDelete

I will appreciate your comments !

Download large bacpac (sandbox database) to DEV environment much faster

As the LCS website gets slower and slower and the database backups get bigger and bigger.  Use AZCopy to download objects out of LCS asset l...