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


updated at:2020-01-05 22:55

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

excel 概念

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

workbook: 在各种库中,workbook 其实就是一个 excel 文件
sheet: 在一个 excel 文件中,可能会有多个 sheet,一个 sheet 可以看做一张表
row: row 其实就是一张表中的一行,正常是用数字 1、2、3、4 表示
column: column 就是一张表中的一列,正常用字母 A、B、C、D 表示
cell: cell 就是一张表中的一格,可以用 row + column 的组合来表示,例如: A3

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

图 1:Excel 概念

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

xlrd/xlwt 读写 xls

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

  1. 安装 xlrd 和 xlwt

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

  1. pip install xlrd
  2. pip install xlwt
  1. 读取 excel

假设我的 excel 内容如下:

图 2:Excelk 内容

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

  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
  9. for i in range(nrows):
  10. # 获取一行的数据,返回数组
  11. row_values = table.row_values(i)
  12. for i in range(ncols):
  13. 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
  2. # 创建工作簿
  3. f = xlwt.Workbook()
  4. # 创建一个 user_info 的 sheet
  5. sheet1 = f.add_sheet(u'user_info',cell_overwrite_ok=True)
  6. rows = [['姓名', '性别', '年龄', '身高', '体重'],
  7. ['张三', '男', '18', '166', '60'],
  8. ['李四', '未知', '未知', '177', '88']]
  9. for i in xrange(len(rows)):
  10. row = rows[i]
  11. for j in xrange(len(row)):
  12. # 以 cell 为单位写出单元格
  13. sheet1.write(i, j, rows[i][j])
  14. # 别忘了保存
  15. 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列合并。

参考链接

python操作Excel读写--使用xlrd
openpyxl文档
Working with Excel Files in Python
The xlrd
Module
python操作excel表格(xlrd/xlwt)