openpyxl对excel报表进行基本操作（一）

安装openpyxl

pip install openpyxl

excel文件的创建和读取

# coding=utf-8

from openpyxl import load_workbook, Workbook
wb = load_workbook(filename='test.xlsx') # 默认一次性全部读取
print(wb)
# 新建一个工作簿，并插入数据
wb = Workbook()
ws = wb.active # 激活sheet
ws['A1'] = 1 # 单元格A1赋值
ws['B1'] = 2 
wb.save(filename='w1.xlsx')

保存结果如下：

对单元格以及多行赋值

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.cell(row=1, column=1).value = '第一行第一列'
ws.cell(row=1, column=2).value = '第一行第二列'

# 对一行单元格赋值
ws.append([1, 2, 3, 4, 5])
ws.append({"C": 'A', "B": "B"})

# 对多行进行赋值
data = [
    ['Fruit', 2011, 2012, 2013],
    ['Apples', 11, 12, 13],
    ['Bananas', 21, 22, 23],
]
for row in data:
    ws.append(row)

wb.create_sheet(title='测试', index=3)  # index表示索引，在第几个sheet后面
wb.create_sheet(title='测试', index=1)

# 拷贝sheet
wb.copy_worksheet(ws)  # 拷贝ws的sheet，生成名称为Sheet Copy
wb.save(filename='w2.xlsx')

保存结果如下：

创建和删除sheet

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.title = 'Sheet名称'
ws.sheet_properties.tabColor = "FFEE0000"  # 更改sheet的标签颜色

# 删除sheet
ws2 = wb.create_sheet()
ws3 = wb.create_sheet()

wb.remove(ws2)
wb.save('w3.xlsx')

保存结果如下：