python处理Excel数据(数据透视、调整打印格式)

from openpyxl.workbook import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, colors
from openpyxl.utils import get_column_letter
import math
import time
import urllib
import os
import pandas as pd
import datetime
import numpy as np
import xlwings as xw
import xlwings as app
import openpyxl
import threading
import warnings

类别 = []

# 样例文件地址：
# https://download.csdn.net/download/copa_ax99/85505664
def warning_info(msg):
    """
    警告用户
    :param msg: 提示信息
    :return:
    """
    warnings.warn(msg)


def excel_file_read(file_name1, file_name2, file_name3, data_loc='单据法人', data_loc2='银行开户行'):
    """
    读取Excel：
    ①将单据法人列填充公式
    ②新增【类型】列
    :param file_name1:
    :param file_name2:
    :param file_name3:
    :param data_loc: 读取列值，用于替换公式
    :param data_loc2: 读取列值，用于判断银行类型
    :return:
    """
    df_1 = pd.read_excel(f'{file_name1}.xlsx')

    # 获取列值
    len_df = df_1[data_loc].values
    # 当前文件所在路径
    base_path = os.path.dirname(__file__)
    for i in range(0, len(len_df)):
        # 将列值替换成公式
        len_df[i] = f"=VLOOKUP(F{i + 2},'{base_path}/[{file_name2}.xlsx]Sheet1'!$D:$K,8,0)"

    # 获取列值
    len_df_types = df_1[data_loc2].values
    # 根据关键字判断其属于哪个银行
    for i in range(0, len(len_df_types)):
        if '人力资源发放' in len_df_types[i]:
            类别1 = '农行'
        elif '中国建设银行' in len_df_types[i]:
            类别1 = '建设'
        else:
            类别1 = '未知'
            warning_info(msg=f'银行开户行【{len_df_types[i]}】属于 “未知”类型，请仔细核对Excel表格或添加新的关键字判断')
        类别.append(类别1)

    # 将新增列保存到Excel
    df_1.insert(loc=len(df_1.columns), column="类型", value=类别)
    pd.DataFrame(df_1).to_excel(f'{file_name3}.xlsx', sheet_name='处理后数据', index=False, header=True)


def threading_loop(num):
    """
    点击Excel中的更新按钮
    :param num:
    :return:
    """
    import time
    from pywinauto import Desktop  # 导包
    while True:
        try:
            app = Desktop(backend="uia")['Excel']
            time.sleep(num)
            app['更新(U)'].click()  # 在弹出的窗口中，定位确定按钮，并点击
            break
        except:
            pass


def xlwings_change(file_name3):
    """
    更新Excel公式计算值
    :param file_name3:
    :return:
    """
    import xlwings as xw
    app = xw.App(visible=True, add_book=False)
    app.display_alerts = False
    app.screen_updating = False
    # 文件位置：file_path，打开文档，然后保存，关闭，结束程序
    file_path = f'{file_name3}.xlsx'
    wb = app.books.open(file_path)
    wb.save()
    wb.close()
    app.quit()


def handles(file_name3):
    """
    通过threading.Thread方法实例化多线程类
    target后面跟的是函数的名称但是不要带括号也不填写参数
    args后面的内容才是要传递给函数haha()的参数。
    :param file_name3:
    :return:
    """
    t = threading.Thread(target=threading_loop, args=(1,))
    # 将线程设置为守护线程
    t.setDaemon(True)
    # 线程准备就绪，随时等候cpu调度
    t.start()
    xlwings_change(file_name3)


def p_change(file_name):
    """
    筛选公式转化为正常数据
    :param file_name: 新生成的文件
    :return:
    """
    file_path = f'{file_name}.xlsx'
    p_data = pd.read_excel(file_path, sheet_name='处理后数据')
    """
    因为 #N/A 无法进行统计，将其转化为 NaNs 进行字段统计
    """
    p_data['单据法人'] = p_data['单据法人'].fillna('NaNs')
    # 将数据写入文件
    pd.DataFrame(p_data).to_excel(file_path, sheet_name='处理后数据', index=False, header=True)


def p_perspective(file_name):
    """
    数据透视操作
    :param file_name: 筛选.py处理完，新生成的文件
    :return:
    """
    file_path = f'{file_name}.xlsx'
    p_data = pd.read_excel(file_path, sheet_name='处理后数据')
    pt = pd.pivot_table(p_data,
                        index=['单据法人', '类型'],  # 行：单据法人、类型
                        columns=['单据类型'],  # 列：单据类型
                        values=['单号', '付款金额'],  # 值：单号、付款金额
                        # aggfunc 就是定义“值”的统计方式，多个值用字典形式传入
                        aggfunc={
                            '单号': 'count',  # 单号：计数
                            '付款金额': np.sum  # 付款金额：求和
                        }, margins=True)
    # 将数据写入到新的sheet页中
    writer = pd.ExcelWriter(file_path, engine='openpyxl')
    book = openpyxl.load_workbook(writer.path)
    writer.book = book
    pd.DataFrame(pt).to_excel(writer, "数据透视")
    writer.save()


def excel_loc(file_name, list_cols):
    """
    筛选非农行信息，并清除无用列
    :param file_name:
    :return:
    """
    df_1 = pd.read_excel(f'{file_name}.xlsx', sheet_name='处理后数据')

    # 条件筛选loc
    df_sel = df_1.loc[(df_1['类型'] != '农行')]  # 筛选 类型 列不等于 农行 的值
    # 过滤需要打印的列值
    cols = [i for i in df_1.columns if i not in list_cols]
    for k in range(0, len(cols)):
        del df_1[cols[k]]
    # 将数据写入到新的sheet页中
    writer = pd.ExcelWriter(f'{file_name}.xlsx', engine='openpyxl')
    book_sheet = openpyxl.load_workbook(writer.path)
    writer.book = book_sheet
    pd.DataFrame(df_1).to_excel(writer, sheet_name="非农行筛选", index=False, header=True)
    writer.save()


def check_contain_chinese(check_str):
    """
    验证某一个字符串是否包含中文字符串，并返回中文字符串个数（ >=0 ）
    :param check_str:
    :return:
    """
    count = 0
    for ch in check_str:
        if u'\u4e00' <= ch <= u'\u9fff':
            count += 1
        else:
            continue
    return count


def excel_print(file_name):
    """
    Excel打印设置
    选择打印区域、调整打印设置
    :param file_name:
    :return:
    """
    # 读取Excel
    wb = load_workbook(f"{file_name}.xlsx")
    # 获取Excel中的sheet
    sheet_name = wb.get_sheet_names()
    # 遍历Excel中的sheet
    for i in range(1, len(sheet_name)):
        # 切换活动sheet
        a_sheet = wb[wb.sheetnames[i]]

        # 将字母A~Z放入列表中
        li = [chr(i) for i in range(ord("A"), ord("Z") + 1)]
        # 取总列数和26个英文字母的余数，判断总列数所在单元格的英文字母是啥
        aaa = a_sheet.max_column % 26
        # 将字母等价于对应列
        max_lie = li[aaa - 1]
        # 如果列数超出了A~Z，那么后面列数对应AA、AB...BA、BB...BZ...ZZ，依然按前面的判断，获取对应字母，进行拼接
        if a_sheet.max_column > 26:
            # 向下取整
            bbb = math.floor(a_sheet.max_column / 26)
            li_1 = [chr(i) for i in range(ord("A"), ord("Z") + 1)]
            max_lie = li_1[bbb - 1] + li[aaa - 1]
        # 验证最终列值
        print(f"{sheet_name[i]}(Sheet)-验证最终列值：" + max_lie)

        """
        以下参照官方文档：
        https://openpyxl.readthedocs.io/en/stable/print_settings.html#add-a-print-area
        """
        # 打印范围
        a_sheet.print_area = f'A1:{max_lie}{a_sheet.max_row}'
        print(f"{sheet_name[i]}(Sheet)-打印范围：{a_sheet.print_area}")

        """
        ValueError: Value must be one of {'mediumDashed', 'dashDotDot', 'thick', 'dashDot', 'thin', 
        'mediumDashDot', 'dashed', 'medium', 'mediumDashDotDot', 'double', 'dotted', 'hair', 'slantDashDot'}

        '中虚线'，'短划线'，'粗划线'，'短划线'，'细划线'，
        “中点”、“虚线”、“中点”、“中点”、“双点”、“点”、“头发”、“斜点”
        """
        # 单元格边框设置
        li_thin = [chr(i) for i in range(ord("A"), ord(f"{max_lie}") + 1)]
        for k in range(0, len(li_thin)):
            for j in range(1, a_sheet.max_row + 1):
                a_sheet[f'{li_thin[k]}{j}'].border = Border(left=Side(style='thin', color=colors.BLACK),
                                                            right=Side(style='thin', color=colors.BLACK),
                                                            top=Side(style='thin', color=colors.BLACK),
                                                            bottom=Side(style='thin', color=colors.BLACK))

        # 设置页脚和页眉(打印头部信息)
        # 文本
        a_sheet.oddHeader.left.text = f"公牛：{sheet_name[i]} Page &[Page] of &N "
        # 大小
        a_sheet.oddHeader.left.size = 14
        # 字体
        a_sheet.oddHeader.left.font = "Tahoma,Bold"
        # 颜色
        a_sheet.oddHeader.left.color = "CC3366"

        # 添加打印标题
        # 可以在每一页上打印标题以确保正确标记数据。
        a_sheet.print_title_cols = 'A:A'  # the first two cols
        a_sheet.print_title_rows = '1:1'  # the first row

        # 自适应调整单元格大小
        # 获取每一列的内容的最大宽度
        i = 0
        # 每列
        col_width = []
        for col in a_sheet.columns:
            # 每行
            for j in range(len(col)):
                if j == 0:
                    # 数组增加一个元素
                    aaa1 = check_contain_chinese(str(col[j].value))
                    col_width.append(len(str(col[j].value)) + aaa1)
                else:
                    # 获得每列中的内容的最大宽度
                    aaa3 = check_contain_chinese(str(col[j].value))
                    # print(len(str(col[j].value)) + aaa3)
                    if col_width[i] < len(str(col[j].value)) + aaa3:
                        col_width[i] = len(str(col[j].value)) + aaa3
                # if i == 2:
                #     print(str(col[j].value))
            i += 1
        # print(col_width)
        # 设置列宽
        for q in range(0, len(col_width)):
            # 根据列的数字返回字母
            col_letter = get_column_letter(q + 1)
            # 当宽度大于100，宽度设置为100
            if col_width[q] > 100:
                a_sheet.column_dimensions[col_letter].width = 100
            # 只有当宽度大于6，才设置列宽
            elif col_width[q] > 6:
                a_sheet.column_dimensions[col_letter].width = col_width[q] + 2

    # 只有 非农行筛选 需要，所以不放在循环里面挨个设置
    # a_sheet.set_printer_settings(paper_size=9, orientation='landscape')
    wb[wb.sheetnames[-1]].set_printer_settings(paper_size=9, orientation='landscape')
    wb.save(f"{file_name}.xlsx")


def test_main():
    import datetime
    # 第一个Excel
    file_name1 = '报盘明细'
    # 第二个Excel
    file_name2 = '报盘明细2'
    # 处理数据最终保存的Excel
    file_name3 = '报盘明细_处理之后的数据'
    # 输入筛选要显示(保留)的列
    list_cols = ['序号', '付款批次号', '支付流水号', '单据类型', '单据名称', '银行开户行', '类型']
    print("开始处理数据。。。")
    start_time = datetime.datetime.now()
    print(start_time)
    excel_file_read(file_name1=file_name1, file_name2=file_name2, file_name3=file_name3)
    handles(file_name3=file_name3)
    p_change(file_name=file_name3)
    p_perspective(file_name=file_name3)
    excel_loc(file_name=file_name3, list_cols=list_cols)
    excel_print(file_name=file_name3)
    end_time = datetime.datetime.now()
    print("程序执行完成。。。")
    print(f"总运行时间：{(end_time - start_time).seconds}")


if __name__ == '__main__':
    test_main()