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 = 1else:# 打开需要操作的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 = 1else:wbsheet = table.get_sheet(sheet_name)row = len(wbsheet.rows)return table, sheet_names, wbsheet, rowdef add_data(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)if __name__ == '__main__':start = Excel_Add_Sheet()start.add_data()
