Python 如何从Excel中读取数据并将其逐行写入文本文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17977584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read data from Excel and write it to text file line by line?
提问by user2639171
I want to write code to get data from Excel and write it to a text file. Here is the code I have:
我想编写代码以从 Excel 获取数据并将其写入文本文件。这是我的代码：
import xlrd
import os.path
wb = xlrd.open_workbook(os.path.join('D:\TRB 2014 Data','SPS1 demo data.xlsx'))
wb.sheet_names()
sh = wb.sheet_by_index(0)
i = 1
while sh.cell(i,11).value != 0:
Load = sh.cell(i,11).value
D1 = sh.cell(i,13).value
D2 = sh.cell(i,14).value
D3 = sh.cell(i,15).value
D4 = sh.cell(i,16).value
D5 = sh.cell(i,17).value
D6 = sh.cell(i,18).value
D7 = sh.cell(i,19).value
DB1 = str(Load) + " " + str(D1) + " " + str(D2) + " " + str(D3)+ " " + str(D4)+ " " + str(D5)+ " " + str(D6)+ " " + str(D7)
file = open("Output.txt", "w")
file.write(DB1 + '\n')
file.close
i = i + 1
The problem with this code is the data written to the text file is always displayed at the first row. Therefore, although I have 20 rows of data in excel, the text file only shows the last data in the excel file at the very first row in the text file. I have '\n'in file.writebut, it doesn't seem to work.
这段代码的问题是写入文本文件的数据总是显示在第一行。因此，虽然我在excel中有20行数据，但文本文件只在文本文件的第一行显示了excel文件中的最后一个数据。我有'\n'，file.write但似乎不起作用。
采纳答案by Nicolás Ozimica
You should openthe output.txtfile with append mode:
你应该打开了output.txt与文件append mode：
file = open("Output.txt", "a")
Also, you should do this before entering the loop, and it should be closed after that loop.
此外，您应该在进入循环之前执行此操作，并且应该在该循环之后关闭。
Update:
更新：
In cases like this one, you could use withinstead of closing the file handle at the end.
在这种情况下，您可以使用with而不是最后关闭文件句柄。
Also including the good suggestion made by @Josh in his own answer, the code could be this:
还包括@Josh 在他自己的回答中提出的好建议，代码可能是这样的：
import xlrd
import os.path
wb = xlrd.open_workbook(os.path.join('D:\TRB 2014 Data','SPS1 demo data.xlsx'))
wb.sheet_names()
sh = wb.sheet_by_index(0)
i = 1
with open("Output.txt", "a") as my_file:
while sh.cell(i,11).value != 0:
Load = sh.cell(i,11).value
all_d = sh.col_values(i, 13, 19)
DB1 = Load + " " + (" ".join(all_d))
my_file.write(DB1 + '\n')
i += 1
回答by Spaceghost
You are opening and closing your output file for each line in the Excel sheet.
您正在为 Excel 工作表中的每一行打开和关闭输出文件。
Open the file before the while loop and then then close it when done with the loop.
在 while 循环之前打开文件，然后在循环完成后关闭它。
import xlrd
import os.path
wb = xlrd.open_workbook(os.path.join('D:\TRB 2014 Data','SPS1 demo data.xlsx'))
wb.sheet_names()
sh = wb.sheet_by_index(0)
i = 1
file = open("Output.txt", "w")
while sh.cell(i,11).value != 0:
Load = sh.cell(i,11).value
D1 = sh.cell(i,13).value
D2 = sh.cell(i,14).value
D3 = sh.cell(i,15).value
D4 = sh.cell(i,16).value
D5 = sh.cell(i,17).value
D6 = sh.cell(i,18).value
D7 = sh.cell(i,19).value
DB1 = str(Load) + " " + str(D1) + " " + str(D2) + " " + str(D3)+ " " + str(D4)+ " " + str(D5)+ " " + str(D6)+ " " + str(D7)
file.write(DB1 + '\n')
i = i + 1
file.close
回答by scohe001
xlrd actually has a nice function for grabbing the values of a bunch of cells all in a column or row. Using that, you can simplify your code a lot (and I'm pretty sure their function is more efficient). That way your code can become:
xlrd 实际上有一个很好的功能，可以抓取一列或一行中的一堆单元格的值。使用它，您可以大大简化您的代码（我很确定它们的功能更有效）。这样你的代码就可以变成：
import xlrd
import os.path
wb = xlrd.open_workbook(os.path.join('D:\TRB 2014 Data','SPS1 demo data.xlsx'))
wb.sheet_names()
sh = wb.sheet_by_index(0)
i = 1
my_file = open("Output.txt", "a")
while sh.cell(i,11).value != 0:
Load = sh.cell(i,11).value
all_d = sh.col_values(i, 13, 19)
DB1 = Load+" "+(" ".join(all_d))
my_file.write(DB1 + '\n')
i += 1
file.close
回答by Michael
import xlrd
workbook=xlrd.open_workbook("xxx.xls")
sh=workbook.sheet_by_name("test1")
print sh.nrows
print sh.ncols
n=0
i=0
file=open("xxx.txt","w")
for n in range(sh.nrows):
for i in range(sh.ncols):
data =sh.cell_value(n,i)+" "
print data,
file.write(data+" ")
print
file.write("\n")
this code is working properly for writing into text file
