Python读写excel表格详解

在实际的工作中我们常常会用到excel表格(或者CSV文件)来保存数据，这就会涉及到对表格的大量操作，当有许多重复、复杂的事情时，我们就应当考虑编写程序来自动完成。许多语言都提供有读写excel文件的API，我在这里使用Python来做一个基本的了解吧。

关于Python读写excel的库，我们可以从网站 http://www.python-excel.org/得到各种方法，这里我们使用xlrd和xlwt来操作excel。你可以从https://pypi.python.org/pypi下载这两个模块，或者直接使用pip命令pip install xlrd(xlwt)将这两个模块安装到本机即可。

1、Python读excel—xlrd

我们可以从以下这个网站得到xlrd的最新API，http://xlrd.readthedocs.io/en/latest/api.html，打开后可以看到以下API分类。

话不多说，我们来看一看最基本的使用。首先我们需要在Excel或WPS中创建一个excel文件，并在其建立几个sheet和添加一些数据，如下图所示：

即，我们要读出此Excel文件中第三个表格中的数据。代码如下：

import xlrd

def read_excel():
  #打开Excel文件
  data=xlrd.open_workbook('d:\TestXlrd.xlsx')
  #sheet3=data.sheets()[2]
  #sheet3=data.sheet_by_name('thirdSheet')
  sheet3=data.sheet_by_index(2)
  rows=sheet3.nrows

  #打印出所有sheet的名称
  print (data.sheet_names(),'\n')

  #打印出第三张表的名称、行数、列数
  print (sheet3.name,sheet3.nrows,sheet3.ncols,'\n')
 
  #获取某个单元格的数值
  print(sheet3.cell(2,2))
  print(sheet3.cell_value(2,2))
  print(sheet3.row(2)[2].value)

  for i in range(rows):
    print (sheet3.row_values(i))
  print('\n')

if __name__ == '__main__':
  read_excel()

运行此代码，可以得到如下输出：

以上代码非常简单仅仅用到了几个基本的方法和属性，需要注意的是行和列的索引都是从0开始的。

我们同时可以发现，当有合并的单元格时，只能读出索引最小的那一个，将另外的读为空值，这就产生了两个问题：

1. 怎么获取合并的单元格？
2. 多数情况下合并的单元格需要的是同一个值，我们怎么去处理它？

请接着往下看。

合并单元格分析

我们查询xlrd的API可以得到以下关键方法：

可以清楚地看见，此方法是以4个数字的形式返回的是合并的单元格信息。我们依然用一开始生成的Excel文件来掩饰，请看以下示例：

import xlrd

def read_excel():
  #打开文件
  data=xlrd.open_workbook('d:\TestXlrd.xlsx')
  sheet3=data.sheet_by_index(2)
  #输出‘一块’合并单元格的信息
  print(sheet3.merged_cells,'\n')
  
  #分解合并的单元格
  for crange in sheet3.merged_cells:
    rlo, rhi, clo, chi = crange
    for rowx in range(rlo, rhi):
        for colx in range(clo, chi):
          print(rowx,colx)
          
if __name__ == '__main__':
  read_excel()

运行它，可以得到以下输出：

这里用cell来代表原始的一个单元格，用"一块"来代表合并后的单元格。

我们可以看到“一块”合并的单元格输出为了一个包含4位数字的list，它的意思就是[row,row_ range, col, col_range]，可以记为[rlow,rhigh)即：行的低位索引为rlow，高位索引不包含rhigh（为rhigh-1）；列的情况同理。

得到合并的单元格后我们可以将其分解成一个一个的cell，我们只需要输出那"一块"最低位的行列值，则此"一块"单元格中所有cell的值都为此，代码片段如下：

for crange in sheet3.merged_cells:
    rlo, rhi, clo, chi = crange
    for rowx in range(rlo, rhi):
        for colx in range(clo, chi):
          print(rowx,colx)
    print ("上面cell的值为："+sheet3.cell_value(rlo,clo)+'\n')

同时可以记录索引最低的那个cell，并将其赋值给同"一块"合并的单元格中的其他cell，记录在我们构造的dict中，这样就可以变相解决xlrd模块读取合并单元格中读出空值的问题了。xlrd中还有很多其它的API，可以实现很多有用的功能。

2、Python写excel—xlwt

写excel的难点在于填充的数据，在这里我们使用固定的数据简化一下。在写excel的操作中还有几个棘手的地方，比如写入合并的单元格、写入不同的样式等等，有些要看源码才能研究的透。相对于xlrd提供的很多API来说，xlwt提供的就要少一些了。

在Excel中添加如下sheet，并在其中写入一些数据，如下图：

这张表里面只存了一些基本的数据，另外有几个合并的单元格，我们应当怎么生成这样一张表呢，代码如下：

import xlwt

def write_excel():
  wb = xlwt.Workbook() 
 
  #创建sheet
  sheet1 = wb.add_sheet('sheet1',cell_overwrite_ok=True) 

  row0 = ['姓名', '统计','数值'	,'排名']
  column0 = ['G','D','P']
  item = ['得分','篮板','助攻']

  #生成第一行
  for i in range(0,len(row0)):
    sheet1.write(0,i,row0[i])

  #生成第一列和最后一列(合并4行)
  i, j = 1, 0
  while i < 3*len(column0) and j < len(column0):
    sheet1.write_merge(i,i+2,0,0,column0[j]) #第一列
    sheet1.write_merge(i,i+2,3,3) #最后一列"排名"
    i += 3
    j += 1

 #生成第二列
  i = 0
  while i < 3*len(column0):
    for j in range(0,len(item)):
      sheet1.write(j+i+1,1,item[j])
    i += 3
    
 #保存文件
  wb.save('demo2.xlsx') 

if __name__ == '__main__':
    write_excel()   

运行之后，效果如下：

可以看到，已经实现了我们想要的效果。只是这里我们没有添加样式，比如框线、居中等。其实这里面主要用到了write和write_merge来往cell里面写数据，即按照cell或"合并单元格"的形式往里面写数据，初始数据都是我们给定的，因此并不复杂。

我们还可以为cell定制各种Formatting和Style，不过这好像并不是很重要，如果不是非常必要，谁会在程序里花大量的时间来做这些呢~~[手动再见]