Wednesday, November 22, 2017

Simple Excel Import to the table using X++ job

static void Esh_ReadExcel(Args _args)
{

SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;

int row=1;
Str Emp_Id,Emp_Name,Emp_Profile;
FileName filename;
Esh_ExcelUpload esh_ExcelUpload; //table buffer
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();

//specify the file path that you want to read
filename = "D:\\ExcelUpload.xlsx";

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
{
    //read data from Excel
    row++;
    Emp_Id = cells.item(row, 1).value().bStr();
    Emp_Name = cells.item(row, 2).value().bStr();
    Emp_Profile = cells.item(row, 3).value().bStr();
    info(strfmt('%1 - %2', Emp_Id, Emp_Name,Emp_Profile));
    type = cells.item(row+1, 1).value().variantType();
 
    //insert data into table
    esh_ExcelUpload.clear();
    esh_ExcelUpload.Emp_Id      = Emp_Id;
    esh_ExcelUpload.Emp_Name    = Emp_Name;
    esh_ExcelUpload.Emp_Profile = Emp_Profile;
    esh_ExcelUpload.insert();
}
 
while (type != COMVariantType::VT_EMPTY);
application.quit();
}

No comments:

Post a Comment

How to enable the dimension fields based on the Item selected on the form.

[Form] public class KMTShipFromWarehouses extends FormRun {     InventDimCtrl_Frm_EditDimensions        inventDimFormSetup;     /// ...