Python操作excel的几种方式--xlrd、xlwt、openpyxl

 

在处理excel数据时发现了xlwt的局限性–不能写入超过65535行、256列的数据（因为它只支持Excel 2003及之前的版本，在这些版本的Excel中行数和列数有此限制），这对于实际应用还是不够的。为此经过一番寻找发现了一个支持07/10/13版本Excel的openpyxl，虽然功能很强大，但是操作起来感觉没有xlwt方便。下面分别说下几个模块的常用操作。

xlrd

xlrd是用来从Excel中读写数据的，但我平常只用它进行读操作，写操作会遇到些问题。用xlrd进行读取比较方便，流程和平常手动操作Excel一样，打开工作簿(Workbook)，选择工作表(sheets)，然后操作单元格(cell)。下面举个例子，例如要打开当前目录下名为”data.xlsx”的Excel文件，选择第一张工作表，然后读取第一行的全部内容并打印出来。Python代码如下：

1
2
3
4
5
6
7
8
9
10
11
 
#打开excel文件
data=xlrd.open_workbook('data.xlsx')     
#获取第一张工作表（通过索引的方式）
table=data.sheets()[0] 
#data_list用来存放数据
data_list=[]    
#将table中第一行的数据读取并添加到data_list中
data_list.extend(table.row_values(0))
#打印出第一行的全部数据
for item in data_list:
    print item

 

上面的代码中读取一行用table.row_values(number)，类似的读取一列用table.column_values(number)，其中number为行索引，在xlrd中行和列都是从0开始索引的，因此Excel中最左上角的单元格A1是第0行第0列。
xlrd中读取某个单元格用table.cell(row,col)即可，其中row和col分别是单元格对应的行和列。
下面简单归纳一下xlrd的用法

xlrd用法总结

  • 打开Excel工作簿

    1
    		 
    data=xlrd.open_workbook(filename)

  • 查看工作簿中所有sheet的名称

    1
    		 
    data.sheet_names()

  • 选择某一个工作表（通过索引或表名称）

    1
    2
    3
    4
    5
    6
    7
    8
    		 
    #获取第一个工作表
    table=data.sheets()[0]

    #通过索引获取第一个工作表
    table=data.sheet_by_index(0)

    #通过表名称选择工作表
    table=data.sheet_by_name(u'哈哈')

  • 获取表格的行数和列数

    1
    2
    		 
    nrows=table.nrows
    ncols=table.ncols

  • 获取整行和整列的值

    1
    2
    		 
    table.row_values(number)
    table.column_values(number)

  • 通过循环读取表格的所有行

    1
    2
    		 
    for rownum in xrange(table.nrows):
        print table.row_values(rownum)

  • 获取单元格的值

    1
    2
    3
    4
    5
    		 
    cell_A1=table.row(0)[0].value
    #或者像下面这样
    cell_A1=table.cell(0,0).value
    #或者像下面这样通过列索引
    cell_A1=table.col(0)[0].value

写操作自己很少用，所以就不归纳了。

xlwt

如果说xlrd不是一个单纯的Reader（如果把xlrd中的后两个字符看成Reader，那么xlwt后两个字符类似看成Writer），那么xlwt就是一个纯粹的Writer了，因为它只能对Excel进行写操作。xlwt和xlrd不光名字像，连很多函数和操作格式也是完全相同。下面简要归纳一下常用操作。

xlwt常用操作

新建一个Excel文件（只能通过新建写入）

1
 
data=xlwt.Workbook()

 

新建一个工作表

1
 
table=data.add_sheet('name')

 

写入数据到A1单元格

1
 
table.write(0,0,u'呵呵')

 

注意：如果对同一个单元格重复操作，会引发overwrite Exception，想要取消该功能，需要在添加工作表时指定为可覆盖，像下面这样

1
 
table=data.add_sheet('name',cell_overwrite_ok=True)

 

保存文件

1
 
data.save('test.xls')

 

这里只能保存扩展名为xls的，xlsx的格式不支持

xlwt支持一定的样式，操作如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
#初始化样式
style=xlwt.XFStyle()

#为样式创建字体
font=xlwt.Font()

#指定字体名字
font.name='Times New Roman'

#字体加粗
font.bold=True

#将该font设定为style的字体
style.font=font

#写入到文件时使用该样式
sheet.write(0,1,'just for test',style)

 

openpyxl

该模块支持最新版的Excel文件格式，对Excel文件具有响应的读写操作，对此有专门的Reader和Writer两个类，便于对Excel文件的操作。虽然如此，但我一般还是用默认的workbook来进行操作。常用操作归纳如下：

openpyxl常用操作

读取Excel文件

1
2
3
 
from openpyxl.reader.excel import load_workbook

wb=load_workbook(filename)

 

显示工作表的索引范围

1
 
wb.get_named_ranges()

 

显示所有工作表的名字

1
 
wb.get_sheet_names()

 

取得第一张表

1
2
 
sheetnames = wb.get_sheet_names()  
ws = wb.get_sheet_by_name(sheetnames[0])

 

获取表名

1
 
ws.title

 

获取表的行数

1
 
ws.get_highest_row()

 

获取表的列数

1
 
ws.get_highest_column()

 

单元格的读取，此处和xlrd的读取方式很相近，都是通过行和列的索引来读取

1
2
 
#读取B1单元格中的内容
ws.cell(0,1).value

 

当然也支持通过Excel坐标来读取数据，代码如下

1
2
 
#读取B1单元格中的内容
ws.cell("B1").value

 

写文件，只有一种操作方式，就是通过坐标。例如要向单元格C1写数据，就要用类似ws.cell(“C1”).value=something这样的方式。
一般推荐的方式是用openpyxl中的Writer类来实现。代码类似下面这样：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
from openpyxl.workbook import Workbook 
 
#ExcelWriter,里面封装好了对Excel的写操作
from openpyxl.writer.excel import ExcelWriter  

#get_column_letter函数将数字转换为相应的字母，如1-->A,2-->B  
from openpyxl.cell import get_column_letter  

#新建一个workbook  
wb = Workbook()  

#新建一个excelWriter  
ew = ExcelWriter(workbook = wb)  

#设置文件输出路径与名称  
dest_filename = r'empty_book.xlsx'  

#第一个sheet是ws  
ws = wb.worksheets[0]  

#设置ws的名称  
ws.title = "range names"

#向某个单元格中写入数据
ws.cell("C1").value=u'哈哈'

#最后保存文件
ew.save(filename=dest_filename)

 

向某个单元格内写文件时要先知道它对应的行数和列数，这里注意行数是从1开始计数的，而列则是从字母A开始，因此第一行第一列是A1，这实际上是采用坐标方式操作Excel。例如，想向表格的第三行第一列插入一个数值1.2，用xlwt写就是table.write(2,0,1.2),因为xlwt中行列索引都从0开始；而如果用openpyxl写就是ws.cell(“A3”).value=1.2。一般对于一个较大的列数，需要通过get_column_letter函数得到相应的字符，然后再调用cell函数写入。
下面是我之前写的一个代码的一部分，可以用来演示将多位数组保存到Excel文件中。为了体现多维数组，这里用到了numpy，另外这里为了简化过程，没有用ExcelWriter。代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
#coding:utf-8

from openpyxl import Workbook
from openpyxl.cell import get_column_letter

import numpy as np
#生成一个对角阵
a=np.diag([1,2,3,4,5])

#新建一个工作簿
wb=Workbook()
#使用当前激活的工作表（默认就是Excel中的第一张表）
ws=wb.active
#下面是对a的遍历，注意cell中行和列从1开始，a中索引从0开始。
for row in xrange(1,a.shape[0]+1):
    for col in xrange(1,a.shape[1]+1):
        col_letter=get_column_letter(col)
        ws.cell('%s%s'%(col_letter,row)).value=a[row-1,col-1]
wb.save('test.xlsx')

 

暂时介绍这么多，基本够用了。

总结

读取Excel时，选择openpyxl和xlrd差别不大，都能满足要求
写入少量数据且存为xls格式文件时，用xlwt更方便
写入大量数据（超过xls格式限制）或者必须存为xlsx格式文件时，就要用openpyxl了。

除以上几个模块外，还有Win32com等模块，但没有用过，就不说了。

 

【转自】 http://wenqiang-china.github.io/2016/05/13/python-opetating-excel/　　author: wenqiang

posted @ 2018-05-08 23:59  yanhuidj  阅读(7385)  评论(1)  编辑  收藏  举报