python：Excel的相关操作

一、读取Excel（xlrd）

1、打开文件

workbook = xlrd.open_workbook(r'F:\demo.xlsx')

2、获取所有sheet的名称

names = workbook.sheet_names() 

names = [sheet1,sheet2,sheet3]

3、根据sheet索引或者名称获取sheet内容

sheet1 = workbook.sheet_by_index(0) 
sheet2 = workbook.sheet_by_name('sheet2')

4、sheet的名称，行数，列数

sheet1.name
sheet1.nrows
sheet1.ncols

5、获取整行和整列的值（数组）

①获取第四行内容

rows = sheet2.row_values(3)

②获取第三列内容

cols = sheet2.col_values(2)

6、获取单元格内容

sheet2.cell(1,0).value.encode('utf-8')
sheet2.cell_value(1,0).encode('utf-8')

encode(‘utf-8’)可省略

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

ctype = sheet2.cell(1,0).ctype

python读取excel中单元格的内容返回的有5种类型：

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

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

如果ctype=3，这时需要使用xlrd的xldate_as_datetime,xldate_as_tuple来处理为date格式

date = xlrd.xldate_as_datetime(sheet1.cell_value(0, 1), workbook.datemode)

2019-02-26 00:00:00

date = xlrd.xldate_as_tuple(sheet1.cell_value(0, 1), workbook.datemode)

(2019, 2, 26, 0, 0, 0)

9、获取合并单元格的内容

①获取合并单元格的第一个cell的行列索引，才能读到值，读错了就是空值。
②获取工作表中所有的合并单元格

>>> sheet1.merged_cells

[(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]

merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一样，即(1, 3, 4, 5)的含义是：第1到2行（不包括3）合并，(7, 8, 2, 5)的含义是：第2到4列合并。

二、写入Excel（xlwt）

1、创建一个工作簿

f = xlwt.Workbook()

2、为工作簿添加sheet

sheet1 = f.add_sheet('sheet1',cell_overwrite_ok=True)

cell_overwrite_ok ： 如果为“真”，添加的工作表中的单元格如果写入一次以上，将不会引发异常

3、对内容进行样式设置

①设置字体

font = xlwt.Font()	  # 创建字体
font.name = 'name Times New Roman'    # 字体类型
font.colour_index = 1  # 字体颜色
font.height = 20*11  # 字体大小，11为字号，20为衡量单位
font.bold = False  # 字体加粗
font.underline = True  # 下划线
font.italic = True  # 斜体字
font.bold = True  # 黑体
font.num_format_str = '#,##0.00' # 设置文字模式

②设置单元格对齐方式

alignment = xlwt.Alignment() # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.horz = 0x02 # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment.vert = 0x01
alignment.wrap = 1 # 设置自动换行

③设置边框

borders = xlwt.Borders() 
borders.left = 1
borders.right = 2
borders.top = 3
borders.bottom = 4
borders.left_colour = i
borders.right_colour = i
borders.top_colour = i
borders.bottom_colour = i

细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7，
大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13

④设置列宽

sheet1.col(1).width = 11 * 256 # 一个中文等于两个英文等于两个字符，11为字符数，256为衡量单位

⑤设置单元格背景颜色

pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # 设置背景颜色的模式 
pattern.pattern_fore_colour = i # 背景颜色

颜色参数： 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta

⑥初始化样式

style = xlwt.XFStyle()
style.font = font
style.alignment = alignment
style.borders = borders
style.pattern = pattern 

4、数据写入

①一个单元格的内容写入：

sheet1.write(1, 0, 'value', style)

省略style则为默认样式。
②合并单元格的内容写入：

sheet1.write_merge(x, x + m, y, y + n, string, sytle)

x表示行，y表示列，m表示跨行个数，n表示跨列个数，string表示要写入的单元格内容，style表示单元格样式。其中x，y，m，n，都是以0开始计算的。
插入一个图片

sheet1.insert_bitmap(path, 2, 9) 

5、文件保存

f.save('demo.xls')

目前仅支持xls文件的保存，xlsx会报错。

三、更改已存在的Excel（xlutils）

①核心模块

from xlutils.copy import copy

②打开需要更改的excel文件

old_excel = xlrd.open_workbook('old.xls', formatting_info=True)

③将操作文件对象拷贝，变成可写的workbook对象

new_excel = copy(old_excel)

④获得第一个sheet的对象

sheet1 = new_excel.get_sheet(0)

⑤写入数据（同xlwt）

sheet1.write(0, 0, string, style)

⑥保存修改后的文件

new_excel.save('new.xls')