文本内容如何插入excel表格中的一列，如其他列空自动填充！

主要功能是从文本文件中读取内容，并插入到Excel文件的特定列中（这里假设是第三列，名称为’Column3’）。以下是代码功能的具体描述：

创建一个Tk窗口，布局包含两个标签分别对应Excel文件路径和文本文件路径，以及两个输入框和两个浏览按钮让用户选择文件路径。

定义load_excel_file和load_text_file函数，分别用于打开文件对话框并选择Excel文件和文本文件。所选文件路径会显示在相应的输入框中。

定义insert_text_to_excel函数，此函数首先检查用户是否同时选择了Excel文件和文本文件。然后，它从文本文件中读取内容并去除空行。接着读取Excel文件内容并存储为一个Pandas DataFrame对象。

函数根据文本内容的数量调整DataFrame的行数，并清空第三列（假设列名为’Column3’）的内容。然后逐行将文本内容插入到第三列。

使用填充前一个非空值的方式填充DataFrame中所有的NaN值。

如果调整后的DataFrame行数超过文本内容的数量，则裁剪DataFrame以保持与文本内容相同的行数。

弹出一个保存对话框让用户选择保存路径，并以Excel文件格式保存修改后的数据。最后，显示一条成功消息框，显示已保存的新Excel文件路径。

启动Tkinter的主循环，等待用户交互并执行相应操作。

完整可以执行的代码如下：

import tkinter as tk
from tkinter import filedialog, messagebox
import pandas as pd


def load_excel_file():
    excel_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx;*.xls")])
    if excel_path:
        excel_entry.delete(0, tk.END)
        excel_entry.insert(0, excel_path)


def load_text_file():
    text_path = filedialog.askopenfilename(filetypes=[("Text files", "*.txt")])
    if text_path:
        text_entry.delete(0, tk.END)
        text_entry.insert(0, text_path)


def insert_text_to_excel():
    excel_path = excel_entry.get()
    text_path = text_entry.get()

    if not excel_path or not text_path:
        messagebox.showerror("Error", "Please select both Excel and Text files.")
        return

        # Read the text content, removing empty lines
    with open(text_path, 'r', encoding='utf-8') as f:
        text_content = [line.strip() for line in f if line.strip()]

        # Read the Excel file
    df = pd.read_excel(excel_path)
    original_length = len(df)

    # If text_content is longer than the DataFrame, add rows to the DataFrame
    if len(text_content) > original_length:
        df = pd.concat([df, pd.DataFrame(index=range(original_length, len(text_content)), columns=df.columns)])

        # Clear column C (except for the header), assuming it's named 'Column3' or adjust accordingly
    df.iloc[1:, 2] = ''

    # Insert text into column C
    for i, text in enumerate(text_content, start=1):
        df.iloc[i - 1, 2] = text

        # Fill NaN values in all columns with the value from the previous row
    df.fillna(method='ffill', inplace=True)

    # Truncate DataFrame if it's longer than the original text_content
    if len(df) > len(text_content):
        df = df[:len(text_content)]

        # Ask for a save path and save the modified Excel file
    save_path = filedialog.asksaveasfilename(defaultextension='.xlsx',
                                             filetypes=[("Excel files", "*.xlsx"), ("All files", "*.*")])
    if save_path:
        if not save_path.endswith('.xlsx'):
            save_path += '.xlsx'
        df.to_excel(save_path, index=False)
        messagebox.showinfo("Success", f"File saved to {save_path}")

    # Create Tk window


root = tk.Tk()
root.title("Excel Text Insertion Tool")
root.geometry("600x200")  # Adjust the size as needed

# Row 1: Excel file path and button
tk.Label(root, text="Excel File Path:").grid(row=0, column=0, sticky="e")
excel_entry = tk.Entry(root, width=50)
excel_entry.grid(row=0, column=1)
load_excel_button = tk.Button(root, text="Browse...", command=load_excel_file)
load_excel_button.grid(row=0, column=2)

# Row 2: Text file path and button
tk.Label(root, text="Text File Path:").grid(row=1, column=0, sticky="e")
text_entry = tk.Entry(root, width=50)
text_entry.grid(row=1, column=1)
load_text_button = tk.Button(root, text="Browse...", command=load_text_file)
load_text_button.grid(row=1, column=2)

# Row 3: Insert text and save button
insert_button = tk.Button(root, text="Insert Text and Save As...", command=insert_text_to_excel)
insert_button.grid(row=2, column=1, pady=10)  # Add some padding to separate from the above row

# Start the Tkinter event loop
root.mainloop()```

展示结果如下

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/0ae877c85a9d473d992c6568e527a3f8.png)