Kayıtları excelden içeri aktarma | Dynamics Ax

Exceldeki verilerinizi direk olarak kendiniz seçebileceğiniz aşağıdaki job örneği işinizi kolaylaştırabilir.

void CreateLedgerBudgetFromExcel()
{
    #Excel
    #AviFiles

    Dialog              dialog = new Dialog("@ETG83");
    DialogField         dialogPath;
    FilenameFilter      filter = ['Excel files 2010','*.xlsx','Excel files 2003', '*.xls' ];
    FilenameOpen        filePath;
    SysExcelApplication application;
    SysExcelWorkBooks   workBooks;
    SysExcelWorkBook    workBook;
    SysExcelWorkSheets  workSheets;
    SysExcelWorkSheet   workSheet;
    SysExcelCells       cells;
    SysOperationProgress progress;
    Int                 NoofSheet,i,lastRow,sn;

    LedgerTable         ledgerTable;
    LedgerBudget        ledgerBudget;

    SysExcelRange range;

    void findLastRow(SysExcelWorkSheet _workSheet)
    {
        #define.Star('*')
        #define.Space(' ')

        ;

        range = _workSheet.cells().range(#ExcelTotalRange);

        try
        {
            // Finds the row where the first contents is found.
            range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
        }
        catch (Exception::Error)
        {
            error("@SYS59926");
        }

        if (range)
        {
            lastRow = range.row();
        }
        else
        {
            lastRow = 0;
        }
    }
    ;

    dialog.filenameLookupFilter(filter);
    dialogPath  = dialog.addField(typeid(FilenameOpen));


    if (dialog.run())
    {
        if(dialog.closedOk())
        {
            filePath            = dialogPath.value();
            application         = SysExcelApplication::construct();
            application.visible(false);
            workBooks           = application.workbooks();
            workBooks.open(filePath,0,true);

            workSheets          = workBooks.item(1).worksheets();
            noOfSheet           = worksheets.count();
            progress = new SysOperationProgress();
            progress.setCaption("Vendor Importing");
            progress.setTotal(lastRow);
            progress.setAnimation(#AviTransfer);
            setprefix("Vendor Import");

            try
            {
                For( i = 1; i <= noOfSheet; i++)
                {
                    workSheet       = workSheets.itemFromNum(i);
                    if(workSheet)
                    {
                        findLastRow(workSheet);
                        for(sn = 2;sn <= lastrow;sn++)
                        {
                            ttsbegin;

                            cells                   = workSheet.cells();

                            ledgerBudget.clear();
                            cells = workSheet.cells();

                            ledgerBudget.ModelNum           = cells.item(sn,1).value().bStr();
                         //   ledgerBudget.RevisionDate       = cells.item(sn,2).value().date();
                            ledgerBudget.AccountNum         = cells.item(sn,2).value().bStr();

                            ledgerTable = LedgerTable::find(cells.item(sn,3).value().bStr());
                            if(ledgerTable)
                            {
                                ledgerBudget.TaxGroup       = ledgerTable.TaxGroup;
                                ledgerBudget.Comment        = ledgerTable.AccountName;
                            }

                            ledgerBudget.Dimension[1]       = cells.item(sn,4).value().bStr();
                            ledgerBudget.Dimension[4]       = cells.item(sn,5).value().bStr();
                            ledgerBudget.Dimension[5]       = cells.item(sn,6).value().bStr();

                            ledgerBudget.StartDate          = cells.item(sn,7).value().date();
                            ledgerBudget.Currency           = cells.item(sn,8).value().bStr();
                            ledgerBudget.Amount             = cells.item(sn,9).value().double();//cells.item(sn,9).value().currency();
                            ledgerBudget.DRTBudgetResponsible = cells.item(sn,10).value().bStr();
                            ledgerBudget.insert();

                            ttscommit;
                        }
                    }
                    //LedgerBudget_ds.executeQuery();
                }
            }
            catch
            {
                ttsabort;
                application.quit();
            }
        }
    }
}