python 模块openpyxl 读取写入.xlsx文件

Python操作Excel的模块有很多，并且各有优劣，不同模块支持的操作和文件类型也有不同。下面是各个模块的支持情况：

1. xlrd：xlrd 读取.xls文件
2. xlwt：xlwt 写入.xls文件

一、安装

pip install openpyxl

二、基本使用

import openpyxl

workbook = openpyxl.load_workbook('excel1.xlsx') #打开Excel文件，并返回Workbook对象
sheet = workbook['sheet1'] #获取工作表
A_cell_list = sheet['A'] #获取A列单元格 sheet['A1:B5']：获取指定范围内的单元格对象。

for A_cell in A_cell_list:
    print(A_cell.value) #获取单元格中的值

三、方法及属性

1. openpyxl.load_workbook(filename, read_only=False, data_only=False, keep_vba=False) 打开 Excel 文件并返回一个 Workbook 对象

import openpyxl
'''
filename：要加载的 Excel 文件的文件名或路径。可以是相对路径或绝对路径。
read_only：指定是否以只读模式加载 Excel 文件。默认值为 False，即以可读写模式加载文件。如果设置为 True，则以只读模式加载文件，这可以提高加载速度，但无法对文件进行修改。
data_only：指定是否仅加载单元格的值而不加载公式。默认值为 False，即同时加载单元格的值和公式。如果设置为 True，则只加载单元格的值，公式将被忽略。
keep_vba：指定是否保留 VBA 宏代码。默认值为 False，即不保留 VBA 宏代码。如果设置为 True，则加载时将保留 VBA 宏代码。
'''
workbook = openpyxl.load_workbook('excel1.xlsx') #打开Excel文件，并返回Workbook对象

2. openpyxl.Workbook(write_only=False,iso_dates=False) 创建一个新的工作簿对象
3. workbook.close() 关闭文件
4. workbook.save(filename) 保存对 Excel 文件的修改
5. workbook.sheetnames：返回工作簿中所有工作表的名称列表
6. workbook.active：返回当前活动的工作表
7. workbook.create_sheet(title=None, index=None)：创建一个新的工作表
8. workbook.copy_worksheet(worksheet)：复制指定的工作表。
9. workbook.remove(sheet)：删除指定的工作表
10. sheet.title：工作表的名称
11. sheet.max_row：工作表中的最大行数
12. sheet.max_column：工作表中的最大列数
13. sheet.dimensions：获取工作表的维度，即包含数据的单元格范围
14. sheet.cell(row, column, value=None)：返回指定行和列的单元格对象
15. sheet.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)：按行迭代工作表中的单元格
16. sheet.iter_cols(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)：按列迭代工作表中的单元格
17. sheet.merge_cells(range_string)：合并指定范围内的单元格
18. sheet.unmerge_cells(range_string)：取消合并指定范围内的单元格
19. sheet.insert_rows(idx, amount=1)：在指定位置插入指定数量的行
20. sheet.delete_rows(idx, amount=1)：删除指定位置的指定数量的行
21. sheet.insert_cols(idx, amount=1)：在指定位置插入指定数量的列
22. sheet.delete_cols(idx, amount=1)：删除指定位置的指定数量的列
23. sheet.column_dimensions：设置和管理单个列的属性，column_dimensions = sheet.column_dimensions['A']

• column_dimensions.width：表示列的宽度，可以设置为一个整数值，单位为字符个数
• column_dimensions.hidden：表示列是否隐藏，可以设置为布尔值，True 表示隐藏，False 表示显示
• column_dimensions.auto_size：表示列是否自动调整大小以适应内容，可以设置为布尔值，True 表示自动调整，False 表示不自动调整
• column_dimensions.best_fit：表示列是否根据内容自动调整为最佳适合宽度，可以设置为布尔值，True 表示自动调整，False 表示不自动调整
• column_dimensions.collapsed：设置列是否折叠，布尔值，True 表示折叠，False 表示展开

• column_dimensions.outline_level：设置列的大纲级别，整数值
• column_dimensions.style：设置列的样式，可以是一个 str 类型的样式名称
• column_dimensions.number_format：设置列的数字格式，可以是一个 str 类型的数字格式代码

24. sheet.row_dimensions：设置和管理单个行的属性，row_dimensions = sheet.row_dimensions[1]

• height：设置行的高度，单位为磅（points）
• auto_size：设置行的高度是否自动调整以适应内容，布尔值，True 表示自动调整，False 表示不自动调整
• hidden：设置行是否隐藏，布尔值，True 表示隐藏，False 表示显示
• outline_level：设置行的大纲级别，整数值
• collapsed：设置行是否折叠，布尔值，True 表示折叠，False 表示展开
• default_height：获取或设置默认行高度。

• group()：将指定范围的行分组为一个大纲组
• ungroup()：取消指定范围的行的分组
• copy()：创建行维度对象的副本，以便在副本上进行修改，而不影响原始对象

25. cell.value：单元格的值
26. cell.row：单元格所在的行号
27. cell.column：单元格所在的列号
28. cell.coordinate：单元格的坐标
29. cell.data_type：单元格类型
30. cell.offset(row_offset, column_offset)：返回相对于当前单元格的偏移单元格
31. cell.has_style：检查单元格是否具有样式
32. cell.clear()：清除单元格的内容和样式
33. cell.check_error()：检查单元格是否包含错误值
34. cell.number_format：获取或设置单元格的数字格式
35. cell.comment：获取或设置单元格的注释
36. cell.font：单元格的字体设置

• cell.font.name：表示字体名称，可以是字符串。默认值为 None
• cell.font.size：表示字体大小，可以是整数。默认值为 None
• cell.font.bold：表示是否加粗，可以是布尔值。默认值为 None
• cell.font.italic：表示是否斜体，可以是布尔值。默认值为 None
• cell.font.underline：表示是否有下划线，可以是字符串，如 'single'、'double'、'singleAccounting' 等。默认值为 None
• cell.font.strikethrough：表示是否有删除线，可以是布尔值。默认值为 None
• cell.font.color：表示字体颜色，可以是 RGB 值或颜色名称的字符串。默认值为 None
• cell.font.copy()：创建字体样式对象的副本

• cell.font.to_tree()：将字体样式对象转换为 xml 树的表示形式

37. cell.fill：单元格的填充设置

• cell.fill.fill_type：表示填充类型，可以是 'solid'、'gradient'、'pattern' 或 None。默认值为 None
• cell.fill.start_color：表示填充的起始颜色，可以是 RGB 值或颜色名称的字符串。默认值为 None
• cell.fill.end_color：表示填充的结束颜色，可以是 RGB 值或颜色名称的字符串。默认值为 None
• cell.fill.fgColor：表示填充的前景色，可以是 RGB 值或颜色名称的字符串。默认值为 None
• cell.fill.bgColor：表示填充的背景色，可以是 RGB 值或颜色名称的字符串。默认值为 None
• cell.fill.patternType：表示填充的图案类型，可以是 'none'、'solid'、'darkGray'、'mediumGray'、'lightGray'、'gray125'、'gray0625' 或 None。默认值为 None
• cell.fill.copy()：创建填充样式对象的副本

• cell.fill.to_tree()：将填充样式对象转换为 xml 树的表示形式

38. cell.border：单元格的边框设置

• cell.border.left：表示左边框的样式，可以是 Side 对象或 None。默认值为 None
• cell.border.right：表示右边框的样式，可以是 Side 对象或 None。默认值为 None
• cell.border.top：表示上边框的样式，可以是 Side 对象或 None。默认值为 None
• cell.border.bottom：表示下边框的样式，可以是 Side 对象或 None。默认值为 None
• cell.border.diagonal：表示对角线的样式，可以是 Side 对象或 None。默认值为 None
• cell.border.diagonal_direction：表示对角线的方向，可以是整数值（角度）或 None。默认值为 None
• cell.border.copy()：创建边框样式对象的副本。

• cell.border.to_tree()：将边框样式对象转换为 xml 树的表示形式。Side 类是 openpyxl 中用于表示边框线样式的类

39. cell.alignment：单元格的对齐设置

• cell.alignment.horizontal：表示水平对齐方式，可选值为 'left'、'center'、'right'、'justify'、'distributed'。默认值为 None
• cell.alignment.vertical：表示垂直对齐方式，可选值为 'top'、'center'、'bottom'、'justify'、'distributed'。默认值为 None
• cell.alignment.text_rotation：表示文本旋转角度，取值范围为 -90 到 90，默认值为 0
• cell.alignment.wrap_text：表示是否启用文本换行，布尔值，默认值为 False
• cell.alignment.shrink_to_fit：表示是否自动缩小单元格以适应文本，布尔值，默认值为 False
• cell.alignment.indent：表示缩进级别，整数值，默认值为 0
• cell.alignment.copy()：创建对齐方式对象的副本

• cell.alignment.to_tree()：将对齐方式对象转换为 xml 树的表示形式

40. cell.hyperlink ：设置或获取单元格的超链接，Hyperlink对象

四、设置样式

1. 字体设置

import openpyxl
from openpyxl.styles import Font

workbook = openpyxl.load_workbook('excel1.xlsx')
sheet = workbook['sheet1']

'''
name：字体名称，例如 'Arial'、'Calibri' 等。
size：字体大小，以磅为单位。
bold：是否加粗字体，可以设置为 True 或 False。
italic：是否使用斜体字体，可以设置为 True 或 False。
underline：下划线样式，可以设置为以下值之一
            Font.UNDERLINE_SINGLE：单下划线。
            Font.UNDERLINE_DOUBLE：双下划线。
            Font.UNDERLINE_SINGLE_ACCOUNTING会计单下划线。
            Font.UNDERLINE_DOUBLE_ACCOUNTING：会计双下划线。
color：字体颜色，可以使用 RGB 值（如 'FF0000' 表示红色）
strikethrough：是否使用删除线，可以设置为 True 或 False。
superscript：是否使用上标，可以设置为 True 或 False。
subscript：是否使用下标，可以设置为 True 或 False。
'''
font = Font(name='Arial', size=12, color='FF0000', bold=True)

for item in sheet.iter_cols(min_row=1, max_row=sheet.max_row):
    for cell in item:
        cell.font = font

workbook.save('test.xlsx')

2. 填充设置

import openpyxl
from openpyxl.styles import PatternFill

workbook = openpyxl.load_workbook('excel1.xlsx')
sheet = workbook['sheet1']
'''
fill_type：填充类型，可以设置为以下值之一
          'none'：无填充。
          'solid'：纯色填充。
          'gray125'：灰色 125 填充。
          'linearGradient'：线性渐变填充。
          'path'：路径填充。
start_color：起始颜色，可以使用 RGB 值（如 'FF0000' 表示红色）
end_color：结束颜色，仅适用于某些填充类型（如线性渐变填充），可以使用 RGB值
fgColor：前景色，用于纯色填充，可以使用 RGB值
bgColor：背景色，用于纯色填充，可以使用 RGB值
'''
fill = PatternFill(fill_type='solid', fgColor='FFFF00')
for item in sheet.iter_cols(min_row=1, max_row=sheet.max_row):
    for cell in item:
        cell.fill = fill

workbook.save('test.xlsx')

3. 边框设置

import openpyxl
from openpyxl.styles import Border,Side

workbook = openpyxl.load_workbook('excel1.xlsx')
sheet = workbook['sheet1']

'''
Brder参数
left：左边框样式，可以设置为 Side 类的实例，用于定义边框的样式、颜色和边框线的粗细
right：右边框样式，同样可以设置为 Side 类的实例
top：上边框样式，同样可以设置为 Side 类的实例
bottom：下边框样式，同样可以设置为 Side 类的实例
diagonal：对角线边框样式，同样可以设置为 Side 类的实例
diagonal_direction：对角线方向，可以设置为以下值之一
                    'none'：无对角线
                    'up'：从左下到右上的对角线
                    'down'：从左上到右下的对角线
Side参数
border_style：边框样式，可以设置为以下值之一
              'none'：无边框
              'thin'：细边框
              'medium'：中等粗细边框
              'thick'：粗边框
color：边框颜色，可以使用 RGB 值（如 'FF0000' 表示红色）                    
'''
border = Border(left=Side(border_style='thin', color='ff0000'),
                right=Side(border_style='thin', color='ff0000'),
                top=Side(border_style='thin', color='ff0000'),
                bottom=Side(border_style='thin', color='ff0000'))

for item in sheet.iter_cols(min_row=1, max_row=sheet.max_row):
    for cell in item:
        cell.border = border

workbook.save('test.xlsx')

4. 对齐设置

import openpyxl
from openpyxl.styles import Alignment

workbook = openpyxl.load_workbook('excel1.xlsx')
sheet = workbook['sheet1']

'''
horizontal：水平对齐方式，可以设置为以下值之一
            'general'：常规对齐
            'left'：左对齐
            'center'：居中对齐
            'right'：右对齐
            'fill'：填充对齐
            'justify'：两端对齐
            'centerContinuous'：连续居中对齐
            'distributed'：分散对齐
vertical：垂直对齐方式，可以设置为以下值之一
          'top'：顶部对齐
          'center'：居中对齐
          'bottom'：底部对齐
          'justify'：两端对齐
          'distributed'：分散对齐
textRotation：文本旋转角度，可以设置为整数值，表示以 90 度为单位的旋转角度
wrapText：布尔值，指示是否自动换行文本
shrinkToFit：布尔值，指示是否自动缩小以适应单元格大小
indent：缩进级别，可以设置为整数值，表示缩进的字符数
'''
alignment = Alignment(horizontal='center', vertical='center')

for item in sheet.iter_cols(min_row=1, max_row=sheet.max_row):
    for cell in item:
        cell.alignment = alignment

workbook.save('test.xlsx')

五、openpyxl.utils 辅助工具

1. FORMULAE：Excel函数和公式的字符串

from openpyxl import Workbook
from openpyxl.utils import FORMULAE

# 创建Workbook对象和Worksheet对象
wb = Workbook()
ws = wb.active

# 构建自定义的Excel公式
custom_formula = FORMULAE['SUM'] % 'A1:A10'
print(custom_formula)  # 输出: '=SUM(A1:A10)'

# 将自定义公式赋值给单元格
ws['A1'].value = custom_formula

# 保存Workbook对象到文件
wb.save('test.xlsx')

2. get_column_letter(column_index)：将列索引转换为对应的字母表示。例如，get_column_letter(1) 返回 'A'，get_column_letter(27) 返回 'AA'
3. column_index_from_string(column_letter)：将列字母表示转换为对应的索引。例如，column_index_from_string('A') 返回 1，column_index_from_string('AA') 返回 27
4. coordinate_from_string(cell_coordinate)：将单元格坐标字符串转换为行索引和列索引的元组。例如，coordinate_from_string('A1') 返回 (1, 1)
5. get_column_letter_range(start_column, end_column)：生成起始列和结束列之间的列字母范围。例如，get_column_letter_range('A', 'C') 返回 ['A', 'B', 'C']
6. range_boundaries(range_string)：获取范围字符串的起始单元格和结束单元格的坐标。例如，range_boundaries('A1:C3') 返回 ('A1', 'C3')
7. quote_sheetname(sheetname)：在工作表名称周围添加引号，以避免特殊字符引起的问题
8. rows_from_range(range_string)：从范围字符串中获取行索引的生成器。例如，rows_from_range('A1:C3') 生成器将生成 1, 2, 3
9. cols_from_range(range_string)：从范围字符串中获取列索引的生成器。例如，cols_from_range('A1:C3') 生成器将生成 1, 2, 3
10. coordinate_to_tuple(coordinate)：将单元格坐标字符串转换为行索引和列索引的元组。例如，coordinate_to_tuple('A1') 返回 (1, 1)
11. get_column_letter.__doc__：get_column_letter 方法的文档字符串，提供了方法的说明和示例
12. column_index_from_string.__doc__：column_index_from_string 方法的文档字符串，提供了方法的说明和示例

六、openpyxl.worksheet.hyperlink 创建和管理Excel工作表中超链接的类和函数

1. Hyperlink(ref=None, target=None, tooltip=None) 类表示单元格的超链接。可以通过创建Hyperlink对象并将其赋值给单元格的hyperlink 属性来插入公式

• ref属性：表示超链接的引用（例如单元格的坐标或范围）。
• target属性：表示超链接的目标（例如URL、文件路径等）。
• tooltip属性：表示超链接的工具提示。

1. get_hyperlink(worksheet, coordinate) 函数用于获取指定单元格的超链接
2. remove_hyperlink(worksheet, coordinate) 函数用于移除指定单元格的超链接