在excel中将文本文件中的列写入单独的表中

I am writing a code that converts .txt file to .xls file and I need for every 4th column of my .txt file to go into, say "Sheet 1" and every third file to go in, say "Sheet 2" and so forth. The text file has headers in the first 2 rows, and the actual data starts in the 3rd column. I am not sure how do this and any guidance or help would be very appreciated. Here is my current code:

我正在编写一个将。txt文件转换成。xls文件的代码，我需要把。txt文件的每4列都转换成“表1”，每输入第三个文件，比如“表2”等等。文本文件的头两行有标题，实际数据从第三列开始。我不知道如何做这和任何指导或帮助将非常感谢。以下是我目前的代码:

import xlwt
import six
import itertools

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

def txt_to_xls():
    f=open('textfile.txt', 'r+')
    row_list=[]
    for row in f:
        row_list.append(row.split())
    column_list=map(list, siz.moves.zip_longest(*row_list, fillvalue=' '))
    workbook=xlwt.Workbook()
    worksheet=workbook.add_sheet('Sheet 1')

    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.save('test.xls')

1 个解决方案

#1

Have you considered using Visual Basic for Applications? It's basically a language designed for manipulating excel spreadsheets. Here's some sample code that I think would accomplish what you're describing:

您是否考虑过在应用程序中使用Visual Basic ?它基本上是一种用于操作excel电子表格的语言。这里有一些示例代码，我认为可以完成您所描述的:

Sub WriteToFile()
    Dim FileNum As Integer
    Dim DataLine As String
    Dim Counter As Integer

    Counter = 0

    FileNum = FreeFile()
    Open "C:\Users\...\yourDocument.txt" For Input As #FileNum


    While Not EOF(FileNum)
        Line Input #FileNum, DataLine
        Counter = Counter + 1

        If Counter > 2 Then
            Dim splitString As Variant
            splitString = Split(DataLine, " ")
            Worksheets("Sheet1").Cells(Counter - 2, 1).Value = splitString(3)
            Worksheets("Sheet2").Cells(Counter - 2, 1).Value = splitString(2)
        End If
    Wend
End Sub

Here's a good link on how to get started with VBA in excel https://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx To reformat this for exactly what you need, you can change the path to yourDocument.txt to go to your txt file, and rename Sheet1 and Sheet2 to whatever the names are of the sheets you want. Also, you can add a few more lines like them if you want to add stuff to other sheets or to different columns in the same sheets. Additionally, based on what your delimiter is, you should change the second parameter in the Split() method. I think the syntax is fairly self explanatory, but there's plenty of documentation online if you get stuck. Hope this helps! Also I based the file IO here off of this other post here Read/Parse text file line by line in VBA if you want to look into that more.

这里有一个关于如何在excel中使用VBA的很好的链接:https://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14)。txt将转到您的txt文件，并将Sheet1和Sheet2重命名为您想要的表的名称。此外，如果您想向其他表或同一表中的不同列添加内容，您还可以添加一些类似于它们的行。此外，根据您的分隔符是什么，您应该更改Split()方法中的第二个参数。我认为语法是很容易理解的，但是如果您陷入困境，可以在网上找到大量的文档。希望这可以帮助!另外，我把文件IO从这里的另一篇文章中删除，如果你想深入了解的话，可以在VBA中逐行读取/解析文本文件。