openpyxl - read only one column from excel file in

Question

I want to pull only column A from my spreadsheet. I have the below code, but it pulls from all columns.

from openpyxl import Workbook, load_workbook

wb=load_workbook("/home/ilissa/Documents/AnacondaFiles/AZ_Palmetto_MUSC_searchterms.xlsx", use_iterators=True)
sheet_ranges=wb['PrivAlert Terms']

for row in sheet_ranges.iter_rows(row_offset=1): 
    for cell in row:
        print(cell.value)

Answer 1

By using openpyxl library and Python's list comprehensions concept:

import openpyxl

book = openpyxl.load_workbook('testfile.xlsx')
user_data = book.get_sheet_by_name(str(sheet_name))
print([str(user_data[x][0].value) for x in range(1,user_data.max_row)])

It is pretty amazing approach and worth a try

Answer 2

Use ws.get_squared_range() to control precisely the range of cells, such as a single column, that is returned.

Answer 3

Using openpyxl

from openpyxl import load_workbook
# The source xlsx file is named as source.xlsx
wb=load_workbook("source.xlsx")

ws = wb.active
first_column = ws['A']

# Print the contents
for x in xrange(len(first_column)): 
    print(first_column[x].value) 

Answer 4

I would suggest using the pandas library.

import pandas as pd
dataFrame = pd.read_excel("/home/ilissa/Documents/AnacondaFiles/AZ_Palmetto_MUSC_searchterms.xlsx", sheetname = "PrivAlert Terms", parse_cols = 0)

If you don't feel comfortable in pandas, or for whatever reason need to work with openpyxl, the error in your code is that you aren't selecting only the first column. You explicitly call for each cell in each row. If you only want the first column, then only get the first column in each row.

for row in sheet_ranges.iter_rows(row_offset=1): 
    print(row[0].value)

Answer 5

Using ZLNK's excellent response, I created this function that uses list comprehension to achieve the same result in a single line:

def read_column(ws, begin, columns):
  return [ws["{}{}".format(column, row)].value for row in range(begin, len(ws.rows) + 1) for column in columns]

You can then call it by passing a worksheet, a row to begin on and the first letter of any column you want to return:

column_a_values = read_column(worksheet, 2, 'A')

To return column A and column B, the call changes to this:

column_ab_values = read_column(worksheet, 2, 'AB')

Answer 6

this is an alternative to previous answers in case you whish read one or more columns using openpyxl

import openpyxl

wb = openpyxl.load_workbook('origin.xlsx')
first_sheet = wb.get_sheet_names()[0]
worksheet = wb.get_sheet_by_name(first_sheet)

#here you iterate over the rows in the specific column
for row in range(2,worksheet.max_row+1):  
    for column in "ADEF":  #Here you can add or reduce the columns
        cell_name = "{}{}".format(column, row)
        worksheet[cell_name].value # the value of the specific cell
        ... your tasks... 

I hope that this be useful.