Ask a Question

How to select Excel sheet in python?

SOLVED
hxiao
Occasional Contributor

How to select Excel sheet in python?

Hi,

From https://support.smartbear.com/viewarticle/69443/#_ga=1.128686159.2137448148.1449520310

I have python code to read Excel file:

 

def ReadDataFromExcel():
Excel = Sys.OleObject["Excel.Application"]
Excel.Workbooks.Open("C:\\Work\\TestBook.xls")
RowCount = Excel.ActiveSheet.UsedRange.Rows.Count
ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count
for i in range(1, RowCount + 1):
s = "";
for j in range(1, ColumnCount + 1):
s = s + VarToString(Excel.Cells.Item[i, j]) + '\r\n'
Log.Message("Row: " + VarToString(i), s);
Excel.Quit();

 

However I didn't find API how to select a sheet for multiple sheet Excel file?   Please advice.

 

Thanks

Heather

 

1 ACCEPTED SOLUTION

Accepted Solutions
ghuff2
Contributor

Re: How to select Excel sheet in python?

The Sheets collection has to be accessed using Item just like the Cells collection.

 

For example, your line of code should change to this:

 

sheet = Excel.Workbooks.open(file).Sheets.Item["Sheet1"]

Hope that helps.

View solution in original post

8 REPLIES 8
NisHera
Valued Contributor

Re: How to select Excel sheet in python?

i'm not a python guy ...

just try 

sheet = Excel.Workbooks.Open("C:\\Work\\TestBook.xls").Sheets("Sheet1")
rowCount = sheet.UsedRange.Rows.Count
hxiao
Occasional Contributor

Re: How to select Excel sheet in python?

Hi NisHera,

 

Thank you for your reply. Actually I have tried the same thing, but it didnot work. The error is "The specified object is not callable."

 

I also tried :

sheet = Excel.Workbooks.open(file).Sheets["Sheet1"],  the error is "The specified object is not indexable."  so it must be some special way in python to select sheet.

 

 

 

ghuff2
Contributor

Re: How to select Excel sheet in python?

Here's the function I use to open an excel spreadsheet for a specific sheet and grab all the columns and their values. It uses the ACE Driver instead of opening Excel itself as an OLE Object. That way you can read from Spreadsheets without needing to have an Excel license on each Test Host. You can download the ACE Driver here

 

def read_excel_to_dict(file_path, sheet_name):
  """
  Function will open an Excel file at the given sheet, then put the values into a dictionary with the
  key being the column name. The top row in the Excel file is considered the Column Name. The top row
  is only considered a Column if a registry key is set (it normally is by default). 
  See http://support.smartbear.com/viewarticle/70340/

  Uses ACE driver (Excel runtime not required, but can't modify the spreadsheet)
  
  Parameters
  --------------
  file_path : string
    File path to the excel spreadsheet
  
  sheet_name : string
    Name of the specific sheet to be opened
  
  Returns
  ---------
  return_dict : dict
    Dictionary object with each column (first row) mapped to its respective value (second row)
  """
  DDT.ExcelDriver(file_path, sheet_name, True)
  driver = DDT.CurrentDriver
  driver.First()
  return_dict = dict()
for i in range(DDT.CurrentDriver.ColumnCount): return_dict[driver.ColumnName[i]] = aqConvert.VarToStr(driver.Value[i]) return return_dict

 

hxiao
Occasional Contributor

Re: How to select Excel sheet in python?

Hi ghuff2,

 

Thank you for your reply. I am sorry I didnot make it clear.   I need to modify the sheet.   Maybe import python library to handle this.  I just think Testcomplete may has its own solution.  

ghuff2
Contributor

Re: How to select Excel sheet in python?

The Sheets collection has to be accessed using Item just like the Cells collection.

 

For example, your line of code should change to this:

 

sheet = Excel.Workbooks.open(file).Sheets.Item["Sheet1"]

Hope that helps.

View solution in original post

hxiao
Occasional Contributor

Re: How to select Excel sheet in python?

Hi ghuff2,

      With your solution I can read sheet in python now.   Thank you very much.

hxiao
Occasional Contributor

Re: How to select Excel sheet in python?

Sorry, my bad, I didnot check TC's samples installed. Actually I forgot they have bunches of examples there.

TanyaYatskovska
Community Manager

Re: How to select Excel sheet in python?

Getting data from Excel is always a tough question. There are many ways of how it can be done. It's very important to choose the best way based on the current requirements. Our SmartBear Community Leader in TestComplete, @shankar_r, shares best practices with us. Watch the interview here:

https://www.youtube.com/watch?v=3zQ_1xhokVQ&feature=youtu.be&t=552

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager


Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Join us in this interview with Shankar Ramasamy, a SmartBear Community Leader. Join the SmartBear Community today: https://community.smartbear.com/
cancel
Showing results for 
Search instead for 
Did you mean: