Python脚本：将文本a.txt(支持任意格式)中的内容，以空格(支持任意字符)为列边界，按行输出到excel格式的文本b.xlsx。使用Cambria字体(可调)，根据每列内容的长度动态调整列宽。

python script:

excel_extract.py

注意：只需将以下代码中的split()稍作修改，即可支持以任意字符为列边界，进行excel的列分割。

比如以'----'为边界，那么data = [line.split('----') for line in lines]

比如以'>'为边界，那么data = [line.split('>') for line in lines]

比如以','为边界，那么data = [line.split(',') for line in lines]

比如以';'为边界，那么data = [line.split(';') for line in lines]

import pandas as pd
import openpyxl
from openpyxl.styles import Font, Border, Side

# 读取文本文件
input_file = "./a.txt"
with open(input_file, 'r') as file:
    lines = file.readlines()

# 处理每行的内容
data = [line.split() for line in lines]

# 创建一个DataFrame
df = pd.DataFrame(data)

# 将DataFrame写入Excel文件
output_excel = "./b.xlsx"
with pd.ExcelWriter(output_excel, engine='openpyxl') as writer:
    df.to_excel(writer, index=False, sheet_name='Sheet1')

    # 设置字体格式为Cambria
    worksheet = writer.sheets['Sheet1']
    font = Font(name='Cambria')
    for col in worksheet.columns:
        for cell in col:
            cell.font = font

            # 添加外边框
            cell.border = Border(top=Side(style='thin'), bottom=Side(style='thin'), left=Side(style='thin'), right=Side(style='thin'))

    # 动态调整列宽
    for col in worksheet.columns:
        max_length = 0
        column = col[0].column_letter  # 获取列名
        for cell in col:
            try:  # 防止空单元格引发异常
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 2)
        worksheet.column_dimensions[column].width = adjusted_width

例子：

1. a.txt 内容如下：

Hierarchical             Absolute    Percent  
half_top                   720688    100.0  
u_half0                     44999     45.0  
u_half0/glag_c6                10      1.0  
u_half0/glag_c6_               00      1.0  
u_half0/glag_c6_               10      1.0  
u_half0/glag_r6_               10      1.0  
u_half0/glag_r6_               10      1.0  
u_half0/glag_s6a               10      1.0  
u_half0/glag_y60               10      1.0  
u_half0/u_lagf0              2990      0.9  
u_half0/u_lagf1              2440      0.4  
u_half0/u_lagf2              2510      0.4  
u_half0/u_lagf3              2550      0.4  
u_half0/u_iulg0              1910      0.9  
u_half0/u_iulg1              1910      0.9  
u_half0/u_iulg2              1950      0.9  
u_half0/u_iulg9              1950      0.9  
u_half0/u_half2_0            1090      0.2  
u_half0/u_half2_1            1090      0.2  
u_half0/u_half2_2            1090      0.2  
u_half0/u_half2_9            1090      0.2  
u_half0/u_half4_0            7290      1.1  
u_half0/u_half4_1            7240      1.1  
u_half0/u_halft_0           29770      9.5  
u_half0/u_halft_1           29780      9.5  
u_half0/u_halft_2           29750      9.4  
u_half0/u_halft_9           29740      9.4  
u_half0/u_halflag_0          7670      1.1  
u_half0/u_halflag_1         17650      2.6  
u_half0/u_halflag_2         11680      1.7  
u_half0/u_halflag_9         17840      2.6  
 

2.运行python excel_extract.py结果如下：

注意：请自行添加pandas和openpyxl库，处理excel表格需要