Python操作Excel

Python操作Excel

版本\操作	读	写	修改(已有内容)
xls	xlrd	xlwt	xlutils
xlsx	openpyxl / xlrd	openpyxl / xlsxWriter	openpyxl

• xlrd / xlwt 是读写03版本excel的好伴侣，需要在已有表格数据的修改需要借助xlutils；
• openpyxl 可以一条龙的操作07版本excel，xlrd可以辅助读，xlwtWriter只能写，基于原有表格基础的修改只能是openpyxl；
• pandas 可以对两个版本的excel进行读取和写入，但是修改需要借助其他库。

一、 xls的读写：

1.1 xlrd - 读取Excel

表格对象	代码	说明
工作簿	wb = xlrd.open_workbook(path)	打开指定位置excel文件
工作表	table = wb.sheet_by_index(0) table = wb.sheet_by_name(‘Sheet1’) wb._sheets_names	通过下标获取表格对象(从零开始) 通过页签名获取工作表 获取所有工作表名称
行数/列数	table.nrows / table.ncols	/
行数据 列数据	table.row_values(colx, start, end) table.col_values(rowx, start, end)	start/end为缺省参数: start=0, end=None 去除表头的第一列数据: table.col_values(0, 1) / table.col_values(0)[1:]
单元格	table.cell(i,j).value table.cell_value(i,j)	特别的获取超链接的值：table.hyperlink_map[(i,j)] table.hyperlink_map : 超链接字典，键为位置(i,j)
单元格 日期值	xlrd.xldate_as_tuple(cell,datemode) xlrd.xldate.xldate_as_datetime(cell,datemode)	->元组(year,month,day,h,m,s) ->datetime对象 datemode表示时间基准(0代表1900-01-01为基准，1代表1904-01-01为基准)

1.2 xlwt - 写入Excel

1.2.1 设置单元格样式

样式	代码	细节	说明
字体设置	font = xlwt.Font()	font.name = ‘微软雅黑’ font.bold = True font.height = 20 * 10	字体名称 是否加粗 字体大小
背景设置	pattern = xlwt.Pattern()	pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 0	背景色设置 颜色设置(黑色)
位置设置	align = xlwt.Alignment()	align.horz = xlwt.Alignment.HORZ_LEFT HORZ_RIGHT HORZ_CENTER align.vert = xlwt.Alignment.VERT_CENTER	水平居左 水平居右 水平居中 垂直居中
边线设置	borders = xlwt.Borders()	borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN	左右上下边线 可选值THIN|BOLD
单元格 样式	stlyle = xlwt.XFStyle()	style.font = font style.pattern = pattern style.align = align style.borders = borders	设置字体 设置背景 设置位置 设置边线

1.2.2 写入数据

写操作	py代码	说明
基础设置	wb = xlwt.Workbook(encoding=‘utf-8’) ws = wb.add_sheet(‘Sheet1’) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0	创建工作簿 添加工作表 冻结表格 删除分割线 纸张方向(横向) 上述设置与默认值相反，详见xlwt.WorkSheet.py
单元格写值	ws.write(r, c, label=‘’, style=style)	向指定单元格写值，样式由上面表格定义 label可以是任何的python基本数据类型
跨单元格写值	ws.write_merge(r1, r2, c1, c2, label=‘’, style=style)	跨两列两列 write_merge(0, 1, 0,1, “大标题”) 跨两列 write_merge(2,2,0,1, ‘次标题’)
图像插入	ws.insert_bitmap( filename, row, col, x = 0, y = 0, scale_x = 1,scale_y = 1)	filename: 文件位置; row/col : 位置 x, y: 距单元格距离; scale 缩放比例
日期值写入	style.num_format_str = ‘yyyy-mm-dd h:mm’	日期值写入需要向单元格样式额外添加该属性
工作簿保存	wb.save(file_or_filelike_obj)	参数可以是一个文件的全路径，也可以是一个file对象( 有write方法, 例如io.IOBytes，二进制数据可通过getvalue()获取)

• 数据类型：abel可以是任何的python基本数据类型，xlwt会自动进行转化：

  python数据类型	Excel显示值
  int / float / decimal.Decimal	float
  str / bytes	string( 按encoding编码)
  datetime/date	按num_format_str写
  bool	TRUE/FALSE
  None	‘’ (空串)

• 单元格宽度设置： ws.col(0).width = 160 * 20

1.2.3 更多显示设置

高阶设置	代码	说明(与默认值相反)（详见链接）
显示设置	ws.set_show_headers(0) ws.set_show_grid(0) ws.set_cols_right_to_left(1)	隐藏行列标签 隐藏网格线 表格右排列
打印设置	ws.set_print_headers(1) ws.set_print_grid(1) ws.set_print_centered_vert(1) ws.set_print_centered_horz(0)	打印头标签 打印网格线 表体垂直居中 水平不居中

1.3 基于原有数据的修改

​ xlrd，xlwt和xlutils是用Python处理Excel文档(*.xls)的高效率工具。xlrd只能读取xls，xlwt只能新建xls（不可以修改）。xlutils能将xlrd.Book转为xlwt.Workbook，从而得以在现有xls的基础上修改数据，并创建一个新的xls，实现修改。

import xlrd
import psycopg2
from xlutils import copy

wb = xlrd.open_workbook(path)
table = wb.sheet_by_index(0)
# 获取工作簿的一个副本
wb_ = copy.copy(data)
table_ = wb_.get_sheet(0)
# wb_ 是xlwt.Workbook的一个对象，可调用table_.write()方法覆写数据
# wb_.save(), 保存到一个新文件中

二、 xlsx的读写

openpyxl 是可以完成对Excel读取、写入、修改的一条龙服务的，所以先来看看openpyxl的用法。

2.1 openpyxl - 读取Excel

openpyxl支持数据的下标索引，取数非常方便；worksheet按照Excel自然语义，行从1开始，列从A开始。

表格对象	代码	说明
工作簿	wb = openpyxl.load_workbook(path)	path 是一个文件名或者文件对象(io.IOBytes)
工作表	ws = wb.active ws = wb[‘Sheet1’] wsheets = wb.worksheets	获取被激活的工作表，默认第一个 通过页签名获取工作表 获取所有页签名，返回列表
行数/列数	ws.maxrow / ws.maxcolumn
行数据/列数据	ws[‘A’] ws[‘1’] ws[‘A:C’] ws[‘1:3’] ws[‘A1:C3’] ws.iter_rows() ws.iter_cols()	获取单列 (第一列) 获取单行 (第一行) 获取多列 (前三列) 获取多行 (前三行) 获取举行选中区域 ->tuple元组 获取所有行 获取所有列 -> 生成器
单元格	ws[‘A1’] ws[‘A1’].value	左上角单元格对象 单元格值(值类型自适应，自动转化为python对象，日期为datetime)

2.1.1 load_workbook缺省参数

• read_only ：是否为只读模式，对于超大型文件，提升效率较有帮助(可以将大型文件切割分片保存操作)
• keep_vba ：是否保留 vba 代码，即打开 Excel 文件时，开启并保留宏
• guess_types ：是否做在读取单元格数据类型时，做类型判断
• data_only ：是否将公式转换为结果，即包含公式的单元格，是否显示最近的计算结果
• keep_links ：是否保留外部链接

2.1.2 iter_rows缺省参数

• （min_row=None, max_row=None, min_col=None, max_col=None, values_only=False），对应其语义默认值；
• 全不传则为全部行，返回一个生成器，使用for循环迭代得到每一行，迭代每一行得到一个单元格，部分传则需要传入字典。

2.2 写入Excel

2.2.1 写入数据

写操作	代码	说明
基础设置	wb = openpyxl.WorkBook() ws = wb.create_sheet(title, index)	创建工作簿 创建工作表(指定页签名，位置，可缺省) ws.title 也可以设置页签名
写入一行	ws.append((‘header1’, ‘header2’))	可以将认以的python数据类型按照元组组织写入一行
写单元格	ws[‘A1’].value = xx ws.cell(row, column, value)	xx可以是python基础数据类型
合并 单元格	ws.merge_cells(start_row,start_column,end_row,end_column) ws.merge_cell(‘A1:C3’)	先写值，后控制样式(合并、边框等)

2.2.2 设置单元格样式

单元格样式属性只能通过创建样式对象构造，而无法通过样式属性(点取)来修改

样式	代码	说明
字体设置	font = Font(name=u’宋体’, size=12, bold=True, color=‘FF0000’)	(字体名称、大小、是否加粗、颜色)
背景设置	fill = PatternFill(fill_type=‘solid’, bgColor=‘000000’)	必须指定fill_type, 设置渐变色借助 start_color, end_color
位置设置	align = Alignment(horizontal=‘right’)	可选参数： horizental: 水平对齐 vertical : 垂直对齐 textRotation：旋转角度[0,180]
边线设置	border = Border( left=Side(border_style=‘thin’, color=‘FF0000’) )	可选参数：left|right|top|bottom 边线通过Side定义
综合样式	style = NamedStyle(“self”) style.font = font style.border = border style.alignment = algin style.fill = fill	命名样式是可变的，可编辑 将同一格式应用于不同单元格，代码简洁 将命名样式分配给单元格后， 对该样式的更改将不会影响该单元格
单元格样式	ws[‘A1’].font = font ws[‘A1’].style = style	多个属性逐一设置 一次性直接修改

• 对齐可选值：

  horizental (general|left|center|right|fill|justify|centerContinuous)

  vertical (top|center|bottom|justify|distributed)

• 单元格样式一旦设置，则不可修改，不可逆

2.3 openpyxl - 基于原有数据的修改

openpyxl 读取和写入的工作表是同一个WorkSheet对象，读取到的单元格，可直接通过ws[‘A1’].value = ‘xx’ 赋值，修改某列/添加某列使用for循环即可，修改后的文件需要wb.save(filename)进行保存，filename与源文件即覆盖，不同则另存。

2.4 基于xlsxWriter的数据写入

openpyxl在操作上xlsx更加灵活，但是我们同样可以基于xlrd读，基于xlsxWriter进行写。

2.4.1 单元格样式

样式	代码	说明
基础样式	format= wb.add_format({ ‘font_size’: 10, ‘bold’: False, ‘border’: 1, ‘align’: align, ‘valign’: ‘vcenter’, })	字体大小 是否加粗 边框 横向对齐 垂直对齐
日期/数字	字典添加num_format属性	日期：‘yyyy-mm-dd’ 数字： ‘#,##0.00’
构建样式	format= wb.add_format({})	构建各种不同的格式

2.4.2 写入数据

写操作	代码	说明
基础设置	wb = xlsxwriter.Workbook(filename) ws = wb.add_worksheet(“Sheet1”)	创建工作簿，设置保存位置 创建工作表
写入一行	ws.write(row, col, value[, cell_format])	value是任意python基础数据类型(自行判断) cell_format是write_*方法的缺省参数，设置样式
合并 单元格	ws.merge_range(first_row, first_col, last_row, last_col, data, cell_format=None)	指定行列的合并范围
工作簿 保存	wb.close()	完成写入工作