Vendor invoices Excel import in D365FO using X++

[FormControlEventHandler(formControlStr(VendEditInvoice, VendInvoiceUpload), FormControlEventType::Clicked)]

    public static void VendInvoiceUpload_OnClicked(FormControl sender, FormControlEventArgs e)

    {

        System.IO.Stream            stream;

        ExcelSpreadsheetName        sheeet;

        FileUploadBuild             fileUpload;

        DialogGroup                 dlgUploadGroup;

        FileUploadBuild             fileUploadBuild;

        FormBuildControl            formBuildControl;

        FileUploadTemporaryStorageResult fileUploadResult = new FileUploadTemporaryStorageResult();

        Dialog                      dialog = new Dialog("@CGL:CGL000156");

        VendInvoiceInfoTable        vendInvoiceInfoTable, vendInvoiceInfoTableLocal ;

        VendInvoiceInfoLine         vendInvoiceInfoLine, vendInvoiceInfoLineLocal;

        VendInvoiceInfoSubTable     vendInvoiceInfoSubTable;

        VendInvoiceInfoSubLine      vendInvoiceInfoSubLine;

        EcoResCategory              ecoResCategory;

        InventDim                   inventDim;

        TransDate                   transDate   =   DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone());

        ProcCategoryItemTaxGroup    procCategoryItemTaxGroup;

        String30                    invoiceNumber, InvoiceNumberLocal;

        AccountNum                  invoiceAccount, invoiceAccountLocal;

        Description                 description;

        str                         businessUnit, costCenter;

        LedgerDefaultDimensionValueSet defaultDimension;

        HcmWorkerRecId                  categoryRecId, budgetManager;

        str                         categorycode;

        VendTable                   vendTable;

        TaxItemGroupHeading         taxItemGroupHeading;

        TaxItemGroup                taxItemGroup;

        TaxGroupHeading             taxGroupHeading;

        TaxGroup                    taxGroup;

        UnitOfMeasureSymbol         unit;

        UnitOfMeasure               unitOfMeasure;

       

        FormRun                     formTable = sender.formRun();

        FormDataSource              vendInvoiceInfoTable_ds = formTable.dataSource(formDataSourceStr(VendEditInvoice,VendInvoiceInfoTable)) as FormDataSource;

        vendInvoiceInfoTable        =   vendInvoiceInfoTable_ds.cursor();

       

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

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

                    try

                    {

                        ttsbegin;

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

                        {

                            invoiceAccount  =   range.get_Item(i, 2).value;

                            invoiceNumber   =   range.get_Item(i, 1).value;

                            description     =   range.get_Item(i, 4).value;

                            costCenter      =   range.get_Item(i, 8).value;

                            businessUnit    =   range.get_Item(i, 7).value;

                            categorycode    =   range.get_Item(i, 3).value;

                            taxItemGroup    =   range.get_Item(i, 10).value;

                            taxGroup        =   range.get_Item(i, 9).value;

                            unit            =   range.get_Item(i, 5).value;

                         

                            if(vendInvoiceInfoTable.RecId)

                            {

                           

                                vendInvoiceInfoLine.clear();

                                vendInvoiceInfoLine.initValue();

                                vendInvoiceInfoLine.TableRefId                  = vendInvoiceInfoTable.TableRefId;

                                vendInvoiceInfoLine.ParmId                      = vendInvoiceInfoTable.ParmId;

                                vendInvoiceInfoLine.ReceiveNow                  = range.get_Item(i, 4).value;

                                vendInvoiceInfoLine.InventNow                   = range.get_Item(i, 4).value;

                                vendInvoiceInfoLine.PurchUnit                   = unit;

                                vendInvoiceInfoLine.PurchPrice                  = range.get_Item(i, 6).value;

                                vendInvoiceInfoLine.CGLProcurementCategoryCode  = range.get_Item(i, 3).value;

                                vendInvoiceInfoLine.ProcurementCategory         = categoryRecId;

                                vendInvoiceInfoLine.InvoiceAccount              = vendInvoiceInfoTable.InvoiceAccount;

                                vendInvoiceInfoLine.OrderAccount                = vendInvoiceInfoTable.OrderAccount;

                                vendInvoiceInfoLine.InventDimId                 = InventDim::findOrCreate(inventDim).InventDimId;

                                vendInvoiceInfoLine.LineAmount                  = vendInvoiceInfoLine.ReceiveNow * vendInvoiceInfoLine.PurchPrice;

                                vendInvoiceInfoLine.CGLBudgetManager            = CGLVendorInvoiceHelper::getBudgetManager(categoryRecId);

                                vendInvoiceInfoLine.DocumentOrigin              = DocumentOrigin::Service;

                                vendInvoiceInfoLine.Ordering                    = DocumentStatus::Invoice;

                                vendInvoiceInfoLine.DeliveryName                = vendInvoiceInfoTable.DeliveryName;

                                vendInvoiceInfoLine.currencyCode                = vendInvoiceInfoTable.CurrencyCode;

                                if(businessUnit && !costCenter)

                                {

                                    vendInvoiceInfoLine.DefaultDimension        =   CGLVendEditInvoiceFormEventHandler::assignBusinessUnit(defaultDimension,businessUnit);

                                }

                                if(businessUnit && costCenter)

                                {

                                    vendInvoiceInfoLine.DefaultDimension        = CGLVendEditInvoiceFormEventHandler::assignDefaultDimensions(defaultDimension, costCenter, businessUnit );

                                }

                                if(taxGroup == '')

                                {

                                    vendInvoiceInfoLine.TaxGroup                = vendTable.TaxGroup;

                                }

                                else

                                {

                                    vendInvoiceInfoLine.TaxGroup                = taxGroup;

                                }

                                if(taxItemGroup == '')

                                {

                                    select TaxItemGroup from procCategoryItemTaxGroup

                                        where procCategoryItemTaxGroup.ProcurementCategory == ecoResCategory.RecId;

                                    vendInvoiceInfoLine.TaxItemGroup                = procCategoryItemTaxGroup.TaxItemGroup;

                                }

                                else

                                {

                                    vendInvoiceInfoLine.TaxItemGroup                = taxItemGroup;

                                }

                                if(vendInvoiceInfoLine.validateWrite())

                                {

                                    vendInvoiceInfoLine.insert();

                                }

                            }

                       

                        }

                        ttscommit;

                        info('@CGL:CGL000153');

                    }

                    catch (Exception::Error)

                    {

                        ttsabort;

                        throw error("@CGL:CGL000154");

                    }

                    }

                }

            }

           

        FormRun                 form = sender.formRun();

        FormDataSource          vendInvoiceInfoLine_ds = form.dataSource(formDataSourceStr(VendEditInvoice,VendInvoiceInfoLine)) as FormDataSource;

        vendInvoiceInfoLine_ds.research();

        vendInvoiceInfoLine_ds.refresh();