There are things you can reduce the performance issue by using OleObject
- Store values in the Project Variable table type
- End of the run create a function that can covert Project Variable into the Excel Application
For Example:
function WriteExcelDataToPV(pvName,rowCell,columnCell,value){
Project.Variables.VariableByName(pvName).$set("Item", rowCell, columnCell, value);
}
//Converting pv to Excel
function convertPVToExcel(strexcelFileName,pvName){
var excelObject, excelWorkbookObj,excelMainSheet;
try
{
var pv = Project.Variables.VariableByName(pvName);
excelObject = Sys.OleObject("Excel.Application");
excelObject.Visible = false;
excelObject.DisplayAlerts = false;
excelWorkbookObj = excelObject.Workbooks.Add();
excelWorkbookObj.SaveAs(strexcelFileName);
excelWorkbookObj.Close();
excelWorkbookObj = null;
excelWorkbookObj = excelObject.Workbooks.Open(strexcelFileName);
excelMainSheet = excelWorkbookObj.Sheets.Item("Sheet1");
excelMainSheet.Name = "Data";
//adding columns
for(var i = 0 ; pv.ColumnCount ; i++){
excelMainSheet.Cells(0,i).Value = pv.ColumnName(i);
}
//adding row data
for(var r = 0 ; r < pv.RowCount ; pv++){
for(var i = 0 ; pv.ColumnCount ; i++){
excelMainSheet.Cells(r,i).Value = pv.ColumnName(i);
}
}
excelWorkbookObj.Save();
excelWorkbookObj.Close();
}
catch(ex)
{
Log.Error("Not able to create excel file. Error Description: " + ex.stack);
}
finally
{
excelObject.Visible = true;
excelObject.DisplayAlerts = true;
excelObject.Quit()
//CleaningUp
excelObject = null;
excelWorkbookObj = null;
excelMainSheet = null;
}
}