#!/usr/bin/env python 3.6.4
# -*- coding: utf-8 -*-
# @Time    : 2018/8/21 14:28
# @Author  : Orient
# @File    : 01-rsc-mk-directory.py
# @Software: PyCharm

from xlutils.copy import copy
from config import *
import xlrd,xlwt
import os

class mk_directory(translate):
    """docstring for mk_directory."""
    def __init__(self, path):
        super(mk_directory, self).__init__()
        self.path = path
    def file_path_walk(self):
        """遍历所有的子文件夹生成文件路径列表
        
        Args:
            self
           
        Returns:
            file_paths
        """
        r = os.walk(self.path)
        file_paths = []
        for path,dir_list,file_list in r:
            for file_name in file_list:
                file_path = os.path.join(path, file_name)
                file_paths.append(file_path)
        return file_paths
    def file_fliter(self):
        """过滤非excel文件

        Args:
            self

        Returns:

        """
        file_paths = self.file_path_walk()
        file_paths_fliter = []
        for file_path in file_paths:
            if '.xls' not in file_path:
                pass
            else:
                file_paths_fliter.append(file_path)
        return file_paths_fliter

    def get_column_index(self, table, columnName):
        columnIndex = None
        for i in range(table.ncols):
            if (table.cell_value(0, i) == columnName):
                columnIndex = i
                break
        return columnIndex

    def get_row_index(self, table, rowName):
        rowIndex = None
        for i in range(table.nrows):
            if (table.cell_value(i, 0) == rowName):
                rowIndex = i
                break
        return rowIndex
    def write_back(self, ws_for_write, row_value, i):
        """目录内容写回到目录文件

        Args:
            self

        Returns:
            当月目录
        """
        with xlrd.open_workbook(base_category_path, formatting_info=True) as rb:
            ws = rb.sheet_by_index(0)
            style0 = xlwt.easyxf('font: name 宋体, color-index black')
            for key in row_value.keys():
                ws_for_write.write(i + 1 , self.get_column_index(ws,key), row_value[key], style0)
            return ws_for_write
    def file_outlook(self):
        """生成目录
        
        Args:
            self,file_paths
           
        Returns:
            CMEI201807.xls
        """
        file_paths = self.file_fliter()
        with xlrd.open_workbook(base_category_path, formatting_info=True) as rb:
            i = 0
            row_value = {}
            wb_for_write = copy(rb)
            ws_for_write = wb_for_write.get_sheet(0)
            for file_path in file_paths:
                time_str = re.findall(r'[0-9]{4}\.[0-9]{1,2}',self.path)[0]
                # 定义格式时间
                row_value['format_time']  = time_transfer.transfer(self,time_str)
                # 定义文件路径
                row_value['file_path'] = os.path.join(self.path,file_path)
                # 定义层级和文件名
                file_name = row_value['file_path'].replace(self.path, '')
                file_name_trans = translate.trans(self,file_name)
                f = file_name_trans.split('\\')
                fst_cate = ''
                try:
                    row_value['id'] = str(i+1).zfill(2)
                    row_value['fst_cate'] = re.findall(r'[A-Z]{1}-[\u4e00-\u9fa5]+',file_name_trans)
                    row_value['sed_cate'] = re.findall(r'\\([0-9]{1,2}-.*)\\', file_name_trans)
                    f_name = re.findall(r'\\([0-9]{1,2}\.[0-9]{1,2}.*?\.x[a-z]{2,3})|(6-1.*.xls)|(6-6-.*.xls)', file_name_trans)
                    f_n = ''
                    for name in f_name:
                        for n in name:
                            if n:
                                row_value['file_name'] = n.replace('.xls','')
                            else:
                                pass
                except SyntaxError as e:
                    continue
                else:
                    pass
                finally:
                    ws_for_write = self.write_back(ws_for_write,row_value,i)
                    i += 1
        format_time = time_transfer.transfer(self, time_str)
        path = os.path.abspath('.')  # 获得当前工作目录
        filepath = f'{path}/category/目录景气月报{format_time}.xls'
        wb_for_write.save(filepath)
        print(format_time)
base_category_path = r'C:\Users\dfli.abcft\PycharmProjects\edb-nbs-rsc\category\目录景气月报2018-07-31.xls'
path = "D:/OneDrive - leverage innovative users/09-EDB/02-景气月报/CMEI2018.08" #文件夹目录
test = mk_directory(path)
test.file_outlook()