We are building a new experience for our SmartBear Community.
This site is now read-only. You can still browse and search as usual. Learn More

Forum Discussion

Afreen's avatar
Afreen
Occasional Contributor
7 months ago
Solved

Can we set sheet name in excel as active using python script

I am try to do an excel file comparison but unable to succeed. I want to set each sheet active by iterating through it i cant install any third party apps as well
Debugging
  • Hassan_Ballan's avatar
    Hassan_Ballan
    7 months ago

    If you’re using TestComplete’s built-in Python and can’t install additional libraries, you can work with Excel through TestComplete’s Excel object model rather than external packages. TestComplete provides the TestComplete ExcelFile object which allows you to access sheets directly by name or index.

    For example, you can iterate through the sheets like this:

    excelFile = Excel.Open("C:\\temp\\DataStorageExcel.xlsx")

for i in range(1, excelFile.SheetCount + 1):
    sheet = excelFile.SheetByIndex(i)
    Log.Message(sheet.Title)

    If you already know the sheet name, you can reference it directly:

    sheet = excelFile.SheetByTitle("Sheet1")

    This approach is documented in the TestComplete documentation for the ExcelFile object and sheet access methods: https://support.smartbear.com/testcomplete/docs/reference/program-objects/excelfile/index.html object and https://support.smartbear.com/testcomplete/docs/reference/program-objects/excelfile/sheetbytitle.htmlmethod

    In most cases you don’t need to “activate” sheets for comparison tasks. Instead, you can reference them directly using SheetByTitle or SheetByIndex and read the data from the returned sheet object.

    If this resolves your scenario, marking it as the solution helps future readers find it quickly.

scot1967's avatar
scot1967
Icon for Champion Level 3 rankChampion Level 3
7 months ago

Hello Afreen, 👋🏼

I am not much on Python but this is a JavaScipt version that will take the workbook object and Sheet Name specification and return the sheet object.

This link should explain a lot.  

https://support.smartbear.com/testcomplete/docs/reference/program-objects/excelsheet/index.html

/* JavaScript */

GetExcelFileSheetObject(objExcelWorkBook, strSheetName) {
  try {
    let objSheet = objExcelWorkBook.Sheets.Item(strSheetName);
    return objSheet;
  }
  catch(ex) {
    Log.Error("GetExcelFileSheetObject: " + ex.stack);
  }
}

AI 🤖 translates it as this...

Python Version (openpyxl):

def get_excel_file_sheet_object(workbook, sheet_name):
    try:
        sheet = workbook[sheet_name]
        return sheet
    except Exception as ex:
        print(f"GetExcelFileSheetObject: {ex}")

Usage Example:

from openpyxl import load_workbook

workbook = load_workbook("example.xlsx")
sheet = get_excel_file_sheet_object(workbook, "Sheet1")

If You Want Logging Instead of print:

import logging

def get_excel_file_sheet_object(workbook, sheet_name):
    try:
        return workbook[sheet_name]
    except Exception as ex:
        logging.error("GetExcelFileSheetObject: %s", ex)
        return None

I hope this gets you close enough to get going!

... If you find my posts helpful drop me a Like👍 Be sure to mark the post as the Solution✅ when you get one to help others out and to credit the one who helped you. 😎