Python生成Excel文件的三种方法

分手后的思念是犯贱 2023-08-17 15:22 141阅读 0赞

### Python生成Excel文件的三种方法 ###

1.  类库xlwt/xlrd

*  用于生成和读取比较老的excel文件，比如xls格式，最大行数限制为65536行
 *  文档地址：[https://xlwt.readthedocs.io/en/latest/][https_xlwt.readthedocs.io_en_latest]

1.  类库openpyxl

*  用于生成2010之后新的excel文件，比如xlsx格式
 *  文档地址：[https://openpyxl.readthedocs.io/en/stable/][https_openpyxl.readthedocs.io_en_stable]

1.  类库pandas

*  pandas是最强大的数据分析库，自带excel读取和生成模块
 *  文档地址：[https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to\_excel.html][https_pandas.pydata.org_pandas-docs_stable_reference_api_pandas.DataFrame.to_excel.html]

#### 0、读取txt文件 ####

columns = []
    datas = []
    with open("./pvuv.txt") as fin:
        is_first_line = True
        for line in fin:
            line = line[:-1]
            if is_first_line:
                is_first_line = False
                columns = line.split("\t")
                continue
            
            datas.append(line.split("\t"))

#### 1、使用xlwt生成xls的excel文件 ####

# pip install xlwt
    import xlwt
    workbook = xlwt.Workbook(encoding='utf-8')
    sheet = workbook.add_sheet('pvuv_sheet')
    
    # 写入标题
    for col,column in enumerate(columns):
        sheet.write(0, col, column)
    # 写入每一行
    for row, data in enumerate(datas):
        for col, column_data in enumerate(data):
            sheet.write(row+1, col, column_data)
    
    workbook.save('./output/pvuv_xlwt.xls')

#### 2、使用openpyxl生成xlsx的excel文件 ####

# pip install openpyxl
    from openpyxl import Workbook
    workbook = Workbook()
    
    # 默认sheet
    sheet = workbook.active
    sheet.title = "默认sheet"
    # sheet = workbook.create_sheet(title="新sheet")
    sheet.append(columns)
    for data in datas:
        sheet.append(data)
    
    workbook.save('./output/pvuv_openpyxl.xlsx')

#### 3、使用pandas生成excel文件 ####

# pip install pandas
    import pandas as pd
    # 读取文本文件
    data = pd.read_csv("./pvuv.txt", sep="\t")
    data.to_excel("./output/pvuv_pandas.xls", index=False)

[https_xlwt.readthedocs.io_en_latest]: https://xlwt.readthedocs.io/en/latest/
[https_openpyxl.readthedocs.io_en_stable]: https://openpyxl.readthedocs.io/en/stable/
[https_pandas.pydata.org_pandas-docs_stable_reference_api_pandas.DataFrame.to_excel.html]: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html