cancel
Showing results for 
Search instead for 
Did you mean: 

JavaScript API for Excel sheet

SOLVED
Contributor

JavaScript API for Excel sheet

Hi,

 

I have 2 queries related to JavaScript API for Excel sheets/workbooks.

 

1. Is there an API/library which can be reused to compare 2 excel sheets/workbooks? If yes, please give me the details with examples. I tried searching through SmartBear documentation but could find code only to read Excel file using JavaScript.  Refered to this: https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sou...

 

2. Reading contents of the excel workbook. If the workbook has multiple sheets, is there a way to read contents of a particular sheet by passing the sheet name? For example I have an excel file named properties.xlsx which has 2 sheets: Sheet1 and Sheet2. 

 

If I want to read only contents of Sheet1, how to do that?

 

The below code which I have written doesn't make sure that only Sheet1 contents are read. It randonmly picks 1 of the sheets and reads it. Maybe its because of the property used: ActiveSheet.

Code snippet:

let Excel = getActiveXObject("Excel.Application");
Excel.Workbooks.Open("properties.xlsx");

let RowCount = Excel.ActiveSheet.UsedRange.Rows.Count;
let ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count;

for (let i = 1; i <= RowCount; i++)
{
let s = "";
for (let j = 1; j <= ColumnCount; j++)
s += (VarToString(Excel.Cells.Item(i, j)) + "\r\n");
Log.Message("Row: " + i, s);
}

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Hero

Re: JavaScript API for Excel sheet

Please keep in mind... this is a TestComplete forum so questions about APIs that are not part of TestComplete... probably not the best place for it.

That said, there are a couple of script extensions you can use that do the comparison.

 

https://support.smartbear.com/viewarticle/9046/

https://support.smartbear.com/viewarticle/42199/

 

As for reading Excel via COM, there's plenty of documentation out there for that as it is Microsoft functionality, not TestComplete that you're looking for.

 

Again, that said...  to select a particular sheet, you're going to go off of the Sheets collection of the workbook and apply the sheet name as the index.  Check out https://docs.microsoft.com/en-us/office/vba/api/excel.sheets


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
2 REPLIES 2
Highlighted
Community Hero

Re: JavaScript API for Excel sheet

Please keep in mind... this is a TestComplete forum so questions about APIs that are not part of TestComplete... probably not the best place for it.

That said, there are a couple of script extensions you can use that do the comparison.

 

https://support.smartbear.com/viewarticle/9046/

https://support.smartbear.com/viewarticle/42199/

 

As for reading Excel via COM, there's plenty of documentation out there for that as it is Microsoft functionality, not TestComplete that you're looking for.

 

Again, that said...  to select a particular sheet, you're going to go off of the Sheets collection of the workbook and apply the sheet name as the index.  Check out https://docs.microsoft.com/en-us/office/vba/api/excel.sheets


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: JavaScript API for Excel sheet

Hi Robert,

 

Thanks for the information provided.

New Here?
Join us and watch the welcome video:
Join the September Hub-bub to show off, learn and win