Static boolean ImportVendorsDetails()
{
#AviFiles
DirPartyTable dirPartyTable;
DirPerson dirPerson;
DirPersonName dirPersonName;
VendGroup vendGroup;
LogisticsLocation logisticsLocation,vendLocation;
DirOrganization dirOrganization;
VendTable vendTable, _vendTable, vendorExist;
DirPartyPostalAddressView dirPartyPostalAddressView;
DirPartyContactInfoView dirPartyContactInfoView;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
COM excelCOM;
int row ,progressbarloop = 1,
lastRow, firstRow, totalRows;
Dialog dialog;
FilenameOpen filename;
dialogField dialogFilename;
SysOperationProgress progressbar;
Description locDescription,contactDiscription;
LogisticsLocationId locationId;
VendAccount accountNum;
Name vendorName,firstName,lastName;
real maritalStatus;
VendGroupId vendGroupId;
LogisticsAddressCity cityId;
LogisticsAddressCountyId CountryId;
InventLocationId warehouseId;
InventSiteId siteId;
LogisticsAddressStreet address;
CurrencyCode currencyCode;
DirParty dirPartyClass;
DirPartyRecId PartyRecId;
TaxWithholdGroup taxWithholdGroup;
PoolId poolId;
HcmWorker hcmWorker;
HcmPersonDetails hcmPersonDetails;
HcmPersonNumberOfDependents noOfDependent;
DirPartyRecId person;
int taxWithHold;
boolean completed, taxWithH;
str locationName;//,personalNumber;
HcmPersonnelNumberId personalNumber;
ValidFromDate validFrom;
LogisticsAddressCountryRegionId countryRegionId;
LogisticsElectronicAddressMethodType contactType;
LogisticsElectronicAddressLocator contactPhone, contactEmail, contactFax, contactTelex, contactURL;
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()));
}
}
dialog = new Dialog('Import Vendors');
dialogFilename = dialog.addField(ExtendedTypeStr("FilenameOpen"));
dialog.filenameLookupTitle('Import from excel.');
dialog.caption('Import From Excel');
dialog.filenameLookupFilter(["@SYS28576","*.xlsx"]);
dialog.filenameLookupTitle("Select from EXCEL");
dialogFilename.value(filename);
if(dialog.run())
{
filename = dialogFilename.value();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
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();
excelCOM = cells.comObject();
excelCOM = excelCOM.SpecialCells(2);
firstRow = excelCOM.Row();
excelCOM = excelCOM.SpecialCells(11);
lastRow = excelCOM.Row();
totalRows = lastRow - firstRow;
row = firstRow;
type = cells.item(row, 1).value().variantType();
progressbar = new SysOperationProgress();
progressbar.setCaption("Processing.....");
progressbar.setAnimation(#AviUpdate);
progressbar.setTotal(totalRows);
try
{
//ttsBegin;
do
{
row++;
progressbar.setText(strFmt("Processing line no. %1 of %2", progressbarloop , totalRows));
type = cells.item(row+1, 1).value().variantType();
personalNumber = COMVariant2Str(cells.item(row, 1).value());
firstName = cells.item(row, 2).value().bStr();
lastName = cells.item(row, 3).value().bStr();
maritalStatus = cells.item(row, 4).value().double();
noOfDependent = cells.item(row, 5).value().double();
HcmWorker = hcmWorker::findByPersonnelNumber(personalNumber,true);
if(hcmworker::findByPersonnelNumber(personalNumber))
{
if(hcmWorker.Person)
{
dirPartyTable = dirPartyTable::find(dirPerson::find(HcmWorker.Person).PartyNumber);
if(!dirPartyTable)
{
dirPartyTable = DirPartyTable::createNew(DirPartyType::Person,firstName);
}
else
{
dirPersonName = DirPersonName::find(hcmWorker.Person,true);
dirPersonName.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
dirPersonName.FirstName = firstName;
dirPersonName.LastName = lastName;
dirPersonName.write();
}
}
ttsBegin;
dirPerson = dirPerson::find(DirPartyTable.RecId, true);
//dirPerson.MaritalStatus = maritalStatus;
dirPerson.write();
ttsCommit;
ttsBegin;
HcmPersonDetails.initValue();
HcmPersonDetails.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
HcmPersonDetails.ValidFrom = DateTimeUtil::utcNow();
HcmPersonDetails.ValidTo = DateTimeUtil::maxValue();
HcmPersonDetails.Person = dirPerson.RecId;
hcmPersonDetails.MaritalStatus = maritalStatus;
hcmPersonDetails.NumberOfDependents= noOfDependent;
hcmPersonDetails.write();
ttsCommit;
}
type = cells.item(row + 1, 1).value().variantType();
progressbar.setCount(progressbarloop,1);
progressbarloop++;
}
while (type != COMVariantType::VT_EMPTY);
completed = true;
//ttsCommit;
}
catch
{
ttsAbort;
application.quit();
throw error('Error in import.');
}
workbook.close(false, fileName);
workbooks.close();
application.quit();
}
else
{
completed = false;
}
info(strFmt("%1 vendors have been imported successfully.", row));
return completed;
}
it is in the loop, and this is complete code. Thanks
