Python

xlrd模块用法的一些总结

Posted by FanHao on 2019-02-28

xlrd模块

介绍

python xlrd库主要用来读取excel表格文件的数据，支持.xls和.xlsx类型的文件。

python版本3.6，xlrd版本1.1.0。

1
pip3 install xlrd
基础用法

打开一份excel表格

1
2
import xlrd
data = xlrd.open_workbook(path)

获取excel文件所有工作簿的名称

1
names = data.sheet_names()

创建此sheet的对象

1
2
3
4
5
6
# 根据索引的顺序
sheet=data.sheet_by_index(sheet_index)
# 根据顺序，以下为获取第一个sheet
sheet=data.sheets()[0]
# 根据sheet名称创建对象
sheet=data.sheet_by_name(u'Sheet1')

获取表格的行数

1
2
3
4
# 获取该sheet中的有效行数
row_num=sheet.nrows
# 返回由该行中所有单元格对象组成的的列表
row_list=sheet.row(rowx)

获取列数

1
2
3
4
5
6
# 获取该sheet中的有效列数
cols_num=sheet.ncols
# 返回由该列中所有单元格对象组成的列表
sheet.col(colx, start_rowx=0, end_rowx=None)
# 返回由该列中所有单元格数据组成的列表
sheet.col(colx, start_rowx=0, end_rowx=None)

通过行和列读取整个sheet中的数据

1
2
3
4
5
6
7
8
9
10
11
12
rowlist=[]
for i in range(row_num):
    row = sheet.row_values(i)
    print("%s row is %s" %(i, row))
    rowlist.append(i)

collist=[]
for j in range(cols_num):
    col=sheet.cl_values(j)
    print("%s cal is %s" %(j, col))
    collist.append(col)

单元格操作

1
2
3
4
# 返回单元格对象，rowx,colx分别为表格的行数,列数。
sheet.cell(rowx,colx)
# 返回单元格的数据
sheet.cell_value(rowx,colx)
数据类型

excel表格的单元格内的数据，有不同的数据类型。对于一些数据，需要经过特殊处理，不然读取到的数据不符合我们的预期结果。这里尤其注意date类型的数据。

python读取excel中单元格的内容返回的有5种类型，如下所示:

  • 0 empty
  • 1 string
  • 2 number
  • 3 date
  • 4 boolean
  • 5 error

获取单元格内容的数据类型

1
ctype=sheet.cell(rowx, colx).ctype

打印单元格的数据类型。

以下是一个简单的读取excel表格，对其中date类型的数据，进行特殊处理。

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
import xlrd
from datetime import datetime
from xlrd import xldate_as_tuple
# 读取excel文件
worksheet=xlrd.open_workbook(filename)
# 获取第一个sheet
sheet=worksheet.sheet_by_index(0)
# 获取sheet的有效行数，有效列数
rows=sheet.nrows
cols=sheet.ncols
# 获取第一行，表格头部信息
head=sheet.row_values(0)
# 遍历行数
for i in range(1,rows):
    row=sheet.row_values(i)
    # 遍历列数
    for j in range(cols):
        # 获取单元格的值
        cell=sheet.cell_value(i, j)
        # 获取单元值的数据类型
        ctype=sheet.cell(i, j).ctype
        if ctype == 0:
            print("%s 类型为empty" % cell)
        elif ctype == 1:
            print("%s 类型为string" % cell)
        elif ctype == 2:
            print("%s 类型为number" % cell)
        elif ctype == 3:
            # date类型的数据需要特殊处理，不然读取数据的时候会被转成数字
            date = datetime(*xldate_as_tuple(cell, 0))
            cell = date.strftime('%Y/%d/%m %H:%M:%S')
            print("%s 类型为date" % cell)
        elif ctype == 4:
            print("%s 类型为boolean" % cell)
代码实例

实例一

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
import xlrd
from datetime import datetime
from xlrd import xldate_as_tuple

def read_excel(filename):
    # filename=os.path.basename(path)
    if filename.endswith('xls'):
        worksheet = xlrd.open_workbook(filename)
        sheet = worksheet.sheet_by_index(0)
        rows = sheet.nrows
        cols = sheet.ncols
        data = []
        head = sheet.row_values(0)
        print('head=',head)
        data.append(head)
        for i in range(1,rows):
            row = sheet.row_values(i)
            print('row=',row)
            for j in range(cols):
                cell=sheet.cell_value(i,j)
                ctype=sheet.cell(i,j).ctype
                # if ctype == 3:
                # print('cell',cell)
                # print(cols,len(row))
            if row:
                app = {}
                for j in range(len(row)):
                    ctype=sheet.cell(i,j).ctype
                    if ctype == 3:
                        date = datetime(*xldate_as_tuple(row[j], 0))
                        row[j]=date.strftime('%Y-%m-%d')
                    print(i,j)
                    print('type,value:',sheet.cell(i,j).ctype,row[j])
                    app[head[j]] = row[j]
                data.append(app)
    elif filename.endswith('xlsx'):
        worksheet = load_workbook(path)
        sheets = worksheet.get_sheet_names()
        sheet = worksheet.get_sheet_by_name(sheets[0])
        rows = sheet.rows
        columns = sheet.columns
        data = []
        head = [col.value for col in rows[0]]
        data.append(head)
        for row in rows:
            app = {}
            if row:
                for i in range(len(row)):
                    app[head[i]] = row[i].value
                data.append(app)
        # data.remove(data[0])
    # print('data=',data)
    return data

xlwt模块

介绍

主要用来写excel文件，生成excel文件。以下总结一些简单的用法

基础用法

创建一个worksheet

1
2
3
4
# 初始化一个excel文件对象
workbook=xlwt.Workbook(encoding='utf-8')
# 创建一个sheet,命名为worksheet1，允许重写
sheet1=workbook.add_sheet('worksheet1', cell_overwrite_ok=True)

写入excel

1
2
# 写入第row行，第col列的数据，style为单元格的风格样式
sheet1.write(row,col,style)

保存，生产excel文件

1
2
# 保存为test.xls
workbook.save('test.xls')
不常用方法

初始化表格样式

1
style=xlwt.XFStyle()

设置字体样式

1
2
3
4
5
6
7
8
9
10
font=xlwt.Font()
# 字体
font.name='Times New Roman'
# 加粗
font.bold=True
# 下划线
font.underline=True
# 斜体字
font.italic=True
style.font=font

设定对齐方式样式

1
2
3
4
5
6
7
alignment = xlwt.Alignment()
# May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT
alignment.horz = xlwt.Alignment.HORZ_CENTER
# May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM
alignment.vert = xlwt.Alignment.VERT_CENTER
# Add Alignment to Style
style.alignment = alignment

设定边框样式

1
2
3
4
5
6
7
8
9
10
11
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.DASHED #DASHED虚线 NO_LINE没有 THIN实线
borders.left_colour = 0x40
style.borders = borders # Add Borders to Style

# Create the Pattern
pattern = xlwt.Pattern()
# May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern = xlwt.Pattern.SOLID_PATTERN 
pattern.pattern_fore_colour = 5 
##May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Graystyle.pattern = pattern # Add Pattern to Style

合并列和行

1
sheet1.write_merge(1, 2, 0, 3, 'Second Merge', style)

单元格添加一个超链接

1
2
hyperlink=xlwt.Formula('HYPERLINK("http://www.google.com";"Google")')
sheet1.write(1, 1, hyperlink)
代码实例

实例一

1
2
3
4
5
6
7
8
9
10
11
12
def write_excel(data,filename):
    """
    根据传入数据data生成excel文件filename
    """
    print("**start write**")
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet('data',cell_overwrite_ok=True)
    # list_table_head = [data[0][0],data[0][1],data[0][2],data[0][3],data[0][4],data[0][5],data[0][6],data[0][7],data[0][8],data[0][9],data[0][10],data[0][11],data[0][12]]
    for row in range(len(data)):
        for col in range(len(data[row])):
            sheet.write(row,col,data[row][col])
    workbook.save(filename)
FEATURED TAGS
Python
FRIENDS