Python openpyxl模块读写Excel文件

openpyxl是一个用于写入和读取xlsx格式的excel文件的Python模块。

安装openpyxl

1.打开cmd
2. 输入 python -m pip install openpyxl 进行安装

读写xlsx文件

1. 导入需要的模块

import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment, colors

2. 读取或新建xlsx文件

# 打开或创建xlsx文件
file_name = 'demo.xlsx'
if os.path.exists(file_name):workbook = load_workbook(file_name) # 打开工作簿# sheet = workbook['产品测试'] # 获取指定名称表sheet = workbook.active # 获取活动表，默认第一张表
else:workbook = Workbook() # 新建工作簿sheet = workbook.active # 获取活动表，默认第一张表

3. 将一行数据写入表格

data = ['2023-06-06', '电源板'， '123456', 'PASS']
sheet.append(data)

4. 设置单元格格式

font = Font(name='黑体', color='ffc7ce', size=12, bold=True) # 字体、颜色、字号、粗体
align = Alignment(horizontal='center', vertical='center') # 居中对齐
col_width = 20 # 列宽
row = 1
# 设置第一行的单元格格式
for column in range(1, sheet.max_column + 1):sheet.cell(row , column).font = font # 设置文字格式sheet.cell(row , column).alignment = align # 设置对齐方式col_name = chr(ord('A') + column - 1)sheet.column_dimensions[col_name].width = col_width # 设置列宽

5. 实例

import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment, colors# 打开或创建xlsx文件
import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment# 打开或创建xlsx文件
file_name = 'demo.xlsx'
if os.path.exists(file_name):workbook = load_workbook(file_name) # 打开工作簿# sheet = workbook['产品测试']       # 获取指定名称表sheet = workbook.active             # 获取活动表，默认第一张表
else:workbook = Workbook()   # 打开工作簿sheet = workbook.active # 获取活动表，默认第一张表# 添加表头tab = ('日期', '产品名称', '产品SN', '测试结果')sheet.append(tab) font = Font(name='黑体', color=colors.BLUE, size=12, bold=True) # 字体、颜色、字号、粗体align = Alignment(horizontal='center', vertical='center')    # 居中对齐col_width = 20 # 列宽# 设置表头格式for column in range(1, sheet.max_column + 1):col_name = chr(ord('A') + column - 1)sheet.cell(sheet.max_row, column).font = font       # 设置文字格式sheet.cell(sheet.max_row, column).alignment = align # 设置对齐方式sheet.column_dimensions[col_name].width = col_width # 设置列宽all_data = {'2023-06-06': [['电源板', '123456', 'PASS'], ['谐波板', '038458', 'FAIL']],'2023-06-07': [['电源板', '083888', 'PASS'], ['电源板', '832509', 'PASS']]}
row_data = tuple()# 追加内容
row_num = sheet.max_row
for date, test_data in all_data.items():start_row_num = sheet.max_row + 1for data in  test_data:row_data = list()row_data.append(date)row_data += dataprint(row_data)sheet.append(row_data)sheet.merge_cells(start_row=start_row_num, end_row=sheet.max_row, start_column=1, end_column=1)# 设置所有写入数据的单元格对齐格式：居中对齐
align = Alignment(horizontal='center', vertical='center')
for row in range(row_num, sheet.max_row + 1):for column in range(1, sheet.max_column + 1):sheet.cell(row, column).alignment = alignworkbook.save(file_name) # 保存
workbook.close()