列宽

  1. from openpyxl.utils import get_column_letter
  3. ws.column_dimensions[get_column_letter(5)].width = 24

行高

  1. ws.row_dimensions[1].height = 15

合并单元格

  1. range_string = 'A1:D1'
  2. range_string = 'A3:F5'
  3. ws.merge_cells(range_string)

冻结窗口

  1. # 冻结首列
  2. ws.freeze_panes = 'B1'
  4. # 冻结首行
  5. ws.freeze_panes = 'A2'
  7. # 冻结首列和首行
  8. ws.freeze_panes = 'B2'

超链接

  1. # 方法1
  2. value='=HYPERLINK("http://www.baidu.com", "baidu")'
  3. ws.cell(row, colum, value=value)
  6. # 方法2
  7. _ = sheet.cell(row, column, value='baidu')
  8. _.hyperlink = 'http://www.baidu.com'

公式

  1. value = '=A2+A5'
  2. value = '=SUM(A1:A5)'
  3. ws.cell(row, column, value=value)

批注

  1. from openpyxl.comments import Comment
  3. cell.comment = Comment('批注内容', '姓名')

添加图片

  1. img = openpyxl.drawing.image.Image(img_path)
  3. anchor = 'C2'
  4. ws.add_image(img, anchor=anchor)

筛选与排序

需要点击才可以触发筛选

  1. ws.auto_filter.ref = "A1:B5"
  2. ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])
  3. ws.auto_filter.add_sort_condition("B2:B5")

数据验证

https://openpyxl.readthedocs.io/en/stable/validation.html

文件保护

https://openpyxl.readthedocs.io/en/stable/protection.html