Custom excel import in Dynamics 365 Finance and Operations

 

Hello. I welcome you to another development article where you will learn how can you develop custom excel import in dynamics 365 Finance and Operations. The custom import is used to import data from the Excel file into the dynamics 365 F&O environments. Today, we will develop a very basic import in which we import barcodes in an excel file onto a form in dynamics 365. 

Elements used for it.

Table (Table Type is TempDB)

A class

Action menu item

Menu extension

Table

Class

Code

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

class AA_CustomExcelImport

{

  #define.utility("Custom excel import")

 // Developed by syed amir ali - Date: XX-XX-XXX

 // declaration variables 

  Name              sku;

  AA_EcomBarcodeTMP        aaEcomBarcodeTMP;

  AA_EcomBarcode         aaEcomBarcode;

  InventItemBarcode        objInventItemBarcode;

  str               fileString,finalStatus,finalStatusFirst, finalStatusLast;

  int               row = 1,iTOCount ;

  Qty               iQuantity;

  NoYes              IsAnyErrorOccured;

  date              dlvDate;

  public static void main(Args _args)

  {

    AA_CustomExcelImport  customExcelImport = new AA_CustomExcelImport();

customExcelImport.importLogic();

    customExcelImport.importEcomBarcode();

  }

  public void importEcomBarcode()

  {

    try

    {

      int counterX=0;

Name existingSKU;

 ttsbegin;

      while select * from aaEcomBarcodeTMP

       order by sku Asc

      {

        select * from objInventItemBarcode

          where objInventItemBarcode.itemBarCode == aaEcomBarcodeTMP.SKU;

if(objInventItemBarcode)

        {

          if (existingSKU != aaEcomBarcodeTMP.SKU)

          {

           aaEcomBarcode.clear();

aaEcomBarcode.AASku = aaEcomBarcodeTMP.SKU;

aaEcomBarcode.insert();

 }

        }

        else

        {

          info(strFmt('Barcode %1 does not exist in dynamics',counterX));

        }

counterX++;

      }

      ttscommit;

      info(strFmt('Total %1 Records inserted',counterX));

 }

catch (Exception::Error)

    {

      ttsAbort;

      throw error('Error while inserting in the table.');

    }

}

public void importLogic()

  {

    System.IO.Stream        stream;

    ExcelSpreadsheetName      sheeet;

    FileUploadBuild         fileUpload;

    DialogGroup           dlgUploadGroup;

    FileUploadBuild         fileUploadBuild;

    FormBuildControl        formBuildControl;

    Dialog             dialog = new Dialog("Import the data from Excel");

    NumberSeq            NumSeq;

   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 + 1) - (worksheet.Dimension.Start.Row);

          for (i = 2; i <= rowCount; i++)

          {

            row++;

sku    = '';

          sku   = range.get_Item(i, 1).Text;

           if(!IsAnyErrorOccured)

            {

 aaEcomBarcodeTMP.initValue();

           aaEcomBarcodeTMP.SKU = sku;

           aaEcomBarcodeTMP.insert();

          }

          }

        }

      }

      else

      {

        throw error("Could not open file.");

      }

if(IsAnyErrorOccured)

{

        throw error('File contains errors. Import cancelled.');

    }

  }

  }

}

Action Menu Item

Change the name, label, Object properties, add the name of your custom class in Object property.

Menu Extension

Create a extension of RetailMain menu and add the sub-menu in it. Drap and Drop the action menu item in it.

Build the project.

Go to your environment -- Retail and Commerce -- Custom imports -- Ecom Barcode Import

Excel file looks like below fig.

Finally, your barcode is imported in dynamics 365 Finance and Operations from the Excel file.

Happy Learning