24 11 2021
# -*- coding:utf-8 -*-

import os
import re
import cv2
import sys
import csv
import time
import json
import xlrd
import xlwt
import autoit
import shutil
import ctypes
import psutil
import inspect
import logging
import zipfile
import pymysql
import requests
import datetime
import calendar
import pyautogui
import threading
import pyperclip
import subprocess
import numpy as np
import configparser
import pandas as pd
import xlwings as xw
from PIL import ImageGrab
from pynput import keyboard
from xlutils.copy import copy
from selenium import webdriver
# from Crypto.Cipher import AES
from PythonCode.Common import ImageSearch
from selenium.webdriver.common.by import By
from selenium.webdriver.common.keys import Keys
from dateutil.relativedelta import relativedelta
from selenium.webdriver.support.select import Select
from selenium.webdriver.chrome.options import Options
from PythonCode.Common.ImageSearch import ImageSearchs
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from PythonCode.Common.ownerException import NotFoundKeyException, PortHasBeenUsedException


def log(filename):
    '''
    log,文件名要传入绝对路径,
    :param author: 201916820
    :param filename:{str} log文件保存路径
    :return:{object} 返回log对象
    '''
    dstdir = os.path.split(filename)[0]  # 父文件路径
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)

    # 创建一个logger
    logger = logging.getLogger()
    logger.setLevel(logging.DEBUG)
    # 创建一个handler,用于写入日志文件
    fh = logging.FileHandler(filename)
    fh.setLevel(logging.INFO)
    # 再创建一个handler,用于输出到控制台
    ch = logging.StreamHandler()
    ch.setLevel(logging.INFO)
    # 定义handler的输出格式
    formatter = logging.Formatter('%(asctime)s %(module)s %(lineno)d [%(levelname)s] :  %(message)s')
    fh.setFormatter(formatter)
    ch.setFormatter(formatter)

    # 给logger添加handler
    logger.addHandler(fh)
    logger.addHandler(ch)
    # 记录一条日志
    logger.info('')
    # logger.warning()
    # logger.debug()
    # logger.error()
    return logger


def renamefile(srcdir, dstdir, fileName):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    files = None
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return 'NO FILE'  # 空文件夹,不进行复制操作
    for file_m in files:
        fileName = '%s.%s' % (fileName, file_m.split('.')[1])
        file_s = os.path.join(srcdir, file_m)
        file_d = os.path.join(dstdir, fileName)
        if os.path.isfile(file_s):
            os.rename(file_s, file_d)
            return file_d


def remove_file2parent_temp(account, filePath):
    '''
    移动文件到../../Temp文件夹下
    :param account:
    :param filePath:
    :return:
    '''
    path_temp = os.path.abspath(filePath + os.path.sep + "../../Back")
    if not os.path.exists(path_temp):
        os.makedirs(path_temp)  # 创建新文件夹路径
    files = os.listdir(filePath)
    for file_m in files:
        if account not in file_m:
            continue
        file_s = os.path.join(filePath, file_m)
        shutil.move(file_s, path_temp)  # 移动文件


def check_last_creat_file(account, filePath):
    if '/' in account:
        account = account.split('/')[1]
    files = os.listdir(filePath)
    file_dict = {}
    for file_m in files:
        if account not in file_m:
            continue
        file_s = os.path.join(filePath, file_m)
        if not os.path.isfile(file_s):
            continue
        file_time = os.stat(file_s).st_mtime
        file_dict[file_time] = file_s
    if not file_dict:
        return
    max_time = max(file_dict.keys())
    return file_dict[max_time]


def movefile(srcdir, dstdir):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    files = None
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return  # 空文件夹,不进行复制操作
    files_temp = []
    for file_m in files:
        file_v = os.path.join(srcdir, file_m)
        if os.path.isfile(file_v):
            files_temp.append(file_v)
    if not files_temp:
        return  # 无可复制文件(当前路径下都是子文件夹)
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    for f in files_temp:
        shutil.move(f, dstdir)  # 移动文件


def move_file_and_dir(srcdir, dstdir):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    files = None
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return  # 空文件夹,不进行复制操作
    files_temp = []
    for file_m in files:
        file_v = os.path.join(srcdir, file_m)
        files_temp.append(file_v)
    if not files_temp:
        return  # 无可复制文件(当前路径下都是子文件夹)
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    for f in files_temp:
        shutil.move(f, dstdir)  # 移动文件


def mycopyfile(srcfile, dstfile):
    if not os.path.isfile(srcfile):
        print("%s not exist!" % (srcfile))
    else:
        fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        shutil.copyfile(srcfile, dstfile)  # 复制文件
        print("copy %s -> %s" % (srcfile, dstfile))


def FileMove(srcfile, dstfile):
    time = getFormatData(format=8)
    if os.path.exists(srcfile):
        try:
            spath, sname = os.path.split(srcfile)
            fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
            dstfile = os.path.join(fpath, time + '_' + sname)
            if not os.path.exists(fpath):
                os.makedirs(fpath)  # 创建路径
            shutil.move(srcfile, dstfile)  # 复制文件
            print("文件移动成功")
        except:
            return "文件移动失败"
    else:
        return "文件不存在"


def rename(srcfile, dstdir, new_name):
    '''
    文件重命名
    :param srcfile:{str} 原始文件
    :param dstdir: {str} 新文件文件夹
    :param new_name: {str} 文件名
    :return: None
    '''
    # if not os.path.exists(dstdir):
    #     return
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    if new_name:  # 重命名文件
        new_name = os.path.join(dstdir, new_name)
        os.rename(srcfile, new_name)


def stop_thread(thread):
    '''
    关闭多线程
    :param thread:{object} 线程对象
    :return:None
    '''
    _async_raise(thread.ident, SystemExit)


def _async_raise(tid, exctype):
    """raises the exception, performs cleanup if needed"""
    tid = ctypes.c_long(tid)
    if not inspect.isclass(exctype):
        exctype = type(exctype)
    res = ctypes.pythonapi.PyThreadState_SetAsyncExc(tid, ctypes.py_object(exctype))
    if res == 0:
        raise ValueError("invalid thread id")
    elif res != 1:
        ctypes.pythonapi.PyThreadState_SetAsyncExc(tid, None)
        raise SystemError("PyThreadState_SetAsyncExc failed")


def getfileName(argv, bankName, count):
    timeArray = time.localtime(time.time())
    filename = getFormatData(argv=argv, format=3) + time.strftime(r"-%H-%M-%S", timeArray)
    return "%s_%s_%s" % (bankName, count, filename)


class Properties(object):
    '''
    读取property文件,返回字典
    '''

    def __init__(self, fileName):
        self.fileName = fileName
        self.properties = {}

    def __getDict(self, strName, dictName, value):

        if (strName.find('.') > 0):
            k = strName.split('.')[0]
            dictName.setdefault(k, {})
            return self.__getDict(strName[len(k) + 1:], dictName[k], value)
        else:
            dictName[strName] = value
            return

    def getProperties(self):
        try:
            pro_file = open(self.fileName, 'Ur')
            for line in pro_file.readlines():
                line = line.strip().replace('\n', '')
                if line.find("#") != -1:
                    line = line[0:line.find('#')]
                if line.find('=') > 0:
                    strs = line.split('=')
                    strs[1] = line[len(strs[0]) + 1:]
                    self.__getDict(strs[0].strip(), self.properties, strs[1].strip())
        except Exception as e:
            raise e
        else:
            pro_file.close()
        return self.properties


# class AESCoder():
#     def __init__(self):
#         self.__encryptKey = "iEpSxImA0vpMUAabsjJWug=="
#         self.__key = base64.b64decode(self.__encryptKey)
#
#     # AES加密
#     def encrypt(self, data):
#         BS = 16
#         pad = lambda s: s + (BS - len(s) % BS) * chr(BS - len(s) % BS)
#         cipher = AES.new(self.__key, AES.MODE_ECB)
#         encrData = cipher.encrypt(pad(data))
#         # encrData = base64.b64encode(encrData)
#         return encrData
#
#     # AES解密
#     def decrypt(self, encrData):
#         unpad = lambda s: s[0:-s[-1]]
#         cipher = AES.new(self.__key, AES.MODE_ECB)
#         decrData = unpad(cipher.decrypt(encrData))
#         return decrData.decode('utf-8')


def Write2CsvF(pathTag, datas, codeflag="utf-8", delimiter="|"):
    print(pathTag)
    if not os.path.exists(os.path.dirname(pathTag)):
        os.makedirs(os.path.dirname(pathTag))

    try:
        with open(pathTag, "w", newline='', encoding=codeflag) as csvFile:

            writer = csv.writer(csvFile, delimiter=delimiter)

            if (str(type(datas)).find("str") + 1):
                writer.writerow(datas)

            elif (str(type(datas)).find("list") + 1):
                writer.writerows(datas)

            elif (str(type(datas)).find("dict") + 1):
                for keys, values in datas.items():
                    writer.writerow([keys, values])

            else:
                csvFile.close()
                return 1

            csvFile.close()
    except:
        print("检查文件名, 文件是否已打开.")

    return 0


def write2Json(msg, file_name):
    '''
    写入json文件
    :param msg:
    :param file_name:
    :return:
    '''
    json_msg = json.dumps(msg, ensure_ascii=False)
    with open(file_name, 'w') as file:
        file.write(json_msg)
        file.close()


def readJsonFile(filename):
    '''
    读取json文件
    :param filename:
    :return:
    '''
    with open(filename) as file:
        json_msg = json.load(file)
    return json_msg


def ReadFromConfig(pathFile, nameSection, dictvalue=''):
    # try:
    if os.path.exists(pathFile):
        with open(pathFile, encoding='UTF-8') as fileIni:
            cf = configparser.ConfigParser()
            # cf.read(pathFile, encoding="utf-8")
            cf.read(pathFile)
            infoConfig = cf.items(nameSection)  # type : [tuple,]
            infoConfig = dict(infoConfig)  # 转为 dict
            # print(infoConfig)
            if dictvalue != "":
                return infoConfig[dictvalue]

        return infoConfig
    else:
        return "error"


def WriteFile(filename, content, write='a'):
    '''
    将一条数据写入到文件中
    :param filename: 写入文件的路径
    :param content: 要写入的内容,换行符一\n分割
    :param write: 写入的方式,a为往后面追加,为写入删除之前的数据
    :return: 成功返回True,失败返回失败的原因
    '''
    try:
        fpath, fname = os.path.split(filename)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        with open(filename, write) as f:  # 如果filename不存在会自动创建, 'w'表示写数据,写之前会清空文件中的原有数据!
            f.write(content)
        return True
    except Exception as e:
        return e


# 文件压缩
def zip_ya(startdir, file_news):
    # startdir = ".\\123"  #要压缩的文件夹路径
    # file_news = startdir +'.zip' # 压缩后文件夹的名字
    z = zipfile.ZipFile(file_news, 'w', zipfile.ZIP_DEFLATED)  # 参数一:文件夹名
    for dirpath, dirnames, filenames in os.walk(startdir):
        fpath = dirpath.replace(startdir, '')  # 这一句很重要,不replace的话,就从根目录开始复制
        fpath = fpath and fpath + os.sep or ''  # 这句话理解我也点郁闷,实现当前文件夹以及包含的所有文件的压缩
        for filename in filenames:
            z.write(os.path.join(dirpath, filename), fpath + filename)
            # print ('压缩成功')
    z.close()


def get_all_file(dirname):
    filelist = []
    for root, dirs, files in os.walk(dirname):
        for name in files:
            filelist.append(os.path.join(root, name))
    return filelist


def zip_dir(dirname, zipfilename):
    filelist = []
    if os.path.isfile(dirname):
        filelist.append(dirname)
    else:
        for root, dirs, files in os.walk(dirname):
            for name in files:
                filelist.append(os.path.join(root, name))

    zf = zipfile.ZipFile(zipfilename, "w", zipfile.zlib.DEFLATED)
    for tar in filelist:
        arcname = tar[len(dirname):]
        zf.write(tar, arcname)


def get_datetime_string(datetime_obj=None, string_format=1, time_interval=0):
    '''
    @ Song Yu
    传入时间和时间差,获取两者相加后的目标时间
    :param datetime_obj: datetime对象
    :param string_format: 返回的数据格式,传入参数与返回数据映射如下
                1  20191018
                2  2019/10/18
                3  2019-10-18
                4  2019_10_18
                5  201910
                6  201910 152711
                7  15:27:11
                8  152711
                9  作为字典返回所有日期数据
    :param time_interval: 目标时间与当前时间的时间间隔
    :return:
    '''
    if not datetime_obj:
        datetime_obj = datetime.datetime.now()
    if isinstance(datetime_obj, datetime.date):
        datetime_obj = datetime.datetime.combine(datetime_obj, datetime.time(0, 0))
    if isinstance(datetime_obj, datetime.datetime):
        time_delta = datetime.timedelta(days=time_interval)
        target_date = datetime_obj + time_delta
        datetime_dict = {'date': target_date,
                         "Y": '{}'.format(target_date.year),
                         'M': '{:0>2d}'.format(target_date.month),
                         'D': '{:0>2d}'.format(target_date.day),
                         'h': '{}'.format(target_date.hour),
                         "m": '{}'.format(target_date.minute),
                         "s": '{}'.format(target_date.second)
                         }
        if string_format == 1:  # 日期格式化并返回
            return '%s%s%s' % (datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'])
        elif string_format == 2:
            return '%s/%s/%s' % (datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'])
        elif string_format == 3:
            return '%s-%s-%s' % (datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'])
        elif string_format == 4:
            return '%s_%s_%s' % (datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'])
        elif string_format == 5:
            return '%s%s' % (datetime_dict['Y'], datetime_dict['M'])
        elif string_format == 6:
            return '%s%s%s %s%s%s' % (
                datetime_dict['Y'], datetime_dict['M'], datetime_dict['D'],
                datetime_dict['h'], datetime_dict['m'], datetime_dict['s'])
        elif string_format == 7:
            return '%s:%s:%s' % (datetime_dict['h'], datetime_dict['m'], datetime_dict['s'])
        elif string_format == 8:
            return '%s%s%s' % (datetime_dict['h'], datetime_dict['m'], datetime_dict['s'])
        elif string_format == 9:
            return datetime_dict
    else:
        return None


def getFormatData(argv=None, format=1, interval=0):
    '''
    时间获取函数,如果命令行参数argv带有时间参数,验证后返回该时间,否则返回系统当前时间与时间差interval相加后的日期时间
    :param argv: sys.argv,传入的命令行参数
    :param format: 返回的数据格式,传入参数与返回数据映射如下
                    1  20191018
                    2  2019/10/18
                    3  2019-10-18
                    4  2019_10_18
                    5  date备用格式
                    6  date备用格式
                    7  15:27:11
                    8  152711
                    9  作为字典返回所有日期数据
    :param interval: 目标时间与当前时间的时间间隔,不对命令行传入的时间进行运算。
    :return:
    '''
    # timeArray = time.localtime(time.time())
    # timeeee = time.strftime(r"%Y%m%d %H:%M:%S", timeArray)

    time_delta = datetime.timedelta(days=interval)  # 设置时间间隔
    sys_time = datetime.datetime.now() + time_delta  # 获取系统时间

    if (argv and len(argv) == 1) or (not argv) or (len(argv) >= 2 and 'null' == argv[1]):  # 命令行未传入时间参数
        yestd = {'date': sys_time,
                 "Y": sys_time.year, 'M': sys_time.month, 'D': sys_time.day,
                 'h': sys_time.hour, "m": sys_time.minute, "s": sys_time.second}
        if int(sys_time.month) < 10:  # 时间格式处理
            yestd["M"] = '0%s' % sys_time.month
        if int(sys_time.day) < 10:
            yestd["D"] = '0%s' % sys_time.day
        print(argv)
    else:  # 命令行传入了时间参数
        print(argv[1])
        # match = re.search(r'\d{4}[-/]\d{1,2}[-/]\d{1,2}', '2019/01/29')
        match = re.search(r'20\d{2}-\d{1,2}-\d{1,2}', argv[1])  # 时间格式匹配
        match2 = re.search(r'20\d{2}/\d{1,2}/\d{1,2}', argv[1])
        match3 = re.search(r'20\d{2}\d{1,2}\d{1,2}', argv[1])
        if match:
            print('match: %s' % match)
            yesterday = datetime.datetime.strptime(match.group(), '%Y-%m-%d')
        elif match2:
            print('match2: %s' % match2)
            yesterday = datetime.datetime.strptime(match2.group(), '%Y/%m/%d')
        elif match3:
            print('match3: %s' % match3)
            yesterday = datetime.datetime.strptime(match3.group(), '%Y%m%d')
        else:
            return None
        yestd = {'date': yesterday,
                 "Y": yesterday.year, 'M': yesterday.month, 'D': yesterday.day,
                 'h': sys_time.hour, "m": sys_time.minute, "s": sys_time.second}
        if int(yesterday.month) < 10:  # 时间格式处理
            yestd["M"] = '0%s' % yesterday.month
        if int(yesterday.day) < 10:
            yestd["D"] = '0%s' % yesterday.day

    if format == 1:  # 日期格式化并返回
        return '%s%s%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 2:
        return '%s/%s/%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 3:
        return '%s-%s-%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 4:
        return '%s_%s_%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 5:
        pass  # date备用格式
    elif format == 6:
        pass
    elif format == 7:
        return '%s:%s:%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 8:
        return '%s%s%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 9:
        return yestd


def getLastMonthDate(format=0):
    first_day = datetime.date(datetime.date.today().year, datetime.date.today().month, 1)  # 本月第一天
    last = first_day - datetime.timedelta(1)  # 获取上个月最后一天日期
    if format == 0:
        return str(last)
    else:
        return str(last).replace('-', '')


class Rfs(object):
    status = {
        'NoDevice': 'key未接入或者未供电',
        'NoLink': 'key未连接',
        'Linked': '连接成功',
    }
    local_ip = '10.1.2.87'
    pathFile = r"D:\ChinaCoalAuto\Common\Config\USBHubConfig.ini"  # 配置文件路径

    def get_allPort_msg(self):
        result = {}
        temp_result = os.popen('RfsNu -l')
        res = temp_result.read()
        No = 1
        for line in res.splitlines():
            if 'UsbNumber' in line:
                temp = line.strip().split(' ')
                result[No] = {
                    temp[0].split(':')[0]: temp[0].split(':')[1],
                    temp[1].split(':')[0]: temp[1].split(':')[1],
                    temp[2].split(':')[0]: temp[2].split(':')[1],
                    temp[3].split(':')[0]: temp[3].split(':')[1],
                    temp[-1].split(':')[0]: temp[-1].split(':')[1],
                }
                No += 1
        return result

    def get_port_msg(self, port):
        port_status = self.get_allPort_msg()
        if isinstance(port, int):
            return port_status[port]

    def is_linked(self, port):
        port_status = self.get_port_msg(port)
        if 'NoDevice' == port_status['linkstatus']:
            return 'NoDevice', port_status['linked']
        elif 'NoLink' == port_status['linkstatus']:
            return 'NoLink', port_status['linked']
        elif 'Linked' == port_status['linkstatus']:
            return 'Linked', port_status['linked']

    def wait_for_linked(self, portName, wait_time):
        port = int(self.ReadFromConfig(Rfs.pathFile, "Main", portName))
        status = None
        while wait_time >= 0:
            status, ip = self.is_linked(port)
            print(port, status)
            if status == 'Linked':
                if self.local_ip in ip:
                    break
                else:
                    raise PortHasBeenUsedException('The port has been used')
            else:
                time.sleep(1)
                wait_time -= 1

        if status != 'Linked':
            raise NotFoundKeyException('Not checked the key')  # key未连接时,抛出异常结束后续操作

    # 读取配置文件 Section->{k:v}
    def ReadFromConfig(self, pathFile, nameSection, dictvalue=''):
        # try:
        if os.path.exists(pathFile):
            with open(pathFile, encoding='UTF-8') as fileIni:
                cf = configparser.ConfigParser()
                cf.read(pathFile, encoding='UTF-8')
                infoConfig = cf.items(nameSection)  # type : [tuple,]
                infoConfig = dict(infoConfig)  # 转为 dict
                # print(infoConfig)
                if dictvalue != "":
                    return infoConfig[dictvalue]
            return infoConfig
        else:
            return "error"


class Mysql(object):
    '''
    操作mysql数据库
    '''

    def __init__(self, host, user, password, port=3306, database=None, logger=None, **kwargs):
        '''
        初始化连接数据库
        :parameter huang
        :param host: 地址
        :param user:用户名
        :param password:密码
        :param database:数据库名称
        :param port:端口号
        :param kwargs: 以下参数
                 unix_socket=None,charset='', sql_mode=None,
                 read_default_file=None, conv=None, use_unicode=None,
                 client_flag=0, cursorclass=Cursor, init_command=None,
                 connect_timeout=10, ssl=None, read_default_group=None,
                 compress=None, named_pipe=None,autocommit=False, db=None,
                 passwd=None, local_infile=False,max_allowed_packet=16*1024*1024,
                 defer_connect=False,auth_plugin_map=None, read_timeout=None,
                 write_timeout=None,bind_address=None, binary_prefix=False,
                 program_name=None,server_public_key=None
        '''
        self.logger = logger
        print('连接到mysql服务器...')
        self.logger and self.logger.info('连接到mysql服务器...')
        self.db = pymysql.connect(host=host, user=user, password=password, database=database, port=port, **kwargs)
        print('数据库连接成功!')
        self.logger and self.logger.info('数据库连接成功!')

    def insertdb(self, sql):
        '''
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
        try:
            cursor.execute(sql)  # 执行sql语句
            self.db.commit()  # 提交到数据库执行
        except Exception as e:
            logging.error(e)
            logging.error('插入数据失败!')
            self.db.rollback()  # 回滚
        finally:
            cursor.close()

    def updatedb(self, sql):
        '''
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
        try:
            cursor.execute(sql)  # 执行SQL语句
            self.db.commit()  # 提交到数据库执行
        except:
            logging.error('更新数据失败!')
            self.db.rollback()  # 发生错误时回滚
        finally:
            cursor.close()

    def update_db_many(self, sql, data):
        '''
            批量插入
        :param sql:
        :param data:列表字典
        :return:
            example:
                db = Mysql("localhost", "root", "admin123", 'ccyboa_service', port=3306)
                sql = ("INSERT INTO `ops_data_bank_copy`(batch_num,data_comment_1,data_decimal_1,data_datetime_5,data_int_1,is_delete) "
                       "VALUES(%s,%s,%s,%s,%s,%s)")
                data = [
                    ('test_20111111', 'test_2019', '33004', '2019/01/02', 234, 1),
                    ('test_2011222', 'test_2018', '33001', '2019/01/03', 235, 1),
                    ('test_2011333', 'test_2017', '33002', '2019/01/04', 234, 1),
                    ('test_20114441', 'test_2019', '33004', '2019/01/05', 234, 1),
                ]
                db.insert_db_many(sql,data)
        '''
        cursor = self.db.cursor()
        try:
            cursor.executemany(sql, data)  # 批量执行多条插入SQL语句
            self.db.commit()  # 提交事务
        except Exception as e:
            logging.error(e)
            self.db.rollback()  # 有异常,回滚事务
            raise e
        finally:
            cursor.close()

    def select_db_fetchall(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()
        # sql = "SELECT * FROM ops_data_bank where batch_num like '%20191202'"
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchall()  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            return results
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchone(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchone()  # 获取查询的所有记录
            return title_name, results
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchmany(self, sql, size=None, *args):
        '''
        数据库结果查询
        :param sql:
        :param size: 条目
        :param args:
        :return: 数据元组
        '''
        cursor = self.db.cursor()
        try:
            cursor.execute(sql, *args)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchmany(size)  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            print(results)
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def closedb(self):
        '''
        关闭数据库
        :return:
        '''
        self.db.close()

    def __del__(self):
        self.closedb()
        print("数据库关闭成功,__del__对象已经销毁")


class MysqlHelper(object):

    def __init__(self, logger=None):

        self.logger = logger
        self.conn = None

    def connect(self, sql_setting):
        print('连接到mysql服务器...')
        self.logger and self.logger.info('连接到mysql服务器...')
        self.conn = pymysql.connect(
            host=sql_setting['host'],
            user=sql_setting['user'],
            password=sql_setting['password'],
            database=sql_setting['database'],
            port=sql_setting['port'],
        )
        self.cursor = self.conn.cursor()
        print('数据库连接成功!')
        self.logger and self.logger.info('数据库连接成功!')

    def insert_line(self, table, **kwargs):
        res_info = {
            'status': False,
            'id': None,
            'rowcount': 1,
        }
        status = False
        sql = "INSERT INTO {}(".format(table)
        keys = ''
        values = ''
        for k, v in kwargs.items():
            keys += '{},'.format(k)
            if v == 'NULL':
                values += "{},".format(v)
            else:
                values += "'{}',".format(v)
        keys = keys.rstrip(',')
        values = values.rstrip(',')
        sql = sql + keys + ")values(" + values + ")"
        print('sql', sql)
        try:
            self.cursor.execute(sql)  # 执行sql语句
            self.conn.commit()  # 提交到数据库执行
            # 影响的行数
            rowcount = self.cursor.rowcount

            res_info['status'] = True
            res_info['id'] = self.cursor.lastrowid  # # 最后插入的数据id
        except Exception as e:
            print('插入数据失败!', e)
            self.logger and self.logger.info('插入数据失败!', e)
            self.conn.rollback()  # 回滚
            res_info['status'] = False
        return res_info

    def insert_list(self, table, data_dict_list):
        pass

    def delete(self, table, condition, ):
        status = False
        sql = "DELETE FROM {} where {}".format(table, condition)
        print('sql', sql)
        try:
            self.cursor.execute(sql)  # 执行sql语句
            self.conn.commit()  # 提交到数据库执行
            # 影响的行数
            rowcount = self.cursor.rowcount
            status = True
            # return rowcount
        except Exception as e:
            print('删除数据失败!', e)
            self.logger and self.logger.info('删除数据失败!', e)
            self.conn.rollback()  # 回滚
            status = False
        return status

    def update(self, table, condition, **kwargs):
        res_info = {
            'status': False,
            'id': None,
            'rowcount': 1,
        }
        # UPDATE t_person SET NAME = '大乔' WHERE country = '蜀国'
        sql = "UPDATE {} SET ".format(table)
        keys = ""
        values = ""
        for k, v in kwargs.items():
            if v == 'NULL':
                sql += '{}={},'.format(k, v)
            else:
                sql += '{}="{}",'.format(k, v)
        sql = sql.rstrip(',')
        sql += 'WHERE {}'.format(condition)
        print('update sql', sql)
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            # 提交到数据库执行
            self.conn.commit()
            # 影响的行数
            rowcount = self.cursor.rowcount
            res_info['status'] = True
        except Exception as e:
            print('更新数据失败!', e)
            self.logger and self.logger.info('更新数据失败!', e)
            self.conn.rollback()  # 回滚
            res_info['status'] = False
        return res_info

    def select(self, sql):
        print(sql)
        try:
            # 执行SQL语句
            self.cursor.execute(sql)
            # 提交到数据库执行
            self.conn.commit()
            # 影响的行数
            rowcount = self.cursor.rowcount
        except Exception as e:
            print('查询数据失败!', e)
            self.logger and self.logger.info('查询数据失败!', e)
            self.conn.rollback()  # 回滚
        title_name = [mem[0] for mem in self.cursor.description]
        results = self.cursor.fetchall()  # 获取查询的所有记录
        data_list = list()
        for item in results:
            data_list.append(dict(zip(title_name, item)))
        return data_list

    def update_db_many(self, sql, data):
        '''
            批量插入
        :param sql:
        :param data:列表字典
        :return:
            example:
                db = Mysql("localhost", "root", "admin123", 'ccyboa_service', port=3306)
                sql = ("INSERT INTO `ops_data_bank_copy`(batch_num,data_comment_1,data_decimal_1,data_datetime_5,data_int_1,is_delete) "
                       "VALUES(%s,%s,%s,%s,%s,%s)")
                data = [
                    ('test_20111111', 'test_2019', '33004', '2019/01/02', 234, 1),
                    ('test_2011222', 'test_2018', '33001', '2019/01/03', 235, 1),
                    ('test_2011333', 'test_2017', '33002', '2019/01/04', 234, 1),
                    ('test_20114441', 'test_2019', '33004', '2019/01/05', 234, 1),
                ]
                db.insert_db_many(sql,data)
        '''
        cursor = self.conn.cursor()
        try:
            cursor.executemany(sql, data)  # 批量执行多条插入SQL语句
            self.conn.commit()  # 提交事务
        except Exception as e:
            self.logger.error(e)
            self.conn.rollback()  # 有异常,回滚事务
            raise e
        finally:
            cursor.close()

    def select_fetchall(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.conn.cursor()
        # sql = "SELECT * FROM ops_data_bank where batch_num like '%20191202'"
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [item[0] for item in cursor.description]
            results = cursor.fetchall()  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            return results
        except Exception as e:
            self.logger.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_list(self, table, condition, ):
        '''
        数据库查询
        :param sql:
        :return:
        '''

        sql = "SELECT * FROM {TABLE} where {CONDITION}".format(TABLE=table, CONDITION=condition)
        print('sql', sql)
        try:
            self.cursor.execute(sql)  # 执行sql语句
        except Exception as e:
            print('查询数据失败!', e)
            self.logger and self.logger.error('删除数据失败!', e)
        title_name = [mem[0] for mem in self.cursor.description]
        results = self.cursor.fetchall()  # 获取查询的所有记录
        data_list = list()
        for item in results:
            data_list.append(dict(zip(title_name, item)))
        return data_list

    def select_db_fetchone(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchone()  # 获取查询的所有记录
            return title_name, results
        except Exception as e:
            self.logger.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchmany(self, sql, size=None, *args):
        '''
        数据库结果查询
        :param sql:
        :param size: 条目
        :param args:
        :return: 数据元组
        '''
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql, *args)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchmany(size)  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            print(results)
        except Exception as e:
            self.logger.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def close_connect(self):
        self.conn.close()

    def __del__(self):
        self.close_connect()
        print("数据库关闭成功,__del__对象已经销毁")
        self.logger and self.logger.info('数据库关闭成功,__del__对象已经销毁')


# class bank_balance(object):
#     data_id = ''
#     batch_num = ''
#     account_no = ''
#     account_name = ''
#     account_balance = ''
#     bank_name = ''
#     account_date = ''
#     insert_time = ''
#     is_delete = ''
#     source_id = ''
#     versionNo = ''
#
#     def change_batch_num(self):
#         '''
#         修改批号
#         :return:
#         '''
#         batch_num = self.batch_num[1]
#         temp = batch_num.split('_')[:-1]
#         temp.append(getFormatData(interval=-1))
#         self.batch_num = (self.batch_num[0], '_'.join(temp))
#
#     def change_versionNo(self, db):
#         '''
#         查询修改版本号
#         :param db:
#         :return:
#         '''
#         sql = "(SELECT IFNULL(MAX(versionNo),0)+1  FROM ops_data_bank_balance " \
#               "WHERE batch_num = '%s'  AND account_no	 = '%s');" % (self.batch_num[1], self.account_no[1])
#         result_data = db.select_db_fetchone(sql)
#         version_no = result_data[1][0]
#         self.versionNo = (self.versionNo[0], version_no)
#
#     def set_property(self, sql_result):
#         '''
#         根据查询结果为对象属性赋值,值为元组,对应值分别为  属性=(数据库字段名,数据库属性对应数据)
#         :param sql_result:
#         :return:
#         '''
#         if len(sql_result) != 2:
#             return
#         for index, name_column in enumerate(sql_result[0]):
#             if 'data_id' == name_column:
#                 self.data_id = (name_column, sql_result[1][index])
#             elif 'batch_num' == name_column:
#                 self.batch_num = (name_column, sql_result[1][index])
#             elif 'account_no' == name_column:
#                 self.account_no = (name_column, sql_result[1][index])
#             elif 'account_name' == name_column:
#                 self.account_name = (name_column, sql_result[1][index])
#             elif 'account_balance' == name_column:
#                 self.account_balance = (name_column, str(sql_result[1][index]))
#             elif 'bank_name' == name_column:
#                 self.bank_name = (name_column, sql_result[1][index])
#             elif 'account_date' == name_column:
#                 self.account_date = (name_column, str(sql_result[1][index]))
#             elif 'insert_time' == name_column:
#                 self.insert_time = (name_column, str(sql_result[1][index]))
#             elif 'is_delete' == name_column:
#                 self.is_delete = (name_column, sql_result[1][index])
#             elif 'source_id' == name_column:
#                 self.source_id = (name_column, sql_result[1][index])
#             elif 'versionNo' == name_column:
#                 self.versionNo = (name_column, sql_result[1][index])
#
#     def get_sql_data(self, db, account):
#         '''
#         数据库根据id排序,获取最后一条数据,属性赋值
#                         sql_select_data = ("(SELECT * FROM `ops_data_bank_balance` WHERE account_no = '%s' "
#                            "AND is_delete = 0 ORDER BY account_date	DESC LIMIT 1);" % account)
#         :param db:
#         :param account:
#         :return:
#         '''
#         sql_select_data = ("(SELECT * FROM `ops_data_bank_balance` WHERE account_no = '%s' "
#                            "AND is_delete = 0 ORDER BY data_id	DESC LIMIT 1);" % account)
#         result_data = db.select_db_fetchone(sql_select_data)
#         self.set_property(result_data)
#
#     def insert_balance(self, db):
#         '''
#         数据库数据插入
#                 sql="INSERT INTO `ops_data_bank_balance`( `batch_num`, `account_no`, `account_name`, `account_balance`, " \
#                         "`bank_name`, `account_date`, `is_delete`, `source_id`, `versionNo`) " \
#             "VALUES('BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191205','770901040000769','中煤新集能源股份有限公司'," \
#             "'66343.49','中国农业银行','2019-12-01 00:00:00','0','','2');"
#         :param db:
#         :return:
#         '''
#         sql2 = "INSERT INTO `ops_data_bank_balance`( `batch_num`, `account_no`, `account_name`, `account_balance`, " \
#                "`bank_name`, `account_date`, `is_delete`, `source_id`, `versionNo`) " \
#                "VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s');" % (
#                    self.batch_num[1], self.account_no[1], self.account_name[1],
#                    self.account_balance[1], self.bank_name[1], self.account_date[1],
#                    self.is_delete[1], '', self.versionNo[1])
#         db.insertdb(sql2)
#         logging.info('余额插入成功')
#
#
# def insert_bank_balance(account):
#     '''
#         SELECT * FROM `ops_data_bank_balance` WHERE account_no	 = '770901040000769' AND is_delete = 0 ORDER BY data_id	DESC LIMIT 1;
#         SELECT  FROM ops_data_bank_balance WHERE batch_num = 'BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191203' ;
#         (SELECT IFNULL(MAX(versionNo),0)+1  FROM ops_data_bank_balance WHERE batch_num = 'BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191204' );
#         @:param 参数账号信息
#         @:param
#         :return:
#             # 查询根据账号余额数据 银行余额类保存相关数据属性
#             # 查询版本
#             # 生成sql插入语句
#             # 插入结果数据
#             # 查询插入结果检验
#             # 确认插入成功
#     '''
#     balance = bank_balance()  # 余额类
#     db = Mysql("localhost", "root", "root", 'ccyboa_service', port=3306)
#     balance.get_sql_data(db, account)  # 根据id排序查找数据
#     balance.change_batch_num()  # 修改batch_num
#     balance.change_versionNo(db)  # 获取versionNo并修改
#     balance.insert_balance(db)  # 数据插入
#
#     balance2 = bank_balance()  # 余额类
#     balance2.get_sql_data(db, account)  # 根据id排序查找数据,余额类添加相关属性
#     if balance.batch_num[1] == balance2.batch_num[1] and balance.versionNo[1] == balance2.versionNo[1]:
#         print('数据库查询道的数据和数据库插入的数据一致,success')
#         return True
#     else:
#         print('数据库查询道的数据和数据库插入的数据不一致,Fail')
#         return False

def csvToList(excelPath):
    '''
    打开csv文件,转换成list
    :param excelPath:
    :return:
    '''
    if os.path.exists(excelPath):
        # 打开csv
        # excelPath = "D:/autoit/项目/华鑫证券/文档/增值税取数文档及模板/附表二.csv"
        print("打开路径下的文件:", excelPath)
        try:
            df = pd.read_csv(open(excelPath), index_col=False, header=None, error_bad_lines=False)
        except Exception as e:
            print("打开文件失败:", e)
            df = pd.read_csv(open(excelPath, encoding='UTF-8'), index_col=False, header=None, error_bad_lines=False)
        train_data = pd.np.array(df)  # np.ndarray()
        train_x_list = train_data.tolist()  # list
    else:
        train_x_list = [[]]
    return train_x_list


def listToCSV(csvPath, csvList):
    '''
    讲列表写入到csv
    :param csvPath: 写入的路径
    :param csvList: 写入的列表
    :return:
    '''
    df_data = pd.DataFrame(csvList)
    df_data.to_csv(csvPath, header=False, index=False, encoding='gb2312')


def resultWriteCSV(resultPath, resultTemplatePath, writeResult):
    '''
    将结果写入到csv文件中
    :param resultPath: 写入文件的路径
    :param resultTemplatePath: 模板的路径
    :param writeResult: 写入的数据值
    :return:
    '''
    if not os.path.exists(resultPath):
        mycopyfile(resultTemplatePath, resultPath)
    readLists = csvToList(resultPath)
    for i in range(0, len(readLists)):
        if readLists[i][0] == writeResult[0]:
            readLists[i][1] = writeResult[1]
            readLists[i][2] = writeResult[2]
            readLists[i][3] = writeResult[3]
            readLists[i][4] = writeResult[4]
    listToCSV(resultPath, readLists)


def creatChrome(url):
    """
    创建谷歌浏览器,成功返回
    :param url:要打开的url
    :return: 若成功,返回driver对象,失败返回False
    :param url:
    :return:
    """
    try:
        p = subprocess.Popen('chrome.exe --remote-debugging-port=9222 --user-data-dir="C:\\selenum\\AutomationProfile"',
                             shell=True,
                             stdin=subprocess.PIPE,
                             stdout=subprocess.PIPE, stderr=subprocess.STDOUT, cwd=os.path.dirname(
                r'C:\Program Files\Google\Chrome\Application\chrome.exe'))
        chrome_option = Options()
        chrome_option.add_experimental_option("debuggerAddress", "127.0.0.1:9222")
        driver = webdriver.Chrome(chrome_options=chrome_option)
        driver.maximize_window()  # 最大化窗口
        driver.get(url)
    except Exception as e:
        print("creatChrome:", e)
        driver = False

    return driver


def create_chrome_fast(url):
    """
    创建谷歌浏览器,成功返回
    :param url:要打开的url
    :return: 若成功,返回driver对象,失败返回False
    :param url:
    :return:
    """
    try:
        options = webdriver.ChromeOptions()
        # 关闭“chrome正受到自动测试软件的控制”
        # V75以及以下版本
        # option.add_argument('disable-infobars')
        # V76以及以上版本
        options.add_experimental_option('useAutomationExtension', False)
        options.add_experimental_option('excludeSwitches', ['enable-automation'])
        prefs = {}
        # 设置这两个参数就可以避免密码提示框的弹出
        prefs = {'profile.default_content_setting_values': {'notifications': 2}}  # 阻止弹窗
        prefs['credentials_enable_service'] = False
        prefs['profile.password_manager_enabled'] = False
        options.add_experimental_option('prefs', prefs)
        # options.add_argument("--block-new-web-contents") # 阻止弹窗
        # 不自动关闭浏览器
        options.add_experimental_option("detach", True)
        # options.add_argument('-proxy-server=http://122.192.29.105:8118')
        # options.add_argument('headless') # 设置后台运行
        driver = webdriver.Chrome(options=options)
        driver.maximize_window()
        driver.get(url)
    except Exception as e:
        print("creatChrome:", e)
        driver = False

    return driver


# def creatChrome(url):
#     """
#     创建谷歌浏览器,成功返回
#     :param url:要打开的url
#     :return: 若成功,返回driver对象,失败返回False
#     :param url:
#     :return:
#     """
#     try:
#         driver = webdriver.Chrome()
#         driver.maximize_window()  # 最大化窗口
#         driver.get(url)
#     except Exception as e:
#         driver = False
#
#     return driver


def createIE(url):
    """
    创建IE浏览器,成功返回
    :param url:要打开的url
    :return: 若成功,返回driver对象,失败返回False
    """
    try:
        driver = webdriver.Ie()
        driver.maximize_window()  # 最大化窗口
        driver.get(url)
    except Exception as e:
        print("creatChrame:", e)
        driver = False

    return driver


def element_exist(driver, element):
    try:
        driver.find_element_by_xpath(element)
    except Exception as e:
        print("[Error:element_exist]", e)
        return False
    return True


def wait_element_exist(driver, element, timeout=5, raise_error=True):
    """

    :param driver:
    :param element:
    :param timeout:
    :param raise_error:
    :return:
    """
    status = False
    try:
        WebDriverWait(driver, timeout, 0.5).until(
            EC.presence_of_element_located((By.XPATH, element)))
        status = True
    except Exception as e:
        if raise_error:
            raise e
        print("[Error:wait_element_exist]", e)
        status = False
    return status


def wait_element_click(driver, element, timeout=5, raise_error=True):
    status = False
    try:
        WebDriverWait(driver, timeout, 0.5).until(EC.presence_of_element_located((By.XPATH, element)))
        driver.find_element_by_xpath(element).click()
        print('点击成功 {}'.format(element))
        status = True
    except Exception as e:
        if raise_error:
            raise e
        print("[Error:wait_click_element]:{},element={}".format(e, element))
        status = False
    return status


def wait_element_send(driver, element, value, timeout=5, raise_error=True):
    """

    :param driver:
    :param element:
    :param value:
    :param timeout:
    :return:
    """
    status = False
    try:
        WebDriverWait(driver, timeout, 0.5).until(EC.presence_of_element_located((By.XPATH, element)))
        driver.find_element_by_xpath(element).clear()
        driver.find_element_by_xpath(element).send_keys(str(value))
        status = True
    except Exception as e:
        if raise_error:
            raise e
        print("[Error:sendKeys]", e)
        status = False
    return status


def element_get_text_excluding_children(element):
    """
    :param element:
    :return:
    """
    children = element.find_elements_by_xpath('*')
    original_text = element.text
    for child in children:
        original_text = original_text.replace(child.text, '', 1)
    return original_text


def clickJump(driver, element):
    """
    Xpath点击某个元素
    :param driver:
    :param element:
    :return:
    """
    try:
        driver.find_element_by_xpath(element).click()
        clickJumpResult = True
        print('点击成功')
    except Exception as e:
        print("[Error:clickJump]", e)
        clickJumpResult = False
        print('点击失败')
    time.sleep(0.5)
    return clickJumpResult


def JsClick(driver, jspath):
    flag = True
    try:
        js = jspath
        driver.execute_script(js)
        return flag
    except Exception as e:
        print(e)
        flag = False
        return flag


def sendKeys(driver, element, value):
    """
    输入网页页面元素
    :param driver: 浏览器对象
    :param element: 要输入的元素
    :param value: 输入的值
    :return:
    """
    try:
        driver.find_element_by_xpath(element).clear()
        driver.find_element_by_xpath(element).send_keys(str(value))
        sendKeysResult = True
    except Exception as e:
        print("[Error:sendKeys]", e)
        sendKeysResult = False
    return sendKeysResult


def get_attributes(driver, element, attributes, defaultResult=""):
    try:
        text = driver.find_element_by_xpath(element).get_attribute(attributes)
        clickJumpResult = text
    except Exception as e:

        print("[Error:element]", e)
        clickJumpResult = defaultResult

    return clickJumpResult


def get_text(driver, element):
    try:
        text = driver.find_element_by_xpath(element).text
        clickJumpresult = text
    except Exception as e:

        print("[Error:element]", e)
        clickJumpresult = ""

    return clickJumpresult


def alertClick(driver, clickButton="确定", timeOut=10):
    try:
        # 等待alert弹出框可见
        WebDriverWait(driver, timeOut).until(EC.alert_is_present())
        # 从html页面切换到alert弹框
        alert = driver.switch_to.alert
        # 获取alert的文本内容
        slertText = alert.text
        if clickButton == "确定":
            # 接受--选择“确定”
            alert.accept()
        elif clickButton == "取消":
            alert.dismiss()
        alertClickResult = True
    except Exception as e:
        print("[Error:alertClick]", e)
        alertClickResult = False
    return alertClickResult


def selectorSelect(driver, element, value, selectorType="value"):
    '''
    select元素,下拉点击某个属性值
    :param driver: 浏览器driver对象
    :param element: 浏览器元素
    :param value: value值
    :param selectorType: 选择的类型,按值选择;按索引选择;按文本选择
    :return: 现在成功返回true,失败返回false
    '''
    try:
        selector = Select(driver.find_element_by_xpath(element))
        if selectorType == "value":
            selector.select_by_value(value)
        elif selectorType == "index":
            selector.select_by_index(value)
        elif selectorType == "text":
            selector.select_by_visible_text(value)
        selectorSelectResult = True
    except Exception as e:
        print("[Error:selectorSelect]", e)
        selectorSelectResult = False
    return selectorSelectResult


def switchToFrame(driver, frameXpath):
    """
    跳转到指定iframe标签里
    :param driver: 浏览器的driver对象
    :param frameXpath: iframe的xpath
    :return:
    """
    try:
        driver.switch_to.frame(driver.find_element_by_xpath(frameXpath))
        switchToFrameResult = True
        print("切换iframe成功")
    except Exception as e:
        print("[Error:switchToFrame]", e)
        switchToFrameResult = False
        print("切换iframe失败")
    time.sleep(0.2)
    return switchToFrameResult


def switchToFatherIframe(driver):
    '''
    跳出iframe标签,切换上一级iframe
    :param driver: 浏览器的driver对象
    :return:
    '''
    try:
        driver.switch_to.parent_frame()
        switchToDefault = True
    except Exception as e:
        print("[Error:switchToDefault]", e)
        switchToDefault = False
    return switchToDefault


def writeListToXls(list2D, filepath, rows="", colums=0, sheet_No=0):
    '''
    仅支持xls格式
    :param list2D:  需要添加的二维列表
    :param filepath: 文件路径
    :return:文件写入结果
    '''
    result = False
    try:
        if os.path.exists(filepath):
            # ,行数为空则获取行数
            if rows == "":
                rows = int(xlrd.open_workbook(filepath).sheets()[sheet_No].nrows)
                rows = int(xlrd.open_workbook(filepath).sheets()[sheet_No].nrows)
            data = xlrd.open_workbook(filepath, formatting_info=True)
            ws = copy(data)
            worksheet = ws.get_sheet(sheet_No)
            for row in range(len(list2D)):
                for colum in range(len(list2D[row])):
                    worksheet.write(rows + row, colums + colum, list2D[row][colum])
            ws.save(filepath)
        else:
            # 模板不存在
            workbook = xlwt.Workbook(encoding='ascii')
            worksheet = workbook.add_sheet('sheet_1')
            for row in range(len(list2D)):
                for colum in range(len(list2D[row])):
                    worksheet.write(row, colum, list2D[row][colum])
            workbook.save(filepath)
        result = True
    except Exception as e:
        print(e)
        result = False
    finally:
        return result


def write_list_to_xls_style(list_2D, file_path, start_row=0, start_column=0, sheet_index=0, font_height=10 * 20,
                            font_color_index=0,
                            align_center=False, border=False, append_mode=False):
    """
    # 仅支持xls格式
    @param list_2D: 需要添加的二维列表
    @param file_path: 文件路径
    @param start_row:数据的写入位置 行下标
    @param start_column:数据的写入位置 列下标
    @param sheet_index:sheet编号
    @param font_height:字体大小,10为字号,20为衡量单位
    @param font_color_index:字体颜色 常用颜色 黑色0 白色1 红色2 蓝色4 黄色5 紫色6 青色7
    @param align_center:文字是否居中
    @param border:是否有边框
    @param append_mode:如果是追加模式 则 数据写入起始行数为已经存在的行数
    @return:
    """
    style = xlwt.XFStyle()  # 格式信息
    font = xlwt.Font()  # 字体基本设置
    # font.name = u'宋体'
    font.colour_index = font_color_index
    font.height = font_height
    style.font = font

    if border:
        border = xlwt.Borders()  # 给单元格加框线
        border.left = xlwt.Borders.THIN  # 左
        border.top = xlwt.Borders.THIN  # 上
        border.right = xlwt.Borders.THIN  # 右
        border.bottom = xlwt.Borders.THIN  # 下
        border.left_colour = 0x40  # 设置框线颜色,0x40是黑色,颜色真的巨多,都晕了
        border.right_colour = 0x40
        border.top_colour = 0x40
        border.bottom_colour = 0x40
        style.borders = border
        '''
        # 细实线:1,小粗实线:2,细虚线:3,中细虚线:4,大粗实线:5,双线:6,细点虚线:7
        # 大粗虚线:8,细点划线:9,粗点划线:10,细双点划线:11,粗双点划线:12,斜点划线:13
        borders.left = 1
        borders.right = 2
        borders.top = 3
        borders.bottom = 4
        borders.left_colour = i
        borders.right_colour = i
        borders.top_colour = i
        borders.bottom_colour = i
        '''
        # style.num_format_str = '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_)'

    if align_center:
        al = xlwt.Alignment()
        al.horz = 0x02  # 设置水平居中
        al.vert = 0x01  # 设置垂直居中
        style.alignment = al

    if os.path.exists(file_path):  # 如果文件存在 则在原来文件的基础上写入
        if append_mode:  # 如果是追加模式 则 数据写入起始行数为已经存在的行数
            start_row = int(xlrd.open_workbook(file_path).sheets()[sheet_index].nrows)
        origin_worksheet = xlrd.open_workbook(file_path, formatting_info=True)
        workbook = copy(origin_worksheet)
        worksheet = workbook.get_sheet(sheet_index)
    else:  # 如果文件不存在 直接创建文件写入
        workbook = xlwt.Workbook(encoding='ascii')
        worksheet = workbook.add_sheet('sheet_1')

    for row in range(len(list_2D)):
        for column in range(len(list_2D[row])):
            worksheet.write(start_row + row, start_column + column, list_2D[row][column], style)
    workbook.save(file_path)


def SaveasFile(FilePath):
    path = os.path.split(FilePath)[0]
    createPath(path)
    saveasTitle = "另存为"
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:2]"
    try:
        autoit.win_wait(saveasTitle, 60)
    except Exception as e:
        print("另存为窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def IESaveas(filePath):
    imagePath = r'D:\亿利\PythonCode\sanjiao.bmp'
    imagePath2 = r'D:\亿利\PythonCode\lingcunwei.bmp'
    ImageSearchs = ImageSearch.ImageSearchs()
    ImageSearchs.search(imagePath)
    ImageSearchs.click(imagePath)
    time.sleep(0.5)
    autoit.send("A")
    # ImageSearchs.search(imagePath2)
    # ImageSearchs.click(imagePath2)
    SaveasFile(filePath)


def clear_send(driver, element, value):
    '''
    输入网页页面元素
    :param driver: 浏览器对象
    :param element: 要输入的元素
    :param value: 输入的值
    :return:
    '''
    try:
        # 设置焦点
        ele = driver.find_element_by_xpath(element)
        # 利用js将为元素设置焦点
        driver.execute_script("arguments[0].focus();", ele)
        driver.find_element_by_xpath(element).click()
        driver.find_element_by_xpath(element).send_keys(Keys.CONTROL, "a")
        driver.find_element_by_xpath(element).send_keys(Keys.DELETE)
        # driver.find_element_by_xpath(element).send_keys(str(value))
        driver.find_element_by_xpath(element).send_keys(str(value))
        sendKeysResult = True
    except Exception as e:
        print("[Error:sendKeys]", e)
        sendKeysResult = False
    return sendKeysResult


def FileExists(filePath):
    '''
    判断文件是否存在
    :param filePath:
    :return:
    '''
    if os.path.exists(filePath):
        return True
    else:
        return False


def createPath(path):
    isExists = os.path.exists(path)
    if not isExists:
        os.makedirs(path)
        print("创建成功:{}".format(path))
    else:
        print("已经存在:{}".format(path))


def waitFileDownload(filePath, waitTime):
    '''
    等待文件下载成功
    :param filePath:文件路径
    :param waitTime:等待时间
    :return:
    '''
    downloadResult = False
    i = 0
    while i < waitTime:
        if os.path.exists(filePath):
            print("文件下载成功")
            downloadResult = True
            break
        else:
            time.sleep(1)
            i += 1
    return downloadResult


def mycopyfile(srcfile, dstfile):
    '''
    文件复制到指定路径
    :param srcfile:
    :param dstfile:
    :return:
    '''
    if not os.path.isfile(srcfile):
        print("%s not exist!" % (srcfile))
    else:
        fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        shutil.copyfile(srcfile, dstfile)  # 复制文件
        print("copy %s -> %s" % (srcfile, dstfile))


def csvToList(excelPath):
    '''
    打开csv文件,转换成list
    :param excelPath:
    :return:
    '''
    try:
        if os.path.exists(excelPath):
            # 打开csv
            # excelPath = "D:/autoit/项目/华鑫证券/文档/增值税取数文档及模板/附表二.csv"
            print("打开路径下的文件:", excelPath)
            try:
                df = pd.read_csv(open(excelPath), index_col=False, header=None, error_bad_lines=False, dtype=str)
            except Exception as e:
                df = pd.read_csv(open(excelPath, encoding='UTF-8'), index_col=False, header=None, error_bad_lines=False,
                                 dtype=str)
            # train_data = numpy.array(df)  # np.ndarray()
            train_x_list = df.values.tolist()  # list
        else:
            train_x_list = [[]]
    except Exception as e:
        print(e)
        train_x_list = [[]]
    return train_x_list


def df_to_dict_list(df):
    """

    @param df: dataframe对象
    @return: [
            {column1:value1,column2:value2},
            {column1:value3,column2:value4},
            ]
    """
    values_list = df.values.tolist()
    res_list = []
    for i in values_list:
        res_list.append(dict(zip(df.keys(), i)))
    return res_list


def df_to_2d_list(df):
    keys_list = list(df.keys())
    values_list = list(df.values.tolist())
    data_2d_list = [keys_list] + values_list
    return data_2d_list


def list_to_dict_list(list):
    """

    @param list: 传入列表格式的数据
    @return:
    """
    values = list[1]
    res_list = []
    res_list.append(dict(zip(list[0], values)))
    return res_list


def listToCSV(csvPath, csvList):
    '''
    讲列表写入到csv
    :param csvPath: 写入的路径
    :param csvList: 写入的列表
    :return:
    '''
    path = os.path.split(csvPath)[0]
    createPath(path)
    with open(csvPath, 'w'):
        pass
    df_data = pd.DataFrame(csvList)
    # df_data.to_csv(csvPath, header=False, index=False, encoding='gb2312')
    df_data.to_csv(csvPath, header=False, index=False)


def resultWriteCSV(resultPath, writeName, writeResult):
    '''
    将结果写入到csv文件中
    :param resultPath: 写入文件的路径
    :param writeName: 写入行的名字
    :param writeResult: 写入的数据值
    :return:
    '''
    # if not os.path.exists(resultPath):
    #     mycopyfile(resultTemplatePath, resultPath)
    readLists = csvToList(resultPath)
    for i in range(0, len(readLists)):
        if readLists[i][0] == writeName:
            readLists[i][1] = writeResult
    listToCSV(resultPath, readLists)


def image(driver, element, path):
    """
    元素截图
    :param element: 要截图的元素
    :param path: 图片保存路径
    :return:
    """
    try:
        path1 = os.path.split(path)[0]
        createPath(path1)
        # driver.save_screenshot(path)
        # left = element.location['x']
        # top = element.location['y']
        # right = element.location['x'] + element.size['width']
        # bottom = element.location['y'] + element.size['height']
        #
        # # 打开刚才的截图
        # im = Image.open(path)
        # # 截取对应位置
        # im = im.crop((left, top, right, bottom))
        # # 保存覆盖原有截图
        # im.save(path)
        element.screenshot(path)
        result = True
    except Exception as e:
        print(e)
        result = False
    return result


def SelectImage(FilePath):
    saveasTitle = "打开"
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:1]"
    try:
        autoit.win_wait(saveasTitle, 60)
    except Exception as e:
        print("打开为窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def search_image(imagepath):
    image_search = ImageSearch.ImageSearchs()
    res = image_search.search(imagepath)
    return res


def SaveFile(FilePath):
    saveasTitle = "选择文件/文件夹"
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:1]"
    try:
        autoit.win_wait(saveasTitle, 60)
    except Exception as e:
        print("选择文件/文件夹窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def combinations(iterable, r):
    # combinations('ABCD', 2) --> AB AC AD BC BD CD
    # combinations(range(4), 3) --> 012 013 023 123
    pool = iterable
    n = len(pool)
    if r > n:
        return
    indices = list(range(r))
    yield tuple(pool[i] for i in indices)
    while True:
        for i in reversed(range(r)):
            if indices[i] != i + n - r:
                break
        else:
            return
        indices[i] += 1
        for j in range(i + 1, r):
            indices[j] = indices[j - 1] + 1
        yield tuple(pool[i] for i in indices)


def listToExcel(list, path):
    # list = [[[["中电建商业保理", "电建一句"], ["中电建商业保理", "电建er句"]], ["中电建商业保理", 5000]], [[["中电建商业保理", "电建san句"], ["中电建商业保理", "电建si句"]], ["中电建商业保理", 6000]]]
    lista = []
    for i in list:
        for j in i[0]:
            listb = [i[1], j]
            lista.append(listb)
    df = pd.DataFrame(lista, columns=['来帐数据', '匹配成功的数据'])
    # 保存到本地excel
    df.to_excel(path, index=False)


def dict_list_to_list(dict_list):
    """

    @param dict_list: 列表套字典的数据类型
    @return: 列索引列表,数据列表
    """
    lis = []
    # if not dict_list:
    #     return
    columns = list(dict_list[0].keys())
    lis.append(columns)
    for i in dict_list:
        lis.append(list(i.values()))
    return lis


def getDate(day):
    end_date = (datetime.datetime.now() + datetime.timedelta(days=day - 1)).strftime("%Y-%m-%d")
    date_list = []
    begin_date = datetime.datetime.strptime(time.strftime('%Y-%m-%d', time.localtime(time.time())), "%Y-%m-%d")
    end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
    while begin_date <= end_date:
        date_str = begin_date.strftime("%Y-%m-%d")
        date_list.append(date_str)
        begin_date += datetime.timedelta(days=1)
    return date_list


def openEXE(path, title):
    """
    先关闭软件再打开软件
    :param path:软件启动路径
    :param title:软件窗口标题
    :return:成功:True, 失败:False
    """
    try:
        autoit.win_close(title)
    except:
        print("窗口不存在,不需要关闭")
    try:
        os.startfile(path)
        result = autoit.win_wait(title, timeout=2)
    except:
        result = 0
    if result == 1:
        return True
    else:
        return False


# 获取剪切板内容
def get_clipboard_text():
    import win32gui
    import win32api
    import win32con
    import win32clipboard as w
    w.OpenClipboard()
    d = w.GetClipboardData(win32con.CF_TEXT)
    w.CloseClipboard()
    return d.decode('GBK')


def getMonthStartEnd():
    """
    获取当月起止日期:格式:2020.09.01 2020.09.30
    :return:2020.09.01 2020.09.30
    """
    now = datetime.datetime.now().date()
    this_month_start = str(datetime.datetime(now.year, now.month, 1).date()).replace("-", ".")
    this_month_end = str(
        datetime.datetime(now.year, now.month, calendar.monthrange(now.year, now.month)[1]).date()).replace("-", ".")
    return this_month_start, this_month_end


def getMonthStartNowEnd():
    """
    获取当月起止日期:格式:2020.09.01 2020.09.15 2020.09.30
    :return:2020.09.01 2020.09.15 2020.09.30
    """
    now = datetime.datetime.now().date()
    this_month_start = str(datetime.datetime(now.year, now.month, 1).date()).replace("-", ".")
    this_month_end = str(
        datetime.datetime(now.year, now.month, calendar.monthrange(now.year, now.month)[1]).date()).replace("-", ".")
    this_month_now = str(now).replace("-", ".")
    return this_month_start, this_month_now, this_month_end


def get_special_date(src_date_obj):
    now_month_days = calendar.monthrange(src_date_obj.year, src_date_obj.month)[1]  # 当月天数

    now_month_first_day = datetime.date(src_date_obj.year, src_date_obj.month, 1)  # 当前月第一天
    now_month_last_day = now_month_first_day + datetime.timedelta(days=now_month_days - 1)  # 当月最后一天

    pre_month_first_day = now_month_first_day - datetime.timedelta(days=1)  # 上个月第一天
    pre_month_last_day = datetime.date(pre_month_first_day.year, pre_month_first_day.month, 1)  # 上个月最后一天

    next_month_first_day = now_month_first_day + datetime.timedelta(days=now_month_days)  # 下个月的第一天
    # 求下个月的最后一天
    next_month_days = calendar.monthrange(next_month_first_day.year, next_month_first_day.month)[1]  # 下月天数
    next_month_last_day = next_month_first_day + datetime.timedelta(days=next_month_days - 1)
    dst_date_dict = {
        'pre_month_first_day': pre_month_first_day,
        'pre_month_last_day': pre_month_last_day,
        'now_month_first_day': now_month_first_day,
        'now_month_last_day': now_month_last_day,
        'next_month_first_day': next_month_first_day,
        'next_month_last_day': next_month_last_day,
    }
    return dst_date_dict


def get_date_span(date_str, date_span):
    """
    获取时间跨度起始值
    @param date_str: 2020-02-02 str
    @param date_span: 10
    @return: 2020.02.12 str
    """
    date_obj = datetime.datetime.strptime(date_str, "%Y-%m-%d")
    res_date_obj = date_obj + relativedelta(days=date_span)
    res_date_str = str(res_date_obj.date())
    return res_date_str


def inputText(title, input_box, text):
    """
    资金系统对输入框输入内容
    :param title:窗口标题
    :param input_box:控件
    :param text:内容
    :return:
    """
    try:
        autoit.control_click(title, input_box)
        autoit.send('^a')
        time.sleep(1)
        autoit.send('{DEL}')
        time.sleep(1)
        autoit.send(text)
        time.sleep(1)
        autoit.send('{ENTER}')
        Result = True
    except Exception as e:
        print(e)
        Result = False
    return Result


def addResultExcel(logging, list, path):
    """
    新建或者追加写入EXCEL文件
    :param logging: 日志
    :param list: 要追加的列表
    :param path: EXCEL文件路径
    :return:成功:True  失败:False
    """
    try:
        logging.info("开始追加写入Excel文件")
        pathParent = os.path.split(path)[0]
        createPath(pathParent)
        # 判断文件是否存在,如果存在就读取文件
        isExists = os.path.exists(path)
        data_list = []
        if isExists:
            data_list = pd.read_excel(path, header=None).values.tolist()
            if data_list:
                list = list[1:]
        newList = data_list + list
        df = pd.DataFrame(newList[1:], columns=newList[0])
        # 将df中的每一列都转换为object类型
        # 保存到本地excel
        df.to_excel(path, index=False)
        Result = True
    except Exception as e:
        logging.exception(e)
        Result = False
    logging.info("追加写入Excel文件结束")
    return Result


def click_move_mouse(img_path, OffsetX=0, OffsetY=0, timeOut=10, sign=False):
    """
    :param sign: 移动后是否点击
    :param timeOut:图片查找超时时间
    :param img_path:图片路径
    :param OffsetX:x偏移
    :param OffsetY:y偏移
    :return:成功:True 失败:False
    """
    Result = False
    print("开始识别图片,并点击")
    im = ImageSearchs()
    if im.click(img_path, OffsetX=OffsetX, OffsetY=OffsetY, timeOut=timeOut):
        Result = True
        print("点击成功")
    if not Result:
        gui_wait_image_click(img_path, confidence=0.99)
    time.sleep(1)
    autoit.mouse_move(160, 10)
    if sign:
        autoit.mouse_click()
    return Result


def click_control_send_text(title, path, text, timeOut=10):
    """
    autoit点击控件并输入文字
    @param title: 窗口标题
    @param path: 控件
    @param text: 输入内容
    @param timeOut: 超时时间
    @return:
    """
    Result = False
    time.sleep(1)
    if autoit.control_click(title, path):
        Result = True
    time.sleep(1)
    autoit.send('^a')
    time.sleep(1)
    autoit.send('{DEL}')
    time.sleep(1)
    autoit.send(text)
    return Result


def GetData(Title, Control_List):
    """
    获取控件的值
    :param Title: 控件窗口标题
    :param Control_List: 控件列表
    :return: 成功:数据列表 失败:空列表
    """
    ResultList = []
    try:
        for control in Control_List:
            text = autoit.control_get_text(Title, control)
            ResultList.append(text)
        ResultList.append(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    except Exception as e:
        print(e)
        ResultList = []
    return ResultList


def task_kill(name, logger=None):
    """
    # 检测是否存在进程 如存在则杀掉
    杀掉进程
    :return:
    """
    result = False

    try:
        os.system("taskkill /F /IM {}".format(name))
        result = True
    except:
        pass
    return result


def WriteExcel(logging, file_path):
    """
    全选复制,并将粘贴板内容写入excel文件
    :param file_path: 写入文件路径
    :param logging:
    :return:
    """
    try:
        autoit.send('^a')
        time.sleep(1)
        autoit.send('^c')
        # 获取剪切板内容
        res = get_clipboard_text()
        # 将数据构造为特定格式
        excel_data_list = []
        for line in res.split('\n'):
            line = line.strip()
            word_list = line.split('\t')
            excel_data_list.append(word_list)
        header_length = len(excel_data_list[0])
        excel_data_list[0].append('操作时间')
        for line in excel_data_list[1:]:
            while len(line) < header_length:
                line.append("")
            line.append(str(datetime.datetime.now()))
        # 将数据写入excel
        logging.info("复制的数据为:\n%s" % excel_data_list)
        res = addResultExcel(logging, excel_data_list, file_path)
        if res:
            logging.info('写入成功')
            WriteResult = True
        else:
            logging.info('写入失败')
            WriteResult = True
    except Exception as e:
        logging.exception(e)
        WriteResult = False
    return WriteResult


def AllCopyList(logging):
    """
    全选复制,并将粘贴板内容写入excel文件
    :param logging:
    :return:
    """
    try:
        autoit.send('^a')
        time.sleep(1)
        autoit.send('^c')
        # 获取剪切板内容
        res = get_clipboard_text()
        # 将数据构造为特定格式
        excel_data_list = []
        for line in res.split('\n'):
            line = line.strip()
            word_list = line.split('\t')
            excel_data_list.append(word_list)
        header_length = len(excel_data_list[0])
        excel_data_list[0].append('操作时间')
        for line in excel_data_list[1:]:
            while len(line) < header_length:
                line.append("")
            line.append(str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
        # 将数据写入excel
        logging.info("复制的数据为:\n%s" % excel_data_list)
    except Exception as e:
        logging.exception(e)
        excel_data_list = []
    return excel_data_list


def CopyLine(logging):
    """
    全选复制,并将粘贴板内容写入excel文件
    :param logging:
    :return:
    """
    try:
        autoit.send('^a')
        time.sleep(0.1)
        autoit.send('^c')
        # 获取剪切板内容
        res = get_clipboard_text()
        # 将数据构造为特定格式
        excel_data_list = []
        for line in res.split('\n'):
            line = line.strip()
            word_list = line.split('\t')
            excel_data_list.append(word_list)
        header_length = len(excel_data_list[0])
        for line in excel_data_list[1:]:
            while len(line) < header_length:
                line.append("")
        # 将数据写入excel
        logging.info("复制的数据为:\n%s" % excel_data_list)
    except Exception as e:
        logging.exception(e)
        excel_data_list = []
    for line_ in excel_data_list[1:]:
        line_.pop(0)
    return list_to_dict_list(excel_data_list[0:-1])


def SaveasFile(FilePath):
    path = os.path.split(FilePath)[0]
    createPath(path)
    saveasTitle = "另存为"
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:2]"
    try:
        autoit.win_wait(saveasTitle, 60)
    except Exception as e:
        print("另存为窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def ExportFile(FilePath, title=None, timeout=5):
    path = os.path.split(FilePath)[0]
    createPath(path)
    saveasTitle = "导出为Excel"
    if title:
        saveasTitle = title
    savePathControl = "[CLASS:Edit; INSTANCE:1]"
    saveControl = "[CLASS:Button; INSTANCE:2]"
    try:
        autoit.win_wait(saveasTitle, timeout)
    except Exception as e:
        print("另存为窗口未弹出")
    time.sleep(1)
    if autoit.win_exists(saveasTitle) == 1:
        autoit.control_set_text(saveasTitle, savePathControl, FilePath)
        time.sleep(1)
        autoit.control_click(saveasTitle, saveControl)


def excel_border(excel_file):
    # 打开存好的excel
    app = xw.App()  # 设置应用
    wb = xw.Book(excel_file)  # 打开文件
    ws = wb.sheets['Sheet1']  # 选择表格

    last_column = ws.range(1, 1).end('right').get_address(0, 0)[0]  # 获取最后列
    last_row = ws.range(1, 1).end('down').row  # 获取最后行

    a_range = f'A1:{last_column}{last_row}'  # 生成表格的数据范围

    # 设置边框
    ws.range(a_range).api.Borders(8).LineStyle = 1  # 上边框
    ws.range(a_range).api.Borders(9).LineStyle = 1  # 下边框
    ws.range(a_range).api.Borders(7).LineStyle = 1  # 左边框
    ws.range(a_range).api.Borders(10).LineStyle = 1  # 右边框
    ws.range(a_range).api.Borders(12).LineStyle = 1  # 内横边框
    ws.range(a_range).api.Borders(11).LineStyle = 1  # 内纵边框

    # 保存并关闭excel
    wb.save("E:/Data/小蜜蜂超市销售报表2.xlsx")
    wb.close()
    app.quit()


def get_gray_text(title, re_left, re_right):
    """
    获取窗口灰色输入框里的内容
    @param title:窗口标题
    @param re_left: 待匹配字符串左边的字符
    @param re_right: 待匹配字符串右边的字符
    @return:left和right中间的分组字符串
    """
    try:
        # 获取页面中所有的text
        win_text = autoit.win_get_text(title)
        # 去掉字符串中的/n/t/r
        win_text = win_text.replace('\n', '').replace('\r', '').replace(' ', '')
        # 提取left和right中间的分组字符串
        re_search_str = re.search('{}(.*){}'.format(re_left, re_right), win_text).group(1)
    except Exception as e:
        print("[Error:get_gray_text]", e)
        re_search_str = 'error'
    return re_search_str


def get_dict_list_field_list(lis, field):
    '''

    @param lis: 传入的字典列表
    @param field: 需要查找的field
    @return: 返回的field对应的value列表
    '''
    res = []
    for i in lis:
        res.append(i[field])
    return res


def dict_list_get_value_by_condition(dict_list, condition_key, condition_value, get_key_name):
    """
    根据条件从字典列表中取值
    @param dict_list: [{},{},{}]
    @param condition_key:
    @param condition_value:
    @param get_key_name:
    @return:
    """
    for dict_line in dict_list:
        if dict_line[condition_key] == condition_value:
            return dict_line[get_key_name]
    return None


def dict_list_get_line_by_condition(dict_list, condition_key, condition_value):
    """
    根据条件从字典列表中取值
    @param dict_list: [{},{},{}]
    @param condition_key:
    @param condition_value:
    @param get_key_name:
    @return:
    """
    for dict_line in dict_list:
        if dict_line[condition_key] == condition_value:
            return dict_line
    return None


def dict_list_get_list_by_condition(dict_list, condition_key, condition_value):
    """
    根据条件从字典列表中取值
    @param dict_list: [{},{},{}]
    @param condition_key:
    @param condition_value:
    @param get_key_name:
    @return:
    """
    res_list = list()
    for dict_line in dict_list:
        if dict_line[condition_key] == condition_value:
            res_list.append(dict_line)
    return res_list


def switch_keyboard_type(language='EN'):
    import win32gui
    import win32api
    import win32con
    from win32con import WM_INPUTLANGCHANGEREQUEST
    # set keyboard layout
    # 设置键盘布局

    # 语言代码
    # https://msdn.microsoft.com/en-us/library/cc233982.aspx
    LID = {0x0804: "Chinese (Simplified) (People's Republic of China)",
           0x0409: 'English (United States)'}

    # 获取前景窗口句柄
    hwnd = win32gui.GetForegroundWindow()

    # 获取前景窗口标题
    title = win32gui.GetWindowText(hwnd)
    print('当前窗口:' + title)

    # 获取键盘布局列表
    im_list = win32api.GetKeyboardLayoutList()
    im_list = list(map(hex, im_list))
    print(im_list)
    result = ''
    if language == 'EN':
        # 设置键盘布局为英文
        result = win32api.SendMessage(
            hwnd,
            WM_INPUTLANGCHANGEREQUEST,
            0,
            0x0409)
        if result == 0:
            print('设置英文键盘成功!')
    elif language == 'CN':
        # 设置键盘布局为中文
        result = win32api.SendMessage(
            hwnd,
            WM_INPUTLANGCHANGEREQUEST,
            0,
            0x0804)
        if result == 0:
            print('设置中文键盘成功!')


class VideoRecord:
    def __init__(self, video_name, video_dir, logger=None):
        self.flag = False
        self.video_name = video_name
        self.date_str = datetime.datetime.now().strftime('%Y-%m-%d')
        self.datetime_str = datetime.datetime.now().strftime('%Y-%m-%d %H-%M-%S')
        self.video_dir = video_dir
        self.video_path = \
            r'{video_dir}\{date_str}\{video_name}\{datetime_str}.avi'.format(
                video_dir=self.video_dir,
                date_str=self.date_str,
                video_name=self.video_name,
                datetime_str=self.datetime_str,
            )
        self.logger = logger
        self.start_time = None
        self.final_time = None

    def video_record(self):  # 录入视频
        screen = ImageGrab.grab()  # 获取当前屏幕
        width, high = screen.size  # 获取当前屏幕的大小
        fourcc = cv2.VideoWriter_fourcc('X', 'V', 'I', 'D')  # MPEG-4编码,文件后缀可为.avi .asf .mov等
        createPath(self.video_dir)
        createPath(os.path.dirname(self.video_path))
        video = cv2.VideoWriter(self.video_path, fourcc, 15, (width, high))  # (文件名,编码器,帧率,视频宽高)
        self.logger.info('录制开始')
        self.start_time = time.time()
        while True:
            if self.flag:
                self.logger.info('录制结束')
                self.final_time = time.time()
                video.release()  # 释放
                time.sleep(1)
                self.video_info()
                break
            im = ImageGrab.grab()  # 图片为RGB模式
            imm = cv2.cvtColor(np.array(im), cv2.COLOR_RGB2BGR)  # 转为opencv的BGR模式
            video.write(imm)  # 写入
            # time.sleep(5) # 等待5秒再次循环

    def on_press(self, key):  # 监听按键
        if key == keyboard.Key.home:
            self.logger.info('捕获到用户输入录屏终止按键 录屏终止')
            self.flag = True  # 改变
            return False  # 返回False,键盘监听结束!

    def video_info(self):  # 视频信息
        self.logger.info('打印录屏信息')
        video = cv2.VideoCapture(self.video_path)  # 记得文件名加格式不要错!
        fps = video.get(cv2.CAP_PROP_FPS)
        frame_sum = video.get(cv2.CAP_PROP_FRAME_COUNT)
        size = (int(video.get(cv2.CAP_PROP_FRAME_WIDTH)), int(video.get(cv2.CAP_PROP_FRAME_HEIGHT)))
        self.logger.info('帧率:{:.1f}'.format(fps))
        self.logger.info('帧数:{:.1f}'.format(frame_sum))
        self.logger.info('分辨率:{}'.format(size))
        self.logger.info('视频时间:{:.3f}秒'.format(int(frame_sum) / fps if frame_sum else 0))
        self.logger.info('录制时间:{:.3f}秒'.format((self.final_time - self.start_time)))
        if frame_sum == 0:
            self.logger.info('推荐帧率:0.00')
        else:
            self.logger.info(
                '推荐帧率:{:.2f}'.format((fps * ((int(frame_sum) / fps) / (self.final_time - self.start_time)))))

    def keyboard_join(self):
        with keyboard.Listener(on_press=self.on_press) as listener:
            if self.flag:
                listener.join()
            else:
                listener.join()


def video_record_decorator(video_name=None):  # video_name=None, logger=None
    from PythonCode import Setting
    from functools import wraps
    def wrapper(func):
        def inner(*args, **kwargs):
            video_record = VideoRecord(video_name, Setting.VIDEO_RECORD_DIR, args[0].logger)
            record_th = threading.Thread(target=video_record.video_record)
            record_th.setDaemon(True)
            record_th.start()
            ret = func(*args, **kwargs)
            time.sleep(5)
            video_record.flag = True
            record_th.join()
            return ret

        return inner

    return wrapper


def clear_and_send(content):
    time.sleep(1)
    autoit.send('^a')
    time.sleep(1)
    autoit.send('{DELETE}')
    autoit.send('{BACKSPACE 20}')
    time.sleep(1)
    autoit.send(content)
    time.sleep(1)


def gui_wait_image(img_path, region=None, timeout=5, confidence=None, raise_error=True):
    """
    :param timeOut:图片查找超时时间
    :param img_path:图片路径
    :param region:格式 region=(0, 0, 810, 630) 限制搜索图片的区域
    :param confidence:模糊匹配相似度 不传值为默认相似度 数据类型为数字,范围为0到1
    :return:成功:True 失败:False
    """
    status = False
    try:
        print("开始识别图片 {}".format(img_path))
        search_count = 0
        while search_count < timeout:
            search_count += 1
            print('当前第{}次查找图片'.format(search_count))
            if confidence:  # 如果confidence传值了 则相似度使用confidence的值
                img_pos_center = pyautogui.locateCenterOnScreen(img_path, region=region, confidence=confidence)
            else:  # 如果confidence未传值 则使用默认相似度
                img_pos_center = pyautogui.locateCenterOnScreen(img_path, region=region, )
            if img_pos_center:  # 搜索到图片位置
                status = True
                print('识别图片成功 {}'.format(img_path))
                break
            time.sleep(1)
        if not status:
            print('执行{}秒内没有找到图片'.format(timeout, img_path))
            if raise_error:
                raise Exception('执行{}秒内没有找到图片:{}'.format(timeout, img_path))
    except Exception as e:
        if raise_error:
            raise e
        status = False
    return status


def gui_wait_image_click(img_path, OffsetX=0, OffsetY=0, region=None, timeout=5, sign=False, homing=True,
                         confidence=None,
                         double_click=False, right_click=False, raise_error=True):
    """
    :param img_path:图片路径
    :param OffsetX:x偏移
    :param OffsetY:y偏移
    :param sign: 点击图片后移动到初始坐标后是否点击
    :param timeOut:图片查找超时时间
    :param region:格式 region=(0, 0, 810, 630) 限制搜索图片的区域
    :param confidence:模糊匹配相似度 不传值为默认相似度 数据类型为数字,范围为0到1
    :return:成功:True 失败:False
    """
    pyautogui.FAILSAFE = False
    status = False
    try:
        print("开始识别图片,并点击 {}".format(img_path))
        search_count = 0
        while search_count < timeout:
            search_count += 1
            print('当前第{}次查找图片'.format(search_count))
            if confidence:  # 如果confidence传值了 则相似度使用confidence的值
                img_pos_center = pyautogui.locateCenterOnScreen(img_path, region=region, confidence=confidence)
            else:  # 如果confidence未传值 则使用默认相似度
                img_pos_center = pyautogui.locateCenterOnScreen(img_path, region=region, )
            if img_pos_center:  # 搜索到图片位置
                x = img_pos_center[0] + OffsetX
                y = img_pos_center[1] + OffsetY
                pyautogui.moveTo(x, y, duration=0.2)
                if double_click:
                    pyautogui.doubleClick()
                else:
                    if right_click:
                        pyautogui.rightClick()
                    else:
                        pyautogui.click()
                status = True
                print('点击图片成功 {}'.format(img_path))
                break
            time.sleep(1)
        if not status:
            print('执行{}秒内没有找到图片'.format(timeout))
            if raise_error:
                raise Exception('执行{}秒内没有找到图片:{}'.format(timeout, img_path))

        time.sleep(0.2)
        if homing:
            pyautogui.moveTo(160, 10, duration=0.2)
            if sign:
                pyautogui.click()
    except Exception as e:
        if raise_error:
            raise e
        status = False
    return status


def check_process_list(name, logger=None, ):
    """
    检测某进程数量
    :param logger:
    :return: 0 1 2
    """
    process_list = []
    for proc in psutil.process_iter():
        try:
            pinfo = proc.as_dict(attrs=['pid', 'name'])
            process_list.append(pinfo)
        except psutil.NoSuchProcess:
            pass

    count = [item['name'] for item in process_list].count(name)
    return count


def by_paste_enter_text(text, clean=True, enter=True):
    pyperclip.copy(text)  # 复制内容到剪切板
    if clean:
        # time.sleep(0.5)
        # pyautogui.hotkey('ctrl', 'a')  # 全选输入框内的内容
        # time.sleep(0.5)
        # autoit.send('{BACKSPACE}')
        # time.sleep(0.5)
        # pyautogui.hotkey('ctrl', 'a')  # 全选输入框内的内容
        # time.sleep(0.5)
        # autoit.send('{DELETE}')
        time.sleep(0.5)
        autoit.send('{BACKSPACE 50}')
        time.sleep(0.5)
        autoit.send('{DELETE 50}')
        time.sleep(0.5)
    pyautogui.hotkey('ctrl', 'v')  # 再粘贴
    if enter:
        autoit.send('{ENTER}')


def check_video_folder(logger, file_path, limit=500, days=31):
    '''

    @param file_path: 录屏文件夹路径
    @param limit: 录屏文件大小限制 单位:GB
    @param days: 录屏文件保留天数
    @return:
    '''
    from os.path import join, getsize
    logger.info('开始检查录屏文件夹占用空间')
    while 1:
        size = 0
        for root, dirs, files in os.walk(file_path):
            size += sum([getsize(join(root, name)) for name in files])
        gsize = size / 1024 / 1024 / 1024
        logger.info('当前文件夹大小:{:.2f}GB,限制大小:{}GB'.format(gsize, limit))
        if gsize > limit:
            for file_dir in os.listdir(file_path):
                logger.info('文件夹占用空间超限,删除:', file_dir)
                shutil.rmtree(os.path.join(file_path, file_dir))
                break
        else:
            break
    logger.info('文件夹占用检查完毕')


def return_executing_status(file_path, process_name, date_str=None):
    """
    检查流程运行结果
    @param file_path:
    @param process_name:
    @param date_str:
    @return:
    """
    if not date_str:
        date_obj = datetime.datetime.now().date()
        date_str = str(date_obj)
    df = pd.read_excel(file_path, keep_default_na=False)
    status = df.loc[[date_str], [process_name]]
    status = status.values.tolist()[0][0]
    if not status or not float(status):
        return False
    else:
        return True


def str_replace_by_map_file(logger, input_str, map_file):
    """

    @param logger:
    @param input_str:
    @param map_file:
    @return: 替换后的字串 如出现异常则return原字符串
    """
    try:
        output_str = input_str
        folder, file = os.path.split(map_file)
        filename, file_type = os.path.splitext(file)
        char_map_list = []
        if file_type == '.txt':
            logger.info('文件是txt')
        elif file_type == '.csv':
            logger.info('文件是csv')
            char_map_list = df_to_dict_list(pd.read_csv(open(map_file), keep_default_na=False))
        for char_map_line in char_map_list:
            if char_map_line['原字符'] in output_str:
                output_str = output_str.replace(char_map_line['原字符'], char_map_line['新字符'])
        logger.info('str_replace_by_map_file:将{}替换为{}'.format(input_str, output_str))
        return output_str
    except Exception as e:
        logger.info('异常错误{}'.format(e))
        return input_str


def num_to_excel_col(num: int) -> str:
    num = int(num) + 1
    if not 1 <= num <= 18278:
        raise ValueError("Invalid column index {0}".format(num))

    excel_col_name = ""
    while num > 0:
        num, remainder = divmod(num, 26)

        if remainder == 0:
            remainder = 26
            num -= 1
        excel_col_name = chr(remainder + 64) + excel_col_name

    return excel_col_name
# -*- coding:utf-8 -*-
import re
# from Crypto.Cipher import AES
import configparser
import csv
import os
import time
import shutil
import logging
import zipfile
import datetime

import pandas as pd
import xlrd
import xlwt
import inspect
import ctypes
import requests
import json
from xlutils.copy import copy
import pymysql

from PythonCode.Common.ownerException import NotFoundKeyException, PortHasBeenUsedException

# __all__ = [
# #     'PATHPAREBT', 'PATHROOT', 'log', 'renamefile', 'getFormatData'
# # ]
PATHPAREBT = os.path.split(os.path.realpath(__file__))[0]
PATHROOT = os.path.abspath(os.path.dirname(PATHPAREBT) + os.path.sep + "..")

def log(filename):
    '''
    log,文件名要传入绝对路径,
    :param author: 201916820
    :param filename:{str} log文件保存路径
    :return:{object} 返回log对象
    '''
    dstdir = os.path.split(filename)[0]  # 父文件路径
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)

    # 创建一个logger
    logger = logging.getLogger()
    logger.setLevel(logging.DEBUG)
    # 创建一个handler,用于写入日志文件
    fh = logging.FileHandler(filename)
    fh.setLevel(logging.INFO)
    # 再创建一个handler,用于输出到控制台
    ch = logging.StreamHandler()
    ch.setLevel(logging.INFO)
    # 定义handler的输出格式
    formatter = logging.Formatter('%(asctime)s [%(levelname)s] :  %(message)s')
    fh.setFormatter(formatter)
    ch.setFormatter(formatter)

    # 给logger添加handler
    logger.addHandler(fh)
    logger.addHandler(ch)
    # 记录一条日志
    logger.info('')
    # logger.warning()
    # logger.debug()
    # logger.error()
    return logger


def renamefile(srcdir, dstdir, fileName):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return 'NO FILE'  # 空文件夹,不进行复制操作
    for file_m in files:
        fileName = '%s.%s' % (fileName, file_m.split('.')[1])
        file_s = os.path.join(srcdir, file_m)
        file_d = os.path.join(dstdir, fileName)
        if os.path.isfile(file_s):
            os.rename(file_s, file_d)
            return file_d


def remove_file2parent_temp(account, filePath):
    '''
    移动文件到../../Temp文件夹下
    :param account:
    :param filePath:
    :return:
    '''
    path_temp = os.path.abspath(filePath + os.path.sep + "../../Back")
    if not os.path.exists(path_temp):
        os.makedirs(path_temp)  # 创建新文件夹路径
    files = os.listdir(filePath)
    for file_m in files:
        if account not in file_m:
            continue
        file_s = os.path.join(filePath, file_m)
        shutil.move(file_s, path_temp)  # 移动文件


def check_last_creat_file(account, filePath):
    if '/' in account:
        account = account.split('/')[1]
    files = os.listdir(filePath)
    file_dict = {}
    for file_m in files:
        if account not in file_m:
            continue
        file_s = os.path.join(filePath, file_m)
        if not os.path.isfile(file_s):
            continue
        file_time = os.stat(file_s).st_mtime
        file_dict[file_time] = file_s
    if not file_dict:
        return
    max_time = max(file_dict.keys())
    return file_dict[max_time]


def movefile(srcdir, dstdir):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return  # 空文件夹,不进行复制操作
    files_temp = []
    for file_m in files:
        file_v = os.path.join(srcdir, file_m)
        if os.path.isfile(file_v):
            files_temp.append(file_v)
    if not files_temp:
        return  # 无可复制文件(当前路径下都是子文件夹)
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    for f in files_temp:
        shutil.move(f, dstdir)  # 移动文件


def move_file_and_dir(srcdir, dstdir):
    '''
    移动文件夹下所有文件到另一个文件夹
    :param srcfile: {str} 初始文件夹
    :param dstfile: {str} 要移动到的文件夹
    :return:
    '''
    if os.path.isdir(srcdir):  # 获取srcdir文件夹下所有文件
        files = os.listdir(srcdir)
    if not files:
        return  # 空文件夹,不进行复制操作
    files_temp = []
    for file_m in files:
        file_v = os.path.join(srcdir, file_m)
        files_temp.append(file_v)
    if not files_temp:
        return  # 无可复制文件(当前路径下都是子文件夹)
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    for f in files_temp:
        shutil.move(f, dstdir)  # 移动文件



def mycopyfile(srcfile, dstfile):
    if not os.path.isfile(srcfile):
        print("%s not exist!" % (srcfile))
    else:
        fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        shutil.copyfile(srcfile, dstfile)  # 复制文件
        print("copy %s -> %s" % (srcfile, dstfile))


def FileMove(srcfile, dstfile):
    time = getFormatData(format=8)
    if os.path.exists(srcfile):
        try:
            spath, sname = os.path.split(srcfile)
            fpath, fname = os.path.split(dstfile)  # 分离文件名和路径
            dstfile = os.path.join(fpath,time+'_'+sname)
            if not os.path.exists(fpath):
                os.makedirs(fpath)  # 创建路径
            shutil.move(srcfile, dstfile)  # 复制文件
            print("文件移动成功")
        except:
            return "文件移动失败"
    else:
        return "文件不存在"

def rename(srcfile, dstdir, new_name):
    '''
    文件重命名
    :param srcfile:{str} 原始文件
    :param dstdir: {str} 新文件文件夹
    :param new_name: {str} 文件名
    :return: None
    '''
    # if not os.path.exists(dstdir):
    #     return
    if not os.path.exists(dstdir):
        os.makedirs(dstdir)  # 创建新文件夹路径
    if new_name:  # 重命名文件
        new_name = os.path.join(dstdir, new_name)
        os.rename(srcfile, new_name)

def stop_thread(thread):
    '''
    关闭多线程
    :param thread:{object} 线程对象
    :return:None
    '''
    _async_raise(thread.ident, SystemExit)


def _async_raise(tid, exctype):
    """raises the exception, performs cleanup if needed"""
    tid = ctypes.c_long(tid)
    if not inspect.isclass(exctype):
        exctype = type(exctype)
    res = ctypes.pythonapi.PyThreadState_SetAsyncExc(tid, ctypes.py_object(exctype))
    if res == 0:
        raise ValueError("invalid thread id")
    elif res != 1:
        ctypes.pythonapi.PyThreadState_SetAsyncExc(tid, None)
        raise SystemError("PyThreadState_SetAsyncExc failed")



def getfileName(argv, bankName, count):
    timeArray = time.localtime(time.time())
    filename = getFormatData(argv=argv, format=3) + time.strftime(r"-%H-%M-%S", timeArray)
    return "%s_%s_%s" % (bankName, count, filename)

class Properties(object):
    '''
    读取property文件,返回字典
    '''

    def __init__(self, fileName):
        self.fileName = fileName
        self.properties = {}

    def __getDict(self, strName, dictName, value):

        if (strName.find('.') > 0):
            k = strName.split('.')[0]
            dictName.setdefault(k, {})
            return self.__getDict(strName[len(k) + 1:], dictName[k], value)
        else:
            dictName[strName] = value
            return

    def getProperties(self):
        try:
            pro_file = open(self.fileName, 'Ur')
            for line in pro_file.readlines():
                line = line.strip().replace('\n', '')
                if line.find("#") != -1:
                    line = line[0:line.find('#')]
                if line.find('=') > 0:
                    strs = line.split('=')
                    strs[1] = line[len(strs[0]) + 1:]
                    self.__getDict(strs[0].strip(), self.properties, strs[1].strip())
        except Exception as e:
            raise e
        else:
            pro_file.close()
        return self.properties


# class AEScoder():
#     def __init__(self):
#         self.__encryptKey = "iEpSxImA0vpMUAabsjJWug=="
#         self.__key = base64.b64decode(self.__encryptKey)
#     # AES加密
#     def encrypt(self,data):
#         BS = 16
#         pad = lambda s: s + (BS - len(s) % BS) * chr(BS - len(s) % BS)
#         cipher = AES.new(self.__key, AES.MODE_ECB)
#         encrData = cipher.encrypt(pad(data))
#         #encrData = base64.b64encode(encrData)
#         return encrData
#     # AES解密
#     def decrypt(self,encrData):
#         unpad = lambda s: s[0:-s[-1]]
#         cipher = AES.new(self.__key, AES.MODE_ECB)
#         decrData = unpad(cipher.decrypt(encrData))
#         return decrData.decode('utf-8')



def Write2CsvF(pathTag, datas, codeflag="utf-8", delimiter="|"):
    print(pathTag)
    if not os.path.exists(os.path.dirname(pathTag)):
        os.makedirs(os.path.dirname(pathTag))

    try:
        with open(pathTag, "w", newline='', encoding=codeflag) as csvFile:

            writer = csv.writer(csvFile, delimiter=delimiter)

            if (str(type(datas)).find("str") + 1):
                writer.writerow(datas)

            elif (str(type(datas)).find("list") + 1):
                writer.writerows(datas)

            elif (str(type(datas)).find("dict") + 1):
                for keys, values in datas.items():
                    writer.writerow([keys, values])

            else:
                csvFile.close()
                return 1

            csvFile.close()
    except:
        print("检查文件名, 文件是否已打开.")

    return 0

def write2Json(msg, file_name):
    '''
    写入json文件
    :param msg:
    :param file_name:
    :return:
    '''
    json_msg = json.dumps(msg, ensure_ascii=False)
    with open(file_name, 'w') as file:
        file.write(json_msg)
        file.close()


def readJsonFile(filename):
    '''
    读取json文件
    :param filename:
    :return:
    '''
    with open(filename) as file:
        json_msg = json.load(file)
    return json_msg

def ReadFromConfig(pathFile, nameSection, dictvalue=''):
    # try:
    if os.path.exists(pathFile):
        with open(pathFile, encoding='UTF-8') as fileIni:
            cf = configparser.ConfigParser()
            # cf.read(pathFile, encoding="utf-8")
            cf.read(pathFile)
            infoConfig = cf.items(nameSection)  # type : [tuple,]
            infoConfig = dict(infoConfig)  # 转为 dict
            # print(infoConfig)
            if dictvalue != "":
                return infoConfig[dictvalue]

        return infoConfig
    else:
        return "error"


def WriteFile(filename, content, write='a'):
    '''
    将一条数据写入到文件中
    :param filename: 写入文件的路径
    :param content: 要写入的内容,换行符一\n分割
    :param write: 写入的方式,a为往后面追加,为写入删除之前的数据
    :return: 成功返回True,失败返回失败的原因
    '''
    try:
        fpath, fname = os.path.split(filename)  # 分离文件名和路径
        if not os.path.exists(fpath):
            os.makedirs(fpath)  # 创建路径
        with open(filename, write) as f:  # 如果filename不存在会自动创建, 'w'表示写数据,写之前会清空文件中的原有数据!
            f.write(content)
        return True
    except Exception as e:
        return e


# 文件压缩
def zip_ya(startdir, file_news):
    # startdir = ".\\123"  #要压缩的文件夹路径
    # file_news = startdir +'.zip' # 压缩后文件夹的名字
    z = zipfile.ZipFile(file_news, 'w', zipfile.ZIP_DEFLATED)  # 参数一:文件夹名
    for dirpath, dirnames, filenames in os.walk(startdir):
        fpath = dirpath.replace(startdir, '')  # 这一句很重要,不replace的话,就从根目录开始复制
        fpath = fpath and fpath + os.sep or ''  # 这句话理解我也点郁闷,实现当前文件夹以及包含的所有文件的压缩
        for filename in filenames:
            z.write(os.path.join(dirpath, filename), fpath + filename)
            # print ('压缩成功')
    z.close()


def get_all_file(dirname):
    filelist = []
    for root, dirs, files in os.walk(dirname):
        for name in files:
            filelist.append(os.path.join(root, name))
    return filelist


def zip_dir(dirname, zipfilename):
    filelist = []
    if os.path.isfile(dirname):
        filelist.append(dirname)
    else:
        for root, dirs, files in os.walk(dirname):
            for name in files:
                filelist.append(os.path.join(root, name))

    zf = zipfile.ZipFile(zipfilename, "w", zipfile.zlib.DEFLATED)
    for tar in filelist:
        arcname = tar[len(dirname):]
        zf.write(tar, arcname)


def get_datetime_from_datetime(the_datetime, format=1, interval=-1):
    '''
    传入时间和时间差,获取两者相加后的目标时间
    :param the_datetime: 可似乎日期时间
    :param format: 返回的数据格式,传入参数与返回数据映射如下
                1  20191018
                2  2019/10/18
                3  2019-10-18
                4  2019_10_18
                5  date备用格式
                6  date备用格式
                7  15:27:11
                8  152711
                9  作为字典返回所有日期数据
    :param interval: 目标时间与当前时间的时间间隔
    :return:
    '''
    if isinstance(the_datetime, datetime.datetime):
        time_delta = datetime.timedelta(days=interval)
        target_date = the_datetime + time_delta
        yestd = {'date': target_date,
                 "Y": target_date.year, 'M': target_date.month, 'D': target_date.day,
                 'h': target_date.hour, "m": target_date.minute, "s": target_date.second}
        if int(target_date.month) < 10:  # 时间格式处理
            yestd["M"] = '0%s' % target_date.month
        if int(target_date.day) < 10:
            yestd["D"] = '0%s' % target_date.day
    else:
        return None

    if format == 1:  # 日期格式化并返回
        return '%s%s%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 2:
        return '%s/%s/%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 3:
        return '%s-%s-%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 4:
        return '%s_%s_%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 5:
        pass  # date备用格式
    elif format == 6:
        pass
    elif format == 7:
        return '%s:%s:%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 8:
        return '%s%s%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 9:
        return yestd


def getFormatData(argv=None, format=1, interval=0):
    '''
    时间获取函数,如果命令行参数argv带有时间参数,验证后返回该时间,否则返回系统当前时间与时间差interval相加后的日期时间
    :param argv: sys.argv,传入的命令行参数
    :param format: 返回的数据格式,传入参数与返回数据映射如下
                    1  20191018
                    2  2019/10/18
                    3  2019-10-18
                    4  2019_10_18
                    5  date备用格式
                    6  date备用格式
                    7  15:27:11
                    8  152711
                    9  作为字典返回所有日期数据
    :param interval: 目标时间与当前时间的时间间隔,不对命令行传入的时间进行运算。
    :return:
    '''
    # timeArray = time.localtime(time.time())
    # timeeee = time.strftime(r"%Y%m%d %H:%M:%S", timeArray)

    time_delta = datetime.timedelta(days=interval)  # 设置时间间隔
    sys_time = datetime.datetime.now() + time_delta  # 获取系统时间

    if (argv and len(argv) == 1) or (not argv) or (len(argv) >= 2 and 'null' == argv[1]):  # 命令行未传入时间参数
        yestd = {'date': sys_time,
                 "Y": sys_time.year, 'M': sys_time.month, 'D': sys_time.day,
                 'h': sys_time.hour, "m": sys_time.minute, "s": sys_time.second}
        if int(sys_time.month) < 10:  # 时间格式处理
            yestd["M"] = '0%s' % sys_time.month
        if int(sys_time.day) < 10:
            yestd["D"] = '0%s' % sys_time.day
        print(argv)
    else:  # 命令行传入了时间参数
        print(argv[1])
        # match = re.search(r'\d{4}[-/]\d{1,2}[-/]\d{1,2}', '2019/01/29')
        match = re.search(r'20\d{2}-\d{1,2}-\d{1,2}', argv[1])  # 时间格式匹配
        match2 = re.search(r'20\d{2}/\d{1,2}/\d{1,2}', argv[1])
        match3 = re.search(r'20\d{2}\d{1,2}\d{1,2}', argv[1])
        if match:
            print('match: %s' % match)
            yesterday = datetime.datetime.strptime(match.group(), '%Y-%m-%d')
        elif match2:
            print('match2: %s' % match2)
            yesterday = datetime.datetime.strptime(match2.group(), '%Y/%m/%d')
        elif match3:
            print('match3: %s' % match3)
            yesterday = datetime.datetime.strptime(match3.group(), '%Y%m%d')
        else:
            return None
        yestd = {'date': yesterday,
                 "Y": yesterday.year, 'M': yesterday.month, 'D': yesterday.day,
                 'h': sys_time.hour, "m": sys_time.minute, "s": sys_time.second}
        if int(yesterday.month) < 10:  # 时间格式处理
            yestd["M"] = '0%s' % yesterday.month
        if int(yesterday.day) < 10:
            yestd["D"] = '0%s' % yesterday.day

    if format == 1:  # 日期格式化并返回
        return '%s%s%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 2:
        return '%s/%s/%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 3:
        return '%s-%s-%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 4:
        return '%s_%s_%s' % (yestd['Y'], yestd['M'], yestd['D'])
    elif format == 5:
        pass  # date备用格式
    elif format == 6:
        pass
    elif format == 7:
        return '%s:%s:%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 8:
        return '%s%s%s' % (yestd['h'], yestd['m'], yestd['s'])
    elif format == 9:
        return yestd


def getLastMonthDate(format=0):
    first_day = datetime.date(datetime.date.today().year, datetime.date.today().month, 1)  # 本月第一天
    last = first_day - datetime.timedelta(1)  # 获取上个月最后一天日期
    if format == 0:
        return str(last)
    else:
        return str(last).replace('-', '')


class Rfs(object):
    status = {
        'NoDevice': 'key未接入或者未供电',
        'NoLink': 'key未连接',
        'Linked': '连接成功',
    }
    local_ip = '10.1.2.87'
    pathFile = r"D:\ChinaCoalAuto\Common\Config\USBHubConfig.ini"  # 配置文件路径

    def get_allPort_msg(self):
        result = {}
        temp_result = os.popen('RfsNu -l')
        res = temp_result.read()
        No = 1
        for line in res.splitlines():
            if 'UsbNumber' in line:
                temp = line.strip().split(' ')
                result[No] = {
                    temp[0].split(':')[0]: temp[0].split(':')[1],
                    temp[1].split(':')[0]: temp[1].split(':')[1],
                    temp[2].split(':')[0]: temp[2].split(':')[1],
                    temp[3].split(':')[0]: temp[3].split(':')[1],
                    temp[-1].split(':')[0]: temp[-1].split(':')[1],
                }
                No += 1
        return result

    def get_port_msg(self, port):
        port_status = self.get_allPort_msg()
        if isinstance(port, int):
            return port_status[port]

    def is_linked(self, port):
        port_status = self.get_port_msg(port)
        if 'NoDevice' == port_status['linkstatus']:
            return 'NoDevice', port_status['linked']
        elif 'NoLink' == port_status['linkstatus']:
            return 'NoLink', port_status['linked']
        elif 'Linked' == port_status['linkstatus']:
            return 'Linked', port_status['linked']

    def wait_for_linked(self, portName, wait_time):
        port = int(self.ReadFromConfig(Rfs.pathFile, "Main", portName))
        status = None
        while wait_time >= 0:
            status, ip = self.is_linked(port)
            print(port, status)
            if status == 'Linked':
                if self.local_ip in ip:
                    break
                else:
                    raise PortHasBeenUsedException('The port has been used')
            else:
                time.sleep(1)
                wait_time -= 1

        if status != 'Linked':
            raise NotFoundKeyException('Not checked the key')  # key未连接时,抛出异常结束后续操作

    # 读取配置文件 Section->{k:v}
    def ReadFromConfig(self, pathFile, nameSection, dictvalue=''):
        # try:
        if os.path.exists(pathFile):
            with open(pathFile, encoding='UTF-8') as fileIni:
                cf = configparser.ConfigParser()
                cf.read(pathFile, encoding='UTF-8')
                infoConfig = cf.items(nameSection)  # type : [tuple,]
                infoConfig = dict(infoConfig)  # 转为 dict
                # print(infoConfig)
                if dictvalue != "":
                    return infoConfig[dictvalue]
            return infoConfig
        else:
            return "error"


class Mysql(object):
    '''
    操作mysql数据库
    '''

    def __init__(self, host, user, password, database=None, port=3306, **kwargs):
        '''
        初始化连接数据库
        :parameter huang
        :param host: 地址
        :param user:用户名
        :param password:密码
        :param database:数据库名称
        :param port:端口号
        :param kwargs: 以下参数
                 unix_socket=None,charset='', sql_mode=None,
                 read_default_file=None, conv=None, use_unicode=None,
                 client_flag=0, cursorclass=Cursor, init_command=None,
                 connect_timeout=10, ssl=None, read_default_group=None,
                 compress=None, named_pipe=None,autocommit=False, db=None,
                 passwd=None, local_infile=False,max_allowed_packet=16*1024*1024,
                 defer_connect=False,auth_plugin_map=None, read_timeout=None,
                 write_timeout=None,bind_address=None, binary_prefix=False,
                 program_name=None,server_public_key=None
        '''
        logging.info('连接到mysql服务器...')
        self.db = pymysql.connect(host, user, password, database, port, **kwargs)
        logging.info('数据库连接成功!')

    def insertdb(self, sql):
        '''
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
        try:
            cursor.execute(sql)  # 执行sql语句
            self.db.commit()  # 提交到数据库执行
        except Exception as e:
            logging.error(e)
            logging.error('插入数据失败!')
            self.db.rollback()  # 回滚
        finally:
            cursor.close()

    def updatedb(self, sql):
        '''
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()  # 使用cursor()方法获取操作游标
        try:
            cursor.execute(sql)  # 执行SQL语句
            self.db.commit()  # 提交到数据库执行
        except:
            logging.error('更新数据失败!')
            self.db.rollback()  # 发生错误时回滚
        finally:
            cursor.close()

    def update_db_many(self, sql, data):
        '''
            批量插入
        :param sql:
        :param data:列表字典
        :return:
            example:
                db = Mysql("localhost", "root", "admin123", 'ccyboa_service', port=3306)
                sql = ("INSERT INTO `ops_data_bank_copy`(batch_num,data_comment_1,data_decimal_1,data_datetime_5,data_int_1,is_delete) "
                       "VALUES(%s,%s,%s,%s,%s,%s)")
                data = [
                    ('test_20111111', 'test_2019', '33004', '2019/01/02', 234, 1),
                    ('test_2011222', 'test_2018', '33001', '2019/01/03', 235, 1),
                    ('test_2011333', 'test_2017', '33002', '2019/01/04', 234, 1),
                    ('test_20114441', 'test_2019', '33004', '2019/01/05', 234, 1),
                ]
                db.insert_db_many(sql,data)
        '''
        cursor = self.db.cursor()
        try:
            cursor.executemany(sql, data)  # 批量执行多条插入SQL语句
            self.db.commit()  # 提交事务
        except Exception as e:
            logging.error(e)
            self.db.rollback()  # 有异常,回滚事务
            raise e
        finally:
            cursor.close()

    def select_db_fetchall(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()
        # sql = "SELECT * FROM ops_data_bank where batch_num like '%20191202'"
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchall()  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            return results
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchone(self, sql):
        '''
        数据库查询
        :param sql:
        :return:
        '''
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchone()  # 获取查询的所有记录
            return title_name, results
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def select_db_fetchmany(self, sql, size=None, *args):
        '''
        数据库结果查询
        :param sql:
        :param size: 条目
        :param args:
        :return: 数据元组
        '''
        cursor = self.db.cursor()
        try:
            cursor.execute(sql, *args)  # 执行sql语句
            title_name = [mem[0] for mem in cursor.description]
            results = cursor.fetchmany(size)  # 获取查询的所有记录
            results = tuple([title_name] + list(results))
            print(results)
        except Exception as e:
            logging.error(e)
            raise e
        finally:
            cursor.close()  # 关闭连接

    def closedb(self):
        '''
        关闭数据库
        :return:
        '''
        self.db.close()

    def __del__(self):
        self.closedb()
        print("数据库关闭成功,__del__对象已经销毁")


# class bank_balance(object):
#     data_id = ''
#     batch_num = ''
#     account_no = ''
#     account_name = ''
#     account_balance = ''
#     bank_name = ''
#     account_date = ''
#     insert_time = ''
#     is_delete = ''
#     source_id = ''
#     versionNo = ''
#
#     def change_batch_num(self):
#         '''
#         修改批号
#         :return:
#         '''
#         batch_num = self.batch_num[1]
#         temp = batch_num.split('_')[:-1]
#         temp.append(getFormatData(interval=-1))
#         self.batch_num = (self.batch_num[0], '_'.join(temp))
#
#     def change_versionNo(self, db):
#         '''
#         查询修改版本号
#         :param db:
#         :return:
#         '''
#         sql = "(SELECT IFNULL(MAX(versionNo),0)+1  FROM ops_data_bank_balance " \
#               "WHERE batch_num = '%s'  AND account_no	 = '%s');" % (self.batch_num[1], self.account_no[1])
#         result_data = db.select_db_fetchone(sql)
#         version_no = result_data[1][0]
#         self.versionNo = (self.versionNo[0], version_no)
#
#     def set_property(self, sql_result):
#         '''
#         根据查询结果为对象属性赋值,值为元组,对应值分别为  属性=(数据库字段名,数据库属性对应数据)
#         :param sql_result:
#         :return:
#         '''
#         if len(sql_result) != 2:
#             return
#         for index, name_column in enumerate(sql_result[0]):
#             if 'data_id' == name_column:
#                 self.data_id = (name_column, sql_result[1][index])
#             elif 'batch_num' == name_column:
#                 self.batch_num = (name_column, sql_result[1][index])
#             elif 'account_no' == name_column:
#                 self.account_no = (name_column, sql_result[1][index])
#             elif 'account_name' == name_column:
#                 self.account_name = (name_column, sql_result[1][index])
#             elif 'account_balance' == name_column:
#                 self.account_balance = (name_column, str(sql_result[1][index]))
#             elif 'bank_name' == name_column:
#                 self.bank_name = (name_column, sql_result[1][index])
#             elif 'account_date' == name_column:
#                 self.account_date = (name_column, str(sql_result[1][index]))
#             elif 'insert_time' == name_column:
#                 self.insert_time = (name_column, str(sql_result[1][index]))
#             elif 'is_delete' == name_column:
#                 self.is_delete = (name_column, sql_result[1][index])
#             elif 'source_id' == name_column:
#                 self.source_id = (name_column, sql_result[1][index])
#             elif 'versionNo' == name_column:
#                 self.versionNo = (name_column, sql_result[1][index])
#
#     def get_sql_data(self, db, account):
#         '''
#         数据库根据id排序,获取最后一条数据,属性赋值
#                         sql_select_data = ("(SELECT * FROM `ops_data_bank_balance` WHERE account_no = '%s' "
#                            "AND is_delete = 0 ORDER BY account_date	DESC LIMIT 1);" % account)
#         :param db:
#         :param account:
#         :return:
#         '''
#         sql_select_data = ("(SELECT * FROM `ops_data_bank_balance` WHERE account_no = '%s' "
#                            "AND is_delete = 0 ORDER BY data_id	DESC LIMIT 1);" % account)
#         result_data = db.select_db_fetchone(sql_select_data)
#         self.set_property(result_data)
#
#     def insert_balance(self, db):
#         '''
#         数据库数据插入
#                 sql="INSERT INTO `ops_data_bank_balance`( `batch_num`, `account_no`, `account_name`, `account_balance`, " \
#                         "`bank_name`, `account_date`, `is_delete`, `source_id`, `versionNo`) " \
#             "VALUES('BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191205','770901040000769','中煤新集能源股份有限公司'," \
#             "'66343.49','中国农业银行','2019-12-01 00:00:00','0','','2');"
#         :param db:
#         :return:
#         '''
#         sql2 = "INSERT INTO `ops_data_bank_balance`( `batch_num`, `account_no`, `account_name`, `account_balance`, " \
#                "`bank_name`, `account_date`, `is_delete`, `source_id`, `versionNo`) " \
#                "VALUES('%s','%s','%s','%s','%s','%s','%s','%s','%s');" % (
#                    self.batch_num[1], self.account_no[1], self.account_name[1],
#                    self.account_balance[1], self.bank_name[1], self.account_date[1],
#                    self.is_delete[1], '', self.versionNo[1])
#         db.insertdb(sql2)
#         logging.info('余额插入成功')
#
#
# def insert_bank_balance(account):
#     '''
#         SELECT * FROM `ops_data_bank_balance` WHERE account_no	 = '770901040000769' AND is_delete = 0 ORDER BY data_id	DESC LIMIT 1;
#         SELECT  FROM ops_data_bank_balance WHERE batch_num = 'BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191203' ;
#         (SELECT IFNULL(MAX(versionNo),0)+1  FROM ops_data_bank_balance WHERE batch_num = 'BSTC_BANK_BALANCE_STATEMENT_中煤新集能源股份有限公司_20191204' );
#         @:param 参数账号信息
#         @:param
#         :return:
#             # 查询根据账号余额数据 银行余额类保存相关数据属性
#             # 查询版本
#             # 生成sql插入语句
#             # 插入结果数据
#             # 查询插入结果检验
#             # 确认插入成功
#     '''
#     balance = bank_balance()  # 余额类
#     db = Mysql("localhost", "root", "root", 'ccyboa_service', port=3306)
#     balance.get_sql_data(db, account)  # 根据id排序查找数据
#     balance.change_batch_num()  # 修改batch_num
#     balance.change_versionNo(db)  # 获取versionNo并修改
#     balance.insert_balance(db)  # 数据插入
#
#     balance2 = bank_balance()  # 余额类
#     balance2.get_sql_data(db, account)  # 根据id排序查找数据,余额类添加相关属性
#     if balance.batch_num[1] == balance2.batch_num[1] and balance.versionNo[1] == balance2.versionNo[1]:
#         print('数据库查询道的数据和数据库插入的数据一致,success')
#         return True
#     else:
#         print('数据库查询道的数据和数据库插入的数据不一致,Fail')
#         return False

def csvToList(excelPath):
    '''
    打开csv文件,转换成list
    :param excelPath:
    :return:
    '''
    if os.path.exists(excelPath):
        # 打开csv
        # excelPath = "D:/autoit/项目/华鑫证券/文档/增值税取数文档及模板/附表二.csv"
        print("打开路径下的文件:",excelPath)
        try:
            df = pd.read_csv(open(excelPath), index_col=False, header=None, error_bad_lines=False)
        except Exception as e:
            print("打开文件失败:", e)
            df = pd.read_csv(open(excelPath, encoding='UTF-8'), index_col=False, header=None, error_bad_lines=False)
        train_data = pd.np.array(df)  # np.ndarray()
        train_x_list = train_data.tolist()  # list
    else:
        train_x_list = [[]]
    return train_x_list


def listToCSV(csvPath, csvList):
    '''
    讲列表写入到csv
    :param csvPath: 写入的路径
    :param csvList: 写入的列表
    :return:
    '''
    df_data = pd.DataFrame(csvList)
    df_data.to_csv(csvPath, header=False, index=False, encoding='gb2312')


def resultWriteCSV(resultPath, resultTemplatePath, writeResult):
    '''
    将结果写入到csv文件中
    :param resultPath: 写入文件的路径
    :param resultTemplatePath: 模板的路径
    :param writeResult: 写入的数据值
    :return:
    '''
    if not os.path.exists(resultPath):
        mycopyfile(resultTemplatePath, resultPath)
    readLists = csvToList(resultPath)
    for i in range(0, len(readLists)):
        if readLists[i][0] == writeResult[0]:
            readLists[i][1] = writeResult[1]
            readLists[i][2] = writeResult[2]
            readLists[i][3] = writeResult[3]
            readLists[i][4] = writeResult[4]
    listToCSV(resultPath, readLists)

 

延伸阅读
    < /body> < /html>