python 如何将txt与excel 进行相互转换（.xls、.xlsx）
Python处理03版xls的excel 需要用到xlrd、xlwt来读写，而处理07版xlsx的excel需要用到openpyxl，首先读取txt内容，写入xlsx中，然后在读取xlsx中的内容。
python 将txt文件转换为excel文件（.xls；.xlsx）
# -*- encoding: utf-8 -*-
import xlwt #需要的模块
def txt_xls(filename,xlsname):
"""
:文本转换成xls的函数
:param filename txt文本文件名称、
:param xlsname 表示转换后的excel文件名
"""
try:
f = open(filename)
xls=xlwt.Workbook()
#生成excel的方法，声明excel
sheet = xls.add_sheet("sheet1",cell_overwrite_ok=True)
x = 0
while True:
#按行循环，读取文本文件
line = f.readline()
if not line:
break #如果没有内容，则退出循环
for i in range(len(line.split("\t"))):
item=line.split("\t")[i]
sheet.write(x,i,item) #x单元格经度，i 单元格纬度
x += 1 #excel另起一行
f.close()
xls.save(xlsname) #保存xls文件
except:
raise
if __name__ == "__main__" :
filename = "G:/test.txt"
xlsname = "G:/test.xls"
txt_xls(filename,xlsname)
python 将excel文件转换为txt文件（.xls；.xlsx）
#-*- coding:UTF-8 -*-
import xlrd
def strs(row):
"""
:返回一行数据
"""
try:
values = "";
for i in range(len(row)):
if i == len(row) - 1:
values = values + str(row[i])
else:
#使用“，”逗号作为分隔符
values = values + str(row[i]) + ","
return values
except:
raise
def xls_txt(xls_name,txt_name):
"""
:excel文件转换为txt文件
:param xls_name excel 文件名称
:param txt_name txt 文件名称
"""
try:
data = xlrd.open_workbook(xls_name)
sqlfile = open(txt_name, "a")
table = data.sheets()[0] # 表头
nrows = table.nrows # 行数
#如果不需跳过表头，则将下一行中1改为0
for ronum in range(1, nrows):
row = table.row_values(ronum)
values = strs(row) # 条用函数，将行数据拼接成字符串
sqlfile.writelines(values) #将字符串写入新文件
sqlfile.close() # 关闭写入的文件
except:
pass
if __name__ == "__main__":
xls_name = "G:/test.xls"
txt_name = "G:/test.txt"
xls_txt(xls_name,txt_name)
Python读取txt内容写入xlsx格式的excel中
# -*- coding: utf-8 -*-
"""
QQ2737499951
"""
import openpyxl
import codecs
from openpyxl.utils import get_column_letter
def txt_to_xlsx(filename,outfile):
fr = codecs.open(filename,"r")
wb = openpyxl.Workbook()
ws = wb.active
ws = wb.create_sheet()
ws.title = "Sheet1"
row = 0
for line in fr:
row +=1
line = line.strip()
line = line.split("\t")
col = 0
for j in range(len(line)):
col +=1
#print (line[j])
ws.cell(column = col,row = row,value = line[j].format(get_column_letter(col)))
wb.save(outfile)
#读取xlsx内容
def read_xlsx(filename):
#载入文件
wb = openpyxl.load_workbook(filename)
#获取Sheet1工作表
ws = wb.get_sheet_by_name("Sheet1")
#按行读取
for row in ws.rows:
for cell in row:
print (cell.value)
#按列读
for col in ws.columns:
for cell in col:
print (cell.value)
if __name__=="__main__":
inputfileTxt = "test.txt"
outfileExcel = "text_result.xlsx"
txt_to_xlsx(inputfileTxt,outfileExcel)
read_xlsx(outfileExcel)
