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.

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

How are you accessing Excel via https://support.smartbear.com/testcomplete/docs/testing-with/working-with-external-data-sources/excel/working-via-com.html or https://support.smartbear.com/testcomplete/docs/testing-with/working-with-external-data-sources/excel/index.html? 

You can either create your own method to perform the comparison (shown in In Script Tests) or use the https://support.smartbear.com/testcomplete/docs/testing-with/checkpoints/excel/index.html. 

Working with Excel COM gives you more flexibility, and there's plenty of examples on the Internet. It does require Excel to be installed though. Whereas, Excel Object has limited functionality and is specific to TestComplete.

Snippets of code to access work sheets,

wb = excel.Workbooks.Open(r"C:\path\file.xlsx")
sheet1 = wb.Worksheets(1) # Accessing sheet via index
sheet2 = wb.Worksheets(2)

sheet1 = wb.Worksheets("Sheet1") # Accessing sheet via name
sheet2 = wb.Worksheets("Sheet2")

Log.Message(wb.Worksheets.Count) # Number of worksheets

# Loop through all worksheets
for sheet in wb.Worksheets:
    Log.Message(sheet.Name)