excel数据转换、文件移动（剪切、复制、删除）

# encoding=utf-8
# 函数调用
import traceback

import os, xlrd, time
from xlrd import xldate_as_tuple
from datetime import datetime
import datetime
from xlutils.copy import copy
import pandas as pd

def open_excel(file, encode=None):
    print("excel open_excel")
    try:
        if encode:
            data = xlrd.open_workbook(file, encoding_override=encode)
        else:
            data = xlrd.open_workbook(file)
        return data
    except Exception as e:
        print("文件打开失败,str(e)是", repr(e))


# 根据索引获取Excel表格中的数据
def excel_table_by_index(file, title_index=0, by_index=0, data_type=0, begin_end=(0, 0), get_data=None, encode=None):
    """
    :param file: copy后的Excel文件路径
    :param title_index: 表头列名所在行的所以
    :param by_index: 表的索引
    :param data_type: 类型 0：标题跟内容上下排列，1：标题跟内容左右排列
    :param begin_end: 左右列开始和结束行
    :param get_data: 上下排列模式：某列值仅为某账号值时的数据（多账户混合一个excel时，只提取该账号的数据）
    :return:
    """
    try:
        if encode:
            data = open_excel(file, encode)
        else:
            data = open_excel(file)
        table = data.sheets()[by_index]
        # 行数
        nrows = table.nrows
        # 文件尾部有汇总记录时，需要剔除begin_end的记录数
        if data_type == 0 and begin_end is not None and len(begin_end) == 2 and begin_end[1] > 0:
            nrows = (nrows - begin_end[1])
        # 列数
        # ncols = table.ncols
        # 标题数据
        data_list = []
        data_obj = {}
        if data_type == 0:
            title_name = table.row_values(title_index)
            for row_num in range(title_index + 1, nrows):
                row = table.row_values(row_num)
                if row:
                    app = {}
                    # 指定列所在得位置
                    column_no = -1
                    for i in range(len(title_name)):
                        ctype = table.cell(row_num, i).ctype
                        if ctype == 3:
                            date = datetime(*xldate_as_tuple(row[i], 0))
                            cell = date.strftime('%Y-%m-%d')  # ('%Y/%m/%d %H:%M:%S')
                            app[title_name[i]] = cell
                        else:
                            app[title_name[i]] = row[i]
                        if get_data is not None and title_name[i] == get_data[0]:
                            # 找到该列所在位置
                            column_no = i
                    # 未指定特殊列 或者 指定列的值为指定数据时获取。
                    # if get_data is not None:
                    #     print('({},{})column_no={}'.format(get_data[0], get_data[1], column_no))
                    if get_data is None or get_data[1] == row[column_no]:
                        data_list.append(app)
            return data_list
        else:
            # 左右排列
            begin = begin_end[0]
            end = begin_end[1]
            for row_num in range(begin, end):
                rows = table.row_values(row_num)
                if rows:
                    other_rows = rows[1::2]
                    for index, row in enumerate(rows[0::2]):
                        if row:
                            data_obj[row] = other_rows[index]
            return data_obj
    except Exception as ex:
        print('excel解析有异常：{}'.format(traceback.format_exc()))
        return []


def data_conversion_pay(data_pre):
    print("======data_conversion=======")
    data_end = list()
    for data in data_pre:
        data_trans_dict = dict()
        data_trans_dict["xxx"] = data.get("xxx", "")
        ... ...
        data_end.append(data_trans_dict)
    return data_end

# 费用转换
def data_conversion_cost(data_trans_source):
    data_end = list()
    for data in data_trans_source:
        data_trans_dict = dict()
        data_trans_dict["xxx"] = data.get("xxx", "")
        ... ...
        cost_date = data.get("日期")
        if cost_date is not None:
            issue_date = cost_date
        else:
            issue_date = ""
        data_trans_dict["日期"] = issue_date
        data_end.append(data_trans_dict)
    print("====data_end======", data_end)
    return data_end

import shutil, os

# 从移动后的路径 copy 到指定处理路径
def rename_move(source_file_path, target_path, file_name):
    if not os.path.isfile(source_file_path):
        print("源文件不存在")
        return False
    if not os.path.exists(target_path):
        print("目标文件夹不存在,创建目前文件夹")
        os.makedirs(target_path)
    targetFile = os.path.join(target_path, file_name)
    # 如果已存在目标文件，先删除
    if os.path.isfile(targetFile):
        os.remove(targetFile)
    # 移动到指定目录（剪切）
    # shutil.move('demo.txt', '新的文件夹/new1.txt')
    shutil.copy(source_file_path, targetFile)
    return targetFile

# 写入excel
def write_to_save_file(excel_path_dir, file_name_path, var_list, excel_sheet_index, excel_sheet_name):
    """
    @param excel_path_dir:  资金模板文件，也是填入数据后的上传的文件
    @param bank_name:
    @param var_list:
    @param excel_sheet_index:
    @param excel_sheet_name:
    @return:

    """
    targetFile = os.path.join(excel_path_dir, file_name_path)
    print("targetFile", targetFile)
    if not os.path.isfile(targetFile):
        print("没有可写入的文件")
        return False
    if isinstance(var_list, list):
        # 列表嵌套字典
        if isinstance(var_list[0], dict):
            # file_path = r"C:\Users\ZJBR\Desktop\111.xls"
            file_path = targetFile
            # 读取文件 formatting_info=True：保留Excel的原格式
            read_file = xlrd.open_workbook(file_path, formatting_info=True)

            # 将文件复制到内存
            write_data = copy(read_file)
            # 读取复制后文件的sheet1
            write_save = write_data.get_sheet(excel_sheet_index)
            # 获取起始行
            df1 = pd.DataFrame(pd.read_excel(file_path, sheet_name=excel_sheet_name))  # 读取原数据文件和表
            print("=====di===", df1)
            df_rows = df1.shape[0]  # 获取原数据的行数

            for index, value in enumerate(var_list):
                j = 0
                for data in value.values():
                    print(df_rows + index + 1, j, data)
                    # 写入 目标位置x, y, data
                    write_save.write(df_rows + index + 1, j, data)
                    j += 1

            # 保存写入数据后的文件到原文件路径
            write_data.save(file_path)
            return True
        else:
            print("数据格式不是 列表嵌套字典")
            return False
    else:
        print("未获取到列表的汇票数据")
        return False

def generate_format_data(excel_file, source_file_path, target_path, file_name):
    data = excel_table_by_index(excel_file, title_index=0, by_index=0, data_type=0, begin_end=(0, 0), get_data=None, encode=None)
    if data:
        # 应付票据数据转换
        data_trans = data_conversion_pay(data)
        print("data_trans===", data_trans)
        # 模板文件就位 模板文件路径(模板要为空), 目标文件夹路径, 文件
        target_file = rename_move(source_file_path, target_path, file_name)
        print("复制到", target_file)
        # 填入应付票据
        pay_write_flag = write_to_save_file(target_path, file_name, data_trans, 0, "应付票据")
        time.sleep(1)
        # 填写费用
        data_cost_end = data_conversion_cost(data_trans)
        # 填入费用
        cost_write_flag = write_to_save_file(target_path, file_name, data_cost_end, 1, "费用")
        if pay_write_flag and cost_write_flag:
            print("文件生成成功")
    else:
        print("未获取到下载文件数据")

if __name__ == '__main__':

    # # 解析文件数据
    excel_file = "文件.xls"
    source_file_path, target_path, file_name = r"C:\Users\ZJBR\Desktop\paydrafts.xls", r"C:\Users\ZJBR\Desktop\bill\GDB", "xxbank.xls"
    # data = excel_table_by_index(file, title_index=0, by_index=0, data_type=0, begin_end=(0, 0), get_data=None, encode=None)
    # if data:
    #     # pay_bill数据转换
    #     data_trans = data_conversion_pay(data)
    #     print("data_trans===", data_trans)
    #     # 模板文件就位 空模板文件路径
    #     target_file = rename_move(source_file_path, target_path, file_name)
    #     print("复制到", target_file)
    #     # 填入pay_bill
    #     pay_write_flag = write_to_save_file(target_path, file_name, data_trans, 0, "应付xx")
    #     time.sleep(1)
    #     # 填写cost
    #     data_cost_end = data_conversion_cost(data_trans)
    #     # 填入cost
    #     cost_write_flag = write_to_save_file(target_path, file_name, data_cost_end, 1, "cost")
    #     if pay_write_flag and cost_write_flag:
    #         print("文件生成成功")
    # else:
    #     print("未获取到下载文件数据")

    generate_format_data(excel_file, source_file_path, target_path, file_name)