一、工作簿的基本操作
(一)创建新工作簿
from openpyxl import Workbook
# 创建工作簿
wb = Workbook()
# 创建工作簿时会默认生成一个Sheet工作表,读取默认选中的工作表
ws = wb.active
# 创建工作表,默认创建到最后,0代表创建到最前,-1代表创建到倒数第二个
ws1 = wb.create_sheet('表1', 0)
ws3 = wb.create_sheet('表3', -1)
ws2 = wb.create_sheet('表2', 1)
# 重命名工作表
ws1.title = "Sheet1"
ws2.title = "Sheet2"
ws3.title = "Sheet3"
# 删除工作表
wb.remove(ws)
# 显示所有工作表
print(wb.sheetnames)
# 保存工作簿,同名则覆盖
wb.save("工作簿1.xlsx")
# ['Sheet1', 'Sheet2', 'Sheet3']
(二)读取工作簿
from openpyxl import load_workbook
wb = load_workbook("工作簿1.xlsx")
print(wb.sheetnames)
# ['Sheet1', 'Sheet2', 'Sheet3']
二、数据操作
(一)单元格
单元格的数据类型
# n 数值, s 字符串, d 日期时间
ws.["A1"].data_type # 查看单元格的数据类型
选择单元格
ws["A2"]
ws.cell(2, 1) # 先行后列,索引从1开始
# 可切片访问多个单元格、多行、多列
ws["A2:D3"]
ws["2:3"]
ws["A:D"]
# 查看单元格的值
ws["A2"].value
ws.cell(2, 1).value # 先行后列,索引从1开始
# 对单元格的值进行赋值
ws["A1"].value = "Python"
ws.cell(2, 2).value = 100 # 先行后列,索引从1开始
ws.cell(3, 3).value = datetime.datetime.now()
ws.cell(4, 4).value = "=SUM(B2+100)" # 插入公式
# 查看所有单元格的值
ws.values # 以行的方式调取
合并/取消合并单元格
ws.merge_cells("A2:D3")
ws.unmerge_cells("A2:D3")
# 要选择的单元格为合并后的单元格时,取其左上方地址
移动单元格
ws.move_range("A2:B3", rows=-1, cols=2)
# A2:B3范围内的单元格向上移动一行,向右移动两列,目标单元格里的内容将被覆盖
(二)行与列
插入行和列
ws.insert_rows(2) # 第一、二行中间插入一行
ws.insert_cols(3) # 第二、三列中间插入一列
删除行和列
ws.delete_rows(2, 3) # 从第二行开始,共删除三行
ws.delete_cols(2, 3) # 从第二列开始,共删除三列
调整行高和列宽
ws.row_dimensions[1].height = 25
ws.column_dimensions['B'].width = 10
三、单元格格式
(一)数字
ws.cell(1, 1).number_format = 'General'
# 常用的数字类型:("0", "0.00", "0%", "0.00%", "0.00E+00", "yyyy/mm/dd", "h:mm:ss AM/PM")
(二)对齐
from openpyxl.styles import Alignment
alignment = Alignment(horizontal="left", vertical="top", text_rotation=0, indent=1, wrap_text=False, shrink_to_fit=False)
# horizontal:水平对齐("general", "left", "center", "right", "fill",
# "justify", "centerContinuous", "distributed")
# vertical:垂直对齐("top", "center", "bottom", "justify", "distributed")
# text_rotation:方向;indent:缩进的字符量;
# wrap_text:自动换行;shrink_to_fit:缩小字体填充;
ws.cell(1, 1).alignment = alignment
(三)字体
from openpyxl.styles import Font
font = Font(size=11, name="宋体", color="FF0000", bold=False, italic=False, strike=False, underline=None, vertAlign=None)
# bold:加粗;italic:倾斜;strike:删除线;
# underline:下划线('single', 'double', 'singleAccounting', 'doubleAccounting')
# vertAlign: 上下标('superscript', 'subscript', 'baseline')
ws.cell(1, 1).font = font
(四)边框
from openpyxl.styles import Border, Side
side1 = Side(border_style="thin", color="FF0000")
side2 = Side(border_style="dashed", color="0000FF")
border = Border(left=side1, right=side1, top=side1, bottom=side1, diagonal=side2, diagonalDown=True, diagonalUp=True)
# border_style:边框样式('dashDot','dashDotDot', 'dashed','dotted','double',
# 'hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
# 'mediumDashed', 'slantDashDot', 'thick', 'thin')
# diagonalDown:右斜线;diagonalUp:左斜线;
ws.cell(1, 1).border = border
当设置合并后的单元格的边框时,会出现边框显示不全的现象,可遍历合并后的单元格的子单元格,分别设置边框。
for cell_row in ws["E2:H4"]:
for cell in cell_row:
cell.border = border
(五)填充
from openpyxl.styles import PatternFill
fill = PatternFill(fill_type=None, start_color="FF0000", end_color="0000FF")
# fill_type:图案样式('lightGrid', 'darkGrid', 'lightHorizontal', 'darkGray', 'darkDown',
# 'lightTrellis', 'darkUp', 'mediumGray', 'darkHorizontal', 'solid',
# 'lightDown', 'lightUp', 'lightVertical', 'gray0625', 'lightGray',
# 'gray125', 'darkTrellis', 'darkVertical');
# start_color:图案颜色;end_color:背景色;
ws.cell(1, 1).fill = fill
(六)保护
from openpyxl.styles import Protection
protection = Protection(locked=True, hidden=True)
# locked:锁定单元格;hidden:隐藏公式;
ws.cell(1, 1).protection = protection
四、图表
(一)柱形图与条形图
from openpyxl.chart import BarChart, BarChart3D, Reference
from openpyxl import load_workbook
wb = load_workbook("工作簿1.xlsx")
ws = wb["Sheet1"]
chart = BarChart() # 三维柱形图:chart = BarChart3D()
chart.title = '人口曲线'
chart.type = "col" # "col"为柱形图,"bar"为条形图
chart.style = 6 # 图表样式,1-48
chart.x_axis.title = '年份'
chart.y_axis.title = '人口(亿)'
chart.height = 8 # 高度8厘米
chart.width = 15 # 宽度15厘米
x_label = Reference(ws, min_col=1, min_row=2, max_row=11) # 预制x轴
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=11) # 预制y轴
chart.add_data(data, titles_from_data=True) # 添加y轴数据
chart.set_categories(x_label) # 添加x轴数据;注意,要先添加y轴数据,再添加x轴数据
ws.add_chart(chart, 'E1') # 添加图表
wb.save("工作簿2.xlsx")
(二)折线图
from openpyxl.chart import LineChart, LineChart3D, Reference
from openpyxl import load_workbook
wb = load_workbook("工作簿1.xlsx")
ws = wb["Sheet2"]
chart = LineChart() # 三维折线图:chart = LineChart3D()
chart.title = '价格曲线'
chart.style = 6 # 图表样式,1-48
chart.x_axis.title = '年份'
chart.y_axis.title = '售价'
chart.height = 8 # 高度8厘米
chart.width = 15 # 宽度15厘米
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=11) # 预制y轴
chart.add_data(data, titles_from_data=True) # 添加y轴数据
# 第一列数据
style1 = chart.series[0]
style1.marker.symbol = "circle"
# 标记点的样式('circle', 'square', 'star', 'triangle', 'x', 'diamond', 'dash', 'picture', 'dot', 'plus', 'auto')
style1.marker.graphicalProperties.solidFill = "FF0000" # 标记点的颜色
style1.graphicalProperties.line.solidFill = "0000FF" # 线条的颜色
style1.graphicalProperties.line.dashStyle = "dot"
# 线条的样式('sysDashDotDot', 'dot', 'sysDot', 'dash', 'lgDash', 'lgDashDot', 'lgDashDotDot', 'solid', 'sysDash', 'dashDot', 'sysDashDot')
style1.graphicalProperties.line.width = 30000 # 线条宽度,单位为EMUS,1cm约为360000EMUS
style1.smooth = True # 线条平滑处理
# 第二列数据
style2 = chart.series[1]
style2.marker.symbol = "triangle"
style2.marker.graphicalProperties.solidFill = "7D7D7D"
style2.graphicalProperties.line.solidFill = "699472"
style2.graphicalProperties.line.dashStyle = "solid"
x_label = Reference(ws, min_col=1, min_row=2, max_row=11) # 预制x轴
chart.set_categories(x_label) # 添加x轴数据;注意,要先添加y轴数据,再添加x轴数据
ws.add_chart(chart, 'E1') # 添加图表
wb.save("工作簿2.xlsx")
(三)饼图
from openpyxl.chart import PieChart, PieChart3D, Reference, series
from openpyxl import load_workbook
wb = load_workbook("工作簿1.xlsx")
ws = wb["Sheet3"]
chart = PieChart() # 三维饼图:chart = PieChart3D()
chart.title = '人口分布'
x_label = Reference(ws, min_col=1, min_row=2, max_row=7) # 预制x轴
data = Reference(ws, min_col=2, min_row=1, max_row=7) # 预制y轴
chart.add_data(data, titles_from_data=True) # 添加y轴数据
chart.set_categories(x_label) # 添加x轴数据;注意,要先添加y轴数据,再添加x轴数据
slice = series.DataPoint(idx=5, explosion=10) # idx:指定分离的索引;explosion:分离的程度
chart.series[0].data_points = [slice]
ws.add_chart(chart, 'E1') # 添加图表
wb.save("工作簿2.xlsx")
子母饼图和复合条饼图
from openpyxl.chart import ProjectedPieChart, Reference
from openpyxl import load_workbook
wb = load_workbook("工作簿1.xlsx")
ws = wb["Sheet4"]
chart = ProjectedPieChart()
chart.type = "pie" # "pie"为子母饼图,"bar"为复合条饼图
chart.splitType = "pos" # 分离样式('percent', 'auto', 'pos', 'val', 'cust')
chart.title = '人口分布'
x_label = Reference(ws, min_col=1, min_row=2, max_row=7) # 预制x轴
data = Reference(ws, min_col=2, min_row=1, max_row=7) # 预制y轴
chart.add_data(data, titles_from_data=True) # 添加y轴数据
chart.set_categories(x_label) # 添加x轴数据;注意,要先添加y轴数据,再添加x轴数据
ws.add_chart(chart, 'E1') # 添加图表
wb.save("工作簿2.xlsx")
(四)面积图
from openpyxl.chart import AreaChart, AreaChart3D, Reference
from openpyxl import load_workbook
wb = load_workbook("工作簿1.xlsx")
ws = wb["Sheet5"]
chart = AreaChart() # 三维面积图:chart = AreaChart3D()
chart.title = '准确率曲线'
chart.style = 1 # 图表样式,1-48
chart.x_axis.title = '迭代次数'
chart.y_axis.title = '准确率'
chart.height = 8 # 高度8厘米
chart.width = 15 # 宽度15厘米
x_label = Reference(ws, min_col=1, min_row=2, max_row=11) # 预制x轴
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=11) # 预制y轴
chart.add_data(data, titles_from_data=True) # 添加y轴数据
chart.set_categories(x_label) # 添加x轴数据;注意,要先添加y轴数据,再添加x轴数据
ws.add_chart(chart, 'E1') # 添加图表
wb.save("工作簿2.xlsx")
(五)气泡图
from openpyxl.chart import BubbleChart, Reference, Series
from openpyxl import load_workbook
wb = load_workbook("工作簿1.xlsx")
ws = wb["Sheet6"]
chart = BubbleChart()
chart.title = '产量分布'
chart.style = 6 # 图表样式,1-48
chart.x_axis.title = 'x坐标'
chart.y_axis.title = 'y坐标'
chart.height = 8 # 高度8厘米
chart.width = 15 # 宽度15厘米
x_label1 = Reference(ws, min_col=1, min_row=2, max_row=6) # 预制x轴
y_label1 = Reference(ws, min_col=2, min_row=2, max_row=6) # 预制y轴
size1 = Reference(ws, min_col=3, min_row=2, max_row=6) # 预制气泡大小
series1 = Series(values=y_label1, xvalues=x_label1, zvalues=size1, title='产量分布1')
chart.series.append(series1)
x_label2 = Reference(ws, min_col=1, min_row=8, max_row=12) # 预制x轴
y_label2 = Reference(ws, min_col=2, min_row=8, max_row=12) # 预制y轴
size2 = Reference(ws, min_col=3, min_row=8, max_row=12) # 预制气泡大小
series2 = Series(values=y_label2, xvalues=x_label2, zvalues=size2, title='产量分布2')
chart.series.append(series2)
ws.add_chart(chart, 'E1') # 添加图表
wb.save("工作簿2.xlsx")