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() | 完成写入工作 |
