Python学习笔记-Excel读写操作

需要先安装xlrd（读）、xlwt（写）库

pip install xlrd

pip install xlwt

代码如下：

import os
import sys
import xlrd
import xlwt
import datetime

def readSheet(filePath, sheetIndex = 0):
    if not os.path.exists(filePath):
        print '%s is not exist' %filePath
        sys.exit(0)
    startTime = datetime.datetime.now()
    workbook = xlrd.open_workbook(filePath)
    sheet = workbook.sheet_by_index(sheetIndex)
    nrows = sheet.nrows
    if nrows == 0:
        print '%s no data need to read' %filePath
        sys.exit(0)
    trow = 2 if nrows > 1 else 1
    ncols = sheet.ncols
    types = [sheet.row(trow)[i].ctype for i in xrange(ncols)]
    datas = [[] * ncols for i in xrange(nrows)]
    for r in xrange(nrows):
        for c in xrange(ncols):
            if (sheet.cell(r, c).ctype == 3):
                date_tuple = xlrd.xldate_as_tuple(sheet.cell_value(r, c), workbook.datemode)
                date_value = datetime.date(*date_tuple[:3]).strftime('%Y/%m/%d')
                datas[r].append(date_value)
            else :
                datas[r].append(sheet.row(r)[c].value)
    endTime = datetime.datetime.now()
    print 'read %s spend time %s seconds' %(filePath, (endTime - startTime).seconds)
    return (sheet.name, types, datas)

def readSheets(filePath):
    if not os.path.exists(filePath):
        print '%s is not exist' %filePath
        sys.exit(0)
    startTime = datetime.datetime.now()
    data = xlrd.open_workbook(filePath)
    sheets = data.sheets()
    endTime = datetime.datetime.now()
    print 'read %s spend time %s seconds' %(filePath, (endTime - startTime).seconds)
    return sheets
    
def writeSheet(sheet, fileDir):
    startTime = datetime.datetime.now()
    if not os.path.exists(fileDir):
        os.mkdir(fileDir)
    filePath = fileDir + os.sep + 'tmp.xls'
    workbook = xlwt.Workbook(encoding='utf-8')
    wsheet = workbook.add_sheet(sheet[0], cell_overwrite_ok = True)
    #style = xlwt.easyxf('font: height 300, name SimSun, bold 1, color red;')
    font = xlwt.Font()
    font.name = 'Times New Roman'
    font.bold = True
    font.height = 300
    font.colour_index = 2
    style = xlwt.XFStyle()
    style.font = font
    #style.num_format_str= 'YYYY-MM-DD'
    #style.num_format_str = '$#,##0.00'
    datas = sheet[2]
    nrows = len(datas)
    ncols = len(datas[0])
    for ncol in xrange(ncols):
        wsheet.col(ncol).width = 256 * 30
        #wsheet.col(ncol).collapse = 1
        #wsheet.col(ncol).best_fit = 1
    for r in xrange(nrows):
        for c in xrange(ncols):
            wsheet.write(r, c, datas[r][c], style)
    workbook.save(filePath)
    endTime = datetime.datetime.now()
    print 'write data to %s spend time %s seconds' %(fileDir, (endTime - startTime).seconds)
    
if __name__ == '__main__' :
    sheet = readSheet("d:\\excel\\a.xlsx")
    writeSheet(sheet, "d:\\excel\\t")