devbox
weixin_40725706
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Python Excel第三方库——openpyxl_openpyxl库

作者：weixin_40725706 | 2024-05-19 03:59:26

赞

踩

openpyxl库

文章目录

简介

纵使xlwings很好用，也掩盖不了无法在Linux上部署的缺点。

openpyxl 是一款读、写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库，它通过操作Office Open XML实现Excel的读写。

本文openpyxl版本为3.0.6




安装

pip install openpyxl

  • 1




初试

from openpyxl import Workbook

wb = Workbook()  # 创建新的工作簿
ws = wb['Sheet']  # 实例化工作表
# ws = wb.active  # 默认实例化最后一个工作表
# ws = wb.create_sheet('Sheet1')  # 在末尾创建工作表
ws.title = 'Sheet1'  # 重命名工作表
ws['A1'] = 'Hello World'  # 写入
print(ws['A1'].value)  # 读取
wb.save('test.xlsx')
wb.close()

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

在这里插入图片描述




读写数据

读取工作簿：openpyxl.load_workbook()

from openpyxl import load_workbook

wb = load_workbook(filename='test.xlsx')
ws = wb.active
for cells in ws.rows:  # 按行遍历
    for cell in cells:
        value = cell.value
        print(value, end=' ')
    print()

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



插入一行数据：Worksheet.append()

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

first_row = ['a', 'b', 'c']  # 首行数据
row = 1
for i, data in enumerate(first_row):  # 逐个写入
    column = i + 1
    ws.cell(row, column).value = data

for row in ws.iter_rows(min_row=1):  # 逐个读取
    for cell in row:
        print(cell.value)

ws.append(['Foo 1', 'Foo 2', 'Foo 3'])  # 批量写入一行（传列表）
ws.append({'A': 'Foo 1', 'C': 'Foo 3'})  # 批量写入一行（传字典）
ws.append({1: 'Foo 1', 3: 'Foo 3'})  # 批量写入一行（传字典）

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

在这里插入图片描述




插入图片

1.jpg

宽高为 200×153
在这里插入图片描述
安装

pip install pillow

  • 1

代码

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active

img = Image('1.jpg')  # 打开图片
_width, _height = img.width, img.height  # 原图片宽高
width = 100  # 图片宽度设为100
height = width / _width * _height  # 等比设置图片高度
img.width = width
img.height = height
ws.add_image(img, 'A1')  # 插入图片

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

效果
在这里插入图片描述

手动调整后
在这里插入图片描述

图片宽高与列宽行高的关系（不同电脑可能有区别）

列宽 = 图片宽度 × 0.13887

行高 = 图片高度 × 0.753

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active

img = Image('1.jpg')  # 打开图片
_width, _height = img.width, img.height  # 原图片宽高
width = 100  # 图片宽度设为100
height = width / _width * _height  # 等比设置图片高度
print(width, height)
img.width = width
img.height = height
ws.add_image(img, 'A1')  # 插入图片
ws.column_dimensions['A'].width = width * 0.13887  # 设置图片列宽
ws.row_dimensions[1].height = height * 0.753  # 设置图片行高

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

效果
在这里插入图片描述




TODO：批量插入图片

Python xlwings插入图片

Drawing.anchor

img.anchor(cell, anchortype="oneCell")

  • 1




行高列宽

import datetime
from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active
ws.append(['Hello', '', 1, datetime.datetime.now()])  # 第1行
ws.append(['Hello World\nHow are you\nFine, thank you'])  # 第2行
ws.merge_cells('A2:D2')  # 合并单元格
ws['A2'].alignment = Alignment(wrap_text=True)  # 第2行允许多行文本

ws.row_dimensions[2].height = 14.4 * 3  # 2行行高
ws.column_dimensions['D'].width = 20.0  # D列列宽

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

效果



自动调整行高列宽

偷懒可以用这个，这块是很大一个坑，远没有Excel本身自动调整行高、列宽精细，且多行或字符较多时无法显示完整

import datetime
from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active
ws.append(['Hello', '', 1, datetime.datetime.now()])  # 第1行
ws.append(['Hello World\nHow are you\nFine, thank you'])  # 第2行
ws.merge_cells('A2:D2')  # 合并单元格
ws['A2'].alignment = Alignment(wrap_text=True)  # 第2行允许多行文本

for i in ws.column_dimensions:
    i.bestFit = True
    i.auto_size = True

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

效果



自动调整行高列宽（自己实现）

比例统计见下文 “统计字符数量与列宽关系”

import datetime

from openpyxl.cell import Cell


def auto_fit(ws, height_scale=1.0, width_scale=1.0):
    """自动调整openpyxl.Worksheet的行高列宽"""
    BEGIN_ASCII = 2.5546875  # 首个ASCII字符所占宽度
    BEGIN_UNICODE = 3.5546875  # 首个Unicode字符所占宽度
    ONE_ASCII = 1.1  # 一个ASCII字符所占宽度
    ONE_UNICODE = 2.2  # 一个Unicode字符所占宽度
    DEFAULT_FONT_SIZE = 11  # 默认字体大小
    DEFAULT_ROW_HEIGHT = 14.4  # 默认行高
    row_heights = {}  # 每一行的行高
    col_widths = {}  # 每一列的列宽
    for row in ws.rows:  # 从上往下遍历
        for cell in row:  # 从左往右遍历
            scale = cell.font.size / DEFAULT_FONT_SIZE if cell.font.size else 1.0  # 当前字体与默认字体的比例
            begin = True  # 是否首个字符
            length = 0  # 该单元格列宽
            cell_type = type(cell.value)  # 该单元格数据类型
            value = str(cell.value)
            row_heights[cell.row] = max((row_heights.get(cell.row, 0),
                                         (value.count('\n') + 1) * DEFAULT_ROW_HEIGHT * scale * height_scale))  # 最大行高
            if not isinstance(cell, Cell) or cell.coordinate in ws.merged_cells:  # 跳过合并单元格
                continue
            value = max(value.splitlines(), key=str.__len__) if value else value  # 多行取最长一行
            for char in value:  # 遍历每个字符
                if cell_type == datetime.datetime and char == '.':  # 日期类型后面不显示
                    break
                if begin and ord(char) > 256:  # 首个Unicode
                    length += BEGIN_UNICODE
                    begin = False
                if begin and ord(char) <= 256:
                    length += BEGIN_ASCII  # 首个ASCII
                    begin = False
                if ord(char) > 256:
                    length += ONE_UNICODE
                else:
                    length += ONE_ASCII
            col_widths[cell.column_letter] = max(
                (col_widths.get(cell.column_letter, 0), length * scale * width_scale))  # 最大列宽
    for row, height in row_heights.items():
        ws.row_dimensions[row].height = height  # 设置行高
    for column, width in col_widths.items():
        ws.column_dimensions[column].width = width  # 设置列宽


if __name__ == '__main__':
    from openpyxl import Workbook
    from openpyxl.styles import Alignment, Font

    wb = Workbook()
    ws = wb.active
    ws.append(['Hello', '', 1, datetime.datetime.now()])  # 第1行
    ws.append(['Hello World\nHow are you\nFine, thank you'])  # 第2行
    ws.merge_cells('A2:D2')  # 合并单元格
    ws['A2'].alignment = Alignment(wrap_text=True)  # 第2行允许多行文本
    ws['A3'] = 'Hello!'
    ws['A3'].font = Font(size=50)
    auto_fit(ws)
    wb.save('test.xlsx')
    wb.close()

  • 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
  • 63

不用 auto_fit()

用了 auto_fit()

如果报错 AttributeError: ‘Cell’ object has no attribute ‘column_letter’
将 column_letter 改成 column




水平垂直居中

默认为底端对齐

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws.append(['a\n\nb', 'c'])  # 写入一行

ws['A1'].alignment = Alignment(vertical='center', wrap_text=True)  # 垂直居中，多行文本
ws['B1'].alignment = Alignment(horizontal='center')  # 水平居中

ws.column_dimensions['A'].width = 10  # 列宽
ws.column_dimensions['B'].width = 10  # 列宽
ws.row_dimensions[1].height = 50  # 行高

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

效果
在这里插入图片描述

推荐阅读：

  1. openpyxl.styles.alignment




遍历所有单元格

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

for i in range(1, 11):
    ws.append({i: i})  # 指定位置写入

for row in ws.rows:  # 按行遍历
    for cell in row:
        value = cell.value
        if value:
            print(value)

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

或

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

for i in range(1, 11):
    ws.append({i: i})  # 指定位置写入

for (row, col), cell in ws._cells.items():
    print((row, col), cell.value)

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

效果
在这里插入图片描述




单元格数字格式

在这里插入图片描述
对应英文
在这里插入图片描述

import time
from datetime import datetime
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws['A1'] = '1'
ws['A2'] = 1234.567
ws['A2'].number_format = '####.#'
ws['A3'] = datetime.now()  # 日期
ws['A4'] = time.strftime('%H:%M:%S', time.localtime())
ws['A5'] = 1234.567
ws['A5'].number_format = '0.00E+00'

print(ws['A1'].number_format)  # General
print(ws['A2'].number_format)  # ####.#
print(ws['A3'].number_format)  # yyyy-mm-dd h:mm:ss
print(ws['A4'].number_format)  # General
print(ws['A5'].number_format)  # 0.00E+00

ws.column_dimensions['A'].width = 20  # 调整A列列宽
wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

效果
在这里插入图片描述

详细阅读：

  1. Number format codes
  2. Standard numeric format strings




数学公式

函数使用英文名，参数用逗号分隔

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws['A1'] = '=SUM(1, 1)'
wb.save('test.xlsx')
wb.close()

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

效果
在这里插入图片描述
查看所有函数

from openpyxl.utils import FORMULAE

print(FORMULAE)

  • 1
  • 2
  • 3




合并单元格

from openpyxl.workbook import Workbook

wb = Workbook()
ws = wb.active

# 合并单元格
ws.merge_cells('A1:C2')
# ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=3)
ws['A1'] = 1

# 拆分单元格
# ws.unmerge_cells('A1:C2')
# ws.unmerge_cells(start_row=1, start_column=1, end_row=2, end_column=3)

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

效果
在这里插入图片描述




折叠

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active
ws.column_dimensions.group('A', 'D', hidden=True)
ws.row_dimensions.group(1, 10, hidden=True)
wb.save('test.xlsx')
wb.close()

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

效果
在这里插入图片描述




插入和删除行、列

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
for i in range(1, 11):
    ws.append({i: i})  # 指定位置写入

ws.insert_rows(2, 1)  # 第2行开始插入1行
ws.insert_cols(3, 1)  # 第3列开始插入1列
ws.delete_rows(5, 2)  # 第5行开始删除2行
ws.delete_cols(6, 1)  # 第5行开始删除2行

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14




移动范围单元格

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 1
ws['A2'] = 2
ws['B2'] = '=SUM(3)'

ws.move_range('A2:B2', rows=-1, cols=1, translate=True)
# rows=-1，向上移动一行
# cols=1，向右移动一列
# translate=True，移动数学公式

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

在这里插入图片描述




开启筛选功能

ws.auto_filter.ref = ws.dimensions  # 开启筛选功能

  • 1




样式

按功能分类：

按作用对象分类：

  • 单元格样式
  • 命名样式（样式模板）




默认样式

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

font = Font(name='Calibri',
            size=11,
            bold=False,
            italic=False,
            vertAlign=None,
            underline='none',
            strike=False,
            color='FF000000')

fill = PatternFill(fill_type=None,
                   start_color='FFFFFFFF',
                   end_color='FF000000')

border = Border(left=Side(border_style=None,
                          color='FF000000'),
                right=Side(border_style=None,
                           color='FF000000'),
                top=Side(border_style=None,
                         color='FF000000'),
                bottom=Side(border_style=None,
                            color='FF000000'),
                diagonal=Side(border_style=None,
                              color='FF000000'),
                diagonal_direction=0,
                outline=Side(border_style=None,
                             color='FF000000'),
                vertical=Side(border_style=None,
                              color='FF000000'),
                horizontal=Side(border_style=None,
                                color='FF000000')
                )

alignment = Alignment(horizontal='general',
                      vertical='bottom',
                      text_rotation=0,
                      wrap_text=False,
                      shrink_to_fit=False,
                      indent=0)

number_format = 'General'

protection = Protection(locked=True,
                        hidden=False)

  • 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




字体

参数含义取值
name字体类型str
b加粗bool
bold加粗bool
sz大小float
size大小float
i斜体bool
italic斜体bool
strike删除线bool
strikethrough删除线bool
color颜色openpyxl.styles.colors.Color
vertAlign上下标下标subscript 上标superscript
u下划线单下划线single 双下划线double 会计用单下划线singleAccounting 会计用双下划线doubleAccounting
underline下划线单下划线single 双下划线double 会计用单下划线singleAccounting 会计用双下划线doubleAccounting
outlinebool
shadowbool
condensebool
extendbool
familyfloat
scheme{‘major’, ‘minor’}
charsetint
from openpyxl import Workbook
from openpyxl.styles import Font, Color

wb = Workbook()
ws = wb.active

ws['A1'] = 123
ws['B2'] = 456
ws['C3'] = 789
ws['D4'] = 'Hello!'
ws['E5'] = '新年好！'
ws['A1'].font = Font(color='FF0000', italic=True)
ws['B2'].font = Font(name='Tahoma', strike=True)
ws['C3'].font = Font(color=Color(indexed=4))  # 按索引取预定义颜色
ws['D4'].font = Font(size=100)
ws['E5'].font = Font(name='微软雅黑')

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

效果

  1. 免费可商用字体




颜色

预定义颜色
在这里插入图片描述

from openpyxl import Workbook
from openpyxl.styles import Font, Color

wb = Workbook()
ws = wb.active

ws['A1'] = 'AAAAA'
ws['B2'] = 'BBBBB'
ws['C3'] = 'CCCCC'
ws['A1'].font = Font(color='FF0000')  # HEX定义红色
ws['B2'].font = Font(color=Color(indexed=32))  # 按索引取预定义颜色
ws['C3'].font = Font(color=Color(theme=6, tint=0.5))  # 按主题取

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

效果
在这里插入图片描述




边框


边框参数

参数含义取值
bottom下框线Side
diagonal对角框线样式Side
diagonalDown是否出现对角框线_斜下框线bool
diagonalUp是否出现对角框线_斜上框线bool
endSide
horizontalSide
left左框线Side
outlinebool
right右框线Side
startSide
top上框线Side
verticalSide

代码

from openpyxl import Workbook
from openpyxl.styles import Side, Border

wb = Workbook()
ws = wb.active
side = Side(border_style='thin', color='FF0000')

cell = ws['A1']
cell.border = Border(diagonal=side, diagonalDown=True, diagonalUp=True)  # 对角框线

cell = ws['B2']
cell.border = Border(top=side, bottom=side, left=side, right=side)  # 上下左右框线

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

效果




线条参数

参数含义取值
border_style样式的别名虚线hair 线点点dashDotDot 线点dashDot 细实线thin 中虚线mediumDashed 斜线点DashDot 点dotted 虚线dashed 中实线medium 粗实线thick 中线点mediumDashDot 双划线double 中线点点mediumDashDotDot
color颜色Color
style样式同上border_style

代码

from openpyxl import Workbook
from openpyxl.styles import Side, Border
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active

border_styles = ['hair', 'dashDotDot', 'dashDot', 'thin', 'mediumDashed', 'slantDashDot', 'dotted', 'dashed', 'medium',
                 'thick', 'mediumDashDot', 'double', 'mediumDashDotDot']

for i, border_style in enumerate(border_styles):
    row = 2 * (i + 1)
    column = 2 + i
    cell = ws.cell(row=row, column=column, value=border_style)
    side = Side(border_style=border_style, color='FF0000')
    cell.border = Border(top=side, left=side, right=side, bottom=side)
    ws.row_dimensions[row].height = 14.4 * 1.5  # 行高
    ws.column_dimensions[get_column_letter(column)].width = 1.5 * len(border_style)  # 列宽

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

效果




设置有值的单元格框线，其余留白

容易失效，慎用

from openpyxl import Workbook
from openpyxl.styles import Side, Border


def set_border(ws, side=None, blank=True):
    """设置有值的单元格框线，其余留白

    :param ws: 工作表
    :param side: 框线样式，默认为黑色实线
    :param blank: 是否留白
    :return:
    """
    wb = ws._parent
    side = side if side else Side(border_style='thin', color='000000')
    for cell in ws._cells.values():
        cell.border = Border(top=side, bottom=side, left=side, right=side)
    if blank:
        white = Side(border_style='thin', color='FFFFFF')
        wb._borders.append(Border(top=white, bottom=white, left=white, right=white))
        wb._cell_styles[0].borderId = len(wb._borders) - 1


if __name__ == '__main__':
    from openpyxl.styles import Alignment

    wb = Workbook()
    ws = wb.active
    ws.merge_cells('A1:J2')
    ws['A1'] = 'Merge'
    ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
    ws._current_row = 2
    for i in range(1, 26):
        ws.append([i] * 10)

    side = Side(border_style='thin', color='FF0000')
    set_border(ws, side)

    wb.save('test.xlsx')
    wb.close()

  • 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

效果




填充

参数含义取值
fill_type填充类型不指定则无效， 取值看下方
fgColor前景色openpyxl.styles.colors.Color
bgColor背景色openpyxl.styles.colors.Color
start_color开始颜色openpyxl.styles.colors.Color
end_color结束颜色openpyxl.styles.colors.Color
patternType填充类型{‘darkUp’, ‘lightTrellis’, ‘darkHorizontal’, ‘darkGray’, ‘lightHorizontal’, ‘darkGrid’, ‘lightUp’, ‘darkDown’, ‘darkTrellis’, ‘lightGrid’, ‘lightGray’, ‘lightVertical’, ‘gray125’, ‘gray0625’, ‘mediumGray’, ‘lightDown’, ‘darkVertical’, ‘solid’}
from openpyxl import Workbook
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active
cell = ws['A1']
cell.value = 'Hello'
cell.fill = PatternFill(fill_type='solid', start_color='DDDDDD', end_color='DDDDDD')
wb.save('test.xlsx')
wb.close()

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

效果
在这里插入图片描述




对齐

参数含义取值
horizontal水平对齐两端对齐justify 跨列居中centerContinuous 靠右（缩进）right 居中center 常规general 填充fill 分散对齐（缩进）distributed 靠左（缩进）left
indent缩进float
justifyLastLinebool
readingOrder文字方向float 0根据内容 1总是从左到右 2总是从右到左
relativeIndentfloat
shrinkToFit缩小字体填充bool
shrink_to_fit缩小字体填充bool
textRotation文本方向[0, 180]
vertical垂直对齐两端对齐justify 居中center 靠上top 靠下bottom 分散对齐distributed
wrapText自动换行bool
wrap_text自动换行bool
from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws['A1'] = '标题'
ws['A1'].alignment = Alignment(textRotation=45)  # 文字旋转45度
ws['A2'] = 1
ws['A2'].alignment = Alignment(horizontal='center')  # 水平居中
ws['A3'] = '\n1\n'
ws['A3'].alignment = Alignment(vertical='center', wrap_text=True)  # 垂直居中，自动换行
ws['A4'] = 'Hello World'
ws['A4'].alignment = Alignment(shrink_to_fit=True)  # 缩小字体填充

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

效果




应用样式

from openpyxl import Workbook
from openpyxl.styles import Border, Side, PatternFill, Font, Alignment


def apply_style(cell, font=None, fill=None, border=None, alignment=None, **kwargs):
    """应用样式

    :param cell: 单元格
    :param font: 字体
    :param fill: 填充
    :param border: 边框
    :param alignment: 对齐
    """
    if font:
        cell.font = font
    if fill:
        cell.fill = fill
    if border:
        cell.border = border
    if alignment:
        cell.alignment = alignment
    for k, v in kwargs.items():
        setattr(cell, k, v)


wb = Workbook()
ws = wb.active

cell = ws['A1']
cell.value = '标题'
thin = Side(border_style='thin', color='000000')
double = Side(border_style='double', color='ff0000')
apply_style(
    ws['A1'],
    font=Font(bold=True),
    fill=PatternFill('solid', fgColor='DDDDDD'),
    border=Border(top=double, left=thin, right=thin, bottom=double),
    alignment=Alignment(horizontal='center', vertical='center'),
)

wb.save('test.xlsx')
wb.close()

  • 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

效果




批量应用样式

官方文档：批量应用样式只能用在文件关闭后，否则需要逐单元格应用


本人没成功过，有成功的朋友评论区留言~

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active
for i in range(1, 6):
    ws.append([i] * 5)

col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[5]
row.font = Font(underline='single')

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15




自己实现，缺点是要有值才会生效，建议结合 ws._current_row 将需要单独设置样式的存起来，填完所有值后再统一设置样式

from openpyxl import Workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment


def apply_style(cell, font=None, fill=None, border=None, alignment=None, **kwargs):
    """应用样式

    :param cell: 单元格
    :param font: 字体
    :param fill: 填充
    :param border: 边框
    :param alignment: 对齐
    """
    if font:
        cell.font = font
    if fill:
        cell.fill = fill
    if border:
        cell.border = border
    if alignment:
        cell.alignment = alignment
    for k, v in kwargs.items():
        setattr(cell, k, v)


def apply_batch_style(ws, min_row=None, max_row=None, min_col=None, max_col=None,
                      font=None, fill=None, border=None, alignment=None, **kwargs):
    """批量应用样式

    :param ws: 工作表
    :param min_row: 起始行，从1开始
    :param max_row: 终止行
    :param min_col: 起始列，从1开始
    :param max_col: 终止列
    :param font: 字体
    :param fill: 填充
    :param border: 边框
    :param alignment: 对齐
    """
    for row in ws.iter_rows(min_row, max_row, min_col, max_col):
        for cell in row:
            apply_style(cell, font=font, fill=fill, border=border, alignment=alignment, **kwargs)


wb = Workbook()
ws = wb.active
for i in range(1, 6):
    ws.append([i] * 5)

# thin = Side(border_style='thin', color='000000')
# double = Side(border_style='double', color='ff0000')
# apply_batch_style(
#     ws,
#     font=Font(bold=True),
#     fill=PatternFill('solid', fgColor='DDDDDD'),
#     border=Border(top=double, left=thin, right=thin, bottom=double),
#     alignment=Alignment(horizontal='center', vertical='center')
# )  # 全局应用样式


apply_batch_style(ws, max_row=1, font=Font(bold=True))  # 首行加粗
apply_batch_style(ws, max_col=1, font=Font(color='FF0000'))  # 首列红色
wb.save('test.xlsx')
wb.close()

  • 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
  • 63
  • 64

效果




设置默认样式

容易失效，慎用

from openpyxl import Workbook


def set_default_style(wb, font=None, fill=None, border=None, alignment=None):
    """设置默认样式

    :param wb: 工作簿
    :param font: 字体
    :param fill: 填充
    :param border: 边框
    :param alignment: 对齐
    """
    if font:
        wb._fonts.append(font)
        for _cell_style in wb._cell_styles:
            _cell_style.fontId = len(wb._fonts) - 1
    if fill:
        wb._fills.append(fill)
        for _cell_style in wb._cell_styles:
            _cell_style.fillId = len(wb._fills) - 1
    if border:
        wb._borders.append(border)
        for _cell_style in wb._cell_styles:
            _cell_style.borderId = len(wb._borders) - 1
    if alignment:
        wb._alignments.append(alignment)
        for _cell_style in wb._cell_styles:
            _cell_style.alignmentId = len(wb._alignments) - 1


if __name__ == '__main__':
    from openpyxl.styles import Font, PatternFill, Side, Border, Alignment

    wb = Workbook()
    ws = wb.active

    # 设置默认样式
    font = Font(name='等线')
    fill = PatternFill('solid', fgColor='DDDDDD')
    side = Side(border_style='thin', color='000000')
    border = Border(top=side, bottom=side, left=side, right=side)
    alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    set_default_style(wb, font, fill, border, alignment)

    # 设置完样式后再填值
    ws.merge_cells('A1:J2')
    ws['A1'] = 'Merge'
    ws._current_row = 2
    for i in range(1, 26):
        ws.append([i] * 10)

    wb.save('test.xlsx')
    wb.close()

  • 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

效果




应用在合并单元格

from openpyxl import Workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment

wb = Workbook()
ws = wb.active
ws.merge_cells('B2:F4')

top_left_cell = ws['B2']
top_left_cell.value = 'My Cell'

thin = Side(border_style='thin', color='000000')
double = Side(border_style='double', color='ff0000')

top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)  # 边框
top_left_cell.fill = PatternFill('solid', fgColor='DDDDDD')  # 图案填充
top_left_cell.fill = GradientFill(stop=('000000', 'FFFFFF'))  # 渐变填充
top_left_cell.font = Font(b=True, color='FF0000')  # 字体
top_left_cell.alignment = Alignment(horizontal='center', vertical='center')  # 垂直水平居中

wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

效果
在这里插入图片描述




冻结首行

冻结首行：Worksheet.freeze_panes = topLeftCell，如 ws.freeze_panes = 'A2' # 冻结首行

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active
ws.merge_cells('A1:J2')
ws['A1'] = '合并单元格'
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws._current_row = 2  # 修改当前行
for i in range(1, 100):
    ws.append([i] * 10)
ws.freeze_panes = 'A3'
wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

效果
在这里插入图片描述




复制样式

import copy

from openpyxl import Workbook
from openpyxl.styles import Font


def copy_style(style, **kwargs):
    """复制样式"""
    new_style = copy.deepcopy(style)
    for k, v in kwargs.items():
        setattr(new_style, k, v)
    return new_style


wb = Workbook()
ws = wb.active

font = Font(name='等线')
bold_font = copy_style(font, bold=True)
ws['A1'] = '标题'
ws['A1'].font = font
ws['A2'] = '标题'
ws['A2'].font = bold_font

wb.save('test.xlsx')
wb.close()

  • 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






  • 1










常用属性

含义参数取值
最大行数ws.max_rowint
最大列数ws.max_columnint
最小行数ws.min_rowint
最小列数ws.min_columnint
选中单元格selected_cellstr
当前行ws._current_rowint




删除工作表

删除工作表：Workbook.remove()

from openpyxl import Workbook

wb = Workbook()  # 创建新的工作簿
wb.create_sheet('Sheet1')  # 在末尾创建工作表
wb.create_sheet('Sheet2')  # 在末尾创建工作表
for i, name in enumerate(wb.sheetnames):
    print(name)
    if i == 0:  # 至少有一张工作表
        continue
    ws = wb[name]
    wb.remove(ws)
    # wb.remove_sheet(ws)  # 效果同上
    # del ws  # 效果同上
wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15




图表

Charts




统计字符数量与列宽关系

默认行高14.4，列宽8.11

默认字体宋体，字号11

遍历输入数字、字母、汉字

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = '数字'
data = ['1' * i for i in range(1, 101)]
ws.append(data)
ws = wb.create_sheet('字母')
data = ['a' * i for i in range(1, 101)]
ws.append(data)
ws = wb.create_sheet('英文标点')
data = ['!' * i for i in range(1, 101)]
ws.append(data)
ws = wb.create_sheet('汉字')
data = ['我' * i for i in range(1, 101)]
ws.append(data)
ws = wb.create_sheet('中文标点')
data = ['！' * i for i in range(1, 101)]
ws.append(data)
wb.save('test.xlsx')
wb.close()

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

手动全选，自动调整列宽，保存
在这里插入图片描述

计算出对应比例

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook('test.xlsx')
scale = {}
for name in wb.sheetnames:
    ws = wb[name]
    _scale = {}
    for i in range(1, 101):
        length = len(str(ws.cell(1, i).value))
        width = ws.column_dimensions[get_column_letter(i)].width
        _scale[length] = width
    scale[name] = _scale

for k, v in scale.items():
    print(k, v)
# 数字 {1: 2.5546875, 2: 3.5546875, 3: 4.5546875, 4: 5.5546875, 5: 6.5546875, 6: 7.5546875, 7: 8.5546875, 8: 9.5546875, 9: 10.5546875}
# 字母 {1: 2.5546875, 2: 3.5546875, 3: 4.5546875, 4: 5.5546875, 5: 6.5546875, 6: 7.5546875, 7: 8.5546875, 8: 9.5546875, 9: 10.5546875}
# 英文标点 {1: 2.5546875, 2: 3.5546875, 3: 4.5546875, 4: 5.5546875, 5: 6.5546875, 6: 7.5546875, 7: 8.5546875, 8: 9.5546875, 9: 10.5546875}
# 汉字 {1: 3.5546875, 2: 5.5546875, 3: 7.5546875, 4: 9.5546875, 5: 11.6640625, 6: 13.88671875, 7: 16.109375, 8: 18.33203125, 9: 20.44140625}
# 中文标点 {1: 3.5546875, 2: 5.5546875, 3: 7.5546875, 4: 9.5546875, 5: 11.6640625, 6: 13.88671875, 7: 16.109375, 8: 18.33203125, 9: 20.44140625}

# 对应关系
s_ascii = scale['数字']
s_utf8 = scale['汉字']
for i in range(2, len(s_ascii) - 1):
    pre = i - 1
    print(s_ascii[i] - s_ascii[pre], s_utf8[i] - s_utf8[pre])

  • 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

观察得到，数字、字母、英文标点为一个类型（ASCII），汉字、中文标点为一个类型（UTF-8、Unicode）。

自动调整列宽后，第一个ASCII字符为2.5546875，随后每个增加 1 - 1.1133，约 1.1

自动调整列宽后，第一个Unicode字符为3.5546875，随后每个增加 2 - 2.2223，约 2.2




参考文献

  1. openpyxl Documentation
  2. openpyxl issue
  3. Python Openpyxl Tutorial
  4. Openpyxl column widths and row heights
  5. openpyxl - adjust column width size
  6. Python最好的Excel第三方库——xlwings快速上手
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/591302
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号