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)

 

posted on 2018-04-23 13:28  AF陈  阅读(403)  评论(0编辑  收藏  举报

导航