Import data from Excel to Ax

HI,

Below is the sample code to read data from excel sheet and update the related table data in ax 2012. In our case we are uploading the wage lines data in ax 2012.
static void ExporttoTablefromExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
CustTable CustTable,custexit;
DirPartyTable DirPartyTable, partyexit;
DirPersonName DirPersonName;
DirPerson DirPerson;
DirParameters DirParameters;
Mzk_OverTime overtime;

int row =1;
int i=0;
str Account,Currency,recordtype, customergroup,pnumber,NameAlias,Language;
str emplcode, allded, value, descr;
real amount;
date pdate;

COMVariant costnew;
date StartDate;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
//filename = “C:\\Users\\46509010\\Desktop\\book2.xlsx”;
filename = “C:\\Users\\46504257\\Desktop\\YME.xlsx”;

//delete_from overtime
//where overtime.MZK_MonthYear == MZK_MonthYear::APR2013
//&& overtime.dataAreaId == “yme”;
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error(“File cannot be opened.”);
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
// Account = cells.item(row, 1).value().bStr();
//pdate= cells.item(row, 2).value().date();
emplcode = cells.item(row,3).value().bstr();
allded=cells.item(row,6).value().bstr();
amount=cells.item(row,7).value().double();
descr=cells.item(row,8).value().toString();
//info(strFmt(“%1, %2,%3,%4,%5”,emplcode,allded,amount));
overtime.MZK_MonthYear = MZK_MonthYear::JULY2013;
overtime.TransferEmployeeCode = emplcode;
overtime.Worker = HcmWorker::findByTransferEmployeeCode(emplcode).RecId;
overtime.Employment = HcmEmployment::findByWorker(overtime.Worker).RecId;
overtime.EndDate = 25\7\2013;
overtime.Amount = amount;
overtime.Remark = descr;
if((allded == “OVER TIME (150%)”) || (allded == “OVER TIME ALLOWANCE”))
{
overtime.OTRates = Mzk_OTRate::OT150;
overtime.Description = “OVERTIME”;
allded = “OVERTIME”;
}
else if(allded == “OVER TIME (125%)”)
{
overtime.OTRates = Mzk_OTRate::OT125;
overtime.Description = “OVERTIME”;
allded = “OVERTIME”;
}
else if(allded == “OVER TIME (200%)”)
{
overtime.OTRates = Mzk_OTRate::OT200;
overtime.Description = “OVERTIME”;
allded = “OVERTIME”;
}
else
{
overtime.OTRates = Mzk_OTRate::Element1;
}

overtime.PayrollCategory = HcmPayrollCategory::findByPayrollCategory(allded).RecId;
if(!overtime.PayrollCategory)
{
overtime.PayrollDeductionType = HcmPayrollDeductionType::findByPayrollDeductionType(allded).RecId;
overtime.Description = HcmPayrollDeductionType::findByPayrollDeductionType(allded).Description;
overtime.Type = type::Deductions;
overtime.PayrollCategory = 0;
}
else
{
overtime.Type = type::Allowance;
overtime.PayrollCategory = HcmPayrollCategory::findByPayrollCategory(allded).RecId;
overtime.Description = HcmPayrollCategory::findByPayrollCategory(allded).Description;
overtime.PayrollDeductionType = 0;
}
overtime.insert();

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

}
while (type != COMVariantType::VT_EMPTY);
application.quit();
info(“Done”);
info(strFmt(“%1”, i));

}

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s