Python操作excel，填充数据

前言

最近产品给了一个文件，文件中每一行都是一个产品，但是后面的日期不是连续的，需要将日期弥补完整，缺失的日期的价格等于其前一天的价格。

文件表现形式

该文件不难看出，使用excel中的公式进行填充，效率比较低，并且如果没有研究过宏的话，其工作量实在之大。因此，本文使用python来进行批量的计算和填充。

思路

对于excel中每个sheet都有四五千条数据，并且产品包括四五十个分组，首先需要想到的是将产品进行分组，然后根据分组对产品的日期进行填充，最后将填充好的新数据写回至excel。因此步骤便有了。

时间的操作（格式化，下一天，前一天）；
读取excel数据；
分组；
时间填充，读取value；
写入excel。

实现

1. 时间的操作
python操作时间使用两个类time和datetime类，在这里就不详解这两个类的模块构成，只列举主要的实现代码。
需要实现的方法有timeStr2StructTime，excelTime2TimeStr，getPriorDayStr，getNextDayStr，generateTimeStrSeq。
其实现如下，

def timeStr2StructTime(timeStr):
    return time.strptime(timeStr, '%Y/%m/%d')

def excelTime2TimeStr(excelTime):
    timeTuple = xlrd.xldate_as_tuple(excelTime, 0)

    return datetime.datetime(*timeTuple).strftime('%Y/%-m/%-d')

def getPriorDayStr(timeStr):
    structTime = timeStr2StructTime(timeStr)

    priorDay = datetime.datetime(structTime.tm_year, structTime.tm_mon, 
            structTime.tm_mday) + datetime.timedelta(days=-1);

    return priorDay.strftime('%Y/%-m/%-d')

def getNextDayStr(timeStr):
    structTime = timeStr2StructTime(timeStr)

    priorDay = datetime.datetime(structTime.tm_year, structTime.tm_mon, 
            structTime.tm_mday) + datetime.timedelta(days=1);

    return priorDay.strftime('%Y/%-m/%-d')

def generateTimeStrSeq(startTimeStr, endTimeStr):
    timeStrSeqArr = []

    while (startTimeStr != endTimeStr):
        timeStrSeqArr.append(startTimeStr)

        startTimeStr = getNextDayStr(startTimeStr)

    timeStrSeqArr.append(endTimeStr)

    return timeStrSeqArr

需要注意的点有
（1）读取excel中的时间cell发现得到的不是字符串，而是一个数字，需要使用excel库xlrd中的xldate_as_tuple转换为元组对象，然后再转换为字符串；
（2）生成时间序列主要是在第四步时间填充的时候，使用标准的时间序列和excel中的数据进行对比，进行价格和其它信息的填充；
（3）上一天和下一次的string，都是使用datetime库进行操作，其提供的timedelta使用比较方便。

2. 读取excel数据
python使用居多的excel库是xlrd和elwt，前者为读，后者为写。
读取excel使用xlrd库中提供的方法。

def readExcel(filePath, sheetIndex):
    workbook = xlrd.open_workbook(filePath)

    sheetNameArr = workbook.sheet_names()

    sheet = workbook.sheet_by_name(sheetNameArr[sheetIndex])
    sheetRows = sheet.nrows
    sheetCols = sheet.ncols

    # 读取内容
    curSheetContentArr = []
    for row in range(1, sheetRows):
        curRowArr = []
        for col in range(0, sheetCols):
            cellVal = sheet.cell(row, col).value

            if (col == 4):
                curRowArr.append(excelTime2TimeStr(cellVal))
            else:
                curRowArr.append(cellVal)

        curSheetContentArr.append(curRowArr)

    return curSheetContentArr

filePath为完整路径，sheetIndex为读取第几个sheet表内容。
最终返回的是一个二维数组。在这里，对于第4列的date数据进行了转换。

3. 分组
分组即是将第二步得到的二维数组，按照URL进行分组。主要思路即是遇到不同的URL时，将上一个数组的数据加入到结果数组中。

def divideContentByGroup(sheetContentArr):
    urlStr = sheetContentArr[0][3]
    sheetDivArr = []
    curGroupArr = []

    for i in range(0, len(sheetContentArr)):
        curRowArr = sheetContentArr[i]

        if curRowArr[3] != urlStr:
            sheetDivArr.append(curGroupArr)
            curGroupArr = []
            curGroupArr.append(curRowArr)

            urlStr = curRowArr[3]
        else:
            curGroupArr.append(curRowArr)

    sheetDivArr.append(curGroupArr)

    return sheetDivArr

4. 时间填充，读取value
（1）将所要处理的分组中的时间和价格数据进行hash化，即组成一个时间:价格的dictionary；
（2）生成时间序列，根据时间序列依次进行hash，如果没有该key，则获取该key前一天的数据进行填充；
（3）最后填充其它数据。

def fillupMissingDate(sheetGroupArr):
    sheetFilledupArr = []

    for i in range(0, len(sheetGroupArr)):
        curGroupArr = sheetGroupArr[i]

        if len(curGroupArr) == 1:
            sheetFilledupArr.append(curGroupArr[0])

            continue

        # 生成hash date:value
        dateValDict = {}
        for curRowArr in curGroupArr:
            dateValDict[curRowArr[4]] = curRowArr[5]

        # 生成日期序列
        startDateStr = curGroupArr[0][4]
        endDateStr = curGroupArr[len(curGroupArr) - 1][4]

        startEndDateSeqArr = generateTimeStrSeq(startDateStr, endDateStr)

        # 填充日期
        fillupDateValDict = {}
        for curDateStr in startEndDateSeqArr:
            if dateValDict.has_key(curDateStr):
                fillupDateValDict[curDateStr] = dateValDict[curDateStr]
            else:
                priorDayStr = getPriorDayStr(curDateStr)

                # 其值等于前一天的值
                fillupDateValDict[curDateStr] = fillupDateValDict[priorDayStr]

        # 生成完整序列
        for curDateStr in startEndDateSeqArr:
            tmpRowArr = []

            for j in range(0, len(curGroupArr[0]) - 2):
                tmpRowArr.append(curGroupArr[0][j])

            tmpRowArr.append(curDateStr)
            tmpRowArr.append(fillupDateValDict[curDateStr])

            sheetFilledupArr.append(tmpRowArr)

   return sheetFilledupArr

5. 写入excel
写入excel使用xlwt类库中的方法进行操作。在这里需要注意的是，如果有必要的话，需要设置一下excel的样式。

def setExcelStyle(fontName, fontHeight, isDate = False):
    cellStyle = xlwt.XFStyle()

    font = xlwt.Font()
    font.name = fontName
    font.height = fontHeight

    if isDate:
        cellStyle.num_format_str = 'yyyy/mm/dd'

    cellStyle.font = font

    return cellStyle

## 写回excel
def write2Excel(filePath, sheetName, sheetContentArr):
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet(sheetName)

    excelHeaderStyle = setExcelStyle(u'微软雅黑', 200)
    excelStyle = setExcelStyle(u'等线', 220)
    excelDateStyle = setExcelStyle(u'等线', 220, True)

    # 如果有表头的话，需要先写入一下表头数据
    headerArr = []

    # 写入表头
    for i in range(0, len(headerArr)):
        sheet.write(0, i, headerArr[i], excelHeaderStyle)

    for i in range(0, len(sheetContentArr)):
        curRowArr = sheetContentArr[i]

        for j in range(0, len(curRowArr)):
            if j == 4:
                sheet.write(i + 1, j, curRowArr[j], excelDateStyle)
            else:
                sheet.write(i + 1, j, curRowArr[j], excelStyle)

    workbook.save(filePath)

上述包含了表头，如果有表头的话，先将表头写入一下。

结束语

python是一个很好用的工具，简捷、快速，并且有着丰富的类库。上述日期填充可以用其它高级语言如Java、C#进行实现，在这里之所以选取了python，是由于其环境的简单及快速的上手使用。这个小需求也是对之前学习python的一个小训练，也使二把刀功夫的我在python的使用上更熟练。总之，多学习，多总结，多提升，这样才能更好的完成以后的工作。

Python操作excel，填充数据

前言

思路

实现

结束语

推荐阅读更多精彩内容