处理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 numbers
wb = 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, Reference
wb = Workbook()
ws = wb.active
rows = [
('月份', '苹果', '香蕉'),
(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, Reference
data = [
['水果', '销量'],
['苹果', 50],
['樱桃', 30],
['橘子', 10],
['香蕉', 40],
]
wb = Workbook()
ws = wb.active
for 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