Hi Crispin John,
I have used SysExcelWorksheet variable even though i can not read the 2 sheet.
Public void ImportQuotationlineactivities(QuotationIdBase QuotationId)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets,worksheets2;
SysExcelWorksheet worksheet;
SysExcelWorksheet worksheet2;
SysExcelCells cells;
SysExcelCells cell;
COMVariantType type;
System.DateTime ShlefDate;
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog;
Integer ActivityDuration, insertedRecords=0;
int row = 1;
System.Text.RegularExpressions.Match myMatch;
#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(),213,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
;
dialog = new Dialog("Excel Upoad");
dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
dialog.filenameLookupTitle("Upload from Excel");
dialog.caption("Upload Excel File");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("@SYS19358");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
{
try
{
do
{
row++;
if(row >=1)
{
quotation = QuotationId;
qutotaiontype = cells.item(row, 1).value().bStr();
projCategory = cells.item(row, 2).value().bStr();
itemId = cells.item(row, 3).value().bStr();
projLinePropertyId = cells.item(row, 6).value().bStr();
salesQuotationTable = salesQuotationTable::find(quotation);
if(salesQuotationTable)
{
salesQuotationLine.clear();
salesQuotationLine.initValue();
salesQuotationLine.QuotationId = quotation;
if(qutotaiontype == "Fee")
{
salesQuotationLine.ProjTransType = QuotationProjTransType::Fee;
}
else if(qutotaiontype == "Expense")
{
salesQuotationLine.ProjTransType = QuotationProjTransType::Expense;
}
else if(qutotaiontype == "Hour")
{
salesQuotationLine.ProjTransType = QuotationProjTransType::Hour;
}
else if(qutotaiontype == "Item")
{
salesQuotationLine.ProjTransType = QuotationProjTransType::Item;
item = salesQuotationLine::existItemId(itemId);
if(item)
{
salesQuotationLine.ItemId = itemId;
}
else
{
throw error("Item Id not exsist");
}
}
categoryTable = categoryTable::find(projCategory);
if(!categoryTable)
{
throw error("Error");
}
else
{
salesQuotationLine.ProjCategoryId = categoryTable.CategoryId ;
salesQuotationLine.ProjDescription = categoryTable.CategoryName;
}
salesQuotationLine.Company = curext();
salesQuotationLine.AccountType = LedgerJournalACType::Project;
salesQuotationLine.CustAccount = salesQuotationTable.CustAccount;
salesQuotationLine.QuotationStatus = SalesQuotationStatus::Created;
salesQuotationLine.CurrencyCode = salesQuotationTable.CurrencyCode;
salesQuotationLine.OffsetAccountType = LedgerJournalACType::Ledger ;
salesQuotationLine.TransDate = systemDateGet();
salesQuotationLine.SalesQty = str2int(cells.item(row, 4).value().bStr());
salesQuotationLine.SalesPrice = str2int(cells.item(row, 5).value().bStr());
if(projLinePropertyId == "Billable")
{
salesQuotationLine.LinePropertyId = "Billable";
}
else
{
salesQuotationLine.LinePropertyId = "NonBill";
}
salesQuotationLine.InventDimId = "AllBlank";
//salesQuotationLine.InventTransId = "011499";
salesQuotationLine.insert();
info(strFmt(" %1 Project Quotationline is created" ,salesQuotationTable.QuotationId));
type = cells.item(row+1, 1).value().variantType();
}
else
{
info ("Project Quotationline is not created");
}
}
}
while (type != COMVariantType::VT_EMPTY);
}
catch (Exception::Error)
{
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();
}
}
{
workbook = workbooks.item(2);
worksheets2 = workbook.worksheets();
worksheet2 = worksheets2.itemFromNum(2);
cell = worksheet2.cells();
try
{
do
{
row++;
if(row >=1)
{
// values inserted to table
quotation = QuotationId;//cells.item(row, 1).value().bStr();
salesQuotationTable = salesQuotationTable::find(quotation);
if (!salesQuotationTable)
{
throw error(strFmt("Project %1 cannot be found", quotation));
}
ActivityNumber = cell.item(row, 2).value().bStr();
if (!ActivityNumber)
{
throw error(strFmt("Activity %1 cannot be found", activityNumber));
}
if (!smmActivities::find(ActivityNumber) && salesQuotationTable)
{
//create root
hierarchyLinkTable = HierarchyLinkTable::findRefTableRecId(tableNum(salesQuotationTable), salesQuotationTable.RecId, true);
if (!hierarchyLinkTable)
{
//create Hierarchy record
hierarchy.clear();
hierarchy.initValue();
hierarchy.Name = salesQuotationTable.QuotationName;
hierarchy.Description = salesQuotationTable.QuotationName;
hierarchy.HierarchyType = HierarchyType::Opportunity;
hierarchy.HierarchyId = salesQuotationTable.QuotationId;
hierarchy.IsActive = NoYes::Yes;
hierarchy.insert();
//create HierarchyTreeTable root record
rootHierarchyTreeTable = HierarchyTreeTable::insertRoot(hierarchy.HierarchyId, salesQuotationTable.QuotationId);
HierarchyLinkTable::insertHierarchyLink(hierarchy.HierarchyId, tableNum(salesQuotationTable), salesQuotationTable.RecId);
}
else
{
if (hierarchyLinkTable)
{
hierarchy = Hierarchy::find(hierarchyLinkTable.HierarchyId, true);
select firstonly rootHierarchyTreeTable
where rootHierarchyTreeTable.HierarchyId == hierarchyLinkTable.HierarchyId &&
rootHierarchyTreeTable.ParentElementNumber == '';
}
}
parentElementNumber = '';
//parentActivityNumber is specified then find the node as the parent
if (parentActivityNumber)
{
parentHierarchyTreeTable = HierarchyTreeTable::findActivityNode(tableNum(salesQuotationTable), salesQuotationTable.RecId, parentActivityNumber);
if (parentHierarchyTreeTable)
{
parentElementNumber = parentHierarchyTreeTable.ElementNumber;
parentHierarchyTreeTable.ElementNumber = WBS;
}
}
if (!parentElementNumber) //if parentElementNumber is blank, then use root node
{
parentElementNumber = rootHierarchyTreeTable.ElementNumber;
}
//create hierarchy tree node or activity
//if activityNumber is specified then create smmActivities. Otherwise, create a node without an activity
if (ActivityNumber)
{
// insert smmActivities record
smmActivities.clear();
smmActivities.initValue();
smmActivities.ActivityNumber = cells.item(row, 2).value().bStr();
smmActivities.Purpose = cells.item(row, 3).value().bStr();
smmActivities.Category = smmActivityCategory::Task;
smmActivities.initFromSalesQuotationTable(salesQuotationTable);
smmActivities.insertParentLink(smmActivityParentType::Quotation, salesQuotationTable.RecId, true);
smmActivities.insert();
//insert psaactivitysetup record
psaactivitysetup.CategoryDefault = cells.item(row, 4).value().bStr();
psaactivitysetup.ActivityNumber = smmActivities.ActivityNumber;
effort = cells.item(row, 5).value().bstr();
psaactivitysetup.effort = str2int (effort);
numberOfResource = cells.item(row, 6).value().bStr();
psaactivitysetup.numberofresources = str2int(numberOfResource);
psaactivitysetup.PSASchedStart = cells.item(row, 7).value().date();
psaactivitysetup.PSASchedEnd = cells.item(row, 8).value().date();
activeNumber = cells.item(row, 9).value().bStr();
psaactivitysetup.ActivityDuration = str2int(activeNumber);
psaactivitysetup.insert();
// create ProjActivity record
if (!ProjActivity::exist(smmActivities.ActivityNumber))
{
projActivity.clear();
projActivity.initFromSmmActivities(smmActivities);
projActivity.insert();
}
HierarchyTreeTable::insertActivity(hierarchy.HierarchyId,parentElementNumber,smmActivities);
}
else
{
HierarchyTreeTable::insertNode(hierarchy.HierarchyId,parentElementNumber,name);
}
//numProcessedRecords++;
insertedRecords++;
info(strFmt("%1, %2 Record Processed", smmActivities.ActivityNumber,smmActivities.Purpose));
type = cells.item(row+1, 1).value().variantType();
}
else
{
info(strfmt("%1 ActiveNumber already exist",ActivityNumber));
type = cells.item(row+1, 1).value().variantType();
}
}
}
while (type != COMVariantType::VT_EMPTY);
}
catch (Exception::Error)
{
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();
}
}
//info(strfmt("%1 records inserted succesfully"));
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();
}
please give any solution.
Thanks&Regards
Ashwini.E
