x++ code to import data from excel to D365 FnO

We can also import data through code in D365 FO. Data import through code in D365 works differently than Ax2012 since cloud services. 

Import Class

we are trying to import data from Excel to D365FO through the following code.

 

using System.IO;

using OfficeOpenXml;

using OfficeOpenXml.ExcelPackage;

using OfficeOpenXml.ExcelRange;

 

class EmplAttendance

{

public void run()

{

this.updateDailyAttendance();

}

 

void updateDailyAttendance()

{

System.IO.Stream                     stream;

ExcelSpreadsheetName            sheeet;

FileUploadBuild                       fileUpload;

DialogGroup                             dlgUploadGroup;

FileUploadBuild                       fileUploadBuild;

FormBuildControl                    formBuildControl;

EmplAttendance_CFS              emplTimeAttendance, insertTimeAttendance, updateTimeAttendance;

COMVariantType                     type;

Dialog                      dialog =    new Dialog(“Daily Attendance Imported”);

 

dlgUploadGroup          = dialog.addGroup(“@SYS54759″);

formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name());

fileUploadBuild           = formBuildControl.addControlEx(classstr(FileUpload), ‘Upload’);

fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);

fileUploadBuild.fileTypesAccepted(‘.xlsx’);

 

str COMVariant2Str(COMVariant _cv)

{

switch (_cv.variantType())

{

case COMVariantType::VT_BSTR:

return _cv.bStr();

 

case COMVariantType::VT_EMPTY:

return ”;

 

default:

throw error(strfmt(“@SYS26908”, _cv.variantType()));

}

}

 

if (dialog.run() && dialog.closedOk())

{

FileUpload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId(‘Upload’));

FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

 

if (fileUploadResult != null && fileUploadResult.getUploadStatus())

{

stream = fileUploadResult.openResult();

 

using (ExcelPackage Package = new ExcelPackage(stream))

{

int                         rowCount, i,columncount,j;

Package.Load(stream);

ExcelWorksheet   worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);

OfficeOpenXml.ExcelRange    range       = worksheet.Cells;

rowCount           = (worksheet.Dimension.End.Row) – (worksheet.Dimension.Start.Row)  + 1;

columncount      = (worksheet.Dimension.End.Column);

 

 

for (i = 2; i<= rowCount; i++)

{

str Emplid;

TransDate                 WorkingDate;

emplid                       = (range.get_Item(i, 1).value);

WorkingDate            = str2Date((range.get_Item(i, 2).value),123);

 

select * from emplTimeAttendance

where emplTimeAttendance.EmplId              = =  Emplid

&& emplTimeAttendance.WorkingDate       = =  WorkingDate;

 

if(emplTimeAttendance)     //if record already exists update it

{

emplTimeAttendance.selectForUpdate(true);

emplTimeAttendance.Timein                          =   any2Str(range.get_Item(i, 3).value);

emplTimeAttendance.Timeout                        =   any2Str(range.get_Item(i, 4).value);

emplTimeAttendance.OT                                =  any2Real(range.get_Item(i, 5).value);

ttsbegin;

emplTimeAttendance.update();

ttscommit;

}

Else      //insert the new record

{

insertTimeAttendance.EmplId                         = (range.get_Item(i, 1).value);

insertTimeAttendance.WorkingDate               = str2Date((range.get_Item(i, 2).value),123);

insertTimeAttendance.Timein                         = any2Str(range.get_Item(i, 3).value);

insertTimeAttendance.Timeout                       = any2Str(range.get_Item(i, 4).value);

insertTimeAttendance.OT                                = any2Real(range.get_Item(i, 5).value);

insertTimeAttendance.insert();

}

}

}

}

else

{

error(“Error here”);

}

 

}

}

 

public static void main (Args args)

{

EmplAttendance     emplDailyAttendanceImport;

 

emplDailyAttendanceImport = new EmplAttendance ();

emplDailyAttendanceImport.run();

}

}