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

off999 2024-10-11 13:58 30 浏览 0 评论

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

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

模块	.xls	.xlsx	读取	写入	修改	保存	样式	图片
xlrd	支持	不支持	支持	不支持	不支持	不支持	不支持	不支持
xlwt	支持	不支持	不支持	支持	不支持	支持	支持	支持
xlutils	支持	不支持	不支持	支持	支持	支持	不支持	不支持
xlwings	支持	支持	支持	支持	支持	支持	支持	支持
openpyxl	不支持	支持	支持	支持	支持	支持	支持	支持
pandas	支持	支持	支持	支持	不支持	支持	不支持	不支持

一、安装

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) #获取单元格中的值

三、方法及属性

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对象

openpyxl.Workbook(write_only=False,iso_dates=False) 创建一个新的工作簿对象
workbook.close() 关闭文件
workbook.save(filename) 保存对 Excel 文件的修改
workbook.sheetnames：返回工作簿中所有工作表的名称列表
workbook.active：返回当前活动的工作表
workbook.create_sheet(title=None, index=None)：创建一个新的工作表
workbook.copy_worksheet(worksheet)：复制指定的工作表。
workbook.remove(sheet)：删除指定的工作表
sheet.title：工作表的名称
sheet.max_row：工作表中的最大行数
sheet.max_column：工作表中的最大列数
sheet.dimensions：获取工作表的维度，即包含数据的单元格范围
sheet.cell(row, column, value=None)：返回指定行和列的单元格对象
sheet.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)：按行迭代工作表中的单元格
sheet.iter_cols(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)：按列迭代工作表中的单元格
sheet.merge_cells(range_string)：合并指定范围内的单元格
sheet.unmerge_cells(range_string)：取消合并指定范围内的单元格
sheet.insert_rows(idx, amount=1)：在指定位置插入指定数量的行
sheet.delete_rows(idx, amount=1)：删除指定位置的指定数量的行
sheet.insert_cols(idx, amount=1)：在指定位置插入指定数量的列
sheet.delete_cols(idx, amount=1)：删除指定位置的指定数量的列
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 类型的数字格式代码

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()：创建行维度对象的副本，以便在副本上进行修改，而不影响原始对象

cell.value：单元格的值
cell.row：单元格所在的行号
cell.column：单元格所在的列号
cell.coordinate：单元格的坐标
cell.data_type：单元格类型
cell.offset(row_offset, column_offset)：返回相对于当前单元格的偏移单元格
cell.has_style：检查单元格是否具有样式
cell.clear()：清除单元格的内容和样式
cell.check_error()：检查单元格是否包含错误值
cell.number_format：获取或设置单元格的数字格式
cell.comment：获取或设置单元格的注释
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 树的表示形式

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 树的表示形式

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 中用于表示边框线样式的类

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 树的表示形式

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

四、设置样式

字体设置

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

填充设置

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

边框设置

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

对齐设置

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 辅助工具

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

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

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