updated at：2020-01-05 22:55

下面有同学评论推荐使用 pandas，我个人没有试验过，据说还不错。

updated at: 2023-06-10 16:38

最近又想要读取 excel，所以又尝试了一下 pandas，果然比较简单，这里顺便做了一个记录。

概述

python 读写 excel 有好多选择，但是，方便操作的库不多，在我尝试了几个库之后，我觉得两个比较方便的库分别是 xlrd/xlwt、openpyxl。之所以推荐两个库是因为这两个库分别操作的是不同版本的 excel，xlrd 操作的是 xls/xlxs 格式的 excel，而 openpyxl 只支持 xlxs 格式的 excel，openpyxl 使用起来会更方便一些，所以如果你只操作 xlxs 文件的话，那么可以优先选择 openpyxl，如果要兼容 xls 的话，那就用 xlrd/xlwt 吧。

excel 概念

在开始介绍库之前，如果你不是很熟悉 excel，我这里简单介绍一些关于 excel 的概念，如果你对 execl 已经很熟悉的话，可以跳过。

综合起来，我们可以用这张图来表示各个概念：

图 1：Excel 概念

介绍 excel 的基本结构之后，我们就来看下怎么用 python 的开源库来操作 excel。

xlrd/xlwt

xlrd/xlwt 读写 xls

因为 xlrd 是只能用来读取 excel 的，所以我们还需要用 xlwt 来写出 excel。

  1. 安装 xlrd 和 xlwt

    xlrd 和 xlwt 都可以通过 pip 直接安装。

    1.  [root@liqiang.io]# pip install xlrd xlwt

  2. 读取 excel

    假设我的 excel 内容如下：

    那么，我要将数据遍历出来，那么代码可以这样实现：

    1.      import xlrd
    2.      # 打开excel 文件
    3.      data = xlrd.open_workbook('query_result.xls')
    4.      # 通过名称获取工作表
    5.      table = data.sheet_by_name(u'Tablib Dataset')
    6.      # 获取表格的行数和列数
    7.      nrows = table.nrows
    8.      ncols = table.ncols
    10.      for i in range(nrows):
    11.          # 获取一行的数据，返回数组
    12.          row_values = table.row_values(i)
    13.              for i in range(ncols):
    14.                  print row_values[i]

    这里用了稍微高效一点点的方法来操作数据，其实获取 cell 的值还有几种方法，例如，直接获取指定位置的 cell 的值可以这样写：

    1.  # 用table直接获取单元格的值
    2.  cell_A1 = table.cell(0,0).value
    3.  cell_C4 = table.cell(2,3).value

    也可以这样来：

    1.  # 使用行列索引来获取值
    2.  cell_A1 = table.row(0)[0].value
    3.  cell_A2 = table.col(1)[0].value

  3. 写出 excel

    假设我们有这样一组数据要写出到 excel

姓名 性别 年龄 身高 体重
张三 男 18 166 60
李四 未知 未知 177 88

那么，这里就使用 xlwt 来写出到 excel，直接上代码，代码里面加注释：

  1.     import xlwt
  3.     # 创建工作簿
  4.     f = xlwt.Workbook() 
  5.     # 创建一个 user_info 的 sheet
  6.     sheet1 = f.add_sheet(u'user_info',cell_overwrite_ok=True)
  7.     rows = [['姓名', '性别', '年龄', '身高', '体重'],
  8.               ['张三', '男', '18', '166', '60'],
  9.               ['李四', '未知', '未知', '177', '88']]
  11.     for i in xrange(len(rows)):
  12.         row = rows[i]
  13.             for j in xrange(len(row)):
  14.                 # 以 cell 为单位写出单元格
  15.                 sheet1.write(i, j, rows[i][j])
  16.     # 别忘了保存
  17.     f.save('/Users/imaygou/Code/test/test.xls')

一些问题

  1. 读取excel中单元格内容为日期的方式

python读取excel中单元格的内容返回的有5种类型，即ctype:

  1. ctype :  0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error

默认读取的时间为：

  1. >>> sheet2.cell(2,2).value  #1990/2/22
  2. 33656.0

设置一下读取的时间为：

  1. >>> xlrd.xldate_as_tuple(sheet2.cell_value(2,2),workbook.datemode)
  2. (1992, 2, 22, 0, 0, 0)

可以抽成一个函数一个函数的：

  1.         def convert_date(sheet, row, col):
  2.         data = sheet.cell(row,col)
  3.         if (data.ctype == 3):
  4.             date_value = xlrd.xldate_as_tuple(sheet.cell_value(rows,3),book.datemode)
  5.             data = date(*date_value[:3]).strftime('%Y/%m/%d')
  6.         return data

  1. 读取合并单元格的内容

    这个是真没技巧，只能获取合并单元格的第一个cell的行列索引，才能读到值，读错了就是空值。

  2. 获取合并的单元格
    读取文件的时候需要将formatting_info参数设置为True，默认是False，所以上面获取合并的单元格数组为空， 

  1.     >>> workbook = xlrd.open_workbook(r'F:demo.xlsx',formatting_info=True)
  2.     >>> sheet2 = workbook.sheet_by_name('sheet2')
  3.     >>> sheet2.merged_cells
  4.     [(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]

merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range)，其中 [row,row_range)包括 row，不包括 row_range，col 也是一样，即 (1, 3, 4, 5) 的含义是：第 1 到 2 行（不包括3）合并，(7, 8, 2, 5)的含义是：第 2 到 4 列合并。

pandas

安装

  1. [root@liqiang.io]# pip install pandas xlrd

使用

  1. [root@liqiang.io]# cat test.py
  2. import pandas as pd
  4. # 加载.xls文件
  5. data = pd.read_excel('./scripts/test.xls')
  7. print(data)
  8. for index, row in data.iterrows():
  9.     print(row['ColumnName'], row[0])

参考链接