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 + 1
    for data in  test_data:
        row_data = list()
        row_data.append(date)
        row_data += data
        print(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 = align

workbook.save(file_name) # 保存
workbook.close()