| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869 |
- from mysql_db import MysqlDB
- class PandaUtil(object):
- def __init__(self, db_name):
- self.con = MysqlDB(db_name, db_type=1).con
- pass
- def query_data(self, sql):
- df = pd.read_sql_query(sql, self.con)
- return df
- def panda_chart(self, df_list, cols, title_x, title_y, file_name):
- """
- data of narray
- index of data_frame: [0,1,2,3]
- cols numbers of static columns
- """
- writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
- for i, df in enumerate(df_list):
- # df = pd.DataFrame(data, index=None, columns=["姓名", "饱和度", "人力"])
- sheet_name = f'Sheet{i}'
- df.to_excel(writer, sheet_name=sheet_name, index=False)
- workbook = writer.book
- worksheet = writer.sheets[sheet_name]
- chart = workbook.add_chart({'type': 'column'})
- # set colors for the chart each type .
- colors = ['#E41A1C', '#377EB8', '#4DAF4A', '#984EA3', '#FF7F00', '#7CFC00', ' #76EEC6', '#7EC0EE', '#00F5FF']
- # Configure the series of the chart from the dataframe data.
- for col_num in range(1, cols + 1):
- chart.add_series({
- 'name': [f'{sheet_name}', 0, col_num],
- 'categories': [f'{sheet_name}', 1, 0, 4, 0], # axis_x start row ,start col,end row ,end col
- 'values': [f'{sheet_name}', 1, col_num, 4, col_num], # axis_y value of
- 'fill': {'color': colors[col_num - 1]}, # each type color choose
- 'overlap': -10,
- })
- # Configure the chart axes.
- chart.set_x_axis({'name': f'{title_x}'})
- chart.set_y_axis({'name': f'{title_y}', 'major_gridlines': {'visible': False}})
- chart.set_size({'width': 900, 'height': 400})
- # Insert the chart into the worksheet.
- worksheet.insert_chart('H2', chart)
- writer.save()
- writer.save()
- if __name__ == '__main__':
- # pdu = PandaUtil('linshi')
- # sql = 'select house_id, COUNT(house_id) as number from t_house_image group by house_id limit 5'
- # df_data = pdu.query_data(sql)
- # print(df_data.size)
- # pdu.panda_chart([df_data], 1, 'title x', 'title y', 'pandas_chart_columns2.xlsx')
- # send_email = EmailUtil()
- # send_email.send_mail(mail_excel='pandas_chart_columns2.xlsx')
- import pandas as pd
- import numpy as np
- df = pd.DataFrame({'ID': [1, 2, 3, None, 5, 6, 7, 8, 9, 10],
- 'Name': ['Tim', 'Victor', 'Nick', None, 45, 48, '哈哈', '嗯呢', 'ess', 'dss'],
- 'address': ['美国', '试试', '单独', None, '刚刚', '信息', '报表', '公司', '是否', '是否'],
- 'address': ['美国', '试试', '单独', None, '刚刚', '信息', '报表', '公司', '是否', '是否'],
- 'address': ['美国', '试试', '单独', None, '刚刚', '信息', '报表', '公司', '是否', '是否']
- }
- )
- df.set_index("ID")
- df.to_excel('output.xlsx')
|
