#!/usr/bin/env python
# -*- coding:utf-8 -*-


import xlwt, time, openpyxl
from datetime import datetime
from common.base import MyPymysql
from pandas import DataFrame as df


def get_data():
    ret = MyPymysql('data', None)
    dataSql = """select * from db2018070610353077628169316404_2"""
    data = ret.selectall_sql(dataSql)
    ret.close()
    return data


def write_data_to_excel_xlwt(path, result):
    path = str(path) if str(path)[-1] == '/' else str(path) + '/'

    # 实例化一个Workbook()对象(即excel文件)
    wbk = xlwt.Workbook()
    # print(result)
    # 注意， excel 不能大于256columns， 否则报错， 需要分页
    max_columns = max([len(i) for i in result])

    r, s = divmod(max_columns, 256)
    if s > 0:
        total_page = r + 1
    else:
        total_page = r

    for sub_sheet_num in range(total_page):

        # 新建一个名为Sheet1的excel sheet。此处的cell_overwrite_ok =True是为了能对同一个单元格重复操作。
        sheet = wbk.add_sheet('Sheet{}'.format(sub_sheet_num + 1), cell_overwrite_ok=True)

        # 获取当前日期，得到一个datetime对象如：(2016, 8, 9, 23, 12, 23, 424000)
        today = datetime.today()

        # 将获取到的datetime对象仅取日期如：2016-8-9
        today_date = datetime.date(today)

        start_index_num = sub_sheet_num * 256

        end_index_num = (sub_sheet_num + 1) * 256

        # 遍历result中的没个元素。
        for i in range(len(result)):

            # 对result的每个子元素作遍历，
            for j in range(len(result[i][start_index_num: end_index_num])):
                # 将每一行的每个元素按行号i,列号j,写入到excel中。
                # sheet.write(i, j, result[i][j])
                sheet.write(i, j, result[i][start_index_num: end_index_num][j])

    # 以传递的name+当前日期作为excel名称保存。
    file_path_name = path + str(time.time()).replace('.', '') + '.xls'
    # print(file_path_name)

    wbk.save(file_path_name)

    return file_path_name


def write_data_to_excel(path, result):
    path = str(path) if str(path)[-1] == '/' else str(path) + '/'

    df1 = df(result)


    file_path_name = path + str(time.time()).replace('.', '') + '.xlsx'

    df1.to_excel(file_path_name, engine='xlsxwriter')  # doctest: +SKIP

    return file_path_name


def main():
    a = time.time()
    # print(a)
    # result = get_data()
    result = [["ID"]] + [[i] for i in range(1000000)]

    b = time.time()
    # print(b, b-a)
    write_data_to_excel("/root/桌面", result)
    c = time.time()
    # print(c, c-b)


if __name__ == '__main__':
    main()
