用最新版本的库用python把df按照两列分组,分组的两个列名分别为表名和sheet名,分别输出到一个文件夹中的和分组df表名相同的EXCEL中,现有excel的SHEET要在最后,要求表头填充成浅蓝色,全部表格加边框、居中、自动适应列宽
时间: 2024-05-14 16:16:08 浏览: 8
好的,我理解了你的问题。你可以使用`pandas`和`openpyxl`库来实现这个任务。下面是具体的步骤:
1. 导入必要的库:
```python
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment
```
2. 读取包含数据的Excel文件,假设文件名为`data.xlsx`:
```python
df = pd.read_excel('data.xlsx', sheet_name=None)
```
3. 按照两列分组,分别输出到一个文件夹中的和分组df表名相同的EXCEL中:
```python
for key in df:
group = df[key].groupby(['表名', 'sheet名'])
for name, group_df in group:
output_file = f'{name[0]}_{name[1]}.xlsx' # 输出的文件名
writer = pd.ExcelWriter(output_file, engine='openpyxl')
group_df.to_excel(writer, sheet_name='Sheet1', index=False) # 写入数据到Sheet1
writer.book.remove(writer.book['Sheet1']) # 删除自带的Sheet1
writer.save()
```
4. 设置表头填充成浅蓝色、加边框、居中、自动适应列宽:
```python
for key in df:
group = df[key].groupby(['表名', 'sheet名'])
for name, group_df in group:
output_file = f'{name[0]}_{name[1]}.xlsx' # 输出的文件名
writer = pd.ExcelWriter(output_file, engine='openpyxl')
group_df.to_excel(writer, sheet_name='Sheet1', index=False) # 写入数据到Sheet1
# 设置Sheet1的样式
workbook = writer.book
worksheet = writer.sheets['Sheet1']
for i, col in enumerate(worksheet.columns):
column = col[0].column
worksheet.column_dimensions[column].width = worksheet.column_dimensions[column].bestFit = True # 自适应列宽
worksheet.cell(row=1, column=i+1).fill = PatternFill(start_color="B7DDEE", end_color="B7DDEE", fill_type="solid") # 填充成浅蓝色
worksheet.cell(row=1, column=i+1).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')) # 加边框
worksheet.cell(row=1, column=i+1).alignment = Alignment(horizontal='center', vertical='center') # 居中
writer.book.remove(writer.book['Sheet1']) # 删除自带的Sheet1
writer.save()
```
这样,你就可以按照两列分组,分别输出到一个文件夹中的和分组df表名相同的EXCEL中,且表头填充成浅蓝色,全部表格加边框、居中、自动适应列宽了。