python实现在excel文件中写入和追加内容

示例代码

# -*- coding: utf-8 -*-
"""
# @Time    : 2023/08/13 18:01
# @Author  : longrong.lang
# @FileName: demo.py
# @Software: PyCharm
# @Blog    ：https://www.cnblogs.com/longronglang/
# @Motto：I am a slow walker , but I never walk backwards.
"""
import xlwt
import xlrd
from xlutils.copy import copy


def write_to_excel(words, filename, sheet_name='sheet1'):
    '''
    将item存储到excel中。
    :param words: 保存item的list    [{},{}]格式
    :return:
    '''
    try:
        # 1、创建工作薄
        work_book = xlwt.Workbook(encoding='utf-8')
        # 2、创建sheet表单
        sheet = work_book.add_sheet(sheet_name)
        # 3、写表头
        # head = ['英文','中文']
        head = []
        for k in words[0].keys():
            head.append(k)

        for i in range(len(head)):
            sheet.write(0, i, head[i])
        # 4、添加内容
        # 行号
        i = 1
        for item in words:
            for j in range(len(head)):
                sheet.write(i, j, item[head[j]])
            # 写完一行，将行号+1
            i += 1
        # 保存
        work_book.save(filename)
        print('写入excel成功！')

    except Exception as e:
        print('写入excel失败！', e)


def append_to_excel(words, filename):
    '''
    追加数据到excel
    :param words: 【item】 [{},{}]格式
    :param filename: 文件名
    :return:
    '''
    try:
        # 打开excel
        word_book = xlrd.open_workbook(filename)
        # 获取所有的sheet表单。
        sheets = word_book.sheet_names()
        # 获取第一个表单
        work_sheet = word_book.sheet_by_name(sheets[0])
        # 获取已经写入的行数
        old_rows = work_sheet.nrows
        # 获取表头信息
        heads = work_sheet.row_values(0)
        # 将xlrd对象变成xlwt
        new_work_book = copy(word_book)
        # 添加内容
        new_sheet = new_work_book.get_sheet(0)
        i = old_rows
        for item in words:
            for j in range(len(heads)):
                new_sheet.write(i, j, item[heads[j]])
            i += 1
        new_work_book.save(filename)
        print('追加成功！')
    except Exception as e:
        print('追加失败！', e)


if __name__ == '__main__':
    # 样例
    words1 = [
        {'name': 'aki', 'age': 18, 'gender': '女'},
        {'name': 'zed', 'age': 20, 'gender': '男'}
    ]

    words2 = [
        {'name': 'leblance', 'age': 19, 'gender': '女'},
        {'name': 'yasuo', 'age': 20, 'gender': '男'},
        {'name': 'yasuo', 'age': 20, 'gender': '男'}
    ]
    # 写入内容
    write_to_excel(words=words1, filename='demo.xls', )
    # 追加内容
    append_to_excel(words=words2, filename='demo.xls')

效果：

name	age	gender
aki	18	女
zed	20	男
leblance	19	女
yasuo	20	男
yasuo	20	男