用python,不用load_workbook代码将新建的df按两列分组,两个列名分别为表名和sheeet名,把分组后的df表头填充为浅蓝色,全表加边框,按顺序输出到已有excel中的倒数第二个sheet
时间: 2024-05-12 13:17:10 浏览: 12
这个问题需要通过pandas和openpyxl库来实现。首先,我们需要创建一个新的DataFrame,将其按照表名和sheet名两列进行分组。然后,我们需要将表头填充为浅蓝色,全表加边框,最后再将其按照顺序输出到已有excel中倒数第二个sheet。
以下是代码示例:
```
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Border, Side, PatternFill
# 读取已有的Excel文件
wb = load_workbook(filename='example.xlsx')
# 创建新的DataFrame,按照表名和sheet名两列进行分组
df = pd.DataFrame({
'table_name': ['table1', 'table1', 'table2', 'table2'],
'sheet_name': ['sheet1', 'sheet2', 'sheet1', 'sheet2'],
'data': ['data1', 'data2', 'data3', 'data4']
})
grouped_df = df.groupby(['table_name', 'sheet_name']).agg({'data': lambda x: ','.join(x)}).reset_index()
# 在倒数第二个sheet中创建一个新的Worksheet
ws = wb.worksheets[-2]
ws.title = 'new_sheet'
# 配置表格样式
fill = PatternFill(fill_type='solid', start_color='ADD8E6', end_color='ADD8E6')
font = Font(b=True, color='FFFFFFFF')
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'))
# 将DataFrame写入工作表
current_row = 1
for table_name, sheet_name, data in grouped_df.itertuples(index=False):
ws.cell(row=current_row, column=1, value=table_name).fill = fill
ws.cell(row=current_row, column=2, value=sheet_name).fill = fill
ws.cell(row=current_row, column=3, value=data).fill = fill
current_row += 1
# 设置表格样式
for col in range(1, 4):
ws.cell(row=current_row-1, column=col).font = font
ws.cell(row=current_row-1, column=col).border = border
# 保存Excel文件
wb.save(filename='example.xlsx')
```