【python智能办公】处理excel 复制sheet模板带样式 并插入修改数据

from pandas import DataFrame

df1 = DataFrame({'a':[1,3,5,7,4,5,6,4,7,8,9],
                   'b':[3,5,6,2,4,6,7,8,7,8,9]})
df2 = DataFrame({'a':[1,3],
                   'b':[3,5]})

# df1.values.tolist()
# df2.values.tolist()

import openpyxl


# 读取表头
workbook = openpyxl.load_workbook('模板表头.xlsx')

# 复制 新建sheet
sheet2 = workbook.copy_worksheet(workbook["Tabelle1"])  # sheet名

# 改名
workbook["Tabelle1"].title="Sheet1"
sheet2.title="Sheet2"

# 添加数据
for rows in df1.values.tolist():
    workbook["Sheet1"].append(rows)
for rows in df2.values.tolist():
    workbook["Sheet2"].append(rows)
    
# 修改字段
workbook["Sheet1"]["D3"] = "hfhhf"
workbook["Sheet1"]["D4"] = "hfhhf"
workbook["Sheet1"]["H3"] = "hfhhf"
workbook["Sheet1"]["J3"] = "hfhhf"

# 添加超链接
cell1 = workbook["Sheet1"].cell(row=1, column=1)
target = '#' + "Sheet2" + '!' + 'A1'
cell1.hyperlink = target
cell1.value = r'XXX'
# 设置连接样式
cell1.style = "Hyperlink"

# 保存
workbook.save(filename='output.xlsx')