python openpyxl模块使用教程-Toy模板网

这篇具有很好参考价值的文章主要介绍了python openpyxl模块使用教程。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

python openpyxl模块使用教程,Python,python,数据库

安装 openpyxl 模块

pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

openpyxl 在只读取数据时 Excel 文件可以打开，但是有修改时则不允许打开

工作簿相关方法

openpyxl.Workbook() : 新建工作簿，配合 save 方法使用
openpyxl.load_workbook( path ) : 打开存在的工作簿
wb[ sheet_name ] : 获取指定名称的工作表对象
wb.get_sheet_by_name('Sheet1'): 获取指定名称的工作表对象
wb.sheetnames: 获取所有工作表名称，以列表形式返回（非函数）
wb.worksheets：获取所有工作表对象，以列表形式返回（非函数）
wb.create_sheet( title=None, index=None ) : 创建/新增工作表。title为工作表的名称 ;index为该工作表的位置，参数可不写，默认在最后增加名称为 Sheet的工作表
wb.copy_worksheet(sheet)：在当前工作簿复制指定的工作表并返回复制后的工作表对象
wb.remove(sheet)：删除指定的工作表
ws.save(path)：保存到指定路径path的Excel文件中，若文件不存在会新建，若文件存在会覆盖（openpyxl 数据修改时必须调用此接口保存，否则数据不会保存）
wb.index( worksheet ): 获取工作表的索引
wb.move_sheet( sheet, offset=0 ): 移动工作表

工作表对象

ws.title：获取或设置工作表名
ws.max_row：工作表最大行数
ws.max_column：工作表最大列数
ws.append(list)：表格末尾追加数据
ws.merge_cells(‘A2:D2’ / start_row= , end_row= , start_column= , end_column= )：合并单元格
ws.unmerge_cells(‘A2:D2’ / start_row= , end_row= , start_column= , end_column= )：解除合并单元格
ws.sheet_properties.tabColor = "1072BA": 设置/获取 sheet 标签按钮颜色
ws.delete_rows(1, ws.max_row): 清空工作表
ws.sheet_view.showGridLines = False: 不显示网格

单元格相关方法

1. 单元格对象获取

ws['A1']：根据坐标获取单个单元格对象
ws.cell( row, column, value=None )：根据行列获取单个单元格对象
ws[1]：获取第一行所有单元格对象，ws[‘1’]也可
ws['A']：获取第A列所有单元格对象
ws['A':'B']：获取从A到B列所有单元格对象，ws[‘A:B’]也可
ws[1:2]：获取从1到2行所有单元格对象，ws[‘1:2’]也可
ws['A1':'B2']：获取从A1到B2范围所有单元格对象，ws[‘A1:B2’]也可。
ws.values：获取所有单元格数据的可迭代对象，可以通过for循环迭代或通过list(ws.values)转换为数据列表
ws.rows：获取所有数据以行的格式组成的可迭代对象
ws.columns：获取所有数据以列的格式组成的可迭代对象
ws.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None)：获取指定边界范围并以行的格式组成的可迭代对象，默认所有行
ws.iter_rows(values_only=True)：获取 sheet 页中边界范围内所有数据内容，以列表形式返回
ws.iter_cols(min_col=None, max_col=None, min_row=None, max_row=None)：获取指定边界范围并以列的格式组成的可迭代对象，默认所有列

单元格写入后一定要保存，否则Excel不会变化
1. 单个单元格数据读写

import openpyxl

source_path = 'test.xlsx'
source_wb = openpyxl.load_workbook(source_path )
sour_sheet = source_wb['Sheet1'] # 获取 Sheet1 对象

# 读/写  B2 单元格数据
sour_sheet['B2'].value = 12
cell_value = sour_sheet['B2'].value
print('B2 ', cell_value)

# 读/写 2行 2列 数据
sour_sheet.cell(2, 3).value = 34
cell_value = sour_sheet.cell(2, 3).value
print('2行3列 ', cell_value)

2. 多个单元格数据读写

import openpyxl

source_path = 'test.xlsx'
source_wb = openpyxl.load_workbook(source_path )
sour_sheet = source_wb['Sheet1'] # 获取 Sheet1 对象

test_data = [
    [1, 2 , 3],
    [4, 5 , 6],
    [7, 8 , 9],
]

# 写  A1:C3 单元格数据
cells_obj = sour_sheet['A1:C3']  # 获取此范围内单元格对象
index_row = 0
index_column = 0
for row_cells in cells_obj:
    for col_cell in row_cells:
        col_cell.value = test_data[index_row][index_column]
        index_column += 1
    index_row += 1
    index_column = 0

# 读 A1:C3 单元格数据
for row_cells in cells_obj:
    for col_cell in row_cells:
        print(col_cell.value)

2. 单元格属性

cell.value ：获取或设置值
cell.column ：数字列标，例：1
cell.column_letter ：字母列标，例：A
cell.row ：行号
cell.coordinate ：坐标，例如'A1'
cell.data_type ：数据类型， 's' = string字符串,'n' = number数值，会根据单元格值自动判断
cell.number_format ：单元格格式，默认'General'常规，详见excel自定义数据类型

3. 单元格样式

cell.font ：获取或设置单元格Font对象（字体名称，字体大小，是否加粗，字体颜色等）
cell.border ：获取或设置单元格边框
cell.alignment ：获取或设置单元格水平/垂直对齐方式
cell.fill：获取或设置单元格填充颜色
ws.row_dimensions[行号]：获取行对象（非行数据，包括行的相关属性、行高等）
ws.column_dimensions[字母列标]：获取列对象（非行数据，包括行的相关属性、列宽等）
get_column_letter(index)：根据列的索引返回字母
column_index_from_string(string)：根据字母返回列的索引
row_obj.height：获取或设置行高
column_obj.width：获取或设置列宽
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4): 合并单元格
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4) : 拆解单元格
ws.column_dimensions['A'].auto_size = True: 自适应第一列的宽度（实测无效）
ws.row_dimensions[1].auto_size = True: 自适应第 1 行的高度（实测无效）
ws.cell(row, column).has_style: 判断该单元格是否有特殊格式
ws.cell(row, column)._style: 复制该单元格所有格式，只能在本工作簿内复制

openpyxl模块中与字体，颜色，背景，边框，字体，行宽，行高，单元格格式等相关的内容都放在了styles模块中

复制合并的单元格，只能一个个的复制单个单元格格式，再执行合并操作

1. 设置单元格字体格式、行高列宽、对齐格式

Font 类设置样式
- name：字体名称。
- size：字体大小。
- color：颜色。
- bold：加粗。
- italic：斜体
- underline：下划线
Alignment 类设置样式
- horizontal：横向（左右）对齐方式。
  - 可选参数 'left' , 'centerContinuous' , 'center' , 'distributed' , 'right' , 'fill' , 'general' , 'justify'
- vertical：竖向（上下）对齐方式。
  - 可选参数 'bottom' , 'center' , 'distributed' , 'top' , 'justify'
- text_rotation=0: 文本旋转
- wrap_text=False：是否换行
- shrink_to_fit = False：是否缩小字体适应列宽
- indent = 0 ：缩进
border_style 控制边框样式
- 'dashDot','dashDotDot', 'dashed','dotted', 'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'thick', 'thin'

from openpyxl.styles import Font, colors, Alignment # 导入必需的类
# Font对象表示字体样式，Alignment对象表示对齐方式
import openpyxl

source_path = 'test.xlsx'
source_wb = openpyxl.load_workbook(source_path )
sour_sheet = source_wb['Sheet1'] # 获取 Sheet1 对象

# 设置字体  设置 等线24号，加粗斜体，字体颜色红色
my_red = openpyxl.styles.colors.Color(rgb='00FF0000')
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=my_red, bold=True)
sour_sheet['A1'].font = bold_itatic_24_font

# 设置对齐方式 设置B1中的数据垂直居中和水平居中
sour_sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')

# 设置行高和列宽 第2行行高
sour_sheet.row_dimensions[2].height = 40
# C列列宽
sour_sheet.column_dimensions['C'].width = 30

source_wb.save(source_path)

2. 设置单元格自动换行

遇到超长文本显示不全或者越格，可以通过自动换行解决

from openpyxl import Workbook
from openpyxl.styles import Alignment

# 创建一个新的Excel工作簿
wb = Workbook()
# 选择第一个工作表
ws = wb.active

# 在A1单元格中添加长文本
ws['A1'] = '这是一份非常长的文本，需要进行自动换行才能全部显示出来。'

# 设置A1单元格自动换行
ws['A1'].alignment = Alignment(wrap_text=True)

wb.save("文件名")

3. 写入整行数据

import openpyxl
wb = openpyxl.load_workbook("test.xlsx")
 
sheet = wb.create_sheet('sheet2')
lis = ['张三','在家','地址','学号','手机','昵称']
sheet.append(lis)
 
wb.save("test.xlsx")

4. 设置单元格边框

import openpyxl
from openpyxl.styles import Border, Side

wb = openpyxl.Workbook()        # 创建一个excel文件
sheet = wb.active               # 获得一个的工作表
sheet.title = "边框控制"

border = Border(left=Side(border_style='thin', color='000000'),
                right=Side(border_style='mediumDashed', color='000000'),
                top=Side(border_style='double', color='000000'),
                bottom=Side(border_style='dashed', color='000000'))

sheet['B2'].border = border

wb.save("test.xlsx")

4. 插入/删除行列

ws.insert_rows(row_index,amount=1)：在第row_index行上方插入amount列，默认插入1列
ws.insert_cols(col_index,amount=1)：在第col_index列左侧插入amount列，默认插入1列
ws.delete_rows(row_index,amount=1)：从row_index行开始向下删除amount行，默认删除1行
ws.delete_cols(col_index,amount=1)：从col_index列开始向右删除amount行，默认删除1列

5. 获取 sheet 页内所有数据内容

# enumerate(可迭代对象, start) 
# 将一个可遍历的数据对象(如列表、元组或字符串)组合为一个索引序列，同时列出数据和数据下标
for i, row in enumerate(sheet.iter_rows(values_only=True), 1):
            d = list(row)
            print(f"行{i}",d, "\n")

6. 判断单元格是否有删除线

import openpyxl

wb = openpyxl.load_workbook('test.xlsx')
ws = wb['Sheet1']
cell = ws.cell(row=1, column=1)  
print(cell.font.strike)

使用openpyxl模块，假设test.xlsx中的Sheet1的A1单元格有删除线，cell.font.strike为True文章来源地址https://www.toymoban.com/news/detail-635833.html

7. 清除excel 单元格所有格式

def removeFormatting(ws):
    # ws is not the worksheet name, but the worksheet object
    for row in ws.iter_rows():
        for cell in row:
            cell.style = 'Normal'