import datetime
import os
import logging
import pandas as pd
import xlrd

from common.utils.excel_map import KeyListDict, KeyMapDict
from common.utils.exceptions import ValidationError
from etc.settings.base import FILE_URL, HOST_NAME, HOST_PORT

logger = logging.getLogger('')


class ExcelOperator(object):
    def excel_import(self, url, key_list, *args, **kwargs):
        """
        param:url 导入Excel文件所在url地址
        param:key_list 导出数据的列表
        导入模板所有字段需要全部采用字符类型
        return: 正确返回数据字典列表  错误返回错误信息
        """
        logger.info('url: <%s>', url)
        try:
            excel_obj = xlrd.open_workbook(url)
            table_obj = excel_obj.sheet_by_index(0)
            dict_list = []
            for H in range(1, table_obj.nrows):
                dic = {}
                N = table_obj.row_values(H)
                for L in range(0, table_obj.ncols):
                    dic[(KeyListDict[key_list])[L].strip()] = str(N[L])
                dict_list.append(dic)
            logger.info('dict_list: <%s>', dict_list)
            return dict_list
        except Exception as e:
            return e

    def excel_export(self, serializer_data, key_list, *args, **kwargs):
        """
        param:serializer_data 需要导出的数据字典列表形式,不能包含外键或object类型
        param:key_list 导出数据的列表
        param:key_map 导出数据的列表字段释义
        return: 正确存储文件路径 错误返回错误信息
        """

        logger.info('serializer_data: <%s>', serializer_data)

        if serializer_data is None:
            raise ValidationError("传入数据为空")
        if not isinstance(serializer_data, list):
            serializer_data = [serializer_data]
        try:
            pf = pd.DataFrame(serializer_data)
            pf = pf[KeyListDict[key_list]]
            key_map = kwargs.get('key_map')
            if key_map is not None:
                pf.rename(columns=KeyMapDict[key_map], inplace=True)
            base_dir = os.path.dirname(
                os.path.dirname(
                    os.path.dirname(os.path.dirname(
                        os.path.abspath(__file__)))))
            file_path = pd.ExcelWriter(
                (base_dir + '/backend/export/' + key_list +
                 datetime.datetime.now().strftime('%Y%m%d%H%M%S') + '.xlsx'),
                mode='w')
            pf.to_excel(file_path, encoding='utf-8', index=False)
            file_path.save()
            name = str(file_path.path).split('/')
            if name:
                return (HOST_NAME + ':' + HOST_PORT + FILE_URL + '/' +
                        name[-1])
            else:
                raise ValidationError('文件名无效')
        except Exception as e:
            return e
