-
操作Excel
openpyxl模块的安装与导入
-
安装openpyxl模块
pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple some-package- 1
Excel文档内容读取
-
打开Excel
-
openpyxl模块中的load_workbook()方法，接受一个文件名，返回一个工作簿对象
from openpyxl import load_workbook wb = load_workbood("4_2_1.xlsx") ##得到一个工作簿对象 wb wb.save("4_2_1.xlsx") ##save()方法保存工作簿- 1
- 2
- 3
-
-
读取Excel工作表信息
-
获取所有工作表的表名
from openpyxl import load_workbook wb = load_workbook("4_2_1.xlsx") wb.sheetnames ['湖南','湖北'] ###这个工作簿包含这两个工作表- 1
- 2
- 3
- 4
- 5
-
获取指定工作表对象
- wb.active：此方法适用于一个工作簿包含一个工作表的情况，它可以直接获取这个工作表对象
- wb[“工作表名”]：此方法适用于一个工作簿包含多个工作表的情况，传入工作表名，获取指定工作表对象
wb.activewb["湖南"] - 1
- 2
- 3
- 4
-
-
读取Excel单个单元格的信息
-
获取单元格的值
-
wb[“位置坐标”]：传入单元格的位置坐标（例：A1），定位指定单元格
-
wb.cell(row=行数,column=列数)：传入单元格的行数、列数，定位指定单元格
from openpyxl import load_workbook wb = load_workbook("4_2_4.xlsx") ws = wb.active cell = ws.cell(row=1, column=1) cellcell.value '姓名' | - 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
在获取工作表对象
ws后，调用cell()方法并传入行数和列数，我们就返回了该单元格的对象。接着，调用values属性，可以直接获取单元格中的具体值
-
-
获取单元格的坐标信息 单元格的坐标信息，指的是单元格的行坐标、列坐标和坐标
-
cell.row：用于获取单元格的行坐标
-
cell.column：用于获取单元格的列坐标
-
cell.coordinate：用于获取单元格的坐标
cell.row 1 cell.column 1 cell.coordinate 'A1'- 1
- 2
- 3
- 4
- 5
- 6
在获取单元格对象cell后，分别调用row，column和coordinate属性，可以帮助我们获取这些属性
-
-
读取Excel单元格区域的信息
-
获取单元格区域的尺寸大小
-
ws.dimensions：返回一个单元格区域包围的字符串对象，例如：‘A1:C5’
-
ws.max_row和ws.max_column：分别返回单元格区域的最大行数和最大列数
from openpyxl import load_workbook wb = load_workbook("4_2_4.xlsx") ws = wb["湖北"] ws.dimensions 'A1:C5' ws.max_row 5 ws.max_column 3- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
'A1:C5’虽然也能够表示单元格区域的大小，但是不够明显。max_row和max_column可以直接获取单元格区域的最大行和最大列
-
-
获取单元格区域的具体值
-
调用工作表对象的
iter_rows()和iter_cols()方法，可以帮助我们获取单元格区域中的值ws.iter_rows(min_row,max_row,min_col,max_col) min_row：单元格区域的最小行行数 max_row：单元格区域的最大行行数 min_col：单元格区域的最小列列数 max_col：单元格区域的最大列列数 for i in ws.iter_rows(min_row=2,max_row=3,min_col=2,max_col=3): for j in i: print(j.value,end=" ") print() 男 18 女 22- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
调用工作表对象的iter_rows()方法，这里我们想要获取的是第23行与第23列围成的单元格区域。由于该单元格区域是两行两列的，因此需要使用两次循环，才能得到每个单元格的值
-
-
-
Excel文档内容写入
-
创建新工作簿
from openpyxl import Workbook wb = Workbook() wb.save("4_3_1.xlsx")- 1
- 2
- 3
创建一个新的工作簿，首先需要导入openpyxl模块中的Workbook()方法
当且仅当调用save()方法后才会在本地生成一个新的工作簿
-
新建/删除/复制工作表
- wb.create_sheet()：新建一个工作表
- wb.remove()：删除指定工作表
- wb.copy_worksheet()：复制指定工作表
from openpyxl import load_workbook wb = load_workbook("4_3_2.xlsx") wb.shheetnames ['湖北','湖南','湖北1'] ###新建工作表 wb.create_sheet('北京') <Worksheet "北京"> wb.sheetnames ['湖北','湖南','湖北1','北京'] ###删除指定工作表 ws=wb['湖北1'] wb.remove(ws) wb.sheetnames ['湖北','湖南','北京'] ###复制工作表 ws = wb['湖南'] new_ws = wb.copy_worksheet(ws) ###new_ws对象中就拥有了这个工作表中的所有信息 new_ws <Worksheet "湖南 Copy">- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
-
插入/删除行与列
-
插入行或列
ws.insert_rows(idx,amount) ###注释 idx：单元格所在的行数 amount：要插入的空行个数 ws.insert_col(idx,amount) idx：单元格所在的列数 amount：要插入的空列个数- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
要想在第3行的上方插入1行，在第2列的左侧插入1列
from openpyxl import load_workbook wb = load_workbook("4_3_3.xlsx") ws = wb["湖北"] ws.insert_rows(idx=3,amount=1) ws.insert_cols(idx=2,amount=1) wb.save("4_3_3_插入行和列后.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
在获取工作表对象ws后，调用insert_rows()方法，我们在第3行数据的上方插入了1个空白行。接着再调用insert_col()方法，我们在第2列数据的左侧插入了一个空白列
-
删除行或列
ws.delete_rows(idx,amount) ###注释 idx：单元格所在的行数 amount:要删除的行个数 ws.delete_cols(idx,amount) ###注释 idx：单元格所在的列数 amount：要删除的列个数- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
from openpyxl import load_workbook wb = load_workbook("4_3_3.xlsx") ws = wb["湖北"] ws.delete_rows(idx=3,amount=1) ws.delete_cols(idx=2,amount=1) wb.save("4_3_3_删除行和列后.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
-
-
将外部数据写入Excel
-
向单个单元格写入数据
from openpyxl import Workbook wb = Workbook() ws = wb.active ws["A1"] = 100 wb.save("4_3_4_1.xlsx")- 1
- 2
- 3
- 4
- 5
-
批量向单元格写入数据 append()
from openpyxl import Workbook wb = Workbook() ws = wb.active data_list = [ ["姓名","学号"],["张三",1001] ] for data in data_list: ws.append(data) ws.save("4_3_4_2.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
-
-
如何调用Excel函数
地区 销售额 东北 5000 华东 4500 华北 4800 华中 6500 华南 7000 西南 3000 西北 3500 -
计算总销售额
from openpyxl import load_workbook wb = load_workbook("4_3_5.xlsx") ws = wb['销售统计'] ws["B9"] = "=sum(B2:B8)" wb.save("4_3_5_写入函数后.xlsx")- 1
- 2
- 3
- 4
- 5
-
-
批量创建多个工作簿
from openpyxl import Workbook name_list = [ "湖南","湖北","河南","河北","山东","陕西","广东","广西","贵州","山西" ] for name in name_list: wb.Wrokbook() wb.save(f"{name}.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
Excel文档格式美化
-
单元格样式设置
-
Font()：字体样式设置
Font(name,size,bold,italic,verAlign,underline,strike,color) name：字体类型 size：字号大小 bold：是否加粗 italic：是否斜体 verAlign：垂直对齐方式 underline：下划线类型 strike：删除线 color：颜色- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-
Alignment()：对齐样式设置
Alignment(horizontal,vertical,text_rotaion,wrap_text,shrink_to_fit,indent) horizontal：水平对齐 vertical：垂直对齐 text_roration：文本旋转 wrap_text：是否换行 shrink_to_fit：是否缩小字体适应列宽 indent：缩进- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
-
Side()和Border()：边框样式设置
Side(border_style,color) border_style：边框样式 color：边框颜色 Border( left=Side(border_style,color), ##左边线样式 right=Side(border_style,color), ##右边线样式 top=Side(border_style,color), ##上边线样式 bottom=Side(border_style,color) ##下边线样式 )- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
-
PatternFill()：填充样式设置
PatternFill(fill_type,bgColor) fill_type：填充图案样式选项 fgColor：前景色 fgbgColor：背景色- 1
- 2
- 3
- 4
-
-
批量设置单元格样式
from openpyxl import load_workbook from openpyxl.styles import Font,Alignment,Side,Border,PatternFill wb = load_workbook("4_3_2.xlsx") ws = wb["湖北"] cell1 = ws["A3"] font = Font(name="微软雅黑",size=20,bold=True,italic=True,color="FF0000") cell1.font=font cell2 = ws["C3"] alignment = Alignment(horizontal='center') cell2.alignment = alignment cell3 = ws["E3"] side1 = Side(style="double",color="6A5ACD") side2 = Side(style="dashed",color="FFF000") border = Border(left=side1,right=side1,top=side2,bottom=side2) cell3.border = border cell4 = ws["G3"] pattern_fill = PatternFill(fill_type="solid",fgColor="99CCFF") ###solid表示纯色填充 cell4.fill = pattern_fill wb.save("4_4_2_样式设置.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
-
单元格区域调整
-
设置行高和列宽
ws.row_dimensions[行编号].height 行编号例如：1、2、3 ws.column_dimensions[列编号].width 列编号例如：A、B、C- 1
- 2
- 3
- 4
- 5
from openpyxl import load_workbook wb = load_workbook("4_4_3_1.xlsx") ws = wb["湖北"] ws.row_dimensions[1].height = 50 ws.column_dimensions["B"].width = 40 wb.save("4_4_3_1_修改行高和列宽.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
调用row_dimensions[1]，获取的是第一行的行维度对象。
调用column_dimensions[“B”]，获取的是B列的列维度对象
-
合并/取消单元格
from openpyxl import load_workbook wb = load_workbook("4_4_3_2.xlsx") ws = wb["湖北"] ws.merge_cells("A1:D6") ###A1:D6单元格区域的数据合并 wb.save("4_4_3_2_合并单元格.xlsx")- 1
- 2
- 3
- 4
- 5
-
移动单元格
ws.move_range(cell_range,rows,cols) cell_range：待移动的单元格区域 rows：上下移动的行数 cols：左右移动的列数- 1
- 2
- 3
- 4
- 5
如果你不能使用move_range()方法，证明你的openpyxl版本过低，请升级到最新版本后再使用
from openpyxl import load_workbook wb = load_workbook("4_4_3_3.xlsx") ws = wb["湖北"] ws.move_range("A1",rows=0,cols=5) ws.move_range("C2:C4",rows=3,cols=-2) wb.save("4_4_3_3_移动单元格.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
-
冻结窗口：freeze_panes
- 当Excel行数过多时，向下移动表格时表头会被隐藏，此时就无法看出每一列字段所表示的含义，冻结窗口能很好的解决这个问题
- 我们冻结了B2单元格。此时，当向下拖动表格的时候，第一行表头会一直存在，当我们向右拖动表格的时候，第一列数据会一直存在
from openpyxl import load_workbook wb = load_workbook("4_4_3_3.xlsx") ws = wb["湖北"] ws.freeze_panes = "B2" wb.save("4_4_3_3_冻结窗口.xlsx")- 1
- 2
- 3
- 4
- 5
冻结窗口，冻结的是某个单元格的左侧和上方的区域，因此，冻结A1单元格是没有任何效果的
-
添加筛选器
- ws.auto_filter.ref = “A1”：仅给A1这个字段添加筛选器
- ws.auto_filter.ref = ws.dimension：为所有字段添加筛选器
from openpyxl import load_workbook wb = load_workbook("4_4_3_5.xlsx") ws = wb["湖北"] ws.auto_filter.ref = "C1:D1" ###给C列和D列添加筛选器 wb.save("4_4_3_5_添加筛选器.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
-
数字格式化设置：将数字以某种格式进行展示 ：number_format
数字格式化选项 数字 正数效果展示 负数效果展示 General 1 1 -1 0 1.6 2 -2 0.0 1 1.00 -1.00 0% 0.5 50% -50% 0.00% 0.5 50.00% -50.00% #,##0 1000 1,000 -1,000 #,##0.00 1000 1000.00 -1000.00 0.00E+00 10000 1.00E+04 -1.00E+04 ￥#,##0;-￥#,##0 1234 ￥1,234 -￥1,234 KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲,##0.00;-#,##0.00 1234 $1,234.00 -$1,234.00 mm-dd-yy 2020-12-12 2020/12/12 h:mm:ss 10:30:30 10:30:30 import datetime from openpyxl import Workbook wb = Workbook() ws = wb.active print(datetime.datetime(2020,12,12,10,30,30)) 2020-12-12 10:30:30 ws["A1"] = datetime.datetime(2020,12,12,10,30,30) ws["A1"].number_format - "mm-dd-yy" ws["B1"] = 12345678 ws["B1"].number_format = "$#,##0.00;-$#,##0.00" wb.save(filename = "4_4_4.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
-
条件格式的应用
-
条件格式包含一系列格式设置，将选定的类型与单元格的值做比较，可能的类型有：
- 数字num
- 百分比percent
- 最大max
- 最小min
- 公式formula
- 百分点值percentile
-
条件格式规则
-
ColorScaleRule()：色阶设置
ColorScaleRule( start_type,start_value,start_color, mid_type,mid_value,mid_color, end_type,end_value,end_color ) type：可能的类型 value：对应类型的开始值，中间值和结束值 color：色阶的开始颜色，中间颜色和结束颜色- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
-
lconSetRule()：图标设置
lconSetRule(icon_style,type,values,showValue,reverse) icon_style：图标集选项 type：可能的类型 values：不同type类型对应的值 reverse：是否将图标进行反转- 1
- 2
- 3
- 4
- 5
- 6
-
DataBarRule：数据条设置
DataBarRule( start_type,start_value,color, end_type,end_value,showValue ) type：可能的类型 value：不同类型对应的值 color：数据条颜色设置 showValue：是否显示数据条上的数字- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
-
CellsRule()和FormulaRule()：突出显示单元格设置
CellRule(operator,formula,fill) operator：条件类型选项 formula：不同条件类型对应的具体值 fill：单元格样式设置 FormulaRule(formula,fill) formula：Excel函数 fill：单元格样式设置- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-
当我们了解了上述条件格式的规则后，调用工作表对象的**conditional_formatting.add()**方法，即可将这些规则应用到指定的单元格区域中，语法格式为：
ws.conditional_formatting.add(range_string,cfRule) range_string：单元格区域 cfRule：条件格式规则- 1
- 2
- 3
- 4
-
批量设置条件格式
from openpyxl import load_workbook from openpyxl.styles import Font,Alignment,Side,Border,PatternFill from openpyxl.formatting.rule import ColorScaleRule,IconSetRule,DataBarRule,FormulaRule wb = load_workbook("4_4_6.xlsx") ws = wb["湖北"] ###A1:A10中的每个值，形成一个从最小值到最大值的颜色渐变 ws.conditional_formatting.add( range_string='A1:A10', cfRule=ColorScaleRule( start_type='min', start_color='00AA00', end_type='max', end_color='0000FF' ) ) ###B1:B10中的每个值，我们将其划分为[1,5],[5,7],[7,max]这三个区间，分别展示不同的图标。 左闭右开 ws.conditional_formatting.add( range_string='B1:B10', cfRule=IconSetRule( icon_style='3Flags', type='num', values=[1,5,7] reverse=True ) ) ###C1:C10中的每个值，依照它们的数值大小，应用不同的数据条 ws.conditional_formatting.add( range_string="C1:C10", cfRule=DataBarRule( start_type='num', start_value=1, end_type='num', end_value=10, showValue=True, color="FF0000" ) ) ###D1:D10中的每个值，如果数字“小于”5，我们突出这些单元格。由于涉及颜色填充，我们需要额外设置填充色 redFill = PatternFill(end_color="00FFFF") ws.conditional_formatting.add( range_string='D1:D10', cfRule=CellIsRule( operator='lessThan', formula=['5'], fill=redFill ) ) ###E1:E10中的每个值，如果数字介于[5,9]之间。我们突出这些单元格，同样这里也需要额外设置填充色 redFill = PatternFill(end_color='FF00FF') ws.conditional_formatting.add( range_string='E1:E10', cfRule=FormulaRule(formula=['AND(E1>5,E1<9)'],fill=redFill) ) wb.save("4_4_6_条件格式.xlsx ")- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
-
-
-
插入图片与图形绘制
-
需求
-
批量读取本地图片，将它插入Excel单元格
-
批量读取Excel中的数据，并绘制相关图形
-
-
单元格插入图片
1、读取本地图片 2、将图片插入到指定单元格 3、调整单元格的行高、列宽，使其与图片大小一致 from openpyxl import Workbook() from openpyxl.drawing.image import Image im = Image("python.png") im.height,im.width (177,182) wb = Workbook() ws = wb.active ws.add_image(im,'A1') def ch_height(height): return height*13.5/18 def ch_width(width): return width*8.38/68 ws.row_dimensions[1].height = ch_height(im.height) ws.column_dimensions["A"].width = ch_width(im.width) wb.save("4_4_7_1.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
我们需要Image()方法读取本地照片，返回一个图片对象im。有height和width属性，像素高和像素宽
add_image()可以将图片插入Excel指定单元格
由于图片的像素宽和高与单元格的宽和高的单位并不一致，所以定义了两个转换函数，用于统一单位。
- Excel2013中的行高和列宽分别是13.5和8.38，他们的单位并不一致。但是行高13.6对应的像素值大概是18px，列宽8.38对应的像素值大约是68px
-
相关图形的绘制
- 打开或创建一个工作簿
- 创建一个指定图形的空坐标系
- 往空坐标系中添加数据源
- 设置图表元素
- 指定在工作表的哪一个位置绘图
- 保存工作簿
-
绘制出“某公众号不同月份关注人数”的折线图
from openpyxl import load_workbook from openpyxl.chart import LineChart,Reference ###打开一个本地的工作簿，获取工作簿对象wb和工作表对象ws wb = load_workbook("test.xlsx") ws = wb["折线图"] ###调用LineChart()方法，创建一个空坐标系对象chart,图形就绘制在这个坐标系上 chart = LineChart() ###向坐标系中添加数据源之前，首先应该选择数据源。这里需要提前导入Reference()方法，直接调用该方法即可帮助我们选择数据源。然后再调用坐标系对象的add_data()方法，即可将选择好的数据源添加到坐标系中 data = Reference(ws,min_row=1,max_row=13,min_col=2,max_col=2) chart.add_data(data,titles_from_data=True) ###设置图表原色 chart.title = "公众号不同月份的关注人数" chart.y_axis.title = "关注人数" chart.x_axis.title = "月份" ws.add_chart(chart,"D1") wb.save("test_折线图.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
实战项目：Excel报表自动化
汇总除公司2011年至2020年这十年的总销售情况，并绘制折线图
-
导入相关模块
import os import pandas as pd from openpyxl import Workbook from openpyxl.chart import LineChart,Reference from openpyxl.utils.dataframe import dataframe_to_rows- 1
- 2
- 3
- 4
- 5
-
获取文件列表
- 2011到2020年一共10个Excel文档
#打印出当前工作目录下的所有文件 file_list = os.listdir("./项目案例原始数据") #解析式筛选出.xlsx结尾的文件 file_list = [i for i in file_list if i.endswith(".xlsx")]- 1
- 2
- 3
- 4
-
计算每一年的总销售额
- 依次读取每个文件，统计汇总出每一年的总销售额，并将它们存储到DaraFrame数据框中
x = [] for index,value in enumerate(file_list): y = [] df = pd.read_excel("./项目案例原始数据/" + value) total = df["销售额（万元）"].sum() y.append(value[:4]) y.append(total) x.append(y) df = pd.DataFrame(x,columns=["年份","总销售额"]) df 年份 总销售额 0 2011 10889 1 2012 10889 2 2013 10889 3 2014 10889 4 2015 10889 5 2016 10889 6 2017 10889 7 2018 10889 8 2019 10889 9 2020 10889- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
-
将DataFrame对象转换为工作簿对象
##创建一个新的工作簿，用于存储对象转换后的数据 wb = Workbook() ws = wb.active ###dataframe_to_rows()可以将数据框对象转换为工作簿对象，此时这个工作簿对象wb就拥有了数据框df中的所有数据 for row in dataframe_to_rows(df,index=False,header=True): ws.append(row)- 1
- 2
- 3
- 4
- 5
- 6
-
绘制折线图
ws = wb.active chart = LineChart() max_row = len(file_list)+1 data = Reference(ws,min_row=1,max_row=max_row,min_col=2,max_col=2) chart.add_data(data,titles_from_data=True) chart.title = "某公司2011-2020年度销售额折线图" chart.y_axis.title = "销售额" chart.x_axis.title = "年份" ws.add_chart(chart,"D1") wb.save("2011-2020.xlsx")- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-
-
相关阅读:
别再用 System.currentTimeMillis 统计耗时了，太 Low，试试 Spring Boot 源码在用的 StopWatch吧，够优雅！
经济2023---风口
概率密度函数，概率分布函数
一篇文章教你Pytest快速入门和基础讲解，一定要看
宏(预编译)详解
选择器汇总
总结js中常见的层次选择器
css 动态点击效果
JDBC快速入门
geoserver的ECQL查询
- 原文地址：https://blog.csdn.net/s_frozen/article/details/126589209
