python 向excel表中添加新的sheet页或者向旧sheet中写入大量数据

向excel 表中写入大量数据

def openpyxl_add_excel(filepath='./data.xlsx', sheet_name='sheet_name', data=None, title=None):
    from openpyxl import Workbook, load_workbook
    import os
    # data_to_append = [['Alice', 25], ['Bob', 30], ['Charlie', 35]]
    # data_to_append = data_to_append * 100000
    # filepath = './data.xlsx'
    # sheet_name = 'sheet_name'
    if os.path.exists(filepath):
        workbook = load_workbook(filepath)
        sheet_names = workbook.sheetnames
        if sheet_name in sheet_names:   # 表存在时
            wbsheet = workbook[sheet_name]
        else:   # 表不存在时则创建
            wbsheet = workbook.create_sheet(title=sheet_name)
        if title:
            for i in range(0, len(title)):
                wbsheet.cell(1, i + 1).value = title[i]
                wbsheet.cell(1, i + 1).alignment = Alignment(horizontal='center', vertical='center')  # 居中对齐
        # workbook = load_workbook('data.xlsx')
        # sheet = workbook.active
    else:
        workbook = Workbook()
        if 'Sheet' in workbook.sheetnames and sheet_name != 'Sheet':
            del workbook["Sheet"]
            wbsheet = workbook.create_sheet(title=sheet_name, index=0)
        else:
            wbsheet = workbook.get_sheet_by_name(sheet_name)
        if title:
            for i in range(0, len(title)):
                wbsheet.cell(1, i + 1).value = title[i]
                wbsheet.cell(1, i + 1).alignment = Alignment(horizontal='center', vertical='center')  # 居中对齐
    for row in data:
        wbsheet.append(row)
    workbook.save(filepath)


def pandas_add_excel(filepath=None, data=None, sheet_name=None, title=None):
    import pandas as pd
    import os
    # ignore_index=True 重新生成索引
    # filepath = 'data1.xlsx'
    # data = [['Alice', 25], ['Bob', 30], ['Charlie', 35]]
    # data = data * 100000
    # title = ['Name', 'Age']
    if os.path.exists(filepath):
        excel_file = pd.ExcelFile(filepath)
        sheet_names = excel_file.sheet_names
        print('sheet_names:', sheet_names)
        if sheet_name in sheet_names:
            # 向同一张表中写入数据
            df = pd.read_excel(filepath, sheet_name=sheet_name)
            new_data = pd.DataFrame(data, columns=title)
            df1 = pd.concat([df, new_data], ignore_index=True)
            df1.to_excel(filepath, sheet_name=sheet_name, index=False)
        else:
            # 追加一张新表
            df = pd.read_excel(filepath)
            with pd.ExcelWriter(filepath, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
                df.to_excel(writer, sheet_name=sheet_name, index=False)
    else:
        # 创建文件并写入数据
        data = pd.DataFrame(data, columns=title)
        data.to_excel(filepath, sheet_name=sheet_name, index=False)


data = [['Alice', 25], ['Bob', 30], ['Charlie', 35]]
data = data * 1000
filepath = r'C:\Users\administered\Desktop\账单明细\2024-08\data.xlsx'
openpyxl_add_excel(filepath=filepath, sheet_name='sheet_name', data=data, title=['a', 'b'])
pandas_add_excel(filepath=filepath, sheet_name='sheet_name', data=data, title=['a', 'b'])

以下两种方式不再使用

用xlwt库来处理，只不过限制了每个sheet最多放5w行数据
import xlwt
import xlrd
from xlutils.copy import copy
import os
import numpy as np
import pandas as pd
class Excel_Add_Sheet():

    def save_table(self, table, file_name):
        # 保存表
        table.save(file_name)

    def add_new_sheet(self, file_name, sheet_name, title=None):
        """ 创建新的文件或者创建新的表
        :param file_name: 文件名
        :param sheet_name: 表名，不存在则创建
        :param title: 表不存在时则写入标题
        :return:
        """
        if not os.path.exists(file_name):
            table = xlwt.Workbook(encoding='utf-8')
            wbsheet = table.add_sheet(sheet_name, cell_overwrite_ok=True)
            if title:
                for i in range(0, len(title)):
                    wbsheet.write(0, i, title[i])
            sheet_names = []
            row = 1
        else:
            # 打开需要操作的excel表
            wb = xlrd.open_workbook(file_name)
            sheet_names = wb.sheet_names()
            table = copy(wb)
            if sheet_name not in sheet_names:
                wbsheet = table.add_sheet(sheet_name)
                if title:
                    for i in range(0, len(title)):
                        wbsheet.write(0, i, title[i])
                row = 1
            else:
                wbsheet = table.get_sheet(sheet_name)
                row = len(wbsheet.rows)
        return table, sheet_names, wbsheet, row

    def add_data_test(self):
        # 测试
        file_name = 'test.xlsx'
        sheet_name = 'sheet1'
        title = ['a', 'b', 'c']
        table, sheet_names, wbsheet, row = self.add_new_sheet(file_name, sheet_name, title)
        # 向新sheet中写入数据。
        num = [[i for i in range(1, 4)], [j for j in range(4, 7)]]
        data = np.array(num)
        pd_data = pd.DataFrame(data=data, index=['A', 'B'], columns=['C', 'D', 'E'])
        for i in range(pd_data.shape[0]):
            wbsheet.write(row + i, 0, int(pd_data.iloc[i, 0]))
            for j in range(1, pd_data.shape[1]):
                wbsheet.write(row + i, j, int(pd_data.iloc[i, j]))
        self.save_table(table, file_name)

    def add_data(self, file_name, title=None, datas=None, sheet_name=None):
        """
        :param file_name: 文件名
        :param title: sheet表标题
        :param datas: 列表数据格式[[1,2,3],[4,5,6]]
        :param sheet_name: sheet表名
        """
        table, sheet_names, wbsheet, row = self.add_new_sheet(file_name, sheet_name, title)
        # 向新sheet中写入数据。
        for i in range(len(datas)):
            wbsheet.write(row + i, 0, datas[i][0])
            for j in range(1, len(datas[i])):
                wbsheet.write(row + i, j, datas[i][j])
        self.save_table(table, file_name)


if __name__ == '__main__':
    start = Excel_Add_Sheet()
    # start.add_data_test()
    save_excel_path = './test.xlsx'
    title = ['a', 'b', 'c']
    lists = [[1, 2, 3], [4, 5, 6]]
    start.add_data(save_excel_path, title, lists, '表名')

#用openpyxl库，能处理的最大行数达到1048576,不支持xls格式,xlrd支持xls格式
#openpyxl 可以写入xls格式表中，无法新增数据
import openpyxl
from openpyxl.styles import Alignment

class Openpyxl_Excel():

    def save_table(self, table, file_name):
        # 保存表
        table.save(file_name)

    def openpyxl_add(self, filepath=None, sheet_name=None, title=None,index=0):
        """
        :param filepath: excel路径
        :param sheet_name: 工作表名
        :param title: 标题
        :param index: 工作表的位置索引
        """
        if os.path.exists(filepath):
            workbook = openpyxl.load_workbook(filepath)
            sheet_names = workbook.sheetnames
            if sheet_name in sheet_names:
                wbsheet = workbook.get_sheet_by_name(sheet_name)
            else:
                wbsheet = workbook.create_sheet(title=sheet_name, index=0)

            max_row = wbsheet.max_row + 1
            if title:
                for i in range(0, len(title)):
                    wbsheet.cell(1, i + 1).value = title[i]
                    wbsheet.cell(1, i + 1).alignment = Alignment(horizontal='center', vertical='center')  # 居中对齐
        else:
            workbook = openpyxl.Workbook()
            # 删除默认表Sheet， 创建指定表名
            if 'Sheet' in workbook.sheetnames and sheet_name != 'Sheet':
                del workbook["Sheet"]
                wbsheet = workbook.create_sheet(title=sheet_name, index=0)
            else:
                wbsheet = workbook.get_sheet_by_name(sheet_name)
            sheet_names = [sheet_name]
            if title:
                for i in range(0, len(title)):
                    wbsheet.cell(1, i+1).value = title[i]
                    wbsheet.cell(1, i + 1).alignment = Alignment(horizontal='center', vertical='center')  # 居中对齐
            max_row = 2
        return workbook, sheet_names, wbsheet, max_row

    def add_data(self, file_name, title=None, datas=None, sheet_name=None):
        """
        :param file_name: 文件名
        :param title: sheet表标题 [title1, title2, title3]
        :param datas: 列表数据[[1,2,3],[4,5,6]]
        :param sheet_name: sheet表名
        """
        table, sheet_names, wbsheet, row = self.openpyxl_add(file_name, sheet_name, title)
        # 循环写入数据，居中对齐
        # datas = [[1, 2, 3], [1, 2, 3]]
        for i in range(len(datas)):
            for j in range(len(datas[i])):
                wbsheet.cell(row + i, j + 1).value = datas[i][j]  # 写入数据
                wbsheet.cell(row + i, j + 1).alignment = Alignment(horizontal='center', vertical='center')  # 居中对齐
        # 保存文件
        self.save_table(table, file_name)

if __name__ == '__main__':
    start = Openpyxl_Excel()
    file_name = './test.xlsx'
    sheet_name = 'Sheet'
    title = ['title1', 'title2', 'title3']
    datas = [[1, 2, 3], [4, 5, 6]]
    start.add_data(file_name, title, datas, sheet_name)