cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Contributor

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();

          }  

1 ACCEPTED SOLUTION

Accepted Solutions
Community Hero

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

A very basic example of how to write out to a text file is given at https://support.smartbear.com/testcomplete/docs/reference/program-objects/aqfile/writetotextfile.htm...

 

To write to a CSV is simply to take that code and modify it to update the output to be comma delimited rows.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
4 REPLIES 4
Highlighted
Community Hero

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

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.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
Contributor

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

@tristaanogre Thanks for the inforamtion.

 

Can you provide any refrence on how to write data in csv file in test complete.

I could get information of reading data from csv file but found no luck related to writing the data.

Community Hero

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

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;
}
}



Thanks
Shankar R

LinkedIn | CG-VAK Software | Bitbucket | shankarr.75@gmail.com

“You must expect great things from you, before you can do them”


Extension Available

Community Hero

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

A very basic example of how to write out to a text file is given at https://support.smartbear.com/testcomplete/docs/reference/program-objects/aqfile/writetotextfile.htm...

 

To write to a CSV is simply to take that code and modify it to update the output to be comma delimited rows.


Robert Martin
[Community Expert Group]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Extensions available
New Here?
Join us and watch the welcome video: