python使用openpyxl模块操作excel

python中的openpyxl模块可以操作excel文件。

使用pip安装pip install openpyxl

支持流行的lxml库(如果已安装)，这在创建大文件时特别有用。

为了能够将图像(jpeg，png，bmp等)包含到文件中，需要安装'pillow'库：pip install pillow

1、创建一个excel对象

import openpyxl
wb=openpyxl.Workbook() #创建一个新的空白excel对象
wb=openpyxl.load_workbook(filename, read_only=False, keep_vba=False, data_only=False, keep_links=True)
    打开一个已存在的文件
    keep_vba：是否保留VBA内容(不一定可以使用)
    data_only：读取单元格值时是否读取为公式计算后的结果，默认读取原公式
    keep_links：是否保留链接
该对象的信息属性
    data_only：是否是只读模式(只读属性)
    encoding：获取或设置编码
    properties：获取该excel文档属性对象，该对象的created属性表示创建时间，modified表示修改时间等

2、创建工作表

wb.create_sheet(title=None, index=None)
    title为新创建的工作表名称
    index为新的工作表插入的位置，默认插到末尾

wb.copy_worksheet(sheet_obj)：复制一个sheet到末尾，名称为'name Copy'

3、获取工作表实例对象

获取打开的excel对象中所有的工作表(新的空白excel对象默认有一个名称为'Sheet'的工作表)：
    wb.sheetnames：获取所有工作表的名称的数组
    wb.worksheets：返回该文档的所有sheet对象组成的数组
    for sheet in wb:
        print(sheet.title)

ws=wb.active #获取当前的活跃sheet对象
ws=wb[name] #工作表的name可以作为key进行索引来获取指定的sheet对象

4、工作表常用操作

获取工作表索引：wb.index(sheet_obj)：索引值从0开始

移动工作表：wb.move_sheet(sheet_obj, offset=0)：将一个sheet向右移动，为负数则向左移动

获取最大行：ws.max_row

获取最大列：ws.max_column

设置行高：ws.row_dimensions[rowx].height = height_num

设置列宽：ws.column_dimensions['C'].width = width_num

合并单元格：

ws.merge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)

    方法1：仅提供range_string参数：ws.merge_cells('A1:C3')
    方法2：省略range_string参数
        ws.merge_cells(start_row=2, start_column=3, end_row=5, end_column=6)
        由于省略了第一个参数，后面的参数要使用关键字
        所有参数值均为数字，行和列均从数字1开始

拆分单元格：

ws.unmerge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)

改变工作表按钮颜色：ws.sheet_properties.tabColor = '16进制颜色'

删除工作表：

wb.remove(ws)
del wb[sheet_name]

冻结窗格：ws.freeze_panes = 'B2'

设置缩放比例：ws.views.sheetView[0].zoomScale = 70

显示/隐藏工作表：ws.sheet_state = 'hidden'

'visible'：显示工作表
'hidden' ：隐藏工作表，在右键取消隐藏菜单中可见
'veryHidden'：深度隐藏，在右键取消隐藏菜单中不可见

添加一行到最底部：

ws.append(['This is A1', 'This is B1', 'This is C1'])
    如果是list，将按顺序将所有元素添加至最后一行，每列一个元素的值
ws.append({'A' : 'This is A1', 'C' : 'This is C1'})
    如果是dict,按照相应的键添加相应的键值

5、单元格操作

要读取或修改单元格的值，需要先获取单元格Cell对象

获取Cell对象

ws['B3'] ：通过excel中的表达方式，即列字母加行数字组成的字符串
ws.cell(rowx, colx, value=None)：通过行和列的索引获取(从1开始)，如果提供了value，则同时更改该单元格值

获取所有单元格对象
    ws.rows   ：每行为一个元祖，然后组成列表
    ws.columns：每列为一个元祖，然后组成列表

获取指定范围所有单元格对象
    ws['A1':'C2'] 或 ws['A1:C2']
        每个元素为一行的所有单元格对象组成的元祖，每行为一个元素
    ws['C']         ：获取C列(第三)列的所有单元格对象列表
    ws['C':'E'] 或 ws['C:E']：先行后列获取所有单元格对象，每行为一个元祖元素
    ws[10]          ：获取第10行的所有单元格对象列表
    ws[5:10]        ：获取第5行到第10行的所有单元格对象列表
    ws.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
        返回指定范围的所有单元格对象，返回格式中每行为一个元祖，然后由这些元祖组成列表，min_*不提供默认为1

根据列索引得到字母：openpyxl.utils.get_column_letter(num)

根据字母得到列索引：openpyxl.utils.column_index_from_string(str)

Cell对象常用的方法

只读属性

column、row：获取列和行的索引
is_date：是否是日期数据

读写属性

value：值
font：字体
aligment：对齐方式
border：边框
fill：填充
style：样式
number_format：数字格式

常用方法

ws.add_image(img,'B5')：插入图片

from openpyxl.drawing.image import Image
from PIL import JpegImagePlugin

img = Image(img_path)
img.width=130
img.height=100
ws.add_image(img,'B5')

cell通过读写属性设置单元格格式

创建颜色对象

color = openpyxl.styles.Color(rgb='16进制颜色',tint=0)

tint表示亮度，值为-1至1的浮点数

以下所有内容中参数color官方均推荐使用Color颜色对象赋值，但直接使用16进制颜色字符串仍可生效，但不保证后期是否会修改

设置字体

cell.font=openpyxl.styles.Font(name='Calibri',size=11, italic=False, color='000000', bold=False)

name        ：字体名称，str('Calibri')
size=       ：字体大小，int(11)，单位为pt(点数)
color       ：字体颜色，str('000000')或openpyxl.styles.Color实例对象
vertAlign   ：垂直对齐，str(None)，"subscript"下标，"superscript"上标，"baseline"基线对齐
underline   ：下划线  ，str(None)，"single"单下划线，"double"双，"singleAccounting"会计用单， "doubleAccounting"会计用双

以下是值为bool类型，默认为False的参数
italic(斜体)、bold(粗体)、underline(下划线)、strike(删除线)、outline(轮廓空心)、shadow(阴影)

设置对齐方式

cell.alignment=openpyxl.styles.Alignment(horizontal='水平对齐',vertical='垂直对齐',text_rotation=旋转角度，wrap_text=是否自动换行)

horizontal可能的值：左对齐left、居中center、右对齐right、分散对齐distributed、跨列居中centerContinuous、两端对齐justify、填充fill、常规general
vertical可能的值：居中center、还可以靠上top、靠下bottom、两端对齐justify、分散对齐distributed
wrap_text参数还可以写成wrapText

设置边框

首先创建边框样式Side对象

openpyxl.styles.Side(border_style,color)

border_style可选值：
    thin：细线
    double：双线
    medium：中等粗细
    thick：粗线
    
    dashed：短线
    dotted：点
    hair：极细的点
    dashDot：点和短线
    dashDotDot：点和两个短线

    mediumDashDot：中等粗细的点和短线
    mediumDashDotDot：中等粗细的点和两个短线
    mediumDashed：中等粗细的短线
    slantDashDot：斜向的点和短划

根据若干边框样式对象创建边框Border对象

openpyxl.styles.Border(left=None,right=None,top=None,bottom=None,diagonal=None,diagonal_direction=0)

top、bottom、left、right分别表示上下左右边框的样式
diagonal：对角线的样式
diagonal_direction：对角线的方向，0为不设置，1为左上到右下，2为左下到右上

最后将Border对象赋值给cell的border属性

cell.border=openpyxl.styles.Border(......)

单元格填充

cell.fill = openpyxl.styles.PatternFill(fill_type='solid',start_color=None)

数字格式

cell.number_format = 'format'

cell.number_format = '0.00%'  # 以两位小数百分数显示
cell.number_format = 'yyyy-mm-dd' ：将数字改为日期并按照指定格式显示

命名样式

创建一个包含字体，填充，边框，对齐，数字格式的样式对象，通过style属性赋值

cell.style = openpyxl.styles.NamedStyle(name='Normal',font=None, fill=None, border=None, alignment=None, number_format=None)

name    ：样式名称，在同一个作用域中，名称为唯一的，再次创建相同名称的实例对象会报错
font    ：参考字体设置
fill    ：参考单元格填充
border  ：参考边框设置
alignment：参考对齐方式
number_format：str(None)，设定数字格式

样式注册：wb.add_named_style(my_style)

将命名样式注册到工作簿中，注册后可以通过命名样式的name字符串设定样式

命名样式也将在首次分配给单元格时自动注册

my_style = openpyxl.styles.NamedStyle(name='mystyle',number_format='0.0%')

ws['D5'].style = 'mystyle'
    # 会报错，因为该名称的样式还没有注册

wb.add_named_style(my_style)
ws['D5'].style = 'mystyle'        # 设定成功

或者

ws['B2'].style=my_style
ws['D5'].style = 'mystyle'     # 在为B2单元格设置样式时会自动注册

由于NamedStyle类创建时各参数由默认值，所以如果先通过font、border等属性修改了单元格格式，后通过style设置了其他样式，那么之前设置的格式会恢复默认值

设置超链接

# 引入创建超链接实例的class
from openpyxl.worksheet.hyperlink import Hyperlink

# 创建超链接实例对象
link = Hyperlink(ref=None, location=None, tooltip=None, display='display', id='id', target=None)

    # 所有参数均为str字符串类型，其中ref为必须指定的参数
    # ref用于指定超链接应用的单元格引用或范围，如"D3","D3:E3"
    # location指定文档中的位置，如"sheet!A2"，"A2","A2:A5"
    # tooltip指定鼠标悬停时的提示文本
    # target指定外部文档会网址，如果同时指定了location，则完整的超链接地址为target#location
    # display按照字面理解应该是超链接显示文本，但实测无效
    # id功能未知

# 将单元格绑定超链接
ws['B2'].hyperlink=link

实测ref的指定单元格不生效，超链接会在绑定的单元格中生效，所以ref可以为任意字符串值

6、关闭或保存成文件

wb.close()：关闭以只读模式打开的文件对象，其他模式打开的没有效果

wb.save('file_name.xlsx')