AX2012 Read And Import Excel File using X++ Code

X++ code to read the Excel file 


static void RB_ReadExcel(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    FilenameOpen                    filename;

    int row = 1; // if the excel has the header
    TransDate     empJoiningDate;
    real          salary;
    str           empId;
    int           totNoOfLeaves;

    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    filename  = "C:\\Users\\balakrishnanr\\Desktop\\emp.xlsx"; // file path

    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

    //Iterate through cells and get the values
    do
    {
    //Incrementing the row line to next Row
    row++;
    empId           = cells.item(row,1).value().bStr();
    empJoiningDate  = cells.item(row,2).value().date();
    salary          = cells.item(row,3).value().double();
    totNoOfLeaves   = cells.item(row,4).value().int();


   info(strFmt("Empl Id:- %1 , DOJ :- %2 , Salary :- %3 ,  TotNoLeaves :-   %4",empId,empJoiningDate,salary,totNoOfLeaves));

    // Loads the next row into the variant type and validating that its is empty or not
    type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);

    // quits the application
    application.quit();

}

Preventing users from opening new workspaces

You can prevent users from opening new workspaces  by adding the  code that immediately closes these aging. Add the the following code to \Classes\Info\workspaceWindowCreated:

void workspaceWindowCreated(int _hWnd)
{
;
    // Put workspace window specific initialization here.
    // Begin -->
    if(xInfo::currentWorkspaceNum()>1)
    {
        Infolog.shutDown(true);
    }
    // End <--
}

Convert Numerals to Text in Indian Format (AX2012)

// Converting Numeral to Text of Indian Format .

// Add a new method in the Class Name : Global and paste the below code .


static TempStr numeralsToTxt_IN(real _num)
{
int numOfPennies = (decround(frac(_num), 2) * 100) mod 100;
real test = _num - frac(_num);

int numOfTenths;
str 20 ones[19], tenths[9], hundreds, thousands, lakhs, crores;

int64 temp;
str 200 returntxt;
str 200 pennytxt;
int penny;

real modOperator(real a1, real a2)
{
int tmpi;
real tmp1, tmp2;
tmp1 = a1 / a2;
tmpi = real2int(tmp1);
tmp2 = tmpi;
return (tmp1 - tmp2)*a2;
}

real checkPower(real _test, int64 _power)
{
int64 numOfPower;

if (_test >= _power)
{
numOfPower = _test div _power;
if (numOfPower >= 100)
{
temp = numOfPower div 100;
returntxt = returntxt + ' ' + ones[temp] + ' ' + hundreds;
numOfPower = numOfPower mod 100;
}
if (numOfPower >= 20)
{
temp = numOfPower div 10;
returntxt = returntxt + ' ' + tenths[temp];
numOfPower = numOfPower mod 10;
}
if (numOfPower >= 1)
{
returntxt = returntxt + ' ' + ones[numOfPower];
numOfPower = numOfPower mod 10;
}
switch(_power)
{
case 10000000 :
{
returntxt = returntxt + ' ' + Crores;
_test = modOperator(_test, 10000000);
break;
}
case 100000 :
{
returntxt = returntxt + ' ' + lakhs;
_test = modOperator(_test, 100000);
break;
}
case 1000 :
{
returntxt = returntxt + ' ' + thousands;
_test = modOperator(_test, 1000);
break;
}
case 100 :
{
returntxt = returntxt + ' ' + hundreds;
_test = modOperator(_test, 100);
break;
}
}
}
return _test;
}

#Define.text_1('One')
#Define.text_2('Two')
#Define.text_3('Three')
#Define.text_4('Four')
#Define.text_5('Five')
#Define.text_6('Six')
#Define.text_7('Seven')
#Define.text_8('Eight')
#Define.text_9('Nine')
#Define.text_10('Ten')
#Define.text_11('Eleven')
#Define.text_12('Twelve')
#Define.text_13('Thirteen')
#Define.text_14('Fourteen')
#Define.text_15('Fifteen')
#Define.text_16('Sixteen')
#Define.text_17(' Seventeen' )
#Define.text_18(' Eighteen' )
#Define.text_19(' Nineteen' )
#Define.text_20(' Twenty' )
#Define.text_30(' Thirty' )
#Define.text_40(' Forty' )
#Define.text_50(' Fifty' )
#Define.text_60(' Sixty' )
#Define.text_70(' Seventy' )
#Define.text_80(' Eighty' )
#Define.text_90(' Ninety' )
#Define.text_100(' Hundred' )
#Define.text_1000(' Thousand' )
#Define.text_100000(' Lakh' )
#Define.text_10000000(' Crore' )
#Define.text_and(' Rupees and' )
#Define.text_paise(' Paise Only' )
#Define.text_ruppe(' Rupees Only' )

ones[1] = #text_1;
ones[2] = #text_2;
ones[3] = #text_3;
ones[4] = #text_4;
ones[5] = #text_5;
ones[6] = #text_6;
ones[7] = #text_7;
ones[8] = #text_8;
ones[9] = #text_9;
ones[10] = #text_10;
ones[11] = #text_11;
ones[12] = #text_12;
ones[13] = #text_13;
ones[14] = #text_14;
ones[15] = #text_15;
ones[16] = #text_16;
ones[17] = #text_17;
ones[18] = #text_18;
ones[19] = #text_19;

tenths[1] = ' Not used' ;
tenths[2] = #text_20;
tenths[3] = #text_30;
tenths[4] = #text_40;
tenths[5] = #text_50;
tenths[6] = #text_60;
tenths[7] = #text_70;
tenths[8] = #text_80;
tenths[9] = #text_90;

hundreds = #text_100;
thousands = #text_1000;
lakhs = #text_100000;
crores = #text_10000000;



test = checkPower(test, 10000000);
test = checkPower(test, 100000);
test = checkPower(test, 1000);
test = checkPower(test, 100);

if (test >= 20)
{
numOfTenths = test div 10;
returntxt = returntxt + ' ' + tenths[numofTenths];
numOfTenths = numOfTenths mod 10;
test = test mod 10;
}
if (test >= 1)
{
numOfTenths = real2int(test);
returntxt = returntxt + ' ' + ones[numOfTenths];
}

if (numOfPennies)
{
if (numOfPennies >= 20)
{
penny = numOfPennies div 10;
pennytxt = tenths[penny];
numOfPennies = numOfPennies mod 10;
}
if (numOfPennies >= 1)
{
pennytxt = pennytxt + ' ' + ones[numOfPennies];
}
returntxt = returntxt + ' ' + #text_and + ' ' + pennytxt + ' ' +#text_paise;
}
else
{
returntxt = returntxt + ' ' + #text_ruppe;
}
return returntxt;
}

To Test the above code , write a job mentioned below:

static void AmountInWords(Args _args)

{

    Global  global;

    str 160  amtInwords;

    ;

    amtInwords = Global::numeralsToTxt_IN(1523.55); // Amount 

    info(strFmt("%1", amtInwords));

}