X++ code to import Budget Registry entries from Excel

X++ code to import Budget Registry entries from Excel

Create a button budget transaction Line Form 

Path:-
Budgeting>>common>>Budget register entries>>All Budget register entries>>Create New Buget Reg Entry

Create a button on budget transaction Line Form

then create a class :-
class SKS_BudgetEtry
        {
     
        }

then create following methods in that Class:-

defaultDimension method:- // to get dimensions in Excel

        public DimensionDynamicAccount defaultDimension(Name  _Dim1,Name  _Dim2,Name  _Dim3,Name  _Dim4,Name  _Dim5,Name  _Dim6, MainAccountNum _mainAccountNum)
                                                     
        {
            Struct        struct = new Struct();
            container                  defDimensionCon;
            DimensionDefault           dimensionDefault=0;
            DimensionAttributeSetItem  dimAttrSetItem;
            DimensionAttribute         dimAttribute;
            int                        i;
            LedgerDimensionAccount     LedgerDim;
            ;
            if(_Dim1)
            {
                struct.add("Department", _Dim1);
            }
            if(_Dim2)
            {
                struct.add("Region", _Dim2);
            }
            if(_Dim3)
            {
                struct.add("Purpose", _Dim3);
            }
            if(_Dim4)
            {
     
                struct.add("AccountingRegion", _Dim4);
            }
            if(_Dim5)
            {
                struct.add("Product", _Dim5);
            }
              if(_Dim6)
            {
                struct.add("Vendor", _Dim6);
            }
            defDimensionCon += struct.fields();
            for (i = 1; i <= struct.fields(); i++)
            {
                defDimensionCon += struct.fieldName(i);
                defDimensionCon += struct.valueIndex(i);
            }
            if (struct.fields() &&(_Dim1||_Dim2||_Dim3||_Dim4||_Dim5||_Dim6))
            {
                dimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(defDimensionCon);
            }
            else
            {
                dimensionDefault =0;
            }
            if(_mainAccountNum)
            {
                ledgerDim         = DimensionDefaultingService::serviceCreateLedgerDimension(DimensionStorage::getDefaultAccountForMainAccountNum (_mainAccountNum), dimensionDefault);
                dimensionDefault  = DimensionAttributeValueCombination::find(ledgerDim).Recid;
            }
            return dimensionDefault;
        }


Openpath Method:- // To Open the dialog when button clicked

  public void Openpath()
        {
            Dialog dialog;
            DialogField dialogField;
            str fileName;
            dialog = new Dialog("Select file…..");
            dialogField = dialog.addField(extendedTypeStr("FileNameOpen"),"Source File");
            if (dialog.run())
            {
                fileName = dialogField.value();
            }

===================================================================
then write the below code in button clicked method:-


void clicked()
{

    #OCCRetryCount
    #AviFiles
    SysOperationProgress progress = new SysOperationProgress();
    int i;
    SysExcelApplication ExcelApplication;
    SysExcelWorkbooks workBooks;
    SysExcelWorkbook workBook;
    SysExcelWorksheets workSheets;
    SysExcelWorksheet workSheet;
    SysExcelCells cells;
    SysExcelcell cell;
    COMVariantType type;
    int row,column;
    int64 countervalue;
    Dialog dialog;
    DialogField dialogField;
    str fileName,s;
    BudgetTransactionLine budgetTransactionLineType;
    Name   AccName;

    BudgetType budgetType1;
    LedgerDimensionBudget ledgerDimensionBudget;

    BudgetTransactionLine budgetTransactionLine1,BudgetTransactionLinecount ;

    SKS_BudgetEtry       BudgetEtry =new SKS_BudgetEtry();
    SKS_ledgerMapping    ledgerMapping = new SKS_ledgerMapping();
    DimensionDynamicAccount    defaultdimension;
    SKS_BudgetEtry       budgetentry1 =new SKS_BudgetEtry();

    dialog = new Dialog("Select file…..");
    dialogField = dialog.addField(extendedTypeStr("FileNameOpen"),"Source File");
    if (dialog.run())
    {
        fileName = dialogField.value();
    }

    if (fileName)
    {
        ExcelApplication = SysExcelApplication::construct();
        workBooks = ExcelApplication.workbooks();
        try
        {
            workBooks.open(fileName);
            workBook = workBooks.item(1);
            workSheets = workBook.worksheets();
            workSheet = workSheets.itemFromNum(1);
            row = 1;

            progress.setCaption("Progress bar example…");
            progress.setAnimation(#AviUpdate);
            progress.setTotal(292);

            cells = workSheet.cells();
            type = cells.item(row+1,1).value().variantType();

            select count(RecId) from BudgetTransactionLinecount where BudgetTransactionLinecount.BudgetTransactionHeader == BudgetTransactionHeader.RecId;
            if(BudgetTransactionLinecount.RecId)
            {
                countervalue = BudgetTransactionLinecount.RecId;
            }

            while(type != COMVariantType::VT_EMPTY)
            {
                ttsBegin;
                row++;
                countervalue++;
                //info(strFmt("re%1----ved%2---cur%3",cells.item(row,4).value().bStr(),cells.item(row,5).value().bStr(),cells.item(row,6).value().double()));
                budgetTransactionLine1.clear();
                budgetTransactionLine1.Date     = cells.item(row,1).value().date();
                BudgetTransactionLine_ds.editAccountStructure(false, BudgetTransactionLine, cells.item(row,2).value().bStr());
                if(cells.item(row,2).value().bStr() =="BFIL BS")
                {
                    defaultdimension =  budgetentry1.defaultDimension("",cells.item(row,4).value().bStr(),"","","","",cells.item(row,3).value().bStr());
                }
                else
                {
                    defaultdimension =  budgetentry1.defaultDimension("",cells.item(row,4).value().bStr(),"","","",cells.item(row,5).value().bStr(),cells.item(row,3).value().bStr());
                }
                budgetTransactionLine1.LedgerDimension  = defaultdimension;
                budgetTransactionLine1.TransactionCurrencyAmount = cells.item(row,6).value().double();
                budgetTransactionLine1.BudgetType = str2enum(BudgetType,cells.item(row,7).value().bStr());
                budgetTransactionLine1.TransactionCurrency       = cells.item(row,8).value().bstr();
                budgetTransactionLine1.BudgetTransactionHeader   = BudgetTransactionHeader.RecId;
                budgetTransactionLine1.LineNumber                = int642int(countervalue);
                budgetTransactionLine1.insert();


                ttsCommit;
                progress.setText(strfmt("The value of i is %1", i));
                progress.setCount(i, 1);

                type = cells.item(row+1, 1).value().variantType();
            }

            workBooks.close();
            ExcelApplication.quit();
        }
        catch(Exception::Error)
        {
            ttsabort;
            workBooks.close();
            ExcelApplication.quit();
            throw error("File cannot be opened");
        }
    }
    else
    {
        info("File must be selected");
    }
    budgetTransactionLine_ds.executeQuery();
}


==================================================================

Note:-give the Data in excel like below

Date	AccountStructure	Dimension main account Values	Dimension region Values	DimensionVendor Values	Amount	Amount Type	Currency
1/17/2017	BFIL BS	'11101010	RO11		20,000	Expense	INR
1/17/2017	BFIL BS	'11101004	RO14		10,000	Revenue	INR
1/17/2017	BFIL PL	'41100002	RO16	'1004	30,000	Expense	INR
1/17/2017	BFIL PL	'41100004	RO12	'1013	30,000	Revenue	INR