使用xlrd/xlwt，openpyxl库处理Excel的例子

  前面介绍了Python中xlrd/xlwt及openpyxl的基本操作方法，并给出了简单的示例，但由于编程相对office面板操作不够直观，比较耗时，因此更多用于处理大量重复操作或较大的文件，这里分享一下最近小项目用到的实例。前三个用到了xlrd与xlwt，主要是从大量相同格式的Excel中取出值，并按时间构成序列，最后一个是将较大文件的横向区域纵向拼接起来，用到了xlrd和openpyxl。

例1.相同格式文件的固定位置的读取

  这是统计建模数据预处理的操作，国家邮政局会按月发布上个月全国快递投递量数据，在对这些数据进行分析之前必须将每个月的值提取出来按时序排列，操作很简单，但由于数据存在于每个月的Excel中，想得出2011年至2016年的序列就必须从72个表格中一一找出，手动处理太繁琐，因此可以使用程序处理。原始数据文件存储及格式如下：

图1.文件存储及命名方式

每个月的表格内容格式相同，以2015年8月月份Excel为例，其内容如下：

图2.每月数据文件格式及内容

我们需要提取的数据位于C8即第8行第3列，最终预期结果为：

图3.提取后的序列

实现代码为：

#从原始数据中选取 2011至2016年每月投递量数据并导出至result
#运行环境 python3.5.6

import xlrd
import xlwt

outfile = xlwt.Workbook()
sheet = outfile.add_sheet('num_post')

sheet.write(0,0,'投递量')
sheet.write(1,0,'日期')

col = 1
for year in range(2011,2017):    #选择年份范围 
    for mon in range(12):        #选择每年月份范围 
        data = xlrd.open_workbook('快递投递原始数据/%s/%sM%s.xlsx'%(year,year,mon+1))   #根据名称特点读取不同文件夹及文件
        table = data.sheets()[0]   #投递量数据位于每份 excel 的第一个 sheet 
        sheet.write(0,col,table.cell(7,2).value)       #将数据写入新文件
        sheet.write(1,col,str(year)+'/'+str(mon+1))    #写入日期
        col += 1

outfile.save('result.xls')       #保存文件

可以发现通过上述很简单的代码即可完成这一过程。

例2.相同格式文件的查找读取

  例1中每次都是读表格的C8位置，比较容易，除了固定位置，又是也需要查找之后读取，如从每月数据中读出延误、丢失、损毁、服务态度的申诉数据，因为这几项在每月表格中只位于同一列，而行位置不固定，而且服务包括投递与收寄两项，因此需要查找、判断合并后读取，其原数据格式如下：

图4.申诉数据表格格式

最终结果为：

图5.数据合并的序列结果

代码如下：

#用于从原始数据集中选取 2013 至 2016 年每月的申诉量 
#运行环境为 python3.5.6 

import xlrd
import xlwt

outfile = xlwt.Workbook()
sheet = outfile.add_sheet('result')

object = ['服务','延迟','丢失','损毁','日期']
for i in range(4):
    sheet.write(i,0,object[i])

col = 1
for year in range(2013,2017):        #选择年份范围 
    for mon in range(12):            #选择每年月份范围 
        data = xlrd.open_workbook('快递申诉原始数据/%s/%sM%s.xlsx'%(year,year,mon+1))
        table = data.sheets()[1]     #投递量数据位于每份 excel 的第二个sheet 
        s = 0                        #存在多项关于服务的申诉，因此需要遍历搜索
        for i in range(table.nrows):     #搜索各项申诉并存储 
            if '服务' in table.cell(i,1).value:
                s += table.cell(i,2).value      #服务申诉量累加
            if '延误' in table.cell(i,1).value:
                sheet.write(1,col,table.cell(i,2).value)
            if '丢失' in table.cell(i,1).value:
                sheet.write(2,col,table.cell(i,2).value)
            if '损毁' in table.cell(i,1).value:
                sheet.write(3,col,table.cell(i,2).value)
        sheet.write(0,col,s)                    #写入服务申诉
        sheet.write(4,col,str(year)+'/'+str(mon+1))
        col += 1

outfile.save('result2.xls')

例3.一个综合的例子

除了总量数据，网站还给出了部分快递企业的月份申诉数据，包括延误、丢失损毁、服务等，如下图所示：

图6.企业数据格式

分别将各企业各项申诉数据提取出来，并按时序排列，得到如下结果：

图7.企业数据提取后的结果

代码如下：

#用于从原始数据集中选取 2013 至 2017 年十家企业每月申诉率数据 
#运行环境为 python3.5.6 

import xlrd
import xlwt

outfile = xlwt.Workbook()
sheet = outfile.add_sheet('result',cell_overwrite_ok = True)

datalist = [['EMS',['EMS','邮政快递']],['SF',['顺丰']],['STO',['申通']],['YT',['圆通']],['ZT',['中通']],['YD',['韵达']],['QF',['全峰']],['JD',['京东']],['TT',['天天']],['HT',['汇通','百世']]]
#列表中每个元素表示一家企业，子列表首位表示公司简称，第二位为检索关键词列表

#写入表头
for i in range(len(datalist)):
    sheet.write_merge(3*i,3*i+2,0,0,datalist[i][0])
    sheet.write(3*i,1,'延误')
    sheet.write(3*i+1,1,'丢失')
    sheet.write(3*i+2,1,'服务')
sheet.write_merge(3*len(datalist),3*len(datalist),0,1,'日期')

#写入数据
col = 2
for year in range(2013,2017):
    for mon in range(12):
        data = xlrd.open_workbook('快递申诉原始数据/%s/%sm%s.xlsx'%(year,year,mon+1))
        table = data.sheets()[2]

        for nrows in range(table.nrows):
            for i in range(len(datalist)):
                for key in datalist[i][1]:
                    if key in table.cell(nrows,0).value:    #当关键字匹配时，写入数据
                        sheet.write(3*i,col,table.cell(nrows,2).value)
                        sheet.write(3*i+1,col,table.cell(nrows,3).value)
                        sheet.write(3*i+2,col,table.cell(nrows,4).value)
        sheet.write(3*len(datalist),col,str(year)+'/'+str(mon+1))
        col += 1

outfile.save('result3.xls')

例4.大文件的合并操作

  这个例子中需要将同一个sheet中原本横向排列的不同企业股票数据纵向拼接到一起，由于有数百家企业信息，每个企业有上千条记录，使用xlwt会因为超行而无法处理，需要用到openpyxl，考虑到xlrd在读取大文件时效率要高于openpyxl，因此仍使用xlrd读取，操作前后表格如下图所示：

图8.合并前数据格式（其中每个纵向区域表示不同企业）

图9.合并后数据格式（共137732条记录）

代码如下：

# Excel set合并
# python3.5.6
import xlrd
import openpyxl
def Set_Ask(file,data): 
    table = data.sheets()[6]
    nrows = table.nrows
    sheet1 = file.active

    # 写入表头
    row0 = ['CUSIP','BarTp','Date','OPEN','HIGH','LOW','LAST_PRICE','NUMBER_TICKS','VOLUME','VALUE']
    for i in range(len(row0)):
        sheet1.cell(row=1,column=i+1).value = row0[i]

    # 写入数据
    for m in range(1,9):
        i = m-1
        j = 2
        try:
            while(table.cell(4,i).value != '' or table.cell(4,i+9).value != '' or table.cell(4,i+18).value != ''):
                while(table.cell(4,i).value == ''):
                    i += 9
                for k in range(3,nrows):
                    if(table.cell(k,i).value != ''):
                        sheet1.cell(row=j,column=m+2).value = table.cell(k,i).value
                        sheet1.cell(row=j,column=1).value = table.cell(0,i-m+1).value[0:8]
                        sheet1.cell(row=j,column=2).value = table.cell(1,i-m+1).value[-1]
                        j += 1
                i += 9          #每个企业数据占8列
        except IndexError:
            continue

def main():
    file = openpyxl.Workbook()
    rdpath = 'data.xlsx'        #文件读取路径
    wtpath = 'new_data.xlsx'    #文件写出路径
    data = xlrd.open_workbook(rdpath)
    Set_Ask(file,data)      
    file.save(wtpath)       #文件保存

main()

从这些例子可以看出，使用python处理较多重复操作的excel以及较大文件相对于手工操作有很大优势。