JavaScript API for Excel sheet

[nish_b]

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

[tristaanogre · Accepted solution]

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

[nish_b]

Hi Robert,

Thanks for the information provided.