Openpyxl笔记

介绍

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

      pip3 install openpyxl
      

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

      pip3 install pillow
      

工作薄操作

创建工作薄对象

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. 语法

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

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. 新建指定工作表

   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. 指定第几个工作表

   ws = wb.worksheets[0]
   

2. 指定工作表名称

   ws = wb['4月']
   

删除工作表

1. 删除指定工作表

   import openpyxl
   file1 = '文件名称.xlsx'
   wb = openpyxl.load_workbook(file1)
   ws = wb['4月']
   wb.remove(ws)
   wb.save(file1)
   

复制工作表

1. 复制指定工作表

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

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

1. ws为工作表对象

     rows = ws.max_row
     columns = ws.max_column
   

工作表的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)
   

删除Table

1. 代码

   del ws.tables["Table1"]
   

工作表属性

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. 代码

   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)
   

2. 输出

   A1
   B1
   C1
   A2
   B2
   C2
   A3
   B3
   C3
   

按列遍历工作表中的单元格

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)
   

2. 输出

   A1
   A2
   A3
   B1
   B2
   B3
   C1
   C2
   C3
   

常用方法

1. 常用方法

   方法	说明
   iter_rows	按行获取所有单元格，内置属性有(min_row,max_row,min_col,max_col)
   iter_columns	按列获取所有的单元格
   append	在表格末尾添加数据
   merged_cells	合并多个单元格
   unmerged_cells	移除合并的单元格

工作表插入空行或空列

2. 代码

   con = ["序号","编号","速率(G)","接口","状态","分配","模块","接口描述"]
   #ws为工作表对象
   ws.append(con)
   

工作表增加一行数据

1. 使用列表
2. 代码

   con = ["序号","编号","速率(G)","接口","状态","分配","模块","接口描述"]
   #ws为工作表对象
   ws.append(con)
   

单元格操作

单元格的表示

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. 遍历单元格

   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. 这会将范围内的单元格D4:F10向上移动一行，向右移动两列。
2. 这些单元格将覆盖任何现有的单元格。
3. 移动公式使用translate=True选项
4. 代码

   ws.move_range("D4:F10", rows=-1, cols=2)
   

设置超链接

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. 设置超链接

   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. 取消

   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. 设置行高

   工作表.row_dimensions[1].height = 200
   

2. 设置列宽

   工作表.column_dimensions['B'].width = 100
   

3. 设置列宽,变量方式

   from openpyxl.utils import get_column_letter
   
   col = 5
   sht.column_dimensions[get_column_letter(col)].width = 18
   

使用数字格式

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. 代码

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

条件格式

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-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)
   

除了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)