How to import Data from excel to a table using sysoperation Framework in Ax 2012 R3?

By

Hi in this Blog we will learn on how to import data from excel to a table using sysoperation framework
For that we will start by creating a new private\shared project(ctrl+shift+p) based on your wish. I will create a shared project named ExcelImportBlog .

Project


Now we will create a table for which we want to import data through excel.
For demonstration purposes I will create a table named ExcelImportTable and I will add 6 fields EmployeeId(String), startTime(hh:mm), endTime(hh:mm), clockedhours(real), Date and WrkCtrId(resourceId).
Table


Now all we need to do is create classes for Contract, Controller, UI Builder and Service class for importing of excel file. We will start with a Contract Class


[DataContractAttribute,
SysOperationContractProcessingAttribute(classStr(ImportExcelDataUIBuilder))]
class ImportExcelDataContract
{
FilenameOpen fileName;
}


[DataMemberAttribute]
public FilenameOpen parmFile_Name(FilenameOpen fname = fileName)
{
fileName = fname;
return fname;
}
Contract Class


Next we will create a UIBuilder class as ImportExcelDataUIBuilder and add the following codes:


class ImportExcelDataUIBuilder extends SysOperationAutomaticUIBuilder
{
DialogField dlgFileName;
ImportExcelDataContract contract;
}


public void postBuild()
{
super();
contract = this.dataContractObject();
dlgFileName = this.bindInfo().getDialogField(contract, methodstr(ImportExcelDataContract,parmFile_Name));
}
UIBuilder Class


Next up we will create a controller class ImportExcelDataController and write the import logic for Excel:


class ImportExcelDataController extends SysOperationServiceController
{
ImportExcelDataContract importExcelDataContract;
Filename fname,filename;
SysOperationProgress progress;
container readCon;
int totalRecords;
Row row;
FormDataSource callerDataSource;
DialogField dialogfileNameOpen;
FilenameOpen fileNameOpen;
Counter inserted;
DialogRunBase dlg;
ExcelImportTable excelimportTable;
COMVariant comVar;
COMVarianttype Comtype;
SysExcelApplication app;
SysExcelWorkbooks Workbooks;
SysExcelWorkbook Workbook;
SysExcelWorksheets Worksheets;
SysExcelWorksheet Worksheet;
SysExcelWorksheet Worksheet2;
SysExcelCells cells;
SysExcelCells cells2;
SysExcelCell cellId;
int record,RecordsImported;
int recordsRead;
int recordsInserted;
}


public boolean canGoBatch()
{
return false;
}


public ImportExcelDataContract parmDataContract(ImportExcelDataContract _importExcelDataContract = importExcelDataContract)
{
importExcelDataContract = _importExcelDataContract;

return importExcelDataContract;
}


public void run()
{
ImportExcelDataContract excelDataContract;
;
excelDataContract = this.getDataContractObject();
fname = excelDataContract.parmFile_Name();
if(fname)
{
this.importExcel();
}
else
{
throw error("Please Choose Excel File");
}
}


public void importExcel()
{
#timeConstants
SysExcelApplication application;
COMVariantType type;
System.IO.FileInfo fileinfo;
FileNameType fileExtension;
boolean validateAll;
real start, end;
inserted = 0;
#File
application = SysExcelApplication::construct();
workbooks = application.workbooks();
comVar = new COMVariant();
new InteropPermission(InteropKind::ClrInterop).assert();
fileinfo = new System.IO.FileInfo(fname);
fileExtension = fileinfo.get_Extension();
filename = fname;
validateAll = true;
if(fileExtension == ".xls" || fileExtension == "xlsx")
{
#excel
progress = new SysOperationProgress();
progress.setCaption(strFmt("@AAF22475"));
progress.setText("@AAF18805");
comVar.bStr(filename);
workbook = workbooks.add(comVar);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 2;
cellId = cells.item(row,1);
while(cellId.value().bStr())
{
cellId = cells.item(row,1);
type = cells.item(row, 1).value().variantType();
progress.setText(strfmt("Importing %1 row", row));
excelImportTable.EmployeeID = cells.item(row,1).value().bStr();
excelImportTable.ResourceId = strLRTrim(cells.item(row,2).value().bStr());
excelImportTable.ExtDate= cells.item(row,3).value().date();
start = cells.item(row,4).value().double();
excelImportTable.StartTime = start*1440*60;
end = cells.item(row,5).value().double();
excelImportTable.EndTime = end*1440*60;
excelImportTable.ClockedHours = cells.item(row,6).value().double();
excelImportTable.doInsert();
row++;
cellId = cells.item(row,1);
}
info(strfmt("Imported %1 rows", row-2));
}
}


public static void main(Args args)
{ ImportExcelDataController controller;
ImportExcelDataContract dataContract;
identifierName className;
identifierName methodName;
SysOperationExecutionMode executionMode;
executionMode = SysOperationExecutionMode::Synchronous;
[className, methodName, executionMode] = SysOperationServiceController::parseServiceInfo(args);
controller = new ImportExcelDataController(className, methodName, executionMode);
dataContract = controller.getDataContractObject();
controller.parmDataContract(dataContract);
controller.parmDialogCaption("Import Excel Data");
if (controller.prompt())
{
controller.run();
}
}


UIBuilder Class


Now we will create a Data Service Class such that we can link it with a menu item
So the class I will create is ImportExcelDataService and add the following codes:


class ImportExcelDataService
{
ImportExcelDataContract contract;
}
[SysEntryPointAttribute]
public void operator(ImportExcelDataContract importExcelDataContract)
{
importExcelDataContract.parmFile_Name();
}
DataService Class


Now, we will create an Action menu item to import excel and we will name it as Import From Microsoft Excel
Menu Item


Import Dialog