1

I want to open an excel file and edit the file. After the edit I want to save the file. Please see below for my current progress. Unfortunately I get an error. I don't know why. Could you please tell me the error and help me to fix it ? Thank you in advance for your supprt. Looking forward to reading your answer. 

import openpyxl as oxl

path = '///My Documents/Python/'
fileName = "test.xlsx"    

# name of files to read from
r_filenameXLSX = path+fileName

# open the Excel file
xlsx_wb = oxl.load_workbook(filename=r_filenameXLSX)

# names of all the sheets in the workbook
sheets = xlsx_wb.get_sheet_names()

# extract the worksheet
xlsx_ws = xlsx_wb[sheets[0]]

labels = [cell.value for cell in xlsx_ws.rows[0]]

data = []  # list to hold the data

for row in xlsx_ws.rows[1:]:
    data.append([cell.value for cell in row])


print([item[labels.index('Content')] for item in data[0:10]])

Error message: 

C:/Users/user/PycharmProjects/Test/Main.py:29: DeprecationWarning: Call to deprecated function get_sheet_names (Use wb.sheetnames).
  sheets = xlsx_wb.get_sheet_names()
Traceback (most recent call last):
  File "C:/Users/user/PycharmProjects/Test/Main.py", line 34, in <module>
    labels = [cell.value for cell in xlsx_ws.rows[0]]
TypeError: 'generator' object is not subscriptable
SSharma
  • 951
  • 6
  • 15
LiaRoller
  • 13
  • 3

1 Answers1

0

ws.rows is a generator, so you cannot iterate, you can call a next() on that generator to get value,

otherwise use as below

wb = oxl.load_workbook(filename=r_filenameXLSX)
ws = wb[wb.sheetnames[0]]

labels = [cell.value for cell in next(ws.rows)]
print(labels)

# 'data' contains data starting from second row 
data = [[ws.cell(i,j).value for j in range(1, ws.max_column+1)] for i in range(2, ws.max_row+1)]

print([item[labels.index('Content')] for item in data[0:10]])

Best way to load an excel sheet to a table is using pandas.read_excel

import pandas as pd
df = pd.read_excel(r_filenameXLSX, sheet_name='your_sheet_name_here')
print(df['Content'])
Shijith
  • 4,602
  • 2
  • 20
  • 34