xlrd 模块用法的一些总结

Question

xlrd 模块

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

python 版本 3.6，xlrd 版本 1.1.0。

pip3 install xlrd

基础用法

打开一份 excel 表格

import xlrd
data = xlrd.open_workbook(path)

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

names = data.sheet_names()

创建此 sheet 的对象

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

获取表格的行数

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

获取列数

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

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

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)

单元格操作

# 返回单元格对象，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

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

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

打印单元格的数据类型。

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

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)

代码实例

实例一

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

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

写入 excel

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

保存，生产 excel 文件

# 保存为 test.xls
workbook.save('test.xls')

不常用方法

初始化表格样式

style=xlwt.XFStyle()

设置字体样式

font=xlwt.Font()
# 字体
font.name='Times New Roman'
# 加粗
font.bold=True
# 下划线
font.underline=True
# 斜体字
font.italic=True
style.font=font

设定对齐方式样式

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

设定边框样式

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

合并列和行

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

单元格添加一个超链接

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

代码实例

实例一

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)