# coding = utf-8

# 将excel中某列数据中，含有指定字符串的记录取出，并生成用这个字符串命名的excel文件

import xlrd, xlwt

import os, sys

# 按项目 导出到新的excel文件

def export_all_excel():

    root_dir = '/Users/zhaojs/Downloads'

    for root, dirs, files in os.walk(root_dir):

        for file in files:

            file_xls = os.path.join(root_dir, file)

            if file_xls.endswith('.xls'):

                wb = xlrd.open_workbook(file_xls)

                # 获取列表

                sheet = wb.sheet_by_index(0)

                # 创建写入文件

                workbook = xlwt.Workbook(encoding="utf-8")

                # 总行数

                total_rows = sheet.nrows

                # 总列数

                total_cols = sheet.ncols

                print("行数 : %d 列数 : %d" % (total_rows, total_cols))

                # 按项目简称小写 创建sheet 对象

                worksheet = workbook.add_sheet('全部')

                new_data = [

                    ['作业号', 'CPU/GPU', '项目', '场景文件', '渲染软件', '帧', '提交时间', '完成时间', '层名', '提交账户', '平台', '内存（GB）',

                     '余额消费（RMB）', '完成帧数',

                     '总帧数', '消耗时间（机时)', '实际消费（RMB）', '差值']

                ]

                loop_res = []

                for i in range(10, total_rows - 1):  # 去除最后一行总计

                    # 取出第i行 第 13 列的数据 平台名称

                    # platform_name = sheet.cell_value(i, 13)

                    excel_filename = 'all_projects.xls'

                    outputs = sheet.row_values(i)

                    # 将列表内的值转成字符串

                    data = [str(z) for z in outputs]

                    if data == '\n':

                        pass

                    else:

                        tmp = ['', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']

                        # 作业号

                        tmp[0] = data[1]

                        # CPU/GPU || 实际消费

                        if data[13] == 'gpu':

                            tmp[1] = 'GPU'

                            sum_real_consumption = round(float(data[20]) * 4, 3)

                            tmp[16] = str(sum_real_consumption)

                        else:

                            tmp[1] = 'CPU'

                            sum_real_consumption = round(float(data[20]) * 1.3, 3)

                            tmp[16] = str(sum_real_consumption)

                        # 项目

                        tmp[2] = data[4][0:3]

                        # 场景文件

                        tmp[3] = data[4]

                        # 渲染软件

                        tmp[4] = data[5]

                        # 帧

                        tmp[5] = data[6]

                        # 提交时间

                        tmp[6] = data[7]

                        # 完成时间

                        tmp[7] = data[8]

                        # 层名

                        tmp[8] = data[10]

                        # 提交账户

                        tmp[9] = data[12]

                        # 平台

                        tmp[10] = data[13]

                        # 内存（GB）

                        tmp[11] = data[14]

                        # 余额消费（RMB）

                        tmp[12] = data[15]

                        # 完成帧数

                        tmp[13] = data[18]

                        # 总帧数

                        tmp[14] = data[19]

                        # 消耗时间（机时)

                        tmp[15] = data[20]

                        # 差值    计算差值 实际消费 - 余额消费

                        difference_value = 0 if (sum_real_consumption - float(data[15])) < 0 else int(

                            sum_real_consumption - float(data[15]))

                        tmp[17] = difference_value

                        loop_res.append(tmp)

                        # 形成带表头的列表集

                        new_data.append(tmp)

                # 计算汇总数据

                sum_prices = 0  # 总消费

                sum_finish_frames = 0  # 总完成帧数

                sum_frames = 0  # 总帧数之和

                sum_times = 0  # 机时之和

                sum_real_consumption = 0  # 实际消费

                difference_value = 0  # 差值

                z = 0

                for res in loop_res:

                    # sys.exit()

                    sum_prices += float(res[12])

                    sum_finish_frames += int(float(res[13]))

                    sum_frames += int(float(res[14]))

                    sum_times += float(res[15])

                    sum_real_consumption += float(res[16])

                    difference_value += float(res[17])

                    z = z + 1

                tmp_data = ['', '', '', '', '', '', '', '', '', '', '', '', round(sum_prices, 3), sum_finish_frames,

                            sum_frames,

                            round(sum_times, 3), sum_real_consumption, difference_value]

                # 合成最终列表

                new_data.append(tmp_data)

                i = 0

                for data in new_data:

                    for j in range(len(data)):

                        worksheet.write(i, j, data[j])

                    i = i + 1

                workbook.save('./' + excel_filename)

if __name__ == "__main__":

    # Common_Excel().abc(123)

    export_all_excel()

# 通过索引

sheet1 = wb.sheets()[0]

sheet1 = wb.sheet_by_index(0)

# 通过名字

sheet1 = wb.sheet_by_name('test')

# 获取所有表名sheet_names = wb.sheet_names()

# 获取行中所有数据，返回结果是一个列表

tabs = sheet1.row_values(rowx=0, start_colx=0, end_colx=None)

# 返回一行一共有多少数据

len_value = sheet1.row_len(rowx=0)

# 获取时间

time_value = sheet1.cell_value(3, 0)

# 获取时间戳

time_stamp = int(xlrd.xldate.xldate_as_datetime(time_value, 0).timestamp())

print(time_stamp)

# 格式化日期

time_rel = time.strftime("%Y/%m/%d", time.localtime(time_stamp))

print(time_rel)