pandas后台导出excel_pandas生成excel并生成透视图

# coding=utf-8

import pandas as pd

def to_excel_with_charts(filename,**data):

df = pd.DataFrame(data)

group_df = df.groupby('url', as_index=False).count() #分组

group_df = group_df.sort_values(by='case') #排序

size = df.groupby('url', as_index=False).count().size

writer = pd.ExcelWriter(filename, engine='xlsxwriter')

group_df.to_excel(writer, sheet_name='数据透视', index=False)

df.to_excel(writer, sheet_name='数据详情')

workbook = writer.book

worksheet = writer.sheets['数据透视']

chart = workbook.add_chart({'type': 'column'})

chart.add_series({'values': '=数据透视!$B$2:$B${}'.format(size + 1), "categories": '=数据透视!$A$2:$A${}'.format(size + 1)})

worksheet.insert_chart