CODEWITHSUNDEEP
pythonexcelwin32com
Dantuzzo
Dantuzzo

Reputation: 271

Python: Iterating through excel column and getting values with win32com

I'm using Python 3.5 and win32com to iterate through an excel document rows (only from the first column) and get all the values from it, the best would be in a simple list. I tried the following implementation but there are a few problems with it: 

excel_app = win32com.client.Dispatch('Excel.Application')
workbook = excel_app.Workbooks.Open(myfile2)
worksheet = workbook.Sheets('Sheet1')
data = excel_app.Range("A1:A60")
print(data)

for i in data:
    if i is not None:
        print(i)

The problems are mainly with: "data = excel_app.Range("A1:A60")"

  1. This takes the first 60 rows, I would like to get only as many rows as there is data in the excel file, otherwise either i don't get enough data, or i get too much and i end up with a bunch of 'None'

  2. It returns a Tuple, so i can't modify it in order to remove the 'None' values if i get more data than the spreadsheet has.

Upvotes: 1

Views: 7946

Answers (2)

Alan
Alan

Reputation: 3042

You can get the row count by using:

worksheet.UsedRange.Rows.Count

where UsedRange represents all the cells that have contained a value at any time.

Once you have the data in your tuple, you can copy it into a list for processing - e.g. new_list = data[:].

If you want to copy into a new list and remove None values at the same time:

new_list = [ item for item in data if item is not None]

Upvotes: 4

SpghttCd
SpghttCd

Reputation: 10880

If you wouldn't mind using xlrd it would be much easier I think. This would be sth like

import xlrd

xlf = xlrd.open_workbook('/wherever/file/may/roam/asdf.xlsx')
xls = xlf.sheet_by_name('Sheet1')
data = xls.col_values(0)

Upvotes: 1

Related Questions