python xlrd模块_Python模块：xlwt、xlrd、openpyxl

一、xlwt写excel

xlwt是一个用于向旧的Excel文件写入数据和格式化信息的库，旧的Excel即后缀为.xls（2007版本之前的Office），新的后缀为.xlsx（2007版本及之后的Office）。xlwt、wlrd只能读写xls文件，而不能操作xlsx文件。旧的Excel一个工作表最多可有65536行，行用数字1—65536表示，最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表。

对于新版本的Office最大行数已经支持到了1048576，最大列数是16384。当然也有新的Python库来对新的Office Excel进行操作，就是openpyxl库，下面也会简单使用一下。

先看xlwt库，在写入Excel表格之前，你必须初始化workbook对象，然后添加一个workbook对象。比如：

import xlwt

file = xlwt.Workbook()

table = file.add_sheet('sheet name')

1

2

3

importxlwt

file=xlwt.Workbook()

table=file.add_sheet('sheet name')

这样表单就被创建了，然后就可以写入数据：

table.write(0,0,'row and column')

1

table.write(0,0,'row and column')

这里第一个参数0表示Excel的行，第二个参数0表示Excel的列，第三个参数就是写入到此单元格的内容。

当你更改表单内容的时候，会有警告提示。解决方式就是使用cell_overwrite_ok=True来创建worksheet。

file.add_sheet('sheet name', cell_overwrite_ok=True)

1

file.add_sheet('sheet name',cell_overwrite_ok=True)

这样就可以更改单元格的内容了。

然后就可以保存文件（这里不需要像打开文件一样需要close文件)

file.save('demo.xls')

1

file.save('demo.xls')

除了上面这些基本操作外，也支持格式化设置。如单元格格式属性或单元格样式的属性设置，字体设置等，格式属性分为6组：

Group

Attributes

Number format

数字格式索引（FORMAT记录的索引）

Font

字体索引（FONT记录的索引）

Alignment

水平和垂直对齐，文本换行，缩进，方向/旋转，文本方向

Border

边框线条样式和颜色

Background

背景区域风格和颜色

Protection

单元格锁定，公式隐藏

使用方式：

# 初始化样式

style = xlwt.XFStyle()

# 创建一个与样式一起使用的字体

font = xlwt.Font()

font.name = 'Times New Roman'

font.bold = True

# 设置样式的字体

style.font = font

# 写入时应用样式

sheet.write(0, 0, 'some bold Times text', style)

1

2

3

4

5

6

7

8

9

10

11

12

13

# 初始化样式

style=xlwt.XFStyle()

# 创建一个与样式一起使用的字体

font=xlwt.Font()

font.name='Times New Roman'

font.bold=True

# 设置样式的字体

style.font=font

# 写入时应用样式

sheet.write(0,0,'some bold Times text',style)

从MySQL中读出数据，然后批量插入Excel表格中。

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import pymysql

import xlwt

connection = pymysql.connect(

host = '127.0.0.1',

port = 3306,

user = 'root',

passwd = '123456',

db = 'sbtest',

charset = 'utf8'

)

try:

file = xlwt.Workbook()

table = file.add_sheet('sheet name', cell_overwrite_ok=True)

with connection.cursor() as cursor:

sql = "select * from sbtest1"

curosr = connection.cursor()

curosr.execute(sql)

column = 0

for i in curosr.description:

table.write(0,column,i[0])

column += 1

row = 1

column = 0

for i in curosr.fetchall():

for i in i:

table.write(row,column,i)

column += 1

row += 1

column = 0

file.save('demo.xls')

finally:

connection.close()

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

29

30

31

32

33

34

35

36

37

38

#!/usr/bin/python

# -*- coding: UTF-8 -*-

importpymysql

importxlwt

connection=pymysql.connect(

host='127.0.0.1',

port=3306,

user='root',

passwd='123456',

db='sbtest',

charset='utf8'

)

try:

file=xlwt.Workbook()

table=file.add_sheet('sheet name',cell_overwrite_ok=True)

withconnection.cursor()ascursor:

sql="select * from sbtest1"

curosr=connection.cursor()

curosr.execute(sql)

column=0

foriincurosr.description:

table.write(0,column,i[0])

column+=1

row=1

column=0

foriincurosr.fetchall():

foriini:

table.write(row,column,i)

column+=1

row+=1

column=0

file.save('demo.xls')

finally:

connection.close()

二、xlrd读excel

上面介绍了写Excel的库，有写自然就有读的库了，就是xlrd，使用起来跟xlwd差不多。

首先，打开workbook：

import xlrd

table = xlrd.open_workbook('demo.xls')

1

2

importxlrd

table=xlrd.open_workbook('demo.xls')

得到表的sheet名称：

table.sheet_names()

1

table.sheet_names()

得到第一个sheet名称，两种方式：索引和名字

sheet = table.sheet_by_index(0)

sheet = table.sheet_by_name('sheet name')

1

2

sheet=table.sheet_by_index(0)

sheet=table.sheet_by_name('sheet name')

递归打印出每行的信息（列表）：

for r in range(sheet.nrows):

print(sheet.row_values(r))

1

2

forrinrange(sheet.nrows):

print(sheet.row_values(r))

如果只想返回第一列数据：

sheet.col_values(0)

1

sheet.col_values(0)

通过索引读取数据：

sheet.cell(0,0).value

sheet.cell(rowx=3,colx=2).value

1

2

sheet.cell(0,0).value

sheet.cell(rowx=3,colx=2).value

注意：这里的索引都是从0开始的，根据行和列的坐标读取具体的Excel单元格。

三、openpyxl操作excel

其实上面的xlwt或xlrd都是相对限制较多，比如无法修改excel表格，以及读写一个sheet不能超过65535条，如果超过就会报错：ValueError: row index was 65536, not allowed by .xls format。这也是因为xlwt或xlrd只能用于后缀为.xls老版本的Excel，也是Excel本身的限制。上面也说了，新版Excel已经支持更大的行数了。

openpyxl库就是用来操作新版的Excel，相对xlwt和xlrd也更加友好。在openpyxl中，主要用到三个概念：Workbooks，Sheets，Cells。Workbook就是一个excel工作表；Sheet是工作表中的一张表页；Cell就是简单的一个格。openpyxl就是围绕着这三个概念进行的，不管读写都是“三板斧”：打开Workbook，定位Sheet，操作Cell。

# 导入模块

>>> from openpyxl import Workbook

# 打开Workbook

>>> file = Workbook()

# 创建Sheet

>>> sheet = file.active

# 定义Sheet名称

>>> sheet.title = "sheet name"

# 操作Cell

>>> sheet['A1'] = 4

# 获取Cell值

>>> sheet['A1'].value

# 保存xlsx文件

>>> file.save('demo.xlsx')

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

# 导入模块

>>>fromopenpyxlimportWorkbook

# 打开Workbook

>>>file=Workbook()

# 创建Sheet

>>>sheet=file.active

# 定义Sheet名称

>>>sheet.title="sheet name"

# 操作Cell

>>>sheet['A1']=4

# 获取Cell值

>>>sheet['A1'].value

# 保存xlsx文件

>>>file.save('demo.xlsx')

关于openpyxl更为详细的使用可以看这篇文章“OpenPyXL的使用教程（一）”。

在使用上与xlwt/xlrt不同的是，xlwt/xlrt可以Excel行和列的坐标（0,0）来定位Cell，而openpyxl使用类似A1-Z1这样的方式来定位Cell。其实在处理起来使用A1-Z1这种方式定位Cell不是很方便，但列数大于A-Z后，就要从AA-AZ开始算起。我这里提供了一个生成定位Cell的字母列表的方法。

def GenerateCharacter(n, limit=65535):

import string

c = int(n / 26)

y = int(n % 26)

if n <= 26:

character = []

for i in range(n):

character.append(string.ascii_uppercase[i])

return character

elif n <= limit:

character = [c for c in string.ascii_uppercase]

for i in range(c):

if i + 1 == c:

for e in range(y):

character.append(character[i] + string.ascii_uppercase[e])

else:

for e in [character[i] + c for c in string.ascii_uppercase]:

character.append(e)

return character

else:

return "more than %s restrictions" % limit

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

defGenerateCharacter(n,limit=65535):

importstring

c=int(n/26)

y=int(n%26)

ifn<=26:

character=[]

foriinrange(n):

character.append(string.ascii_uppercase[i])

returncharacter

elifn<=limit:

character=[cforcinstring.ascii_uppercase]

foriinrange(c):

ifi+1==c:

foreinrange(y):

character.append(character[i]+string.ascii_uppercase[e])

else:

forein[character[i]+cforcinstring.ascii_uppercase]:

character.append(e)

returncharacter

else:

return"more than %s restrictions"%limit

只需要传入一个数字即可，数字就是你要生成的字母数量，默认最大生成65535，你也可以自己限制。

>>> print(GenerateCharacter(10))

['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']

1

2

>>>print(GenerateCharacter(10))

['A','B','C','D','E','F','G','H','I','J']

下面提供了一个从数据库读取数据然后循序插入到Excel中的例子：

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import pymysql

from openpyxl import Workbook

class DatabaseConn:

def __init__(self, ip=None, user=None, password=None, db=None, port=None):

self.ip = ip

self.user = user

self.password = password

self.db = db

self.port = int(port)

self.con = object

def __enter__(self):

self.con = pymysql.connect(

host=self.ip,

user=self.user,

passwd=self.password,

db=self.db,

charset='utf8mb4',

port=self.port

)

return self

def __exit__(self, exc_type, exc_val, exc_tb):

self.con.close()

def select_execute(self, sql=None):

with self.con.cursor() as cursor:

cursor.execute(sql)

result = cursor.fetchall()

fields = cursor.description

return fields, result

class ExportSQL:

def GenerateCharacter(self, n, limit=65535):

import string

c = int(n / 26)

y = int(n % 26)

if n <= 26:

character = []

for i in range(n):

character.append(string.ascii_uppercase[i])

return character

elif n <= limit:

character = [c for c in string.ascii_uppercase]

for i in range(c):

if i + 1 == c:

for e in range(y):

character.append(character[i] + string.ascii_uppercase[e])

else:

for e in [character[i] + c for c in string.ascii_uppercase]:

character.append(e)

return character

else:

return "more than %s restrictions" % limit

def execute(self):

try:

with DatabaseConn(

ip='127.0.0.1',

user='root',

password='',

port=3306,

db='sbtest',

) as curosr:

fields, result = curosr.select_execute(sql='select * from sbtest1 limit 10')

file = Workbook()

sheet = file.active

sheet.title = "sheet name"

ascii_uppercase = self.GenerateCharacter(len(fields))

for field in range(0, len(fields)):

sheet["%s%d" % (ascii_uppercase[field], 1)] = fields[field][0]

for row in range(1, len(result) + 1):

for field in range(0, len(fields)):

sheet["%s%d" % (ascii_uppercase[field], row + 1)] = result[row - 1][field]

file.save('demo.xlsx')

except Exception as e:

print(e)

ExportSQL().execute()

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

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

#!/usr/bin/python

# -*- coding: UTF-8 -*-

importpymysql

fromopenpyxlimportWorkbook

classDatabaseConn:

def__init__(self,ip=None,user=None,password=None,db=None,port=None):

self.ip=ip

self.user=user

self.password=password

self.db=db

self.port=int(port)

self.con=object

def__enter__(self):

self.con=pymysql.connect(

host=self.ip,

user=self.user,

passwd=self.password,

db=self.db,

charset='utf8mb4',

port=self.port

)

returnself

def__exit__(self,exc_type,exc_val,exc_tb):

self.con.close()

defselect_execute(self,sql=None):

withself.con.cursor()ascursor:

cursor.execute(sql)

result=cursor.fetchall()

fields=cursor.description

returnfields,result

classExportSQL:

defGenerateCharacter(self,n,limit=65535):

importstring

c=int(n/26)

y=int(n%26)

ifn<=26:

character=[]

foriinrange(n):

character.append(string.ascii_uppercase[i])

returncharacter

elifn<=limit:

character=[cforcinstring.ascii_uppercase]

foriinrange(c):

ifi+1==c:

foreinrange(y):

character.append(character[i]+string.ascii_uppercase[e])

else:

forein[character[i]+cforcinstring.ascii_uppercase]:

character.append(e)

returncharacter

else:

return"more than %s restrictions"%limit

defexecute(self):

try:

withDatabaseConn(

ip='127.0.0.1',

user='root',

password='',

port=3306,

db='sbtest',

)ascurosr:

fields,result=curosr.select_execute(sql='select * from sbtest1 limit 10')

file=Workbook()

sheet=file.active

sheet.title="sheet name"

ascii_uppercase=self.GenerateCharacter(len(fields))

forfieldinrange(0,len(fields)):

sheet["%s%d"%(ascii_uppercase[field],1)]=fields[field][0]

forrowinrange(1,len(result)+1):

forfieldinrange(0,len(fields)):

sheet["%s%d"%(ascii_uppercase[field],row+1)]=result[row-1][field]

file.save('demo.xlsx')

exceptExceptionase:

print(e)

ExportSQL().execute()

上面是一个一次性导出 Excel 的脚本，如果有多次查询追加导入到 Excel 中，大概的方式如下：

#!/usr/bin/python

# -*- coding: UTF-8 -*-

import pymysql

from openpyxl import Workbook

class DatabaseConn:

def __init__(self, ip=None, user=None, password=None, db=None, port=None):

self.ip = ip

self.user = user

self.password = password

self.db = db

self.port = int(port)

self.con = object

def __enter__(self):

self.con = pymysql.connect(

host=self.ip,

user=self.user,

passwd=self.password,

db=self.db,

charset='utf8mb4',

port=self.port

)

return self

def __exit__(self, exc_type, exc_val, exc_tb):

self.con.close()

def select_execute(self, sql=None):

with self.con.cursor() as cursor:

cursor.execute(sql)

result = cursor.fetchall()

fields = cursor.description

return fields, result

def execute(sql):

try:

with DatabaseConn(

ip='',

user='',

password='',

port=3306,

db='',

) as curosr:

fields, result = curosr.select_execute(sql=sql)

return (fields, result)

except Exception as e:

print(e)

def generate_character(n, limit=65535):

import string

c = int(n / 26)

y = int(n % 26)

if n <= 26:

character = []

for i in range(n):

character.append(string.ascii_uppercase[i])

return character

elif n <= limit:

character = [c for c in string.ascii_uppercase]

for i in range(c):

if i + 1 == c:

for e in range(y):

character.append(character[i] + string.ascii_uppercase[e])

else:

for e in [character[i] + c for c in string.ascii_uppercase]:

character.append(e)

return character

else:

return "more than %s restrictions" % limit

if __name__ == '__main__':

count = 0

file = Workbook()

sheet = file.active

sheet.title = "sheet name"

f = open('/tmp/1.txt', 'r')

while True:

line = f.readline()

if not line:

break

else:

sql = ("selec * from sbtest.sbtest1 where id={}".format(line))

fields, result = execute(sql)

ascii_uppercase = generate_character(len(fields))

if count == 0:

for field in range(0, len(fields)):

sheet["%s%d" % (ascii_uppercase[field], 1)] = fields[field][0]

if result:

for row in range(count + 1, count + len(result) + 1):

for field in range(0, len(fields)):

sheet["%s%d" % (ascii_uppercase[field], row + 1)] = result[row - count - 1][field]

count += len(result) + 1

else:

fields = [None] * len(fields)

if len(fields) >= 2:

fields[0] = line

else:

print("fields data error")

for field in range(0, len(fields)):

sheet["%s%d" % (ascii_uppercase[field], count + 1)] = fields[field]

count += 1

print(count)

file.save('demo.xlsx')

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

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

#!/usr/bin/python

# -*- coding: UTF-8 -*-

importpymysql

fromopenpyxlimportWorkbook

classDatabaseConn:

def__init__(self,ip=None,user=None,password=None,db=None,port=None):

self.ip=ip

self.user=user

self.password=password

self.db=db

self.port=int(port)

self.con=object

def__enter__(self):

self.con=pymysql.connect(

host=self.ip,

user=self.user,

passwd=self.password,

db=self.db,

charset='utf8mb4',

port=self.port

)

returnself

def__exit__(self,exc_type,exc_val,exc_tb):

self.con.close()

defselect_execute(self,sql=None):

withself.con.cursor()ascursor:

cursor.execute(sql)

result=cursor.fetchall()

fields=cursor.description

returnfields,result

defexecute(sql):

try:

withDatabaseConn(

ip='',

user='',

password='',

port=3306,

db='',

)ascurosr:

fields,result=curosr.select_execute(sql=sql)

return(fields,result)

exceptExceptionase:

print(e)

defgenerate_character(n,limit=65535):

importstring

c=int(n/26)

y=int(n%26)

ifn<=26:

character=[]

foriinrange(n):

character.append(string.ascii_uppercase[i])

returncharacter

elifn<=limit:

character=[cforcinstring.ascii_uppercase]

foriinrange(c):

ifi+1==c:

foreinrange(y):

character.append(character[i]+string.ascii_uppercase[e])

else:

forein[character[i]+cforcinstring.ascii_uppercase]:

character.append(e)

returncharacter

else:

return"more than %s restrictions"%limit

if__name__=='__main__':

count=0

file=Workbook()

sheet=file.active

sheet.title="sheet name"

f=open('/tmp/1.txt','r')

whileTrue:

line=f.readline()

ifnotline:

break

else:

sql=("selec * from sbtest.sbtest1 where id={}".format(line))

fields,result=execute(sql)

ascii_uppercase=generate_character(len(fields))

ifcount==0:

forfieldinrange(0,len(fields)):

sheet["%s%d"%(ascii_uppercase[field],1)]=fields[field][0]

ifresult:

forrowinrange(count+1,count+len(result)+1):

forfieldinrange(0,len(fields)):

sheet["%s%d"%(ascii_uppercase[field],row+1)]=result[row-count-1][field]

count+=len(result)+1

else:

fields=[None]*len(fields)

iflen(fields)>=2:

fields[0]=line

else:

print("fields data error")

forfieldinrange(0,len(fields)):

sheet["%s%d"%(ascii_uppercase[field],count+1)]=fields[field]

count+=1

print(count)

file.save('demo.xlsx')

这是一个从文件中读取数据，然后进行查询，查询结果追加到 Excel 中，没有查询结果的把文件读取的数值追加进 Excel 中。

四、xlutils修改excel

Python中一般使用xlrd（excel read）来读取Excel文件，使用xlwt（excel write）来生成Excel文件（可以控制Excel中单元格的格式），需要注意的是，用xlrd读取excel是不能对其进行操作的：xlrd.open_workbook()方法返回xlrd.Book类型，是只读的，不能对其进行操作。而 xlwt.Workbook()返回的xlwt.Workbook类型的save(filepath)方法可以保存excel文件。因此对于读取和生成Excel文件都非常容易处理，但是对于已经存在的Excel文件进行修改就比较麻烦了。不过，还有一个xlutils（依赖于xlrd和xlwt）提供复制excel文件内容和修改文件的功能。其实际也只是在xlrd.Book和xlwt.Workbook之间建立了一个管道而已。如果有用到可以参考：Python xlrd、xlwt、xlutils读取、修改Excel文件

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。