Ask a Question

How to read a range of excel cells as List or List of List Python using Excel OLE Objects?

Vicky_17
New Contributor

How to read a range of excel cells as List or List of List Python using Excel OLE Objects?

I am migrating the existing vba code to py. so we are using the ole object to read excel like below.

 

can someone help to read range of cells, the documentation points to read only a single value instead of range. https://support.smartbear.com/testcomplete/docs/reference/test-objects/members/sys/oleobject-propert...

 

Even i tried to download additional samples and that also don't have any range function. i need a good documentaion for excel using ole


Excel = Sys.OleObject["Excel.Application"]
Excel.Workbooks.Open("C:\\MyFile.xlsx")

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):

         ## Is there anyway to read range or all content of the sheet to list of list?
        s = s + VarToString(Excel.Cells.Item[i, j]) + '\r\n' 
    Log.Message("Row: " + VarToString(i), s);

Excel.Quit();

6 REPLIES 6
ebarbera
Staff

Hi @Vicky_17 -

I would suggest trying to use the excel methods instead of the ole objects - these methods allow you to pull data from a single cell.

Take a look at this information : https://support.smartbear.com/testcomplete/docs/reference/program-objects/excel/index.html

Let me know if this is not an option - or if there was a reason you were looking to use the ole object over the excel object.

Thanks,
Emma

i want to read a range of cell not Single cell

 Hello @Vicky_17 - 

 

I apologize I read that post incorrectly - is the script you have there not working? Or is it the vba script and you are just having trouble with the conversion process? Also - where are you looking to store the data after you read it from the excel sheet. 

 

Thanks,

Emma

It is taking long time to load data one by one and My PC is hanging and fan is running fast once i click run .It was a python script. i want to store the read data as List of Lists in python

chriscc
Occasional Contributor

Hi Vicky,

 

I’m not well versed in Python, but you should be able to declare the range like this:

 

     Range = Excel.ActiveSheet.Range("A2:C3") 

 

So, the sample code would be something like this...changes are in bold.

 

Excel = Sys.OleObject["Excel.Application"]
Excel.Workbooks.Open("C:\\MyFile.xlsx")

Range = Excel.ActiveSheet.Range("A2:C3"); 

      ##whatever range you need…I used A2 to C3

for i in range(1, Range.Rows.Count + 1):
    s = "";
    for j in range(1, Range.Columns.Count + 1):
        s = s + VarToString(Excel.Cells.Item[i, j]) + '\r\n' 
    Log.Message("Row: " + VarToString(i), s);

Excel.Quit();

Chris
sonya_m
SmartBear Alumni (Retired)

Thanks everyone for the help!

 

@Vicky_17 have you tried the suggested approach? Did it work? Please let us know!


Sonya Mihaljova
Community and Education Specialist

cancel
Showing results for 
Search instead for 
Did you mean: