Python脚本优化：ICCID未匹配时在Excel写入‘not found’

需求说明 #

现有Python脚本功能为：读取TXT文件中的ICCID列表，从alle_08.12.2022.xlsx文件中匹配对应的手机号，并将结果写入output.xlsx。当前脚本会忽略未找到匹配项的ICCID，需优化为未匹配的ICCID对应的手机号列写入not found。

示例数据 #

• 查询源TXT文件内容：

89410211684401105500
 89410211684402105500
 89410211684403105500
 89410211684404105500
 89410211684405105500

• 待查询Excel文件（alle_08.12.2022.xlsx）内容：

ICCID Nummer             Telefonnummer
89410211684401105500    0763029300
89410211684402105500    0763029400
89410211684403105500    0763029500
89410211684404105500    0763029600

• 期望output.xlsx输出格式：

Telefonnummer    ICCID Nummer
0763029300       89410211684401105500
0763029400       89410211684402105500
0763029500       89410211684403105500
0763029600       89410211684404105500
not found        89410211684405105500

现有代码 #

# !/usr/bin/env python3
import pandas as pd
import xlsxwriter


def main():
    path = 'C:\\Users\\test\\Desktop\\'
    alle_nummern = path + 'alle_08.12.2022.xlsx'

    wb = xlsxwriter.Workbook(path + 'output.xlsx')
    ws = wb.add_worksheet('Tabelle1')
    ws.set_column(0, 2, 22)
    format = wb.add_format({'num_format': '@'})

    root = tk.Tk()
    root.withdraw()
    file_path = filedialog.askopenfilename()
    row = 1

    with open(file_path) as fp:
        Lines = fp.readlines()

        for line in Lines:
            lline = line.strip()
            df = pd.read_excel(alle_nummern, dtype=str)
            df_abc = df[df["ICCID"] == lline]

            for rows in df_abc.itertuples():
                print('Nummer', rows[2], 'ICCID', rows[1])
                ws.write_row(0, 0, ['Telefonnummer', 'ICCID'], format)
                ws.write_row(row, 0, [rows[2], rows[1]], format)
                row = row + 1

    wb.close()


if __name__ == '__main__':
    main()

优化后的代码 #

#!/usr/bin/env python3
import pandas as pd
import xlsxwriter
import tkinter as tk
from tkinter import filedialog


def main():
    path = 'C:\\Users\\test\\Desktop\\'
    alle_nummern = path + 'alle_08.12.2022.xlsx'

    # 提前加载Excel数据并构建ICCID到手机号的字典，提升查询效率
    df = pd.read_excel(alle_nummern, dtype=str)
    # 假设Excel列名为"ICCID Nummer"和"Telefonnummer"，根据实际调整
    iccid_to_phone = dict(zip(df["ICCID Nummer"], df["Telefonnummer"]))

    # 初始化输出工作簿
    wb = xlsxwriter.Workbook(path + 'output.xlsx')
    ws = wb.add_worksheet('Tabelle1')
    ws.set_column(0, 1, 22)
    text_format = wb.add_format({'num_format': '@'})

    # 写入表头
    ws.write_row(0, 0, ['Telefonnummer', 'ICCID Nummer'], text_format)

    # 选择TXT文件
    root = tk.Tk()
    root.withdraw()
    file_path = filedialog.askopenfilename()

    current_row = 1
    with open(file_path, 'r') as fp:
        for line in fp:
            iccid = line.strip()
            # 查找手机号，未找到则用"not found"
            phone_number = iccid_to_phone.get(iccid, 'not found')
            # 写入当前行数据
            ws.write_row(current_row, 0, [phone_number, iccid], text_format)
            print(f'Nummer {phone_number} ICCID {iccid}')
            current_row += 1

    wb.close()


if __name__ == '__main__':
    main()

优化要点 #

• 提升效率：将Excel数据提前加载一次并构建字典映射，避免原代码中循环读取Excel的冗余操作，大幅提升处理速度。
• 保留未匹配项：遍历每个ICCID时，使用字典的get方法，未找到匹配值时自动返回not found，确保所有ICCID都被写入结果。
• 修复缺失依赖：补充了原代码中缺失的tkinter和filedialog导入，避免运行报错。
• 优化表头写入：表头仅在初始化时写入一次，避免原代码中循环重复写入表头的问题。
• 格式统一：保持文本格式为字符串，避免手机号或ICCID被识别为数字导致格式错误。

内容的提问来源于stack exchange，提问作者user385411