elab_mvp/panda_util.py

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')