Forum Discussion

rushikesh's avatar
rushikesh
Contributor
6 years ago
Solved

Writing data in Excel file using JavaScript is resulting in performance issue.

Writing data in Excel file using JavaScript is resulting in performance issue.

 

I am using Excel 2016 and for simple code also observing perforamnce issue. Any solution ???

 

code :

 

function WriteExcelSheet(sheetName,rowCell,columnCell,value)

          {
             var app = Sys.OleObject("Excel.Application");
             var book = app.Workbooks.Open(excelPath);
             var sheet = book.Sheets.Item(sheetName);

            app.DisplayAlerts = false;
            sheet.Cells.$set("Item", rowCell, columnCell, value);

            book.Save();
           

            app.Quit();

          }  

4 Replies

Sort By
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    6 years ago

    How frequently are you calling this code? Basically, each time it's called, you're creating another instance of the Excel.Application.  Which, BTW, IS opening and running Excel, just not with a full UI... So, everytime the below code is called, you run Excel... that will, certainly, create memory and performance issues.  You should make sure that, once you're finished with objects, you dispose of them.  Yes, there are "garbage collectors" and such, but good practice is to always free objects when you're done with them.

     

    An alternative solution which doesn't have quite so much overhead is to, instead of using Excel, use the aqFile object and associated methods and properties to write the data out to CSV.  Excel can easily read and process CSV files.  Less overhead and it's a lot more portable.

  • shankar_r's avatar
    shankar_r
    Community Hero
    6 years ago

    There are things you can reduce the performance issue by using OleObject

     

    1. Store values in the Project Variable table type 
    2. 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; 
     }
    }