import os
import xlwt
import xlrd
from xlutils.copy import copy
class ExcelUtil():
def __init__(self,excel_path,title_value,sheet_name="Sheet1"):
self.excelPath = excel_path
self.sheetName = sheet_name
self.titleValue = title_value
def createExcelXls(self):
index = len(self.titleValue) # 获取需要写入数据的行数
workbook = xlwt.Workbook() # 新建一个工作簿
sheet = workbook.add_sheet(sheet_name) # 在工作簿中新建一个表格，就是右下角sheet表的名字，默认sheet1,sheet2...因为一个excel中有很多表格，当要读取哪个表格就要设置
for i in range(0, index):
for j in range(0, len(self.titleValue[i])):
sheet.write(i, j, self.titleValue[i][j])
workbook.save(self.excelPath) # 保存工作簿
print("{} xls create success!".format(self.excelPath))
def readExcel(self):
if not os.path.exists(self.excelPath):
print("{}is not exists".format(self.excelPath))
return []
self.data = xlrd.open_workbook(self.excelPath)
self.table = self.data.sheet_by_name(self.sheetName)
# 获取第一行作为key值
self.keys = self.table.row_values(0)
# 获取总行数
self.rowNum = self.table.nrows
# 获取总列数
self.colNum = self.table.ncols
if self.rowNum <= 1:
print("总行数小于1")
# # 存入list 中
# else:
# excel_value = []
# j = 1
# for i in list(range(self.rowNum - 1)):
# s = {}
# dict_value = {}
# # 从第二行取对应values值
# s['rowNum'] = i + 2
# values = self.table.row_values(j)
# # print(values)
# for x in list(range(self.colNum)):
# s[self.keys[x]] = values[x]
# # 存入字典，然后字典存入list中
#
# excel_value.append(values)
# j += 1
# 存入字典，然后字典存入list中
else:
excel_value = {}
j = 1
for i in list(range(self.rowNum - 1)):
values = self.table.row_values(j)
print(values)
excel_value.setdefault(values[0],[]).append(values[1:])
print(excel_value)
j += 1
return excel_value
def addContent2Excel(excel_path, value):
index = len(value)
workbook = xlrd.open_workbook(excel_path)
sheets = workbook.sheet_names()
worksheet = workbook.sheet_by_name(sheets[0])
rows_old = worksheet.nrows
new_workbook = copy(workbook)
new_worksheet = new_workbook.get_sheet(0)
for i in range(0, index):
for j in range(0, len(value[i])):
new_worksheet.write(i + rows_old, j, value[i][j])
new_workbook.save(excel_path)
print("xls append success!")
def parseExcel(excel_path):
pass
if __name__ == "__main__":
excel_path = "D:/work/test/test.xls"
title_value = [["file_name","size","row","col"]] # 表格每一列代表的什么
sheet_name = "test_data"
excel_util = ExcelUtil(excel_path,title_value,sheet_name)
excel_util.createExcelXls()
value_list = [
["000.jpg","128","3","4"],
["001.jpg","23","5","6"],
["002.txt","345","8","9"]]
addContent2Excel(excel_path,value_list)
value_list = [
["003.jpg", "128", "3", "4"],
["004.jpg", "23", "5", "6"],
["002.txt", "345", "8", "9"]]
addContent2Excel(excel_path, value_list)
excel_value = excel_util.readExcel()
参考：
https://blog.csdn.net/u013250071/article/details/81911434
https://blog.csdn.net/u013155359/article/details/99831617
