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 |
