Python openpyxl模块读写Excel文件
openpyxl是一个用于写入和读取xlsx格式的excel文件的Python模块。
安装openpyxl
1.打开cmd
2. 输入 python -m pip install openpyxl 进行安装
读写xlsx文件
- 导入需要的模块
import os
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment, colors
- 读取或新建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 # 获取活动表,默认第一张表
- 将一行数据写入表格
data = ['2023-06-06', '电源板', '123456', 'PASS']
sheet.append(data)
- 设置单元格格式
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 # 设置列宽
- 实例
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()