当前位置 博文首页 > 广大菜鸟的博客:Python读写excel和txt格式文件交互
今天在网上学习,看到python读写excel有4种方法,并各有优劣
参考资料:
用Python读写Excel文件的方式比较
import os.path
import xlrd
import xlwt
import pandas as pd
import numpy as np
# fileName = r'C:\Users\Lenovo\Desktop\1.txt'
def readFromTxt(filename):
excel_list = []
if not os.path.isfile(filename):
print(filename + "does not exit")
else:
try:
infile = open(filename, "r")
line = infile.readline()
while line != '': # 这里不是空格
print(line)
tmp = line.split()
tmp = [eval(i) for i in tmp] # 转换成整数
excel_list.append(tmp)
line = infile.readline()
except:
print("Something wrong happens")
finally:
infile.close()
return excel_list
def writeToTxt(filename, excel_list):
outFile = open(filename, "w")
for row in range(len(excel_list)):
for col in range(len(excel_list[row])):
outFile.write(str(excel_list[row][col]))
outFile.write(" ")
outFile.write("\n")
outFile.close()
# data1.xls
def readFromExcelByXlrd(filename, sheetName='Sheet1'):
excel_list = []
try:
book = xlrd.open_workbook(filename)
sheet1 = book.sheet_by_name(sheetName)
nrows = sheet1.nrows
print('表格总行数', nrows, end=", ")
ncols = sheet1.ncols
print('表格总列数', ncols)
for i in range(nrows):
row = sheet1.row_values(i)
excel_list.append(row)
for lst in excel_list:
for data in lst:
print(data, end=" ")
print('\n')
except Exception as e:
print(str(e))
finally:
return excel_list
def writeToExcelByXlwt(fileName, excel_list, sheetName='Sheet1'):
# # 创建一个workbook 设置编码
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet(sheetName, True)
for row in range(len(excel_list)):
for col in range(len(excel_list[row])):
sheet.write(row, col, excel_list[row][col])
book.save(fileName)
# # data1.xls
def readFromExcelByPandas(filename, sheetName='Sheet1'):
excel_list = []
try:
df = pd.read_excel(filename, sheet_name=sheetName, header=None)
print('表格总行数', df.shape[0], '表格总列数', df.shape[1])
# 查看某一列: print(df['标题'].values)
# 查看某一行: print(df.values[0])
excel_list = df.values.tolist()
for lst in excel_list:
for data in lst:
print(data, end=" ")
print('\n')
except Exception as e:
print(str(e))
finally:
return excel_list
def writeToExcelByPandas(filename, excel_list, sheetName='Sheet1'):
tmp = np.array(excel_list)
keys = tmp[0][:] # 把list数据第一行作为标题
tmp1 = tmp[1:][:] # 把list数据第二行开始作为数据
values = [tmp1[:, i].tolist() for i in range(tmp1.shape[1])]
dict1 = dict(zip(keys, values))
df = pd.DataFrame(dict1)
df.to_excel(filename, sheet_name=sheetName, index=False)
if __name__ == '__main__':
txtFileName = r'C:\Users\Lenovo\Desktop\1.txt'
excelFileName = r'F:\PycharmWorkPlace\机器学习\data1.xls'
excelFileName1 = r'C:\Users\Lenovo\Desktop\data2.xls'
excel_list = readFromExcelByPandas(excelFileName)
writeToTxt(txtFileName,excel_list)
writeToExcelByPandas(excelFileName1,excel_list)
背景:有个师兄想保存某个网站所有的数据记录,但数据过多,一个一个复制是不可能的,于是叫我帮他写一个,我的想法是:
先把网站数据全部复制到txt文件内,然后用程序读到excel内
选择方式:手动输入文件完整路径或可以打开文件夹的方式选择读写文件目录
(但我感觉python应该可以自动化脚本从网站直接爬取,省了手动复制到txt,但是考虑到那样要帮他爬数据,我想想时间有限,还是算了)
import os.path
import pandas as pd
import tkinter
from tkinter import Tk
from tkinter.filedialog import askopenfilename
def readFromTxt(fileName) -> '读取数据':
if not os.path.isfile(fileName):
print(fileName + "does not exit")
return
df = pd.read_table(fileName)
return df
def writeToExcel(fileName, df, Header=False):
if os.path.isfile(fileName):
choose = input(fileName + " has already exits.Would you wanna overwrite it?Please input Y to continue").strip()
if choose != 'Y' and choose != 'y':
return
df.to_excel(fileName, index=False, header=Header)
def main():
choice = input("Would you wanna input fileName or select by yourself?Please input choice between L and R")
selectMode = False
if choice == 'L' or choice == 'l':
print("Now select the absolute path of the *.txt you wanna copy")
selectMode = True
window = Tk()
window.withdraw() # 除掉小框
fileName = askopenfilename(defaultextension=".txt",
title='Select the .txt file',
filetypes=[('TXT', '*.txt'), ('All Files', '*')], initialdir='C:\\Windows')
else:
fileName = input("Input the absolute path of *.txt file:").strip()
df = readFromTxt(fileName)
if df is not None:
if not selectMode:
fileName1 = input("Input the absolute path of *.xls file:").strip()
else:
print("Now select the absolute path of the *.xls file you wanna save")
window = Tk()
window.withdraw() # 除掉小框
fileName1 = tkinter.filedialog.asksaveasfilename(title=u'Save as *.xls file', filetypes=[("XLS", ".xls")],
initialdir='C:\\Windows')
if not fileName1.endswith(".xls"):
fileName1 += ".xls"
choose =