Hi,
To upload excel data to ax table we use following x++ code
static void UploadExceldatatoTable(Args _args)
{
SysExcelRange sysExcelRange;
#AviFiles
#define.Star('*')
#define.ExcelDataRange("A1:IV65536")
SysOperationProgress _progressBar = new SysOperationProgress();
int record;
DialogField dialogPath;
Dialog dialog;
Filename filename;
CommaIO inFile;
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorksheets xlsWorkSheetCollection;
SysExcelWorksheet xlsWorkSheet;
SysExcelCells Cells;
SysExcelCell RCell;
int nRow; //,i;
NoYes _NoYes;
TimeOfDay time;
S3_FrequencyIdentifier _frequency;
S3_EquipmentTypes eqptTypeMaster;
WCL_bk_EqptTaskLastInspectionDate _eqptTaskLastInspDate;
WCL_SS_RouteEquipment _RouteEquipment;
S3_EquipmentTable _eqptTable;
WCL_InspectionCheckListHeader _inspChkLstHeader;
WCL_InspectionCheckListLines _inspChkLstLine;
#Excel
// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR) :
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
;
dialog = new Dialog('Equipment task Last inspection date import');
dialogPath = dialog.addField(extendedTypeStr(Filenameopen), 'File Name');
dialog.run();
if (dialog.run())
{
filename = (dialogPath.value());
}
inFile = new CommaIO (filename, 'R');
if (!inFile || infile.status() != IO_Status::Ok )
{
throw error (strfmt("@SYS19312",filename));
}
try
{
xlsApplication = SysExcelApplication::construct();
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBookCollection.open(filename);
xlsWorkSheetCollection = xlsApplication.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);
Cells = xlsWorkSheet.Cells();
nRow = 2;
RCell = Cells.Item(nRow, 1);
sysExcelRange = xlsWorkSheet.cells().range("A1:IV65536");
sysExcelRange = sysExcelRange.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
if (sysExcelRange)
{
record = sysExcelRange.row();
}
_progressBar.setCaption("Last inspection date uploading. . .");
_progressBar.setAnimation(#AviUpdate);
_progressBar.setTotal(record);
ttsBegin;
while (COMVariant2Str(RCell.value()) != "")
{
_progressBar.setText(strfmt("Step %1", nRow));
_progressBar.setCount(nRow, 1);
if(WCL_SS_RouteMaster::find(strLTrim(strRTrim(COMVariant2Str(Cells.item(nRow,1).value())))))
{
while select _RouteEquipment join _eqptTable join _inspChkLstHeader join _inspChkLstLine
where _RouteEquipment.EquipmentId == _eqptTable.S3_EquipmentId &&
_eqptTable.EquipmentType == _inspChkLstHeader.EquipmentType &&
_eqptTable.EquipmentSubType == _inspChkLstHeader.EquipmentSubType &&
_inspChkLstHeader.CheckListHeaderID == _inspChkLstLine.CheckListHeaderID &&
_RouteEquipment.RouteID == strLTrim(strRTrim(COMVariant2Str(Cells.item(nRow,1).value()))) &&
_inspChkLstLine.Frequency == _frequency
{
if(!WCL_bk_EqptTaskLastInspectionDate::find(_RouteEquipment.EquipmentId, _inspChkLstLine.TaskCode))
{
_eqptTaskLastInspDate.EquipmentId = _RouteEquipment.EquipmentId;
_eqptTaskLastInspDate.TaskCode = _inspChkLstLine.TaskCode;
_eqptTaskLastInspDate.LastInspectionDate = str2Date(strLTrim(strRTrim(COMVariant2Str(Cells.item(nRow,3).value()))),123);
_eqptTaskLastInspDate.insert();
}
}
}
else
{
error(strFmt("%1 - Route not found in the master",strLTrim(strRTrim(COMVariant2Str(Cells.item(nRow,1).value())))));
}
nRow++;
RCell = Cells.Item(nRow, 1);
}
ttsCommit;
info("Equipment Task last inspection date uploaded successfully");
}
catch
{
error('Error!');
xlsApplication.quit ();
xlsApplication.finalize ();
return;
}
}
Thanks
B K Sharma