Read data in Excel column into Python list

Question

I am using python xlwings to read a column of data in Excel 2013. Column A is populated with numbers. To import this column into a python list py_list, I have the following code;

import xlwings as xw

wb = xw.Book('BookName.xlsm')
sht = xw.Book('SheetName')
py_list = sht.range('A2:A40').value

The above code works if the column data is populated at A2:A40. However, the column data can keep growing. Data can grow and stretch to A2:A46 or A2:A80. The last row is empty. It is not known at compile time how many rows of data is in this column.

How can I modify the code to detect the empty cell at the last row so that the range of data can be read by py_list?

I am open to using other python libraries to read the Excel data besides xlwings. I am using python v3.6

Answer 1

I say this a lot about reading files in from csv or excel, but I would use pandas.

import pandas as pd

df = pd.read_excel('filename.xlsm', sheetname=0) # can also index sheet by name or fetch all sheets
mylist = df['column name'].tolist()

an alternative would be to use a dynamic formula using soemthing like OFFSET in excel instead of 'A2:A40', or perhaps a named range?

Answer 2

I know this is an old question, but you can also use openpyxl

from openpyxl import load_workbook
wb = load_workbook("BookName.xlsx")  # Work Book
ws = wb.get_sheet_by_name('SheetName')  # Work Sheet
column = ws['A']  # Column
column_list = [column[x].value for x in range(len(column))]

Notes:

• Pandas is an awesome library, but installing it just to read an excel column into a list is an overkill IMHO.

• xlrd is not maintained anymore. From the xlrd github page

  PLEASE NOTE: This library currently has no active maintainers. You are advised to use OpenPyXL instead.