Python TXT 转 Excel (自动识别分隔符)

pip install spire.xls

from spire.xls import *
from spire.xls.common import *

def detect_delimiter(file_path, sample_lines=5):
    """自动检测文本文件中最可能的分隔符"""
    common_delimiters = ["\t", ",", "|", ";", " "]

    with open(file_path, "r") as file:
        sample = [file.readline() for _ in range(sample_lines)]

    delimiter_counts = {}
    for delim in common_delimiters:
        count = sum(line.count(delim) for line in sample)
        if count > 0:
            delimiter_counts[delim] = count

    if not delimiter_counts:
        return "\t"  # 未检测到时默认使用制表符

    return max(delimiter_counts, key=delimiter_counts.get)

# 执行转换
file_path = "Data.txt"
delimiter = detect_delimiter(file_path)
print(f"检测到的分隔符: {repr(delimiter)}")

# 读取并按分隔符拆分数据
with open(file_path, "r") as file:
    lines = file.readlines()
data = [line.strip().split(delimiter) for line in lines]

# 创建Excel工作簿并写入数据
workbook = Workbook()
sheet = workbook.Worksheets[0]

for row_num, row_data in enumerate(data):
    for col_num, cell_data in enumerate(row_data):
        sheet.Range[row_num + 1, col_num + 1].Value = cell_data
        sheet.Range[1, col_num + 1].Style.Font.IsBold = True  # 标题行加粗

sheet.AllocatedRange.AutoFitColumns()  # 自动调整列宽
workbook.SaveToFile("TXTtoExcel.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

姓名|部门|工资|入职日期
张三|技术部|8500|2023-01-15
李四|市场部|9200|2022-11-20