from openpyxl import load_workbook, Workbook
写
# 写一个新的xlsx
wb_xlsx = Workbook()
wb_xlsx.create_sheet(title='花名册',index=0)
ws_xlsx = wb_xlsx.active
# title
ws_xlsx.append(['a', 'b', 'c', 'd', 'e', 'f'])
for i in range(6):
ws_xlsx.append([1,2,3,4,5,6])
wb_xlsx.save(filename='openpyxl_file.xlsx')
追加单元表
wb = load_workbook('openpyxl_file.xlsx')
wb.create_sheet(title='Word',index=0)
ws = wb.active
# title
ws.append(['a', 'b', 'c', 'd', 'e', 'f'])
for i in range(3):
ws.append([1,1,1,1,1,1])
wb.save(filename='openpyxl_file.xlsx')
读
第一种方式用openpyxl读
# 读取xlsx文件
workbook = load_workbook('D:\\sbd\\baidu\\openpyxl_file.xlsx')
# 可以使用workbook对象的sheetnames属性获取到excel文件中哪些表有数据
sheetnames = workbook.sheetnames
print(sheetnames)
# 默认只能获取第一个工作表
table = workbook.active
for sheetname in sheetnames:
table = workbook[sheetname]
# table = workbook.get_sheet_by_name(sheetname)
rows = table.max_row
cols = table.max_column
for row in range(rows):
for col in range(cols):
data = table.cell(row + 1, col + 1).value
print(data, end=' ')
pandas读
# pandas读取方式
import pandas as pd
result_a = pd.read_excel(r'./openpyxl_file.xlsx', sheet_name=sheetnames)
for sheetname in sheetnames:
result_a[sheetname].fillna('',inplace=True)
for res in result_a[sheetname].index.values:
row = result_a[sheetname].loc[res,result_a[sheetname].columns.values].to_dict()
print(row)
追加
# 追加xlsx文件
wb = load_workbook('D:\\sbd\\baidu\\openpyxl_file.xlsx')
sheet1 = wb['Word']
# 复制工作表并新建名为复制工作表的工作表
sheet2 = wb.copy_worksheet(sheet1)
sheet2.title = "复制工作表"
# for i in range(3):
# sheet1.append([3,3,3,3,3,3])
wb.save(filename='openpyxl_file.xlsx')