static void DataimportWithProgress(Args _args)
{
#avifiles
int insertcount= 0;
int row = 1;
Name Division,Department,Section;
MainAccountNum mainAccountId;
real Amount;
uploadTable _uploadTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType comtype;
Dialog dialog;
DialogField fileName;
// SysOperationProgress progress;
SysOperationProgress progress = new SysOperationProgress();
//
//progress.setAnimation(#AviUpdate);
//progress.setTotal(row, 60000);
str COMVariant2Str(COMVariant _cv, int _decimals = 2,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();
dialog.caption("Select File");
fileName = dialog.addField(extendedTypeStr(FilenameOpen));
dialog.run();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(fileName.value());
}
catch
{
throw error("File cannot be opened");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
mainAccountId = COMVariant2Str(cells.item(row,1).value());
division = COMVariant2Str(cells.item(row,2).value());
Department = COMVariant2Str(cells.item(row,3).value());
Section = COMVariant2Str(cells.item(row,4).value());
Amount = any2real(COMVariant2Str(cells.item(row,5).value()));
progress.setAnimation(#AviUpdate);
progress.setTotal(row, 60000);
progress.setText(strfmt("Processing"));
progress.setCaption(strFmt('Fetching data from line %1',row));
ttsBegin;
_uploadTable.clear();
_uploadTable.MainAccountId = mainAccountId;
_uploadTable.Division = Division;
_uploadTable.Department = Department;
_uploadTable.Section = Section;
_uploadTable.Amount = Amount;
_uploadTable.insert();
ttsCommit;
comtype = cells.item(row+1, 1).value().variantType();
}
while (comtype != COMVariantType::VT_EMPTY);
application.quit();
}
{
#avifiles
int insertcount= 0;
int row = 1;
Name Division,Department,Section;
MainAccountNum mainAccountId;
real Amount;
uploadTable _uploadTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType comtype;
Dialog dialog;
DialogField fileName;
// SysOperationProgress progress;
SysOperationProgress progress = new SysOperationProgress();
//
//progress.setAnimation(#AviUpdate);
//progress.setTotal(row, 60000);
str COMVariant2Str(COMVariant _cv, int _decimals = 2,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();
dialog.caption("Select File");
fileName = dialog.addField(extendedTypeStr(FilenameOpen));
dialog.run();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(fileName.value());
}
catch
{
throw error("File cannot be opened");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
mainAccountId = COMVariant2Str(cells.item(row,1).value());
division = COMVariant2Str(cells.item(row,2).value());
Department = COMVariant2Str(cells.item(row,3).value());
Section = COMVariant2Str(cells.item(row,4).value());
Amount = any2real(COMVariant2Str(cells.item(row,5).value()));
progress.setAnimation(#AviUpdate);
progress.setTotal(row, 60000);
progress.setText(strfmt("Processing"));
progress.setCaption(strFmt('Fetching data from line %1',row));
ttsBegin;
_uploadTable.clear();
_uploadTable.MainAccountId = mainAccountId;
_uploadTable.Division = Division;
_uploadTable.Department = Department;
_uploadTable.Section = Section;
_uploadTable.Amount = Amount;
_uploadTable.insert();
ttsCommit;
comtype = cells.item(row+1, 1).value().variantType();
}
while (comtype != COMVariantType::VT_EMPTY);
application.quit();
}