打开，编辑并保存ex

Question

我想打开一个excel文件并编辑该文件。编辑后，我想保存文件。 请看下面我目前的进展。不幸的是我出错了。我不知道为什么。你能告诉我这个错误并帮我改正吗？谢谢你的支持。期待阅读你的答案。你知道吗

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]]) 

错误消息：

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

Answer 1

ws.rows是一个生成器，因此不能迭代，可以调用该生成器上的next（）来获取值

否则使用如下

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]]) 

将excel工作表加载到表中的最佳方法是使用pandas.read_excel

import pandas as pd
df = pd.read_excel(r_filenameXLSX, sheet_name='your_sheet_name_here')
print(df['Content'])