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