Forum Discussion

Vicky_17's avatar
Vicky_17
New Contributor
4 years ago

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-property-sys-object.html

 

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

Script Tests
  • chriscc's avatar
    chriscc
    Contributor
    4 years ago

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

    • sonya_m's avatar
      sonya_m
      SmartBear Alumni (Retired)
      4 years ago

      Thanks everyone for the help!

       

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

    • Vicky_17's avatar
      Vicky_17
      New Contributor
      4 years ago

      i want to read a range of cell not Single cell

      • ebarbera's avatar
        ebarbera
        Staff
        4 years ago

         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