Excel操作 - Python

#xlrd - Python读excel

##安装xlrd模块
	pip install xlrd

##打开文件（获取一个工作表）：

data = xlrd.open_workbook("01.xls")   #打开当前目录下名为01.xls的文档
	
	table = data.sheet_by_name("sheet1")  #通过名称获取，如读取sheet1表单
	table = data.sheet_by_index(0)        #通过索引获取，例如打开第一个sheet表格
	table = data.sheets()[0]              #通过索引顺序获取
 
	names = data.sheet_names()            #返回book中所有工作表的名字

##对行进行操作：
	nrows = table.nrows                   #获取该sheet中的有效行数
	table.row(rowx)                       #返回由该行中所有的单元格对象组成的列表
	table.row_slice(rowx)                 #返回由该列中所有的单元格对象组成的列表
	table.row_types(rowx, start_colx=0, end_colx=None)    #返回由该行中所有单元格的数据类型组成的列表
	table.row_values(rowx, start_colx=0, end_colx=None)   #返回由该行中所有单元格的数据组成的列表
	table.row_len(rowx)                   #返回该列的有效单元格长度

##对列进行操作：
	ncols = table.ncols                                 #获取列表的有效列数
	table.col(colx, start_rowx=0, end_rowx=None)        #返回由该列中所有的单元格对象组成的列表
	table.col_slice(colx, start_rowx=0, end_rowx=None)  #返回由该列中所有的单元格对象组成的列表
	table.col_types(colx, start_rowx=0, end_rowx=None)  #返回由该列中所有单元格的数据类型组成的列表
	table.col_values(colx, start_rowx=0, end_rowx=None) #返回由该列中所有单元格的数据组成的列表

##对单元格进行操作：
	table.cell(rowx, colx)  # 返回单元格对象
	table.cell_type(rowx, colx)  # 返回单元格中的数据类型
	table.cell_value(rowx,colx)   #返回单元格中的数据

##对合并单元格处理：
	import xlrd

workbook = xlrd.open_workbook('./data/test_data.xlsx')
	sheet = workbook.sheet_by_name('Sheet1')
	
	# merged_cells 获取当前表格所有合并单元格的位置信息 ，返回一个列表
	print( sheet.merged_cells )
	[(1, 5, 0, 1)]
	# merged_cells 返回的是一个列表，每一个元素是合并单元格的位置信息的数组，数组包含四个元素（起始行，结束行，起始列，结束列）

##对合并单元格判断取值：
	import xlrd

workbook = xlrd.open_workbook('./data/test_data.xlsx')
	sheet = workbook.sheet_by_name('Sheet1')
	# merged_cells 获取当前表格所有合并单元格的位置信息 ，返回一个列表
	merged = sheet.merged_cells
	row_index = 3  ; col_index = 0
	for (rlow, rhigh, clow, chigh) in merged:  # 遍历表格中所有合并单元格位置信息
	    if (row_index >= rlow and row_index < rhigh):  # 行坐标判断
	        if (col_index >= clow and col_index < chigh):  # 列坐标判断
	            # 如果满足条件，就把合并单元格第一个位置的值赋给其它合并单元格
	            cell_value = sheet.cell_value(rlow,clow)
	print( cell_value )

---
#xlsxwriter - Python写excel

##添加sheet并写入
	import xlsxwriter
	workbook = xlsxwriter.Workbook("test.xlsx")
	table = workbook.add_worksheet("Sheet1")
	table.write(0,0,'hello',【格式】)
	workbook.close()

---
##设置长宽
	import xlsxwriter
	workbook = xlsxwriter.Workbook("test.xlsx")
	table = workbook.add_worksheet("Sheet1")
	table.set_column(0, 0, 10, 【格式】)  # 设置第1列宽度
    table.set_column(1, 2, 20, 【格式】)  # 设置第2，3列宽度
    table.set_row(0, 10, 【格式】)  # 设置第1行高度
    table.set_row(1, 20, 【格式】)  # 设置第2行高度
	workbook.close()

---
##设置字体颜色、大小、边框、背景颜色、居中
####通过字典的方式直接设置格式。
	import xlsxwriter
	workbook = xlsxwriter.Workbook("test.xlsx")
	table = workbook.add_worksheet("Sheet1")
	workfomat = workbook.add_format({
		'bold':  True,                 #字体加粗
		'border':1,                    #单元格边框宽度
		'align':    'center',          #对齐方式
		'valign':   'vcenter',         #字体对齐方式
		'fg_color': '#F4B084',         #单元格背景颜色
		'font_color': '#FFFFFF',	   #字体颜色
	})
	table.write(0,0,'hello',workfomat)
	workbook.close()

####通过format对象的方式设置单元格格式。
	import xlsxwriter
	workbook = xlsxwriter.Workbook("test.xlsx")
	table = workbook.add_worksheet("Sheet1")
	workfomat = workbook.add_format()
	workfomat.set_bold(1)                #设置边框宽度
	workfomat.set_num_format('0.00')     #格式化数据格式为小数点后两位
	workfomat.set_align('center')        #设置对齐方式
	workfomat.set_fg_color('blue')       #设置单元格背景颜色
	table.write(0,0,'hello',workfomat)
	workbook.close()

---
##合并单元格
>####merge_range(参数1, 参数2, 参数3, 参数4, 参数5, 参数6)
参数1和2：起始row和起始col
参数3和4：结束row和结束col
参数5：内容
参数6：cell_format样式

---
	import xlsxwriter
	workbook = xlsxwriter.Workbook("test.xlsx")
	table = workbook.add_worksheet("Sheet1")
	table.merge_range(0, 1, 0, 2, "merge",【格式】)
	workbook.close()

---
##使用excel函数
	import xlsxwriter
	workbook = xlsxwriter.Workbook("test.xlsx")
	table = workbook.add_worksheet("Sheet1")
	table.wirte(5,0,'=SUM(A1:A4)')
	workbook.close()

---
##插入图片
>####insert_image(row, col, image[, options])
row：行坐标，起始索引值为0；
col：列坐标，起始索引值为0；
image：string类型，是图片路径；
options：dict类型，是可选参数，用于指定图片位置，如URL等信息；

---
	import xlsxwriter
	workbook = xlsxwriter.Workbook("test.xlsx")
	table = workbook.add_worksheet("Sheet1")
	table.insert_image('B5', '1.png', {'url': 'http://www.baidu.com'})
	workbook.close()

---
##图表
>####add_chart({type, 'column'})
	#常见的图表样式如下：
	area：面积样式的图表
	bar：条形图
	column：柱状图
	line：线条样式的图表
	pie：饼形图
	scatter：散点图
	stock：股票样式的图表
	radar：雷达样式的图表

---
>####set_x_axis
	chart.set_x_axis({
	      'name': 'x name',									#设置x轴名称；
	      'name_font': {'size': 14, 'bold': True}			  #设置x轴字体；
	      'num_font': {'italic': True}						 #设置x轴数字字体属性；
	  })

---
>####add_series
	chart_fd.add_series({   # 给图表设置格式，填充内容
	    'name': '折线一',						# 名称
	    'categories': ['Sheet1', 1, 0, 4, 0],   # 从第几行到第几行的内容(x轴数据)
	    'values': ['Sheet1', 1, 1, 4, 1],       # 从第几行到第几行的内容(y轴数据)
	})

---
####示例：
	import xlsxwriter
	workbook = xlsxwriter.Workbook("test.xlsx")
	table = workbook.add_worksheet("Sheet1")
	#chart = workbook.add_chart({'type', 'column'})
	table.write(0, 0, "X")
	table.write(1, 0, 1)
	table.write(2, 0, 2)
	table.write(3, 0, 3)
	table.write(4, 0, 4)
	table.write(0, 1, "Y")
	table.write(1, 1, 4)
	table.write(2, 1, 5)
	table.write(3, 1, 5)
	table.write(4, 1, 3)
	table.write(0, 2, "Y2")
	table.write(1, 2, 14)
	table.write(2, 2, 15)
	table.write(3, 2, 15)
	table.write(4, 2, 13)
	chart_fd = workbook.add_chart({'type': 'line'})   # 添加折线图
	chart_fd.set_title({'name': '折线图'})  # 添加图标名字
	chart_fd.set_x_axis({'name': 'x轴名称'})  # 添加X名字（test1为sheet表）
	chart_fd.set_y_axis({'name': 'y轴名称'})  # 添加Y名字（test1为sheet表）
	chart_fd.add_series({   # 给图表设置格式，填充内容
	    'name': '折线一',
	    'categories': ['Sheet1', 1, 0, 4, 0],  # 从第几行到第几行的内容(x轴数据)
	    'values': ['Sheet1', 1, 1, 4, 1],  # 从第几行到第几行的内容(y轴数据)
	})
	chart_fd.add_series({   # 给图表设置格式，填充内容
	    'name': '折线二',
	    'categories': ['Sheet1', 1, 0, 4, 0],  # 从第几行到第几行的内容(x轴数据)
	    'values': ['Sheet1', 1, 2, 4, 2],  # 从第几行到第几行的内容(y轴数据)
	})
	table.insert_chart(1, 3, chart_fd)
	workbook.close()

---
#xlutils.copy修改现有excel
	import xlrd
	import xlsxwriter
	from xlutils.copy import copy
 
	# 打开想要更改的excel文件
	old_excel = xlrd.open_workbook('test.xlsx')
	
	# 将操作文件对象拷贝，变成可写的workbook对象
	new_excel = copy(old_excel)
	
	# 获得第一个sheet的对象
	ws = new_excel.get_sheet(0)
	
	# 写入数据
	ws.write(0, 2, 'hello')
	
	# 另存为excel文件，并将文件命名
	new_excel.save('test.xlsx')