dataframe 对象写入 excel 添加样式

import pandas as pd
import xlwt
from xlutils.copy import copy
import xlrd
import os
import win32com.client
from win32com.client import Dispatch

bb = {‘one’: [‘a’, ‘b’, ‘c’], ‘two’: [‘d’, ‘e’, ‘c’], ‘three’: [‘f’, ‘g’, ‘h’],‘four’: [‘f’, ‘g’, ‘h’]}
bb = pd.DataFrame(bb) # bb 是 dataframe 对象

path_2 = r’D:\ 模板 \text3.xls’
rows_old = 0

def excel_reoord(path, bb):
if os.path.exists(path_2):
# 关闭 excel
xlBook = win32com.client.Dispatch(‘Excel.Application’).Workbooks.Open(path_2)
xlBook.Save()
xlBook.Close(SaveChanges=0)
else:
# 创建工作簿对象
book = xlwt.Workbook()
# 创建工作表
sheet = book.add_sheet(‘text’)
book.save(path_2)

book1 = xlrd.open_workbook(path_2, formatting_info=True)   # formatting_info保持已有格式不覆盖
book2 = copy(book1)
sheet2 = book2.get_sheet(0)
# 设置列宽带
sheet2.col(1).width = 13333  # 3333 = 1" (one inch)
sheet2.col(3).width = 13333  # 3333 = 1" (one inch)
# 获取表格中已有文件行数
sheets = book1.sheet_names()  # 获取工作簿中的所有表格
worksheet = book1.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
rows_old = worksheet.nrows   # 获取表格中已有写入excel的行数


# 样式
style_1 = xlwt.easyxf("font:colour_index red") # 字体颜色
style_2 = xlwt.easyxf("pattern:pattern solid, fore_colour light_green") # 背景色
style_4 = xlwt.easyxf("pattern:pattern solid, fore_colour ice_blue;align: wrap on") # 背景色,自动换行
style_3 = xlwt.easyxf("font:height 450") # 行高

for row in range(bb.shape[0] + 1):
    row_new = row + rows_old
    for col in range(bb.shape[1]):
        if row == bb.shape[0]:
            # 合并单元
            sheet2.write_merge(row_new, row_new, 0, 3, 'First Merge', style=style_4)  # 依次： 起始行，终止行，起始列，终止列
            # 设置行高
            sheet2.row(row_new).set_style(style_3)
            break
        else:
            if bb.iloc[row, 1] == bb.iloc[row, 3]:
                sheet2.write(row_new, 1, str(bb.iloc[row, 1]), style_2)
                sheet2.write(row_new, 3, str(bb.iloc[row, 3]), style_2)
                sheet2.write(row_new, 0, str(bb.iloc[row, 0]))
                sheet2.write(row_new, 2, str(bb.iloc[row, 2]))
                break
            else:
                sheet2.write(row_new, col, str(bb.iloc[row, col]))
book2.save(path_2)

excel_reoord(path_2, bb)