处理execl表格的库----openpyxl

一、打开现有的工作文档

import openpyxl# 打开工作簿
workbook = openpyxl.load_workbook('download/nihao.xlsx')# 获取工作表
sheet = workbook['Sheet1']# 读取单元格数据
cell_value = sheet['A1'].value
print(cell_value)

二、创建新的execl文档

import openpyxl# 创建新的工作簿
workbook = openpyxl.Workbook()# 获取默认工作表
sheet = workbook.active# 写入数据到单元格中
sheet['A1'] = 'hello'
sheet['B1'] = 'world'# 保存工作铺
workbook.save("download/new_example.xlsx")

三、读取数据

import openpyxl# 打开工作簿
workbook = openpyxl.load_workbook('download/nihao.xlsx')# 获取工作表
sheet = workbook['Sheet1']# 获取 sheet
ws3 = workbook.get_sheet_by_name(u"Sheet1")# 读取单元格数据
cell_value = sheet['A1'].value
print(cell_value)# 通过行和列索引读取数据
cell_value = sheet.cell(row=2, column=3).value
print(cell_value)

四、写入数据到单元格

import openpyxl# 创建新的工作簿
workbook = openpyxl.Workbook()# 获取默认工作表
sheet = workbook.active# 写入数据到单元格中
sheet['A1'] = 'hello'
sheet['B1'] = 'world'
sheet.cell(row=2, column=2, value='2-2')# 保存工作铺
workbook.save("download/new_example.xlsx")

五、创建新工作表

import openpyxl# 创建新的工作簿
workbook = openpyxl.Workbook()# 创建新的工作sheet
new_sheet = workbook.create_sheet(title='newsheet')#创建一个 sheet 名为 sheet
ws1 = wb.create_sheet("sheet")  # 设置 sheet 标题
ws1.title = "新表单" # 创建一个 sheet，插入到最前面 默认插在后面
ws2 = wb.create_sheet("mysheet", 0)# 设置 sheet 标题
ws2.title = u"你好" # 保存工作铺
workbook.save("download/new_example.xlsx")

六、复制工作表

import openpyxl# 创建新的工作簿
workbook = openpyxl.load_workbook("download/nihao.xlsx")# 复制工作表
copied_sheet = workbook.copy_worksheet(workbook['Sheet1'])
copied_sheet.title = 'copy of Sheet1'# 保存工作铺
workbook.save("download/nihao.xlsx")

七、删除工作表

import openpyxl# 创建新的工作簿
workbook = openpyxl.load_workbook("download/nihao.xlsx")# 删除sheet
del workbook['copy of Sheet1']# 删除 sheet
workbook.remove(copy of Sheet1)# 保存工作铺
workbook.save("download/nihao.xlsx")

八、设置字体样式

import openpyxl
from openpyxl.styles import Font# 打开工作簿
workbook = openpyxl.load_workbook('download/nihao.xlsx')# 获取工作表
sheet = workbook['Sheet1']# 创建字体样式
font = Font(name='Arial', size=14, bold=True)# 将字体样式应用到单元格
sheet['A1'].font = font# 保存工作簿
workbook.save('download/nihao.xlsx')

九、设置背景颜色

import openpyxl
from openpyxl.styles import Font, PatternFill# 打开工作簿
workbook = openpyxl.load_workbook('download/nihao.xlsx')# 获取工作表
sheet = workbook['Sheet1']# 创建背景颜色样式
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')# 设置 sheet 标签背景色
ws1.sheet_properties.tabColor = "1072BA" # 将背景颜色应用到单元格
sheet['B2'].fill = fill# 保存工作簿
workbook.save('download/nihao.xlsx')

十、设置边框

import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side# 打开工作簿
workbook = openpyxl.load_workbook('download/nihao.xlsx')# 获取工作表
sheet = workbook['Sheet1']# 创建边框样式
border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))# 将边框应用到单元格
sheet['C2'].border = border# 保存工作簿
workbook.save('download/nihao.xlsx')

十一、批量操作

指定行列

# 操作单列
for cell in ws["A"]:print(cell.value)
# 操作单行
for cell in ws["1"]:print(cell.value)
# 操作多列
for column in ws['A:C']:for cell in column:print(cell.value)
# 操作多行
for row in ws['1:3']:for cell in row:print(cell.value)
# 指定范围
for row in ws['A1:C3']:for cell in row:print(cell.value)

所有行或者列

# 所有行
for row in ws.iter_rows():for cell in row:print(cell.value)
# 所有列
for column in ws.iter_cols():for cell in column:print(cell.value)

设置整行数据

ws.append((1,2,3))

合并单元格

# 合并
ws.merge_cells('A2:D2')
# 解除合并
ws.unmerge_cells('A2:D2')ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)

十二、单元格格式

OpenPyXl 用6种类来设置单元格的样式

NumberFormat 数字
Alignment 对齐
Font 字体
Border 边框
PatternFill 填充
Protection 保护

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protection
from openpyxl.styles import numberswb = Workbook()
ws = wb.active
ws.cell(row=1, column=1, value='宋体').font = Font(name=u'宋体', size=12, bold=True, color='FF0000')
ws.cell(row=2, column=2, value='右对齐').alignment = Alignment(horizontal='right')
ws.cell(row=3, column=3, value='填充渐变色').fill = PatternFill(fill_type='solid', start_color='FF0000')
ws.cell(row=4, column=4, value='设置边线').border = Border(left=Side(border_style='thin', color='FF0000'), right= Side(border_style='thin', color='FF0000'))
ws.cell(row=5, column=5, value='受保护的').protection = Protection(locked=True, hidden=True)
ws.cell(row=6, column=6, value=0.54).number_format =numbers.FORMAT_PERCENTAGE

引入字体类
用 cell 方法，为单元格设置值的同时，设置格式
每种格式都有特定的属性，为其设置特定的格式对象
数字格式有点区别，通过设置格式名称来完成，numbers.FORMAT_PERCENTAGE 是个字符串
Border 类，需要配合 Side 类使用，它们都在 openpyxl.styles 中定义
需要注意的是，单元格样式属性只能通过样式对象赋予，而无法通过样式属性来修改，例如 ws.cell(1, 1).font.color = ‘00FF00’ 会报错，如果真要换，需要重新创建一个样式实体，重新赋值
上面展示的是单个单元格格式的设置，也可以批量设置，有两种方式，一种是循环范围内的所有单元格，逐个设置，另一种是对整列或者整行设置：

font = Font(bold=True)# 遍历范围内的单元格
for row in ws['A1:C3']:for cell in row:cell.font = font# 设置整行
row = ws.row_dimensions[1]
row.font = font# 设置整列
column = ws.column_dimensions["A"]
column.font = font

十三、图表

from openpyxl import Workbook
from openpyxl.chart import BarChart, Referencewb = Workbook()
ws = wb.activerows = [('月份', '苹果', '香蕉'),(1, 43, 25),(2, 10, 30),(3, 40, 60),(4, 50, 70),(5, 20, 10),(6, 10, 40),(7, 50, 30),
]for row in rows:ws.append(row)chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "销量柱状图"
chart1.y_axis.title = '销量'
chart1.x_axis.title = '月份'data = Reference(ws, min_col=2, min_row=1, max_row=8, max_col=3)
series = Reference(ws, min_col=1, min_row=2, max_row=8)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(series)
ws.add_chart(chart1, "A10")

引入柱状图类 BarChart 和 数据应用类 Reference
创建 Workbook，并为活动 Sheet 添加数据
创建柱状图对象，设置图表属性，type 为 col 为列状图，bar 为水平图
创建数据引用对象，指定从那个 sheet 以及数据范围
创建系列数据引用对象
将数据和系列加入到图表对象中
最后将图表对象用 add_chart 添加到 sheet 里

十四、圆饼图

from openpyxl import Workbook
from openpyxl.chart import PieChart, Referencedata = [['水果', '销量'],['苹果', 50],['樱桃', 30],['橘子', 10],['香蕉', 40],
]wb = Workbook()
ws = wb.activefor row in data:ws.append(row)pie = PieChart()
pie.title = "水果销量占比"
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)ws.add_chart(pie, "D1")

引入饼图类 PieChart 和 数据应用类 Reference
创建图表数据
创建图表对象，设置图表标题
定义标签数据引用和数据引用，并将其加入到图表
将图表对象添加到 sheet 的指定位置

注：https://openpyxl-chinese-docs.readthedocs.io/zh-cn/latest/index.html