时间戳

首页 > 解决方案 > Python：在 Excel 中将列表写入一行

python - Python：在 Excel 中将列表写入一行

问题描述

我正在尝试从文本文件中读取数据，创建一个包含该文本文件中所有内容的长字符串，然后将其按空格分割。然后，我尝试将该列表按空格分割发送到printStringArray并打印到 Excel 工作表。我无法弄清楚如何跟踪数据应输入的行和列。

rowNum = 1

def createStringArray(theFilePath):
    theFinalString = ""
    with open(theFilePath) as file_in:
        for line in file_in:
            lineToString = str(line)
            theCompleteString = lineToString.split()
            printStringArray(theCompleteString)


def printStringArray(theStringArray):
    with xlsxwriter.Workbook('testingThis.xlsx') as workbook:
        worksheet = workbook.add_worksheet()
        colNum = 1
        global rowNum
        for data in theStringArray:
            worksheet.write(rowNum, colNum, data)
            colNum = colNum + 1
    rowNum = rowNum + 1

编辑：

def printStringArray(theStringArray):
    with xlsxwriter.Workbook('testingThis.xlsx') as workbook:
        worksheet = workbook.add_worksheet()
        colNum = 0
        global rowNum
        for data in theStringArray:
            worksheet.write(rowNum, colNum, str(data))
            print(rowNum)
            print(colNum)
            print(data)
            colNum = colNum + 1
    rowNum = rowNum + 1

我添加了一些prints以查看哪里出错了，但是所有数字都正是我希望它们用于rowNum、colNum和data的。现在它只打印最后一行。在此处输入图像描述

编辑#2

rowNum = 0
with xlsxwriter.Workbook('testingThis.xlsx') as workbook:
    worksheet = workbook.add_worksheet()

def createStringArray(theFilePath):
    theFinalString = ""
    with open(theFilePath) as file_in:
        for line in file_in:
            lineToString = str(line)
            theCompleteString = lineToString.split()
            printStringArray(theCompleteString)
            for aString in theCompleteString:
                theFinalString = theFinalString + aString + "--"
    print(theFinalString)


def printStringArray(theStringArray):
    colNum = 0
    global rowNum
    worksheet.write(15, 15, "Aapple")
    for data in theStringArray:
        worksheet.write(rowNum, colNum, str(data))
        print(rowNum)
        print(colNum)
        print(data)
        colNum = colNum + 1
    rowNum = rowNum + 1

标签： pythonxlsxwriter

解决方案

这是我读取文本文件并将内容写入 Excel 工作表的尝试，也许这会有所帮助。

我使用write_row()函数将字符串列表写入一行。这样我就不需要照顾正确的列号。

import xlsxwriter

#  read textfile line by line
with open(path2txt, 'r') as txtfile:
    lines_raw = txtfile.readlines()

#  remove newline and whitespace on every line
lines = [line.strip() for line in lines_raw]

#  write lines to worksheet, 
#  here we use "write_row()" to write the list of words column by column
with xlsxwriter.Workbook('test.xlsx') as workbook:
    sheet = workbook.add_worksheet()
    row, col = 0, 0
    for line in lines:
        parts = line.split()
        if len(parts)>0:  # only write nonempty lines
            sheet.write_row(row, col, parts)
            row += 1

推荐阅读