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

1、使用pandas保存到Excel文件

from pandas import DataFrame
l1 = [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 xlwt

def 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 openpyxl
wb = 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 Workbook

def 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)