任务简述:excel开头有两行装饰精美的header,剩下的都是数据。现在要想办法把这两行样式“复制”下来,然后把dataframe数据填充到接下来的几行。
- 把excel开头两行的格式和样式和颜色构造出来
- 写入新的excel中时,先写两行花里胡哨的,再从第三行开始写数据
代码如下:
1.把excel开头两行的格式和样式和颜色构造出来
#pip install -i https://pypi.tuna.tsinghua.edu.cn/simple/ xlsxwriter
import pandas as pd
# 这是
def write_to_excel(name,cor_df): # cor_df 为要保存的 dataframe
writer = pd.ExcelWriter(out_dir + name +'.xlsx', engine='xlsxwriter') # 这里用
cor_df.to_excel(writer,sheet_name='Sheet1', encoding='utf8', header=False, index=False, startcol=0, startrow=1) # 把dataframe的数据从第2行开始
workbook = writer.book
format1 = workbook.add_format({ # 先把样式打包,然后之后赋值即可
'bold': True, # 字体加粗
'text_wrap': True, # 是否自动换行
'valign': 'bottom', #垂直对齐方式
'align': 'center', # 水平对齐方式
'fg_color': '#D7E4BC', # 单元格背景颜色
'border': 1,# 边框
})
writer_sheet = writer.sheets['Sheet1']
# 设置宽度
writer_sheet.set_column("A:I", 16)
writer_sheet.set_column('C:C',30)
# merge_range(first_row, first_col, last_row, last_col, data[, cell_format])
writer_sheet.merge_range(0,0,1,0,'1',format1)
writer_sheet.merge_range(0,1,1,1,'2',format1)
writer_sheet.merge_range(0,2,1,2,'3',format1)
writer_sheet.merge_range(0,3,1,3,'4',format1)
writer_sheet.merge_range(0,8,1,8,'5',format1)
writer_sheet.merge_range(0,4,0,7,'6',format1)
writer_sheet.write(1,4,'a',format1)
writer_sheet.write(1,5,'b',format1)
writer_sheet.write(1,6,'c',format1)
writer_sheet.write(1,7,'d',format1)
writer.save()
writer.close()
2. 写入新的excel中时,先写两行花里胡哨的,再从第三行开始写数据:
#pip install -i https://pypi.tuna.tsinghua.edu.cn/simple/ xlsxwriter
import pandas as pd
dataframe = pd.DataFrame([[1,2],[3,4]])
write_to_excel('save_test',dataframe)