Openpyxl设置Excel 单元格样式

一、打开Excel文件

方法1: 如果Excel不存在，新建一个Excel

import openpyxl
# 新建一个Excel
wb = openpyxl.Workbook() 
sheetname = wb.active
# 设置sheet 名称
sheetname.title = "first"  
# 操作完最后保存
wb.save("./test.xlsx")  

方法2: 如果已存在Excel，直接打开

from openpyxl import load_workbook
# 目标文件的路径
path1 = './test.xlsx'
# 打开目标文件，获取第一个sheet
wb = load_workbook(path1)
sh1 = wb[wb.sheetnames[0]]
# 保存到目标路径
wb.save(path1)

二、设置单元格样式

设置前excel 单元格：

设置后Excel单元格：

1. 设置单元格边框样式

def format_border(sh):
    # common border
    border = Border(left=Side(border_style='thin', color='000000'),
                    right=Side(border_style='thin', color='000000'),
                    top=Side(border_style='thin', color='000000'),
                    bottom=Side(border_style='thin', color='000000'))
    # cell format , horizontal: 水平对齐；vertical：中心对齐；wrap_text：自动换行
    align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    # set cell border
    for row in range(1, sh.max_row+1):
        for col in range(1, sh.max_column+1):
            sh.cell(row, col).border = border
            sh.cell(row, col).alignment = align
    return sh

2. 合并单元格

# merge cell
dict1 = {}
# 获取键为列值，值为对应行的字典
for cell in sh1["A"]:
    if cell.value:
        dict1[cell.value] = cell.row
# 将字典对应值拼接成单元格“A1”的样式，合并
for key in dict1:
    index = dict1[key]
    index_start = "A" + str(index)
    index_end = "A" + str(index+13)
    sh1.merge_cells(f"{index_start}:{index_end}")

3.完整代码

from openpyxl import load_workbook
from openpyxl.styles import *
import time


def format_border(sh):
    # common border
    border = Border(left=Side(border_style='thin', color='000000'),
                    right=Side(border_style='thin', color='000000'),
                    top=Side(border_style='thin', color='000000'),
                    bottom=Side(border_style='thin', color='000000'))
    # set cell format
    align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    # set cell border
    for row in range(1, sh.max_row+1):
        for col in range(1, sh.max_column+1):
            sh.cell(row, col).border = border
            sh.cell(row, col).alignment = align
    return sh


def set_format():
    # 目标文件的路径
    path1 = './test.xlsx'
    # 打开目标文件，获取第一个sheet
    wb = load_workbook(path1)
    sh1 = wb[wb.sheetnames[0]]
    # 保存到目标路径
    wb.save(path1)
    # set format
    format_border(sh1)
    # merge cell
    dict1 = {}
    # 获取键为列值，值为对应行的字典
    for cell in sh1["A"]:
        if cell.value:
            dict1[cell.value] = cell.row
    # 将字典对应值拼接成单元格“A1”的样式，合并
    for key in dict1:
        index = dict1[key]
        index_start = "A" + str(index)
        index_end = "A" + str(index+13)
        sh1.merge_cells(f"{index_start}:{index_end}")
    wb.save(f"./test_{time.strftime('%Y%m%d%H%M%S')}.xlsx")


set_format()