Python3操作Excel(写入)
先决条件:安装xlwt,xlrd
以下为操作实例
#!/usr/bin/env python3 # _*_ coding:utf8 _*_ def CreateExcel(fileName,sheetName,title,dataSet): """创建一个EXCEL文件并写入数据""" import xlwt wbk = xlwt.Workbook() # 新建一个excel对象 sheet = wbk.add_sheet(sheetName) # 添加一个sheet页 for col in range(len(title)): # 写入sheet的表头 sheet.write(0, col, title[col]) for row in range(len(dataSet)): # 循环写入sheet的每行数据 for col in range(len(title)): sheet.write(row + 1, col, dataSet[row][col]) #加1是为了不覆盖第一行的内容 """可以添加多个sheet""" wbk.save(fileName) #保存excel必须使用后缀名是.xls的,不是能是.xlsx的 def AddExcelSheet(fileName,sheetName,title,dataSet): """往一个已经存在的EXCEL中添加一个sheet""" import os,xlrd from xlutils.copy import copy rb = xlrd.open_workbook(fileName) ##打开EXCEL文件 wb = copy(rb) ##复制Excel sheet = wb.add_sheet(sheetName) # 添加一个sheet页 for col in range(len(title)): # 写入sheet的表头 sheet.write(0, col, title[col]) for row in range(len(dataSet)): # 循环写入sheet的每行数据 for col in range(len(title)): sheet.write(row + 1, col, dataSet[row][col]) os.remove(fileName) ##移除原Excel文件 wb.save(fileName) #保存excel必须使用后缀名是.xls的,不是能是.xlsx的
title1 = ["姓名","性别","年龄"] info1 =[ ["张三","男",18], ["李四","男",20], ["王五","女",22] ] title2 = ["姓名","科目","成绩"] info2 =[ ["张三","语文",80], ["李四","语文",85], ["王五","语文",88] ] CreateExcel('abc.xls','sheet1',title1,info1) AddExcelSheet('abc.xls','sheet2',title2,info2)