Ask a Question

How to write into excel file using Javascript?

SOLVED
shane_manjunath
Occasional Contributor

How to write into excel file using Javascript?

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

6 REPLIES 6
HKosova
SmartBear Alumni (Retired)

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

Helen Kosova
SmartBear Documentation Team Lead
________________________
Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
iamraj09
Contributor

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
SmartBear Alumni (Retired)

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

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager



Join us in this interview with Shankar Ramasamy, a SmartBear Community Leader. Join the SmartBear Community today: https://community.smartbear.com/

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. 

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...


Robert Martin
[Hall of Fame]
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
Vegas Thrill Rider
Extensions available

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. 

cancel
Showing results for 
Search instead for 
Did you mean: