main.py

import pandas as pd
from lib.func import copy_and_rename_file, modify_cell_value


def read_income_data(file_path):
    df = pd.read_excel(file_path, sheet_name="收入明细", skiprows=4, index_col=0, dtype=str)
    # df = df[:-1]  # 去除最后一行数据
    row_lists = df.values.tolist()
    return row_lists


# 请将以下路径替换为您的实际文件路径
file_path = "俩表对比.xls"
data_lists = read_income_data(file_path)
print(data_lists)

for row_list in data_lists:
    source_file = "模板.xlsx"
    destination_folder = "生成"
    new_file_name = f"{row_list[0]}.xlsx"

    copy_and_rename_file(source_file, destination_folder, new_file_name)

    file_path = f"生成/{row_list[0]}.xlsx"
    sheet_name = "人均收入测算表"
    cell_reference = "A2"
    new_value = f"户主：     {row_list[0]}\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000户类型：脱贫户□   防贫监测户□\n \n当前家庭人口数： {row_list[1]}\u3000\u3000\u3000年度家庭人口数  {row_list[1]} \u3000\u3000\u3000\u3000\u3000\u3000调查时间：2024年\u3000\u3000月\u3000\u3000日"
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 公益岗
    cell_reference = "B5"
    new_value = row_list[18]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 最低生活保障金
    cell_reference = "B15"
    new_value = row_list[3]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 特困人员救助供养金
    cell_reference = "B16"
    new_value = row_list[4]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 养老金或离退休金
    cell_reference = "B17"
    new_value = row_list[5]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 计划生育金
    cell_reference = "B22"
    new_value = row_list[6]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 生态补偿金
    cell_reference = "B23"
    new_value = row_list[7]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 产业奖补
    cell_reference = "B24"
    new_value = row_list[10]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 交通补助
    cell_reference = "B25"
    new_value = row_list[11]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 孝心养老奖补
    cell_reference = "B27"
    new_value = row_list[13]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 残疾人补贴
    cell_reference = "D6"
    new_value = row_list[8]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 转移性收入
    cell_reference = "D8"
    new_value = row_list[22]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)
    # 非确权到户的资产收益分红
    cell_reference = "D9"
    new_value = row_list[15]
    modify_cell_value(file_path, sheet_name, cell_reference, new_value)

lib\func.py

import shutil
import os


def copy_and_rename_file(source_file, destination_folder, new_file_name):
    """
    复制文件到指定文件夹并以新名称命名

    参数：
    source_file (str)：源文件的路径
    destination_folder (str)：目标文件夹的路径
    new_file_name (str)：新的文件名

    返回：
    None
    """
    # 检查目标文件夹是否存在，如果不存在则创建
    if not os.path.exists(destination_folder):
        os.makedirs(destination_folder)

    # 构建目标文件路径
    destination_file = os.path.join(destination_folder, new_file_name)

    # 复制文件
    shutil.copy(source_file, destination_file)

# source_file = "模板.xlsx"
# destination_folder = "test"
# new_file_name = "123.xlsx"
#
# copy_and_rename_file(source_file, destination_folder, new_file_name)


def modify_cell_value(file_path, sheet_name, cell_reference, new_value):
    workbook = load_workbook(file_path)
    sheet = workbook[sheet_name]
    sheet[cell_reference] = new_value
    workbook.save(file_path)


from openpyxl import load_workbook
from openpyxl.styles import Font


def underline_partial_text(file_path, sheet_name, cell_reference, start_index, end_index):
    workbook = load_workbook(file_path)
    sheet = workbook[sheet_name]
    cell = sheet[cell_reference]

    # 获取原始文本
    original_text = cell.value

    # 拆分文本为三部分：前缀、要加下划线的部分、后缀
    prefix = original_text[:start_index]
    underlined_part = original_text[start_index:end_index]
    suffix = original_text[end_index:]

    # 创建新的单元格内容，将下划线部分用特殊标记包裹
    new_text = prefix + f"__{underlined_part}__" + suffix

    # 设置单元格的值为新的文本
    cell.value = new_text

    # 定义一个字体样式，用于识别特殊标记并添加下划线
    underline_font = Font(underline='single')

    def set_underline(cell, text):
        if text.startswith("__") and text.endswith("__"):
            cell.font = underline_font
            return text[2:-2]
        return text

    # 遍历单元格内的每个字符
    new_value = ""
    for char in new_text:
        new_value += set_underline(cell, char)

    cell.value = new_value

    workbook.save(file_path)

VBA(添加下划线)，有意思的是给EXCEL同一个单元格中的部分文字添加下划线python竟然无法实现，这里智能使用VBA了

Sub UnderlinePartInAllWorkbooks()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim wb As Workbook
    Dim ws As Worksheet

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("E:\WorkPro\扶贫填表\生成")  '修改为您的文件夹路径

    For Each file In folder.Files
        If Right(file.Name, 5) = ".xlsx" Or Right(file.Name, 4) = ".xls" Then
            Set wb = Workbooks.Open(file.Path)
            Set ws = wb.Sheets(1)
            With ws.Range("A2").Characters(4, 12).Font  '户名
             .Underline = True
            End With
            With ws.Range("A1").Characters(1, 3).Font  '五指山
             .Underline = True
            End With
            With ws.Range("A2").Characters(58, 3).Font  '当前家庭人口数
             .Underline = True
            End With
            With ws.Range("A2").Characters(71, 3).Font  '庭人口数
             .Underline = True
            End With
            wb.Close SaveChanges:=True
        End If
    Next file
End Sub