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
    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_rangecol 也是一样,即 (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
  3. # 加载.xls文件
  4. data = pd.read_excel('./scripts/test.xls')
  5. print(data)
  6. for index, row in data.iterrows():
  7. print(row['ColumnName'], row[0])

参考链接