How to write into excel file using Javascript?

[shane_manjunath]

I have written the following javascript to write into excel.However "book.Sheets" doesn't seem to exist so I am unable to create sheet object

Also when It tries to execute the code - app.Cells(1,1) = "test"; --- get the error that the function does not exist

var fileName = "C:\\Files\\test.xlsx";
var sheetName = "Sheet1";

var app = Sys.OleObject("Excel.Application")

app.Visible = "True";

var book = app.Workbooks.Open(fileName);

var sheet = book.Sheets(sheetName);

rowCount = app.ActiveSheet.UsedRange.Rows.Count + 1;
colCount = 1;

app.Cells(1,1) = "test";

Please suggest if I am missing anything?Thanks in advance

[HKosova · Accepted solution]

In JavaScript (not JScript) you need to access Excel collection items by using the .Items property:

book.Sheets(sheetName) => book.Sheets.Item(sheetName)
app.Cells(1,1) => app.Cells.Item(1,1)

etc.

More details here: https://support.smartbear.com/viewarticle/82954/#collections

Also, you can't assign to a cell directly because it's an object, you need to use something like:

app.Cells.Item(1,1).Value2 = "test";

[iamraj09]

var fileName = "C:\\Files\\test.xlsx";
var sheetName = "Sheet1";

var app = Sys.OleObject("Excel.Application")

app.Visible = "True";

var book = app.Workbooks.Open(fileName);

var sheet = book.Sheets(sheetName);    

rowCount = app.ActiveSheet.UsedRange.Rows.Count + 1;
colCount = 1;

sheet.Cells(1,1) = "test";  //-----> try sheet.Cells(1,1)= "test";  instead of  app.Cells(1,1)= "test";

[TanyaYatskovska]

Hi,

Getting data from Excel is always a tough question. There are many ways of how it can be done. It's very important to choose the best way based on the current requirements. Our SmartBear Community Leader in TestComplete, @shankar_r, shares best practices with us. Watch the interview here:

https://www.youtube.com/watch?v=3zQ_1xhokVQ&feature=youtu.be&t=552

[forrestaustin]

Can you summarize what he said in that interview? I just watched it and I literally could not understand any of his response due to poor mic quality. 

[tristaanogre]

Question:

What are you trying to achieve by writing out to Excel?  A lot of the suggestions on how to do so and what to do are dependant, somewhat, on the specific use case.

Generally speaking, to access an excel sheet and write out to it, you're going to want to use the COM object of Excel.  High level is documented here https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sou...

[forrestaustin]

Thanks for the link! I'll check it out today. I have a wpf application that has the option to create a 'print out' form in excel. Each page already has an excel template to sort of mirror the wpf page, but I would want to check and make sure the data from the excel print form matches the data that was just entered into the form. The location the data appears in the print out is hard coded so that should make it relatively easy to do I imagine.