[Python知识库]openpyxl对xlsx文件的读写操作

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