• Openpyxl笔记

    文章目录

    介绍

    1. 官方文档
    2. 安装
      1. 安装openpyxl
        pip3 install openpyxl

        • 1
      2. 如果需要插入图片,还需要安装pillow库
        pip3 install pillow

        • 1

    工作薄操作

    创建工作薄对象

    1. 语法
      from openpyxl import Workbook

wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
ws1['F5'] = 3.14
print(ws1['F5'].value)
wb.save(filename = dest_filename)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9

    打开工作薄

    1. 语法
      import openpyxl
file1 = r'd:\\文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
print(wb.sheetnames)

      • 1
      • 2
      • 3
      • 4
    2. 选项
      选项说明
      read_only=True只读模式,节省内存,必须使用close()关闭
      write_only = True只写模式,

    工作薄属性

    1. 常用属性
      属性说明
      active获取当前活跃的Worksheet
      worksheets以列表的形式返回所有的Worksheet(表格)
      data_only默认为False,为True时只读取数据不显示公式
      read_only判断是否以read_only模式打开Excel文档
      encoding获取文档的字符集编码
      properties获取文档的元数据，如标题，创建者，创建日期等
      sheetnames获取工作簿中的表（列表）

    工作薄方法

    1. 常用方法
      方法说明
      工作簿.sheetnames获取所有表格的名称
      工作簿[‘工作表名’]通过表格名称获取Worksheet对象
      工作簿.active获取活跃的表格
      remove删除一个工作表对象【对象】
      create_sheet创建一个空的表格【表名】
      copy_worksheet在Workbook内拷贝表格【对象】

    遍历工作薄中的工作表

    1. 遍历工作表
      import openpyxl
file1 = r'd:\\文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
for sheet in wb:
    print(sheet.title)

      • 1
      • 2
      • 3
      • 4
      • 5

    工作表操作

    创建工作表

    1. 新建指定工作表
      import openpyxl
file1 = r'd:\\文件名称.xlsx'
wb = openpyxl.Workbook()
ws1 = wb.create_sheet("Mysheet_1") # 在末尾插入工作表(默认值)
ws2 = wb.create_sheet("Mysheet_2", 0) # 在最前插入工作表
ws3 = wb.create_sheet("Mysheet_3", -1) #在倒数第二位插入
ws3.sheet_properties.tabColor = "1072BA" # 设置工作表背景
wb.save(filename = file1)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    指定工作表

    1. 指定第几个工作表
      ws = wb.worksheets[0]

      • 1
    2. 指定工作表名称
      ws = wb['4月']

      • 1

    删除工作表

    1. 删除指定工作表
      import openpyxl
file1 = '文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb['4月']
wb.remove(ws)
wb.save(file1)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    复制工作表

    1. 复制指定工作表
      import openpyxl
file1 = '文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb.copy_worksheet(wb['Sheet']) # 这里是工作表对象
ws.title = "新复制工作表"
wb.save(file1)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    获取工作表的总行数和总列数

    1. ws为工作表对象
        rows = ws.max_row
  columns = ws.max_column

      • 1
      • 2

    工作表的Table

    创建Table
    1. res为Table的范围如A1:E5;ws.dimensions表示整个工作表
    2. style
      1. name:Table样式
      2. showRowStripes 行间隔变色
      3. showColumnStripes 列间隔变色
    3. 创建Table
      import openpyxl
file1 = 'd:\\06fping_net.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb.active
tab = openpyxl.worksheet.table.Table(displayName="Table1", ref=ws.dimensions)

# Add a default style with striped rows and banded columns
style = openpyxl.worksheet.table.TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=False)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save(file1)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
    删除Table
    1. 代码
      del ws.tables["Table1"]

      • 1

    工作表属性

    1. 常用属性
      属性说明
      title工作表的名称
      dimensions表格的大小，这里的大小是指含有数据的表格的大小，即：左上角的坐标:右下角的坐标
      max_row表格的最大行
      min_row表格的最小行
      max_column表格的最大列
      min_column表格的最小列
      rows按行获取单元格(Cell对象) - 生成器 工作表.rows
      columns按列获取单元格(Cell对象) - 生成器 工作表.columns
      freeze_panes冻结窗格 工作表.freeze_panes = “C3”
      sheet_properties.tabColor工作表背景色如:“1072BA”
      values按行获取表格的内容(数据) - 生成器
      1. freeze_panes
        1. 参数比较特别，主要用于在表格较大时冻结顶部的行或左边的行.
        2. 对于冻结的行，在用户滚动时，是始终可见的，可以设置为一个Cell对象或一个端元个坐标的字符串，单元格上面的行和左边的列将会冻结(单元格所在的行和列不会被冻结)。
          1. 例如我们要冻结第一行那么设置A2为freeze_panes
          2. 如果要冻结第一列，freeze_panes取值为B1
          3. 如果要同时冻结第一行和第一列，那么需要设置B2为freeze_panes
          4. freeze_panes值为none时 表示 不冻结任何列。

    工作表方法

    获取工作表中的所有值
    1. 只想要工作表中的值
    2. 代码
      import openpyxl
file1 = r'd:\\文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb.active
for row in ws.values:
    for value in row:
        print(value)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
    按行遍历工作表中的单元格
    1. 代码
      import openpyxl
file1 = r'd:\\文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb.active
# values_only=True 参数,可以仅仅获取value
for row in ws.iter_rows(min_row=1, max_col=3, max_row=3,values_only=True):
    for cell in row:
        # 按行遍历单元格
        print(cell.value)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
    2. 输出
      A1
B1
C1
A2
B2
C2
A3
B3
C3

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
    按列遍历工作表中的单元格
    1. 代码
      import openpyxl
file1 = r'd:\\文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb.active
for col in ws.iter_cols(min_row=1, max_col=3, max_row=3):
    for cell in col:
        # 按列遍历单元格
        print(cell.value)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
    2. 输出
      A1
A2
A3
B1
B2
B3
C1
C2
C3

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
    常用方法
    1. 常用方法
      方法说明
      iter_rows按行获取所有单元格，内置属性有(min_row,max_row,min_col,max_col)
      iter_columns按列获取所有的单元格
      append在表格末尾添加数据
      merged_cells合并多个单元格
      unmerged_cells移除合并的单元格
    工作表插入空行或空列
    1. 代码
      con = ["序号","编号","速率(G)","接口","状态","分配","模块","接口描述"]
#ws为工作表对象
ws.append(con)

      • 1
      • 2
      • 3
    工作表增加一行数据
    1. 使用列表
    2. 代码
      con = ["序号","编号","速率(G)","接口","状态","分配","模块","接口描述"]
#ws为工作表对象
ws.append(con)

      • 1
      • 2
      • 3

    单元格操作

    单元格的表示

    1. 单元格的表示
      表示说明
      ws[‘A1:C10’]A1:C10区域
      ws[‘1:10’]1到10行
      ws[‘A:C’]A到C列

    单元格方法

    1. 常用方法
      方法说明
      row单元格所在的行
      column单元格坐在的列
      value单元格的值
      coordinate单元格的坐标
    查看单元格的值
    1. 查看单元格的值
      import openpyxl
file1 = 'd:\\文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb.active
print(ws["B10"].value)
print(ws.cell(row=1,column=3).value)
print(ws.cell(1,3).value)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
    遍历单元格
    1. 遍历单元格
      import openpyxl
file1 = 'd:\\文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb.active
rng = ws["A1:C10"]
for row in rng:    # 循环每行
    for cell in row:   # 循环每个单元格
        print(cell.value)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
    单元格的移动范围
    1. 这会将范围内的单元格D4:F10向上移动一行，向右移动两列。
    2. 这些单元格将覆盖任何现有的单元格。
    3. 移动公式使用translate=True选项
    4. 代码
      ws.move_range("D4:F10", rows=-1, cols=2)

      • 1
    设置超链接
    1. 设置超链接
      import openpyxl
file1 = Path('d:\\文件名称.xlsx')

wb = openpyxl.Workbook()
ws1 = wb.active
wb.create_sheet('temp1')
# 设置网址超链接
url = "http://www.baidu.com"
ws1.cell(row = 1,column = 1).value = '=HYPERLINK("{}","{}")'.format(url,'百度')
# 设置文件内超链接
link = f"{ file1.name }#temp1!A1"
ws1.cell(row = 2,column = 2).value = '=HYPERLINK("{}","{}")'.format(link,'temp表')
# 设置单元格为蓝色
ws1.cell(row = 1,column = 1).style = "Hyperlink"
ws1.cell(row = 2,column = 2).style = "Hyperlink"
wb.save(filename = file1)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
    设置百分比格式
    1. 设置超链接
      import openpyxl
file1 = 'd:\\文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
ws = wb.active
ws["A5"].number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[10]
ws["A5"].value = 0.02
wb.save(file1)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
    合并/取消合并单元格
    1. 取消
      from openpyxl.workbook import Workbook
wb = Workbook()
ws = wb.active
ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')
# or equivalently
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    装饰部分

    行高和列宽

    1. 设置行高
      工作表.row_dimensions[1].height = 200

      • 1
    2. 设置列宽
      工作表.column_dimensions['B'].width = 100

      • 1
    3. 设置列宽,变量方式
      from openpyxl.utils import get_column_letter

col = 5
sht.column_dimensions[get_column_letter(col)].width = 18

      • 1
      • 2
      • 3
      • 4

    使用数字格式

    1. 代码
      import datetime
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# set date using a Python datetime
ws['A1'] = datetime.datetime(2010, 7, 21)
ws['A1'].number_format

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7

    使用公式

    1. 代码
      from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# add a simple formula
ws["A1"] = "=SUM(1, 1)"
wb.save("formula.xlsx")

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    条件格式

    1. 代码
      from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule,Rule
wb = Workbook()
ws = wb.active
# 创建填充'redFill'
redFill = PatternFill(start_color='EE1111',
               end_color='EE1111',
               fill_type='solid')

# 添加双色刻度'A1:A10'
# 采用excel“RRGGBB”风格的颜色。
ws.conditional_formatting.add('A1:A10',
            ColorScaleRule(start_type='min', start_color='AA0000',
                          end_type='max', end_color='00AA00')
                          )
# 添加一个三色刻度'B1:B10'
ws.conditional_formatting.add('B1:B10',
               ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000',
                           mid_type='percentile', mid_value=50, mid_color='0000AA',
                           end_type='percentile', end_value=90, end_color='00AA00')
                             )

# 基于单元格比较添加条件格式'C2:C10'
# addCellIs（范围字符串、运算符、公式、stopIfTrue、wb、字体、边框、填充）
# 如果单元格小于“公式”，则设置格式
ws.conditional_formatting.add('C2:C10',
            CellIsRule(operator='lessThan', formula=['C$1'], stopIfTrue=True, fill=redFill))
# 如果单元格位于“公式”之间，则设置格式
ws.conditional_formatting.add('D2:D10',
            CellIsRule(operator='between', formula=['1','5'], stopIfTrue=True, fill=redFill))
# 使用公式格式化
ws.conditional_formatting.add('E1:E10',
            FormulaRule(formula=['ISBLANK(E1)'], stopIfTrue=True, fill=redFill))
# 除了2色和3色刻度之外，格式规则采用字体、边框和填充进行样式设置：
myFont = Font()
myBorder = Border()
ws.conditional_formatting.add('E1:E10',
            FormulaRule(formula=['E1=0'], font=myFont, border=myBorder, fill=redFill))
# 使用特殊公式高亮显示包含特定文本的单元格
red_text = Font(color="9C0006")
red_fill = PatternFill(bgColor="FFC7CE")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="containsText", operator="containsText", text="highlight", dxf=dxf)
rule.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))']
ws.conditional_formatting.add('A1:F40', rule)
wb.save(r"d:\\test.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

    实例

    新建1-12月工作表

    1. 新建1-12月工作表
      import openpyxl
file1 = '文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
for i in range(1,12):
    wb.create_sheet(f'{ i }月')
wb.save(file1)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    除了9月份的工作表以外都删除

    1. 除了9月份的工作表以外都删除
      import openpyxl
file1 = '文件名称.xlsx'
wb = openpyxl.load_workbook(file1)
AllWS = wb.sheetnames
for ws in AllWS:
   if ws != "9月":
      wb.remove(wb[ws])
wb.save(file1)

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
  • 相关阅读:
    杰理之录音模式改录音AUX会出现复位【篇】
    Java内码编码之UTF-16讲解 [Java][编码]
    SSM+校园好货APP的设计与实现 毕业设计-附源码121619
    spring集成mybatis
    逻辑漏洞---登录验证码安全
    Mybatis高级部分
    【AIGC调研系列】llama 3与GPT4相比的优劣点
    动态规划一
    [EECS][Design Lab 3][All Carrot, No Stick] 实现目标线性轨迹运动 实现状态机的级联 距离控制实现避障
    《向量数据库指南》——向量数据库Milvus Cloud如何面对市场质疑？
  • 原文地址：https://blog.csdn.net/liujinbao8000/article/details/127861968