Python 保存数据到Excel文件的方法(pandas、xlwt、openpyxl、xlsxwriter)

1、使用pandas保存到Excel文件

from pandas import DataFramel1 = [1,2,3,4]l2 = [1,2,3,4]df = DataFrame({'序号': l1, '值': l2})df.to_excel('test.xlsx', sheet_name='sheet1', index=False)

2、使用xlwt保存到Excel文件

import xlwtdef output(filename, sheet, list1, list2, x, y, z):    book = xlwt.Workbook()    sh = book.add_sheet(sheet)    variables = [x, y, z]    x_desc = 'Display'    y_desc = 'Dominance'    z_desc = 'Test'    desc = [x_desc, y_desc, z_desc]    col1_name = 'Stimulus Time'    col2_name = 'Reaction Time'    for n, v_desc, v in enumerate(zip(desc, variables)):        sh.write(n, 0, v_desc)        sh.write(n, 1, v)    n+=1    sh.write(n, 0, col1_name)    sh.write(n, 1, col2_name)    for m, e1 in enumerate(list1, n+1):        sh.write(m, 0, e1)    for m, e2 in enumerate(list2, n+1):        sh.write(m, 1, e2)    book.save(filename)

3、使用openpyxl保存到Excel文件

import openpyxlwb = openpyxl.load_workbook('example.xlsx')sheet = wb.get_sheet_by_name('Sheet1')stimulusTimes = [1, 2, 3]reactionTimes = [2.3, 5.1, 7.0]for i in range(len(stimulusTimes)):    sheet['A' + str(i + 6)].value = stimulusTimes[i]    sheet['B' + str(i + 6)].value = reactionTimes[i]wb.save('example.xlsx')

4、使用xlsxwriter保存到Excel文件

import xlsxwriter# 创建一个新的Excel文件并添加一个工作表。=workbook = xlsxwriter.Workbook('demo.xlsx')worksheet = workbook.add_worksheet()# 加宽第一列，使正文更清楚worksheet.set_column('A:A', 20)# 添加用于突出显示单元格的粗体格式。bold = workbook.add_format({'bold': True})# 写一些简单的文本worksheet.write('A1', 'Hello')# 文本格式。worksheet.write('A2', 'World', bold)# 用行/列表示法写一些数字worksheet.write(2, 0, 123)worksheet.write(3, 0, 123.456)# 插入一个图像worksheet.insert_image('B5', 'logo.png')workbook.close()

或者

from xlsxwriter import Workbookdef create_xlsx_file(file_path: str, headers: dict, items: list):    with Workbook(file_path) as workbook:        worksheet = workbook.add_worksheet()        worksheet.write_row(row=0, col=0, data=headers.values())        header_keys = list(headers.keys())        for index, item in enumerate(items):            row = map(lambda field_id: item.get(field_id, ''), header_keys)            worksheet.write_row(row=index + 1, col=0, data=row)headers = {    'id': 'User Id',    'name': 'Full Name',    'rating': 'Rating',}items = [    {'id': 1, 'name': "Ilir Meta", 'rating': 0.06},    {'id': 2, 'name': "Abdelmadjid Tebboune", 'rating': 4.0},    {'id': 3, 'name': "Alexander Lukashenko", 'rating': 3.1},    {'id': 4, 'name': "Miguel Díaz-Canel", 'rating': 0.32}]create_xlsx_file("my-xlsx-file.xlsx", headers, items)