Forum Discussion

nish_b's avatar
nish_b
Contributor
6 years ago

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-sources/excel/working-via-com.html

 

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