python操作excel——读，写，追加

郑*杰

已于 2022-08-26 11:52:34 修改

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： python三方库文章标签： python

于 2022-04-08 17:17:08 首次发布

本文链接：https://blog.youkuaiyun.com/qq_34577961/article/details/124046461

python三方库专栏收录该内容

24 篇文章

订阅专栏

这篇博客介绍了如何使用Python进行Excel文件的操作，包括读取xls文件、写入数据以及追加数据到已有表格。通过`xlrd`和`xlwt`库实现文件的读写，同时展示了在追加数据时处理表头不匹配的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

# 不支持xlsx后缀
# -*- coding: utf-8 -*-
from xlutils.copy import copy
import xlrd
import xlwt


class read_wirte_xls():
    def read_excel(self, filename, sheetname):
        '''
        读文件
        :param filename: 文件名
        :sheetname: 要添加数据的表单名
        '''

        # 打开一个xls文件
        workbook = xlrd.open_workbook(filename)
        # 抓取所有sheet页的名称
        # worksheets = workbook.sheet_names()
        # 指定读取表单
        worksheet = workbook.sheet_by_name(sheetname)
        # 表单所有行数：row
        num_rows = worksheet.nrows
        row_list = []
        for curr_row in range(num_rows):
            row = worksheet.row_values(curr_row)
            row_list.append(row)

        # 表单中所有列数：col
        num_cols = worksheet.ncols
        col_list = []
        for curr_col in range(num_cols):
            col = worksheet.col_values(curr_col)
            col_list.append(col)
        return row_list, col_list

    def write_excel(self, filename, sheetname):
        '''
        :param filename: 文件名
        :sheetname: 要添加数据的表单名
        '''
        # 创建workbook和sheet对象
        workbook = xlwt.Workbook()  # 注意Workbook的开头W要大写
        sheet1 = workbook.add_sheet(sheetname, cell_overwrite_ok=True)
        # 向sheet页中写入数据
        sheet1.write(0, 0, 'Name1')  # 第一个0表示第一行，第二个0表示第一列
        sheet1.write(0, 1, 'aaaa111')
        sheet1.write(1, 0, 'Name2')
        sheet1.write(1, 1, 'bbbb')
        # 保存该excel文件,有同名文件时直接覆盖
        workbook.save(filename)
        print('创建excel文件完成!')

    def append_to_excel(self, words, filename, sheetname):
        '''
        追加数据到excel
        :param words: [{},{}]格式，字典的key为表头的key，
        :param filename: 文件名
        :sheetname: 要添加数据的表单名
        '''

        # 打开excel
        word_book = xlrd.open_workbook(filename)
        # 获取所有的sheet表单。
        sheets = word_book.sheet_names()
        sheetnameIndex = sheets.index(sheetname)
        # 获取目标表单
        work_sheet = word_book.sheet_by_name(sheets[sheetnameIndex])
        # 获取已经写入的行数
        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(sheetnameIndex)

        try:
            a=0
            for item in words:
                a += 1
                for j in range(len(heads)):
                    # 当要添加的数据表头对不上时，则不添加数据
                    if item.get(heads[j]) == None:
                        print(f'第{a}行数据的表头{heads[j]}没找到！！！',)
                        new_sheet.write(old_rows, j, None)
                    else:
                        # 添加日期格式
                        # datastyle = xlwt.XFStyle()
                        # datastyle.num_format_str = 'yyyy:mm:dd'
                        # new_sheet.write(old_rows, j, 20221011,datastyle)
                        new_sheet.write(old_rows, j, item.get(heads[j]))
                old_rows += 1
            new_work_book.save(filename)
            print('追加成功！')
        except Exception as e:
            print('追加失败！', e)


word = [{"Name1": "添加1", "aaaa111": "tx"}, {"开放API名称": "添加2"}]
read_wirte_xls().append_to_excel(words=word, filename='le.xls', sheetname='student')
# print(read_wirte_xls().read_excelfilename='le.xls', sheetname='student'))
# print(read_wirte_xls().write_excel(filename='test.xls', sheetname='student'))


报错这个时，Non-UTF-8 code starting with ‘\xe5，首行加个这个编码注释：# -*- coding: utf-8 -*- 
更多操作：https://www.jb51.net/article/137220.htm