openpyxl常用方法

最新推荐文章于 2024-08-27 14:36:28 发布

五力

最新推荐文章于 2024-08-27 14:36:28 发布

阅读量1.1k

点赞数 1

分类专栏： python 文章标签： openpyxl Workbook load_workbook style add_image

本文链接：https://blog.csdn.net/lxy210781/article/details/103329978

版权

python 专栏收录该内容

74 篇文章 7 订阅

订阅专栏

包括：打开、写入xlsx，读写数据，单元格格式设置，插入图片，公式的使用，一般的使用基本就这些。

以下脚本已包括这些操作:

import os
from time import sleep
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill, NamedStyle

font   = Font(size=11) # 字体
fill1  = PatternFill(fill_type='solid', start_color='FFFF00', end_color='FFFF00') # 单元格背景填充
fill2  = PatternFill(fill_type='solid', start_color='00B050', end_color='00B050') # 单元格背景填充
fill3  = PatternFill(fill_type='solid', start_color='8389E0', end_color='8389E0') # 单元格背景填充
side   = Side(style='thin', color='000000') # 边框样式及颜色
border = Border(left=side, right=side, top=side, bottom=side) # 单元格边框格式
align  = Alignment(horizontal='center', vertical='center', wrap_text=False) # 字体对齐方式

data_style  = NamedStyle(name='data_style')
data_style1 = NamedStyle(name='data_style1')
data_style2 = NamedStyle(name='data_style2')
data_style3 = NamedStyle(name='data_style3')

# 样式
data_style.font = font
data_style.border = border
data_style.alignment = align

# 样式1
data_style1.font = font
data_style1.fill = fill1
data_style1.border = border
data_style1.alignment = align

# 样式2
data_style2.font = font
data_style2.fill = fill2
data_style2.border = border
data_style2.alignment = align

# 样式3
data_style3.font = font
data_style3.fill = fill3
data_style3.border = border
data_style3.alignment = align

path = 'C:\\Users\\qbgao\\Desktop\\test.xlsx'
wb = Workbook()
ws = wb.active
# wb.add_named_style(data_style)
# wb.add_named_style(data_style1)
# wb.add_named_style(data_style2)
# wb.add_named_style(data_style3)

# 应用对应的样式来写入数据
for row in range(1, 11):
    for col in range(1, 11):
        ws.cell(row, col).value = row + col
        if col == 2:
            ws.cell(row, col).style = data_style1
        elif col == 3:
            ws.cell(row, col).style = data_style2
        elif col == 4:
            ws.cell(row, col).style = data_style3
        else:
            ws.cell(row, col).style = data_style

# 保存文件
wb.save(path)
print('文件第一次写入完成. ')
sleep(2)

# 重新打开文件
wb = load_workbook(path, data_only=False)
ws = wb.active
        
# 用公式计算结果
for row in range(1, 11):
    ws.cell(row, 11).value = f"=SUM(A{row}:J{row})"  # =SUM(Ax:Jx)
    ws.cell(row, 11).style = data_style2
    
# 合并单元格
ws.merge_cells('A11:C13')
ws['A11'] = '合并单元格'
ws['A11'].style = data_style1

# 插入图片
img_path = 'C:\\Users\\qbgao\\Desktop\\test.jpg'
img_path2 = 'C:\\Users\\qbgao\\Desktop\\test.jpg'
img = Image(img_path)
img.width, img.height = (58, 50) # 重新调整图片大小
img2 = Image(img_path2)
img2.width, img2.height = (58, 50) # 重新调整图片大小
ws.add_image(img,)
ws.add_image(img2, 'D14')

# 保存文件
wb.save(path)
print('文件第二次写入完成. ')