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