static void excelUpload(Args _args)
{
//dialog
dialog d = new dialog("Excel upload Tool");
dialogfield dExcelFilepath,dTablename,dFieldName1,dFieldName2,dFieldName3,dFieldName4,dFieldName5,dExcelStart,dExcelEnd;
dialogfield type1,type2,type3,type4,type5;
DialogGroup fGroup1,fGroup2,fGroup3,fGroup4,fGroup5;
//general variables
filepath excelFilepath;
tablename tableName;
FieldName fieldName1,fieldName2,fieldName3,fieldName4,fieldName5;
str 25 fieldtype1,fieldtype2,fieldtype3,fieldtype4,fieldtype5;
str 100 NexcelField1,NexcelField2,NexcelField3,NexcelField4,NexcelField5;
int excelStart,excelEnd;
boolean ok = true;
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
//dictTable variables
dicttable dictTable;
dictField dictField1,dictField2,dictField3,dictField4,dictField5;
common common;
//excel variables
SysExcelApplication ldA;
SysExcelWorkbooks ldWbs;
SysExcelWorkbook ldWb;
SysExcelWorksheets ldWss;
SysExcelWorksheet ldWs;
SysExcelCells ldCs;
SysExcelCell ldCA;
SysExcelRange ldR;
int j;
container value( str _Value,types _type,fieldName _fieldName = '')
{
container conLocal [1];
dictEnum dictEnum;
int i;
;
switch (_type)
{
case types::Integer :
conlocal[1] = [str2int(_value)];
break;
case types::Real :
conlocal[1] = [str2num(_value)];
break;
case types::Date :
conlocal[1] = [str2date(_value,1)];
break;
case types::Int64:
conlocal[1] = [str2int64(_value)];
break;
case types::UtcDateTime:
conlocal[1] = [str2datetime(_value,1)];
break;
case types::Enum:
{
if(_fieldName != '')
{
dictEnum = new DictEnum(EnumName2Id(_fieldName));
for(i=0;i<= dictEnum.values();i++)
{
if(dictEnum.index2Label(i) == _value)
{
conlocal[1] = [dictEnum.name2Value(_value)];
Break;
}
}
}
}
break;
default:
conlocal[1] = [strfmt(_value)];
break;
}
return conlocal[1];
}
boolean validate()
{
Dictionary dictionary = new Dictionary();
tableName tableNameLocal;
;
if(!excelFilepath)
ok = ok && checkFailed("Please give the Excel path to import data");
if(!tableName && ok)
ok = ok && checkFailed("Table Name is mandatory");
if(tablename && ok)
{
tableNameLocal = Dictionary.tableName(tableName2Id(tableName));
if(!tableNameLocal)
ok = ok && checkFailed(strfmt("Table %1 does not exist",tablename));
}
if(!fieldName1 && ok)
{
ok = ok && checkfailed("Primary Key is mandatory Proceed");
}
if((!FieldName2 && !fieldname3 && !FieldName4 && !Fieldname5) && ok)
{
ok = ok && checkFailed("Atleast one field is mandatory Proceed");
}
if(ok && !ExcelStart || !Excelend)
{
ok = ok && checkFailed("Excel start and end rows are mandatory to proceed");
}
if(ok && (ExcelStart > Excelend))
{
ok = ok && checkfailed("Excel Start row number cannot be less than Excelend row number");
}
if((!FieldName2 || !fieldname3 || !FieldName4 || !Fieldname5) && ok )
{
ok = box::yesNo("All fields are not given, you still want to proceed ?",dialogbutton::Yes,"Check validation") == dialogButton::Yes;
}
return ok;
}
;
dExcelFilepath = d.addField(extendedtypestr(FilePath),"Excel file path","Please select the excel");
dTableName = d.addField(extendedTypeStr(tablename),"Table name","Please enter tablename");
fgroup1 = d.addGroup("Primary Key");
dFieldName1 = d.addField(extendedtypestr(FieldName),"Primary Key","Please enter Field-1");
type1 = d.addField(extendedtypestr(fieldType),"Primary Key","Select the field type");
fgroup2 = d.addGroup("Field list 1");
dFieldName2 = d.addField(extendedtypestr(FieldName),"Field-1","Please enter Field-2");
type2 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");
fgroup3 = d.addGroup("Field list 2");
dFieldName3 = d.addField(extendedtypestr(FieldName),"Field-2","Please enter Field-3");
type3 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");
fgroup4 = d.addGroup("Field list 3");
dFieldName4 = d.addField(extendedtypestr(FieldName),"Field-3","Please enter Field-4");
type4 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");
fgroup5 = d.addGroup("Field list 4");
dFieldName5 = d.addField(extendedtypestr(FieldName),"Field-4","Please enter Field-5");
type5 = d.addField(extendedtypestr(fieldType),"Type","Select the field type");
dExcelStart = d.addField(extendedtypestr(integer),"Excel Start","Enter excel Starting row");
dExcelEnd = d.addField(extendedtypestr(integer),"Excel End","Enter excel ending row");
if(d.run()== noyes::Yes)
{
//reading dialog
excelFilepath = dExcelFilepath.value();
tablename = dtablename.value();
dictTable = new dicttable(tableName2Id(tableName));
if(!dictTable)
throw error(strfmt("Table %1 is not found ",tableName));
FieldName1 = dFieldName1.value();
if(FieldName1)
dictField1 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName1));
if(FieldName1 && !dictField1)
throw error(strfmt("Field %1 Not found in table %2",FieldName1,tableid2name(dictTable.id())));
FieldName2 = dFieldName2.value();
if(FieldName2)
dictField2 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName2));
if(FieldName2 &&!dictField2)
throw error(strfmt("Field %1 Not found in table %2",FieldName2,tableid2name(dictTable.id())));
FieldName3 = dFieldName3.value();
if(FieldName3)
dictField3 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName3));
if(FieldName3 && !dictField3)
throw error(strfmt("Field %1 Not found in table %2",FieldName3,tableid2name(dictTable.id())));
FieldName4 = dFieldName4.value();
if(FieldName4)
dictField4 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName4));
if(FieldName4 && !dictField4)
throw error(strfmt("Field %1 Not found in table %2",FieldName4,tableid2name(dictTable.id())));
FieldName5 = dFieldName5.value();
if(FieldName5)
dictField5 = new dictField(dictTable.id(),fieldname2id(dictTable.id(),FieldName5));
if(FieldName5 && !dictField5)
throw error(strfmt("Field %1 Not found in table %2",FieldName5,tableid2name(dictTable.id())));
ExcelStart = dExcelStart.value();
ExcelEnd = dExcelEnd.value();
if(validate())
{
if(winapi::pathExists(excelFilepath))//&& winapi::fileExists(excelFilepath +'test2.Xlsx'))
{
// reading excel
ldA = SysExcelApplication::construct();
ldA.visible(false);
ldWbs = ldA.workbooks();
ldWbs.open(excelFilepath+'\\'+'excel.Xlsx',0,true);
ldWb = ldWbs.item(1);
ldWss = ldWb.worksheets();
ldws = ldwss.itemFromName('Sheet1');
ldCs = ldWs.cells();
ldR = ldCs.range('A1');
for (j=ExcelStart;j<=ExcelEnd;j++)
{
progress.setCaption("Data update is in progress...");
progress.setAnimation(#AviUpdate);
progress.setTotal(ExcelEnd);
progress.setText(strfmt("Record %1 is been updated", j-1));
progress.setCount(j-1, 1);
ldCA = ldCs.item(j,1);
NexcelField1 = ldCA.value().bStr();
ldCA = ldCs.item(j,2);
NexcelField2 = ldCA.value().bStr();
ldCA = ldCs.item(j,3);
NexcelField3 = ldCA.value().bStr();
ldCA = ldCs.item(j,4);
NexcelField4 = ldCA.value().bStr();
ldCA = ldCs.item(j,5);
NexcelField5 = ldCA.value().bStr();
common = dictTable.makeRecord();
ttsbegin;
select forupdate common where common.(fieldname2id(dicttable.id(),fieldname1)) == conpeek(value(NexcelField1,type1.value(),fieldname1),1);//str2int64(NexcelField1);//;
if(Common)
{
if(NexcelField2 && fieldName2)
{
common.(fieldname2id(dicttable.id(),fieldname2)) = conpeek(value(NexcelField2,type2.value(),fieldname2),1);
}
if(NexcelField3 && fieldName3)
{
common.(fieldname2id(dicttable.id(),fieldname3)) = conpeek(value(NexcelField3,type3.value(),fieldname3),1);
}
if(NexcelField4 && fieldName4)
{
common.(fieldname2id(dicttable.id(),fieldname4)) = conpeek(value(NexcelField4,type4.value(),fieldname4),1);
}
if(NexcelField5 && fieldName5)
{
common.(fieldname2id(dicttable.id(),fieldname5)) = conpeek(value(NexcelField5,type5.value(),fieldname5),1);
}
common.update();
}
ttscommit;
common = null;
}
ldWbs.close();
info(strFmt("Task has completed,Succcessfully updated %1 records",j));
}
else
{
throw error(strfmt("Please check excel path - %1",excelFilepath));
}
}
}
else
info("Task has cancelled");
}