Python读写excel和txt格式文件交互

今天在网上学习，看到python读写excel有4种方法，并各有优劣

• XlsxWriter只能用来写文件,不支持XLS格式文件
• OpenPyXL所支持的功能介于XlsxWriter和xlwt之间,不支持XLS格式文件
• xlrd&xlwt,支持XLS,XLSX格式
• 用pandas进行excel读写，比xlrd&xlwt快且简单

参考资料：
用Python读写Excel文件的方式比较

excel和txt格式互换基本

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文件

背景：有个师兄想保存某个网站所有的数据记录，但数据过多，一个一个复制是不可能的，于是叫我帮他写一个，我的想法是:
先把网站数据全部复制到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 = input('Does the *.txt file contains header?Please input Y(y) or other character to continue').strip()
        if choose == 'Y' or choose == 'y':
            writeToExcel(fileName1, df, True)
        else:
            writeToExcel(fileName1, df)
        print("Succeed copy datas!")
    else:
        print("Failed copy datas to the excel file.")


if __name__ == '__main__':
    main()