一. 案例一：文件比对

1.1 需求

现有一个文件夹，里面存放若干PDF文件。还有一个Excel表格，记录了这些PDF的文件名。现在要进行双向核查，即 Excel表格中的PDF文件名 和 文件夹中的PDF 是否一一对应，有无缺漏。

1.2 实现

大致思路：使用xlrd读取Excel表格进行比对

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
import os
import glob
import sys

import xlrd

# 指定要遍历的文件夹路径
folder_path = './pdf'
# 指定Excel文件路径
excel_file_path = './'

try:
    """处理PDF"""
    # 使用glob模块获取文件夹中所有的PDF文件
    pdf_files = glob.glob(os.path.join(folder_path, '*.pdf'))

    # 提取文件名并存储到列表中
    pdf_file_names = [os.path.basename(pdf_file) for pdf_file in pdf_files]
    pdf_serial_number_list = []
    excel_serial_number_list = []

    # 式提取第二个-后面的内容
    for name in pdf_file_names:
        pdf_serial_number_list.append(name.split("-")[2])

    """处理Excel"""
    # 过滤出扩展名为.xlsx或.xls的Excel文件
    files = os.listdir(excel_file_path)
    excel_files = [file for file in files if file.endswith(('.xlsx', '.xls'))]

    if len(excel_files) == 1:
        # 如果只有一个Excel文件，读取它
        excel_file = excel_files[0]
        excel_file_path = os.path.join(excel_file_path, excel_file)

    elif len(excel_files) == 0:
        print("当前目录下没有Excel文件。")
    else:
        print("当前目录下有多个Excel文件，请确保只有一个Excel文件。")
        sys.exit(-1)

    # 指定要读取的列数（N从1开始计数）
    column_number = 14  # 这里示例为第2列

    # 打开Excel文件以进行读取
    print(excel_file_path)
    workbook = xlrd.open_workbook(excel_file_path)
    sheet = workbook.sheet_by_index(0)  # 假设要读取第一个工作表

    # 从第二行开始获取指定列的数据
    column_data = [sheet.cell_value(row, column_number - 1) for row in range(1, sheet.nrows)]

    # 打印读取的列数据
    for data in column_data:
        excel_serial_number_list.append(data.split("-")[1])

    with open('output.txt', 'w') as file:
        """以PDF为准，开始比对"""
        file.write("回单中存在，流水不存在：\n")
        for number in pdf_serial_number_list:
            if number not in excel_serial_number_list:
                file.write(number + "\n")

        file.write("\n")

        """以Excel为准，开始比对"""
        file.write("流水中存在，回单不存在：\n")
        for number in excel_serial_number_list:
            if number not in pdf_serial_number_list:
                file.write(number + "\n")
except Exception as e:
    with open('error.txt', 'w') as file:
        file.write("运行出错，错误信息如下：\n" + e)

二. 案例二：合并表单

2.1 需求

现有一个 Excel 文件，里面包含多个 Table，现在要合并部分 Table（它们的表头都一样），生成新的 Table 并写入到新的 Excel 文件中，尽量保证格式不变。

2.2 实现

Python 擅长的是对数据进行处理，如果要完全保证格式一致会很困难，因此最佳实践方案为：先手动创建一个模版 Excel，把大致格式先调整好，然后 Python 复制一份模版副本，追加写入该文件。

随后再对表格进行样式的设置，参考：https://blog.csdn.net/weixin_43790276/article/details/127794041

在下面的代码中也对单元格样式进行了部分调整。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
from openpyxl.styles import Alignment
from openpyxl.styles import Font
from openpyxl.styles import Side, Border
import openpyxl
import pandas as pd

input_file_path = "input.xlsx"
output_file_path = "output.xlsx"


def merge_excel():
    # 复制模版
    df = openpyxl.load_workbook('template.xlsx')
    df.save(output_file_path)

    # 两行表头，如果 header=[0,1] 则为多级索引
    excel_data = pd.read_excel(input_file_path, sheet_name=None, header=1)

    # 合并表格
    merged_data = pd.concat([excel_data['北京'], excel_data['北京总部'], excel_data['成都'], excel_data['福州'],
                             excel_data['广西'], excel_data['广州 深圳'], excel_data['深圳总部'], excel_data['贵阳'],
                             excel_data['哈尔滨'], excel_data['杭州']], ignore_index=True)

    with pd.ExcelWriter(output_file_path, engine='openpyxl', mode='a', if_sheet_exists='overlay') as writer:
        df1 = pd.DataFrame(pd.read_excel(output_file_path, sheet_name='汇总'))
        df_rows = df1.shape[0]  # 获取原数据的行数
        # 将数据df写入excel中的sheet1表,从第一个空行开始写：
        merged_data.to_excel(writer, sheet_name='汇总', startrow=df_rows + 1, index=False, header=False)


def beautify_cell(start_row):
    workbook = openpyxl.load_workbook(output_file_path)
    worksheet = workbook.active

    # 批量单元格居中 前四列和第六列
    alignment = Alignment(horizontal='center', vertical='center')
    for row in worksheet.iter_rows(min_row=start_row, max_row=worksheet.max_row):
        for index, cell in enumerate(row):
            if index in [0, 1, 2, 3, 5]:
                cell.alignment = alignment

    # 设置单元格的边框线条
    side = Side(style="thin", color="000000")
    border = Border(top=side, bottom=side, left=side, right=side)
    for row in worksheet.iter_rows(min_row=start_row, max_row=worksheet.max_row):
        for cell in row:
            cell.border = border

    # 设置字体和文字样式 前8列
    font = Font(name="宋体", size=9, color='000000', bold=False,
                italic=False, strike=False)
    for row in worksheet.iter_rows(min_row=start_row, max_row=worksheet.max_row):
        for cell in row[:8]:
            cell.font = font

    workbook.save(output_file_path)


if __name__ == '__main__':
    try:
        merge_excel()
        beautify_cell(6)  # 从第六行开始美化
        print("Merge completed, output file path: ", output_file_path)
    except Exception as e:
        print("Error message: ", e)

    input("Press Enter to exit the program...")

三. 打包为单个EXE可执行文件

创建 file_version_info.txt ：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# UTF-8
#
# For more details about fixed file info 'ffi' see:
# http://msdn.microsoft.com/en-us/library/ms646997.aspx
VSVersionInfo(
  ffi=FixedFileInfo(
    # filevers and prodvers should be always a tuple with four items: (1, 2, 3, 4)
    # Set not needed items to zero 0. filevers和prodvers应该始终是包含四个项的元组：（1、2、3、4）,将不需要的项设置为0
    filevers=(1, 0, 0, 0), # 文件版本******，鼠标悬浮exe会显示，也显示在 详细信息-文件版本,这个是检测版本的依据
    prodvers=(3, 6, 0, 0), # 生产商，未见显示在哪里
    # Contains a bitmask that specifies the valid bits 'flags'r
    mask=0x3f, # 两个位掩码
    # Contains a bitmask that specifies the Boolean attributes of the file.
    flags=0x0,
    # The operating system for which this file was designed.
    # 0x4 - NT and there is no need to change it.
    OS=0x40004, # 为其设计此文件的操作系统,0x4-NT，无需更改它
    # The general type of file.
    # 0x1 - the file is an application.
    fileType=0x1, # 文件的常规类型，0x1-该文件是一个应用程序
    # The function of the file.
    # 0x0 - the function is not defined for this fileType
    subtype=0x0, # 文件的功能，0x0表示该文件类型未定义
    # Creation date and time stamp.
    date=(0, 0) # 创建日期和时间戳
    ),
  kids=[
    StringFileInfo(
      [
      StringTable(
        u'080404b0',
        [StringStruct(u'CompanyName', u'515code.com'), # 公司,鼠标悬浮exe会显示
        StringStruct(u'FileDescription', u'如如的回单-流水双向核查工具'), # 文件说明，鼠标悬浮exe会显示，也会显示在 详细信息-文件说明
        StringStruct(u'FileVersion', u'1.0.0.0'), # 没见哪里显示
        StringStruct(u'LegalCopyright', u'Copyright (C) 2024 515CODE.COM'), # 版权，会显示在 详细信息-版权
        StringStruct(u'ProductName', u'双向核查工具'), # 原始文件名，会显示在 详细信息-原始文件名
        StringStruct(u'ProductVersion', u'1.0.0.0')]) # 产品版本，会显示在 详细信息-产品版本
      ]),
    VarFileInfo([VarStruct(u'Translation', [2052, 1200])]) # 语言，中文简体
  ]
)

使用 pyinstaller，执行命令：

1
pyinstaller --version-file=file_version_info.txt -i icon.ico --onefile main.py

四. 总结

  1. 三种常见库（要处理 xls 文件，首选 xlrd&xlwt&xlutils ，处理 xlsx 则选择 pandas 或 OpenPyXL）

    类型 xlrd&xlwt&xlutils pandas OpenPyXL
    读取 支持 支持 支持
    写入 支持 支持 支持
    修改 支持 支持 支持
    xls 支持 支持 不支持
    xlsx 高版本支持 支持 支持
    大文件 不支持 支持 支持
    效率 快 快 快
    功能 较弱 强大 一般

  2. 如果要对 xls 中的表格进行修改而不改变样式（实测，虽然样式不变，但是自动求和等带有函数的单元格会失效），最好先用 xls2xlsx 包将 xls 格式转成 xlsx，然后用 pandas 进行处理（追加写入模式）。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    def xls2xlsx(temp_folder):
        print("正在将xls文件转换为xlsx...")
        os.makedirs(temp_folder, exist_ok=True)

        # 遍历所有xls文件，转换为xlsx
        xls_files = glob.glob('*.xls')
        for file_path in xls_files:
            file_name = os.path.splitext(os.path.basename(file_path))[0]
            XLS2XLSX(file_path).to_xlsx(temp_folder + file_name + '.xlsx')

        # 将当前目录下所有xlsx文件复制到临时目录中
        xlsx_files = glob.glob('*.xlsx')
        for file_path in xlsx_files:
            file_name = os.path.splitext(os.path.basename(file_path))[0]
            shutil.copy(file_path, temp_folder + file_name + '.xlsx')

  3. 如果要修改单元格样式，请使用 openpyxl.styles 包。