一.读Excel表（xlrd模块）

import xlrd #导入模块

data = xlrd.open_workbook('./report/result.xls') #打开excel

table = data.sheets()[0] #读sheet1

# table = data.sheet_by_name(u'Sheet1') #或通过sheet名获取

print(table.nrows) #行数

print(table.ncols) #列数

print(table.row_values(0)) #第一行

print(table.col_values(0)) #第一列

print(table.cell(1,2).value) #输出单元格坐标为（1,2）的值

二.写Excel表（xlwt模块）

# -*- coding: utf-8 -*-

#!/usr/bin/python

import xlwt #导入模块

wb = xlwt.Workbook() #创建Excel

ws = wb.add_sheet('test_sheet1') #创建sheet

ws.write(0, 0, 'hello') #在（0,0）写入hello

ws.write(1, 0, u'你好') #在（1,0）写入"你好"

wb.save('./report/result.xls') #保存

三.改Excel表（xlutils模块）

# -*- coding: utf-8 -*-

#!/usr/bin/python

import xlrd

import xlwt

from xlutils.copy import copy #导入模块

value=[[1,2,3],[4,5,6]]

oldwb = xlrd.open_workbook("./report/result.xls",formatting_info=True) # 打开excel，formatting_info保留原有格式

newwb = copy(oldwb) # 将xlrd拷贝转化为xlwt，变为可编辑

#sheet1中改写数据

newws = newwb.get_sheet(0) # 获取sheet1，括号里变为1，则获取sheet2，result.xls在创建时必须创建了sheet2,不然会报错

newws.write(1,0,"end"); #sheet1中改写数据

#追加新的sheet

add_sheet = newwb.add_sheet("sheet2",cell_overwrite_ok=True) # 增加新的sheet

rows = len(value) #行数

for i in range(0, rows):

for j in range(0, len(value[i])):

add_sheet.write(i, j, value[i][j]) #新的sheet写入数据

newwb.save("./report/result.xls")

注：写入中文时，如不加u，则会报：UnicodeDecodeError: 'ascii' codec can't decode byte 0xe4 in position 0: ordinal not in range(128)

四.增加样式，合并单元格

# -*- coding: utf-8 -*-

#!/usr/bin/python

import xlrd

import xlwt

from xlutils.copy import copy

def write_excel_xls(path,sheet_name,value):

workbook = xlwt.Workbook()

sheet = workbook.add_sheet(sheet_name,cell_overwrite_ok=True) #cell_overwrite_ok可以覆盖原单元格中数据

# 添加边框

borders = xlwt.Borders()

borders.left = xlwt.Borders.THIN

borders.right = xlwt.Borders.THIN

borders.top = xlwt.Borders.THIN

borders.bottom = xlwt.Borders.THIN

borders.bottom_colour = 0x12 #添加边框颜色为黑色

#居中显示

al = xlwt.Alignment()

al.horz = 0x02

al.vert = 0x01

#加入style

style = xlwt.XFStyle()

style.borders = borders

style.alignment = al

#设置第1列列宽

sheet.col(0).width = 4500

#数据写入excel

rows = len(value) #行数

for i in range(0, rows):

for j in range(0, len(value[i])):

sheet.write(i, j, value[i][j],style)

#合并单元格

#worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # 合并第1行到第2行的第0列到第3列。

type_list=['TLC Single plane','SLC Single plane']

list=[]

for i in type_list:

for j in range(rows):

if value[j][0]==i: #判断0列每行的数据是否等于type_list中的值

list.append(j) #将行号加入到list中

if len(list)!=0:

sheet.write_merge(list[0],list[-1], 0, 0, i,style) #合并首行、合并末行、合并首列、合并末列、单元格内容、格式

list=[]

workbook.save(path)

if __name__ == '__main__':

value=[[u'操作类型',u'Channel'],['TLC Single plane',0],['TLC Single plane',1],['TLC Single plane',2],['SLC Single plane',0],['SLC Single plane',1],['SLC Single plane',2]]

write_excel_xls("a.xls","a",value)