Import Excel Data through code in D365 f&o

To Import Data from Excel to D365 FO tables through JOB

Scenario: Too add additional data into invoice journal form based on invoice id , which is actually stored in custinvoicejour table

Steps:

Create excel file with data

Create JOB

Format:

Using System.IO;

Using OfficeOpenXml;
Using OfficeOpenXml.ExcelPackage;
Using OfficeOpenXml.ExcelRange;
class TestImportdatajob
{


    public static void main(Args _args)
    {
        
        System.IO.Stream stream;


        ExcelSpreadsheetName sheet;

        FileUploadBuild fileUpload,fileUploadBuild;

        DialogGroup dialogUploadGroup;
        Name                                   name;
        CustInvoiceJour                         custInvoiceJour;
        real                                    Softex;
        String30 softexNumber;
        CustInvoiceId    invoiceId;
        CurrencyCode     softexCurrency;
        AmountCur        softexAmtCur;
        AmountMST        softexAmtMST;
        TransDate        softexDate;


        FormBuildControl formBuildControl;

        Dialog dialog=new Dialog("Excel Import Example");

        dialogUploadGroup=dialog.addGroup("@SYS54759");

        formBuildControl=dialog.formBuildDesign().control(dialogUploadGroup.name());

        fileUploadBuild=formBuildControl.addControlEx(classStr(fileUpload),"UploadExcel");

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

        fileUploadBuild.fileTypesAccepted(".xlsx");

        if(dialog.run() && dialog.closedOk())

        {

            FileUpload fileUploadControl=dialog.formRun().control(dialog.formRun().controlId("Upload"));

            FileUploadTemporaryStorageResult
                fileUploadResult=file::GetFileFromUser(classStr(FileUploadTemporaryStorageStrategy));

            //fileUploadResult=fileUploadControl.getFileUploadResult();

            if(fileUploadResult!= null && fileUploadResult.getUploadStatus())
            {
                stream=fileUploadResult.openResult();

                using(ExcelPackage  package= new ExcelPackage(stream))
                {
                    int rowCount, iterator;
                    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;

                    ttsbegin;
                    for(iterator=2;iterator<=rowCount;iterator++)
                    {
                        invoiceId = range.get_Item(iterator, 1).Value;
                        softexNumber = range.get_Item(iterator, 2).Value;
                        softexDate      = range.get_Item(iterator, 3).Value;
                        softexCurrency  = range.get_Item(iterator, 4).Value;
                        softexAmtMST    = range.get_Item(iterator, 5).Value;

                        if(!softexNumber)
                        {
                            Softex = range.get_Item(iterator, 2).value;
                            softexNumber = int2str(real2int(Softex));
                        }

                        select forUpdate custInvoiceJour
                                where custInvoiceJour.InvoiceId == invoiceId;

                        if(!custInvoiceJour.InvoiceId)
                        {
                            throw error (strFmt("This %1 InvoiceId does not exist",invoiceId));
                        }

                        select softexNumber, invoiceId from custInvoiceJour where custInvoiceJour.SoftexNumber == softexNumber;
                        if(custInvoiceJour.SoftexNumber)
                        {
                        
                            throw error (strFmt("Softex no already exists on Invoice id %1 ",custInvoiceJour.InvoiceId));
                        }

                        if(!softexNumber)
                        {
                       
                            throw error(strFmt("Please enter softex no for invoice id %1 in line %2", invoiceid, iterator-1));
                        }

                        custInvoiceJour.SoftexNumber = softexNumber;
                        custInvoiceJour.SoftexDate      = softexDate;
                        custInvoiceJour.SoftexCur  = softexCurrency;
                        custInvoiceJour.SoftexAmount    = softexAmtMST;

                        custInvoiceJour.Update();
                    }

                    ttscommit;
                }

            }
        }
    }

}