openpyxl

这是一篇关于openpyxl的思维导图，介绍了工作簿、工作表、图标操作、单元格、列字母与数字之间的转换等。

openpyxl

• 工作簿
  • 创建工作簿
    • openpyxl.Workbook('文件名称.xlsx')（返回工作簿实例，参数文件名称可有可无）
  • 保存工作簿
    • 工作簿.save(路径)：每次操作工作簿之后必须添加这行代码，如果不添加，则操作不会保存下来，类似于mysql中的提交
  • 打开工作簿
    • openpyxl.load_workbook('路径+文件名称.xlsx')
  • 属性
    • active：获取当前活跃的Worksheet
    • data_only：默认为False,为True时只读取数据不显示公式
    • read_only：判断是否以read_only模式打开Excel文档
    • encoding：获取文档的字符集编码
    • properties：获取文档的元数据，如标题，创建者，创建日期等
    • worksheets：以列表的形式返回所有的Worksheet(返回值是一个列表，但是李彪里边是表格对象)
    • sheetnames：获取工作簿中的表（返回值是一个列表，并且列表中是字符串，并不是工作表对象）
  • 方法
    • 工作簿['工作表名']：通过表格名称获取Worksheet对象
    • remove：删除一个工作表对象【对象】（工作簿.remove(工作表)）
    • create_sheet：创建一个空的表格【表名】（工作簿.create_sheet('4月')）
    • copy_worksheet：在Workbook内拷贝表格【对象】（复制表 = 工作簿.copy_worksheet(工作簿['9月'])）
• 工作表（e1:代表工作簿）
  • 获取一个工作簿里的所有工作表
    • e1.worksheets(返回工作表列表)
    • e1.sheetnames（返回字符串列表）
  • 获取指定工作表
    • e1['工作表名称']
  • 激活第一个工作表
    • e1.active
  • 获取工作表名称
    • e1.title
  • 属性
    • title：工作表的名称
    • dimensions：表格的大小，这里的大小是指含有数据的表格的大小，即：左上角的坐标:右下角的坐标
    • max_row：表格的最大行
    • min_row：表格的最小行
    • max_column：表格的最大列
    • min_column：表格的最小列
    • rows：按行获取单元格(Cell对象) - 生成器 工作表.rows
    • columns：按列获取单元格(Cell对象) - 生成器 工作表.columns
    • freeze_panes：冻结窗格 工作表.freeze_panes = "C3"(冻结区域为【A1：B2（C-1）（3-1）之间的单元所在的行和列】)
    • values：按行获取表格的内容(返回值就是数据) - 生成器
  • 方法
    • iter_rows：按行获取所有单元格。获取一个限定范围内的数据。参数有四个(min_row,max_row,min_col,max_col)
    • iter_columns：按列获取所有的单元格
    • merged_cells：合并多个单元格，例子：t1.merge_cells('A1:C4')
    • unmerged_cells：移除合并的单元格
• 单元格
  • 属性
    • row：单元格所在的行
    • column：单元格坐在的列
    • value：单元格的值
    • coordinate：单元格的坐标
  • 获取单元格的值
    • cell：按照行和列获取一个单元格（工作表.cell(row=i,column=2)）
    • 获取单个单元格的值：t1['A2'].value
    • 工作表['1:10']：按行的优势：这一行所有使用的单元格 都会获取到，新增列也可以获取到
    • 工作表['A:C']：按列的优势：取完一列的数据之后再取 下一列。【这是唯一的按列取数据】
    • 使用list获取：list(工作表.values)，返回值为元组类型列表，数据按照行获取
  • 按照行或者列获取数据
    • 按行：工作表.rows
    • 按列：工作表.columns
  • 写入数据
    • 向一个单元格写入数据
      • 工作表.cell(1,5,value='孙兴华')
      • 工作表['E2']='兴华'
    • 在最后一行写入一行数据
      • 工作表.append(列表)
    • 向一个区域内写入数据：先获取区域单元格对象，在循环写入数据
  • 行列的插入与删除
    • 插入列
      • 工作表.insert_cols(位置,列数)，其中位置是指在工作表的第几列前插入多少列
    • 插入行
      • 插入行：工作表.insert_rows(位置,行数)，其中位置是指在工作表的第几行前插入多少行
    • 删除列
      • 删除列：工作表.delete_cols(位置,列数)，从指定位置开始向后删除指定的列数
    • 删除行
      • 工作表.delete_rows(位置,行数)，从指定位置开始向下删除指定的行数
  • 移动单元格
    • rows和cols正数为向下或向右、负数为向左或向上，rows和cols的值为移动的单元格格数 工作表.move_range("A1:C3",rows=10,cols=10)
  • 冻结单元格
    • 工作表.freeze_panes = "C3"
  • 合并和取消单元格
    • 工作表.merge_cells(start_row=起始行号，start_column=起始列号,end_row=结束行号,end_column=结束列号)
    • 合并：工作表.merge_cells('B3:F5') # 合并
    • 取消：工作表.unmerge_cells('B3:F5') # 取消合并
  • 公式的使用
    • 前提：在加载工作簿是必须将data_only的参数设置为True，这样在读取时指挥读取公式的值，相反则会读取公式。工作簿 = vb.load_workbook(路径,data_only=True) 示例：工作表['F1'] = '=sum(C1:E1)'
  • 对行和列进行分组
    • 行分组
      • 工作表.row_dimensions.group(1,5, hidden=True)
    • 列分组
      • 工作表.column_dimensions.group('A','D', hidden=True)
  • 添加批注
    • from openpyxl.comments import Comment 批注 = vb.comments.Comment('这里写批注','孙兴华') # 第一个参数是批注，第二个参数是批注的姓名 工作表['F20'].comment = 批注
  • 装饰部分
    • 字体
      • 1、导入 from openpyxl.styles import Font
      • 2、定义字体 Font(name='Calibri', size=11, bold=False,italic=False,vertAlign=None, underline='none',strike=False, color='FF000000') name：字体名称，注意中文字体前面加u size：字号大小 bold：True（加粗）/ False（不加粗） italic：True（倾斜）/ False（不倾斜） vertAlign：'None'（默认）/ 'superscript'（上标）/ 'subscript'（下标） underline：'None'（默认）/ 'single'（单下划线）/ 'double'（双下划线）/ 'singleAccounting'（会计用单下划线）/ 'doubleAccounting'（会计用双下划线） strike：'True'（显示删除线）/ 'False'（不显示删除线） color：字体的颜色 （RGB转HEX：https://www.sioe.cn/yingyong/yanse-rgb-16/）
      • 示例：字体对象 = vb.styles.Font(name=u'微软雅黑', bold=True, italic=True, size=72) 工作表['A1'].font = 字体对象
    • 对齐方式
      • 1、导入 from openpyxl.styles import Alignment
      • 2、定义 Alignment(horizontal='general',vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) horizontal：'general'（常规）/ 'justify'（两端对齐）/ 'right'（靠右）/ 'centerContinuous'（跨列居中）/ 'distributed'（分散对齐）/ 'fill'（填充）/ 'center'（居中）/ 'left'（靠左） vertical：'center'（垂直居中）/ 'top'（靠上）/ 'bottom'（靠下）/ 'justify'（两端对齐）/ 'distributed'（分散对齐） text_rotation：指定文本旋转角度 wrap_text：是否自动换行 shrink_to_fit：是否缩小字体填充 indent：指定缩进
      • 3、示例 alignment_style1 = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True, shrink_to_fit=True) danyuange.alignment = alignment_style1
    • 边框
      • 1、导入 from openpyxl.styles import Side
      • 2、定义 Side(style=连线样式,color=边线颜色) Border(left=左边线样式,right=右连线样式,top=上边线样式,bottom=下边线样式) style参数的种类： 'double, ‘mediumDashDotDot’, ‘slantDashDot’,‘dashDotDot’,‘dotted’,‘hair’, 'mediumDashed, ‘dashed’, ‘dashDot’, ‘thin’,‘mediumDashDot’,‘medium’, 'thick’
      • 3、示例 side = vb.styles.Side(style='thin',color='FF000000') border = vb.styles.Border(left=side,right=side,top=side,bottom=side) 工作表['A1'].border = border 详解：一个单元格有四个边框，分别是上下左右、border对应的是设置哪一个方向的边框，side对应的设置边框的样式
    • 背景颜色
      • 1、导入 from openpyxl.styles import PatternFill
      • 2、示例 PatternFill(fill_type=None, start_color='FFFFFFFF', end_color='FF000000') fill_type：'None'（不填充）/ 'solid'（实心填充）/ 'darkGray'（75%灰色）'mediumGray'（50%灰色）/ 'lightGray'（25%灰色）/ 'gray125'（12.5%灰色）/ 'gray0625'（6.25%灰色）/ 'darkHorizontal'（水平条纹）/ 'darkVertical'（垂直条纹）/ 'darkDown'（逆对角线条纹）/ 'darkUp'（对角线条纹）/ 'darkGrid'（对角线剖面线）/ 'darkTrellis'（粗对角线剖面线）/ 'lightHorizontal'（细水平条纹）/ 'lightVertical'（细垂直条纹）/ 'lightDown'（细逆对角线条 纹）/ 'lightUp'（细对角线条纹）/ 'lightGrid'（细水平剖面线）/ 'lightTrellis'（细对角线剖面线） start_color / fgColor：背景颜色 RGB转HEX end_color / bgColor：图案颜色 RGB转HEX
    • 渐变颜色填充
      • GradientFill（stop=(渐变颜色1，渐变颜色2，….)） fill_type：'linear'（线性渐变）/ 'path'（中心扩散） degree：旋转角度 stop：一个元组 (OO, XX)，OO 为起始颜色，XX 为结束颜色
    • 锁定单元格和隐藏公式
      • Protection(locked=True, hidden=False) locked：指定是否锁定单元格 hidden：指定是否隐藏公式 只有在开启 “保护工作表” 之后，“锁定单元格” 和 “隐藏公 式” 才生效。 ”保护工作表“至今未找到代码操作方法
    • 设置行高与列宽
      • 工作表.row_dimensions[1].height = 200 工作表.column_dimensions['B'].width = 100
• 列字母与数字之间的转换
  • 字母转数字：openpyxl.utils.column_index_from_string('D')(把列字母d转换成数字4)
  • 数字转字母：openpyxl.utils.get_column_letter(2)(把2转换成对应的列字母B)
• 图标操作
  • 插入图片
    • from openpyxl.drawing.image import Image pic = Image(r'C:\Users\82632\Desktop\a.png') # pic.height = 100 # 设置图片的高度 # pic.width = 70 # 设置图片的宽度 t1.add_image(pic,"A20") e1.save(path)
  • 柱状图
    • import openpyxl as vb 路径 = 'c:/柱状图.xlsx' 工作簿 = vb.load_workbook(路径,data_only=True) 工作表 = 工作簿['Sheet1'] # 新建一个柱状图 chart = vb.chart.BarChart() # 设定数据引用范围 数据 = vb.chart.Reference(工作表,min_row=1,max_row=5,min_col=2,max_col=3) # X轴项目名称 项目 = vb.chart.Reference(工作表,min_row=2,max_row=5,min_col=1) # 给柱状图添加数据，数据源中有标题，因为数据中有标题行，这里为True chart.add_data(数据,titles_from_data=True) # 设定X轴的项目 chart.set_categories(项目) 工作表.add_chart(chart,"F1") 工作簿.save(路径)
  • 折线图
    • import openpyxl as vb 路径 = 'c:/折线图.xlsx' 工作簿 = vb.load_workbook(路径,data_only=True) 工作表 = 工作簿['Sheet1'] # 新建一个折线图 chart = vb.chart.LineChart() # 设定数据引用范围 数据 = vb.chart.Reference(工作表,min_row=2,max_row=3,min_col=1,max_col=13) # 分类的项目 项目 = vb.chart.Reference(工作表,min_row=1,min_col=2,max_col=13) # 给柱状图添加数据，数据源中有标题，from_rows=True因为数据是横项 chart.add_data(数据,from_rows=True,titles_from_data=True) # 设定X轴的项目 chart.set_categories(项目) 工作表.add_chart(chart,"A8") 工作簿.save(路径)