python - 读取多个txt文件，然后将每个文件保存为xlsx文件，每个文件具有相同的标题

Question

我的任务是使用在文本文件中提供给我的 SAP 自定义报告 - 下面的示例(删除了名称并将此示例的所有值设为 0.00)。任务是使用此数据并根据此数据创建 xlsx 文件。我在特定文件夹中有数百个这样的 txt 文件。我如何将其加载到 python 中并创建一个 xlsx 文件，以将每个文件的表头数据保存在表格上方的相同位置/格式中？

我有下面的代码，为每个文件创建 xlsx，但格式全乱了。我需要为每个单元格/列/行提供更好的格式。

非常感谢任何帮助!

当前代码:

import glob
import os
from xlsxwriter import Workbook
filepath = 'mypath'
txtfiles = glob.glob(os.path.join(filepath, '*z*.txt*'))

def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        return False

for filename in txtfiles:
    readfiles = open(filename, 'r') 

    row_list = []

    for row in readfiles:
        row_list.append(row.split('\n'))

    column_list = zip(*row_list)

    workbook = Workbook(filename.replace('.txt', '.xlsx'))
    worksheet = workbook.add_worksheet('Sheet1')

    i = 0
    for column in column_list:
        for item in range(len(column)):
            value = column[item].strip()
            if is_number(value):
                worksheet.write(item, i, float(value))
            else:
                worksheet.write(item, i, value)
        i += 1
    workbook.close()

下面的示例报告:

                                                 SAMPLE REPORT TEMPLATE

Page Number:  1 of   1                                                                         Time of Output:06:37:00
Author of Report:ME                                                                            Date of Output:09/27/2018
Ledger:SAMPLE                                                                                  Version: 1
Currency: USD                                                                                  Fiscal Year:2018
Report Group:RANDOM                                                                            Period:  0 to   10

|.                                                           |    Outside MONEY    | Outside MONEY2      |    Outside MONEY3   |   Subtotal MONIES   |
|------------------------------------------------------------|---------------------|---------------------|---------------------|---------------------|
|   INCOME MONIES BEFORE CERTAIN CALCULATIONS SAMPLE         |                0.00 |                0.00 |                0.00 |                0.00 |
|------------------------------------------------------------|---------------------|---------------------|---------------------|---------------------|
|   1 - Line Data 1                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   1 - Line Data 2                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   1 - Line Data 3                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   1 - Line Data 4                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   1 - Line Data 5                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   2 - Line Data 1                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   2 - Line Data 2                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   2 - Line Data 3                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|*  Sample Random Line W/ Star                               |                0.00 |                0.00 |                0.00 |                0.00 |
|   3 - Line Data 1                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   3 - Line Data 2                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   3 - Line Data 3                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   3 - Line Data 4                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   3 - Line Data 5                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   3 - Line Data 6                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   3 - Line Data 7                                          |                0.00 |                0.00 |                0.00 |                0.00 |
|   3 - Line Data 8                                          |                0.00 |                0.00 |                0.00 |                0.00 |

Answer 1

从文本文件读取并将每一行作为一个项目添加到列表中可以按如下方式完成:

lines = []
with open('input_file.txt', 'r') as textinputfile:
    for readline in textinputfile:
        lines.append(readline)