python3 操作excel表

 python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel的库可从这里下载https://pypi.python.org/pypi。下面分别记录python读和写excel.

python读excel——xlrd

 

import xlrd
data = xlrd.open_workbook('host_list02.xlsx')
# 获取sheets数目：
print (data.nsheets)
# 获取sheets列表：
print(data.sheets())

# 获取sheets name列表：
print(data.sheet_names())
# #sheets返回一个sheet列表
sheet = data.sheets()[0]
sheet = data.sheet_by_index(0)    #通过索引顺序获取
sheet = data.sheet_by_name(u'Sheet1')#通过名称获取
# 第几行的数据内容,索引是从0开始的
print(sheet.row_values(0))
# 第几列的数据,这列所有的数据
print(sheet.col_values(3))
# 获取列数
print(sheet.ncols)
# 获取行数
print(sheet.nrows)
# 获取名字
print(sheet.name)

# 可以循环获取行内容
for i  in range(sheet.nrows):
    print(sheet.row_values(i))

 现有一份表格文件:

其中sheet1 的内容是

sheet2 的内容是

写python代码操作表格:

def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook(r'host_list02.xlsx')
    # 获取所有的sheet的名字
    print(workbook.sheet_names())
    # 获取第二个sheet的表名
    sheet2_name = workbook.sheet_names()[1]

    # sheet1,索引是从0开始,得到sheet1表的句柄.用这个可以操作表
    sheet1 = workbook.sheet_by_index(0)
    print(sheet1.cell(1, 0).value.encode('utf-8'))
    # 得到第一行,第一列的数据是二进制的数据
    s = sheet1.cell(0, 0).value.encode('utf-8')
    #  1 ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
    print(sheet1.cell(1,0).ctype)
    # 将二进制的数据转换成utf-8编码
    print(s.decode('utf-8'))

    # 获取整行和整列的数据
    # 获取第二行的数据
    row2 = sheet1.row_values(1)
    # 获取第二列的数据
    cols2 = sheet1.col_values(1)

    sheet1 = workbook.sheet_by_index(0)

    # 循环得到sheet1表的内容
    for i in range(sheet1.nrows):
       print(sheet1.row_values(i))


read_excel()

 运行的结果:

['Sheet1', 'Sheet2', 'Sheet3']
b'10.127.0.2'
1
10.127.0.1
['10.127.0.1', 'ceshi', 43466.0]
['10.127.0.2', 'ceshi', 43467.0]
['10.127.0.3', 'ceshi', 43468.0]
['10.127.0.4', 'ceshi', 43469.0]
['10.127.0.5', 'ceshi', 43470.0]
['10.127.0.6', 'ceshi', 43471.0]
['10.127.0.7', 'ceshi', 43472.0]
['10.127.0.8', 'ceshi', 43473.0]
['10.127.0.9', 'ceshi', 43474.0]
['10.127.0.10', 'ceshi', 43475.0]
['10.127.0.11', 'ceshi', 43476.0]
['10.127.0.12', 'ceshi', 43477.0]
['10.127.0.13', 'ceshi', 43478.0]
['10.127.0.14', 'ceshi', 43479.0]
['10.127.0.15', 'ceshi', 43480.0]

 从上面的结果,我们可以看出,在操作时间类型的数据时,结果不是我们想要的

1、python读取excel中单元格内容为日期的方式

python读取excel中单元格的内容返回的有5种类型，即上面例子中的ctype:

 1 ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error 

即date的ctype=3，这时需要使用xlrd的xldate_as_tuple来处理为date格式，先判断表格的ctype=3时xldate才能开始操作。现在命令行看下：

 代码:

import xlrd
import xlwt
from datetime import date
from datetime import datetime


def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook(r'host_list02.xlsx')
    # sheet1,索引是从0开始,得到sheet1表的句柄.用这个可以操作表
    sheet1 = workbook.sheet_by_index(0)

    # 循环得到sheet1表的内容
    for i in range(sheet1.nrows):
        for j in range(sheet1.ncols):
            # 当表格中的内容是date的时间类型是换成python的时间类型
            if sheet1.cell(i,j).ctype == 3:
                d = xlrd.xldate_as_tuple(sheet1.cell_value(i, j), workbook.datemode)
                print(date(*d[:3]),end=' ')
            else:
                print(sheet1.cell(i,j).value,end=' ')

        print()



read_excel()

结果:

10.127.0.1 ceshi 2019-01-01 
10.127.0.2 ceshi 2019-01-02 
10.127.0.3 ceshi 2019-01-03 
10.127.0.4 ceshi 2019-01-04 
10.127.0.5 ceshi 2019-01-05 
10.127.0.6 ceshi 2019-01-06 
10.127.0.7 ceshi 2019-01-07 
10.127.0.8 ceshi 2019-01-08 
10.127.0.9 ceshi 2019-01-09 
10.127.0.10 ceshi 2019-01-10 
10.127.0.11 ceshi 2019-01-11 
10.127.0.12 ceshi 2019-01-12 
10.127.0.13 ceshi 2019-01-13 
10.127.0.14 ceshi 2019-01-14 
10.127.0.15 ceshi 2019-01-15

 那么问题又来了，print(sheet1.cell(2,2).value)是空,但这是合并后的单元格print(sheet1.cell(2,2).ctype)返回的值是0，说明这个单元格的值是空值，明明是合并的单元格内容"ceshi"，这个是我觉得这个包功能不完善的地方，如果是合并的单元格那么应该合并的单元格的内容一样，但是它只是合并的第一个单元格的有值，其它的为空。

2、读取合并单元格的内容

这个是真没技巧，只能获取合并单元格的第一个cell的行列索引，才能读到值，读错了就是空值。

即合并行单元格读取行的第一个索引，合并列单元格读取列的第一个索引，如上述，读取行合并单元格"好朋友"和读取列合并单元格"暂无"只能如下方式：

1、获取合并的单元格

　　读取文件的时候需要将formatting_info参数设置为True，默认是False，所以上面获取合并的单元格数组为空，,早python当中,这个字段是False但可以通print(sheet1.merged_cells)查看是否有合并的单元格

def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook(r'host_list02.xlsx')
    # sheet1,索引是从0开始,得到sheet1表的句柄.用这个可以操作表
    sheet1 = workbook.sheet_by_index(0)
  
    # 获取是否有合并的单元格,空是没有,不为空是有
    print(sheet1.merged_cells)

read_excel()

 结果:

[(14, 15, 1, 3), (8, 9, 1, 3), (9, 10, 1, 3), (10, 11, 1, 3), (11, 12, 1, 3), (12, 13, 1, 3), (13, 14, 1, 3), (0, 1, 1, 3), (1, 2, 1, 3), (2, 3, 1, 3), (3, 4, 1, 3), (4, 5, 1, 3), (5, 6, 1, 3), (6, 7, 1, 3), (7, 8, 1, 3)]

是说第二列到第三列合并,merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一样

发现规律了没？是的，获取merge_cells返回的row和col低位的索引即可！ 于是可以这样一劳永逸：

 1 >>> merge = []
 2 >>> for (rlow,rhigh,clow,chigh) in sheet1.merged_cells:
 3   merge.append([rlow,clow])
 4    
 5 >>> merge
 6 [[14,1], [8, 1], [9, 1]'......]
 7 >>> for index in merge:
 8   print sheet2.cell_value(index[0],index[1])

python写excel——xlwt

def set_style(name,height,bold=False):
    # 初始化样式
    style = xlwt.XFStyle()

    font = xlwt.Font() # 创建字体
    font.bold = bold
    font.color_index = 4
    font.height = height
    font.name = name
    style.font = font
    return style

def write_execl():
    f = xlwt.Workbook()

    sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True)
    row0 = [u'业务',u'状态',u'北京',u'上海',u'广州',u'深圳',u'状态小计',u'合计']
    column0 = [u'机票', u'船票', u'火车票', u'汽车票', u'其它']
    status = [u'预订', u'出票', u'退票', u'业务小计']
    for i in range(0, len(row0)):
        sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True))

    i ,j = 1,0
    while i < 4*len(column0) and j < len(column0):
        sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True)) #第一列
        sheet1.write_merge(i, i + 3, 7, 7)  # 最后一列"合计"
        i += 4
        j +=1
    sheet1.write_merge(21, 21, 0, 1, u'合计', set_style('Times New Roman', 220, True))


    i =0
    while i < 4*len(column0):
        for j in range(0,len(status)):
            sheet1.write(j+i+1,1,status[j])
        i+=4

    sheet2 = f.add_sheet(u'sheet2',cell_overwrite_ok=True) #创建sheet2
    row0 = [u'姓名',u'年龄',u'出生日期',u'爱好',u'关系']
    column0 = [u'小杰',u'小胖',u'小明',u'大神',u'大仙',u'小敏',u'无名']

    #生成第一行
    for i in range(0,len(row0)):
     sheet2.write(0,i,row0[i],set_style('Times New Roman',220,True))

    #生成第一列
    for i in range(0,len(column0)):
     sheet2.write(i+1,0,column0[i],set_style('Times New Roman',220))

    sheet2.write(1,2,'1991/11/11')
    sheet2.write_merge(7,7,2,4,u'暂无') #合并列单元格
    sheet2.write_merge(1,2,4,4,u'好朋友') #合并行单元格


    f.save('demo1.xls')

write_execl()

需要稍作解释的就是write_merge方法：

write_merge(x, x + m, y, w + n, string, sytle)
x表示行，y表示列，m表示跨行个数，n表示跨列个数，string表示要写入的单元格内容，style表示单元格样式。其中，x，y，w，h，都是以0开始计算的。

这个和xlrd中的读合并单元格的不太一样。

如上述：sheet1.write_merge(21,21,0,1,u'合计',set_style('Times New Roman',220,True))

即在22行合并第1,2列，合并后的单元格内容为"合计"，并设置了style。

如果需要创建多个sheet，则只要f.add_sheet即可。

如在上述write_excel函数里f.save('demo1.xlsx') 

效果: