Read data in Excel column into Python list

I found this as the easiest way to create lists from the entire columns in excel and it only takes the populated excel cells. import pandas as pd import numpy as np

#Insert complete path to the excel file and index of the worksheet
df = pd.read_excel("PATH.xlsx", sheet_name=0)
# insert the name of the column as a string in brackets
list1 = list(df['Column Header 1']) 
list2 = list(df['Column Header 2'])

print(list1)
print(list2)

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?

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.

I went through xlwings documentation to look for something, didn't find something like this, but you can always try and go around this:

temp = [x for x in xw.Range('A2:A200').value if x != None] #A200 just put a big number..

or I don't know try this:

from itertools import takewhile
temp =[takewhile(lambda x: x != None, xw.Range('A2:A70').value)]
while True:
    try:
         next(temp)
    except StopIteration:
         break

at line 2, at first I tried doing something like this:

temp =[lambda x: x for x in xw.Range('D:D').values if x != None else exit()] #or to replace this with quit() but there is no option to break lambdas as far as I know

another option:

temp = iter(xw.Range('A:A').value)
list = []
a = next(temp)               #depending your first cell starts at row 1
while a != None:             #might want zeros or '' etc
    list.append(a)
    a = next(temp)