openpyxl模块安装及使用

痛定思痛。 2022-02-21 14:25 964阅读 0赞

**openpyxl模块：**  
是用于解决Excel（WPS等均可使用）中扩展名为xlsx/xlsm/xltx/xltm的文件读写的第三方库。xls文件要使用xlwt 、wlrd两个模块。

**主要概念：**  
Workbooks，Sheets，Cells。Workbook就是一个excel工作簿；Sheet是工作簿中的一张工作表；Cell就是表的一个单元格。openpyxl不管读写都是：打开Workbook，定位Sheet，操作Cell。

**安装：**  
cmd下pip install openpyxl安装。

**操作：**  
在内存中新建一个工作薄：

>>> from openpyxl import Workbook
    >>> wb = Workbook()

保存工作薄：

>>> wb = Workbook()
    >>> wb.save('test.xlsx')						#必须提交保存才能显示操作产生的效果，此操作不能在文件打开（指双击文件打开）时进行，必须先关闭。

读写已经存在的文件sheet：

>>> from openpyxl import load_workbook
    >>> wb2 = load_workbook('test.xlsx')			#加载工作簿
    >>> print(wb2.sheetnames)  						
    ['Sheet2', 'New Title', 'Sheet1']

获取默认工作表：

>>> ws = wb.active

通过指定的工作表名获取工作表对象：

>>> ws3 = wb["New Title"]						#建议使用

或者

>>> ws = wb.get_sheet_by_name('New Title')

创建新的工作表：

>>> ws1 = wb.create_sheet("Mysheet") 			#默认在最后面创建一个Mysheet工作表，参数也可为空，默认为sheet*

或者

>>> ws2 = wb.create_sheet("Mysheet", 0) 		#在0索引位置插入一个工作表，此时插入的表名不可为空

复制工作表：

>>> source = wb.active
    >>> target = wb.copy_worksheet(source)

删除工作表（删除后新建，相当于清空工作表）：

wb.remove(wb['Sheet'])							#wb.remove_sheet()已弃用

或

del wb['Sheet']

修改工作表名称：

ws.title = "New Title"

显示所有工作表名称：

>>> print(wb.sheetnames)
    ['Sheet2', 'New Title', 'Sheet1']
    >>> for sheet in wb:
    ...     print(sheet.title)

表中的数据操作：

>>> from openpyxl import Workbook
    >>> wb = Workbook()
    >>> ws = wb.active

修改表中的数据：

>>> ws['A4'] = 4

或者

>>> d = ws.cell(row=4, column=2, value=10)		#第4行第2列值为10
    >>> c = ws['A4']
    >>> c.value = 'hello, world'
    >>> print(c.value)
    'hello, world'
    >>> d.value = 3.14
    >>> print(d.value)
    3.14

获取行列：

ws.max_row 										#获取最大行数
    ws.max_column									#获取最大列数
    ws.rows											#获取所有行
    ws.columns										#获取所有列

列的字母与数字互转：

from openpyxl.utils import get_column_letter, column_index_from_string 			
    # 根据列的数字返回字母： 	
    print(get_column_letter(2)) 					#结果为B
    #根据字母返回列的数字： 
    print(column_index_from_string('D'))  			#结果为4

获取单元格对象：

>>> c = ws['A4']

切片多选（获取多个单元格）：

>>> cell_range = ws['A1':'C2']

选择整行、整列：

>>> colC = ws['C']								#选择C列
    >>> col_range = ws['C:D']						#选择C、D列
    >>> row10 = ws[10]								#选择第10行
    >>> row_range = ws[5:10]						#选择5-10行

获取单个单元格中的数据：

>>> print(ws['A2'].value)

遍历获取指定区域的数据：

>>> for i in range(1, 3):
    ... 	for j in range(1, 3):
    ... 		ws.cell(row=i, column=j)
    ... 
    <Cell 'Sheet'.A1>
    <Cell 'Sheet'.B1>
    <Cell 'Sheet'.A2>
    <Cell 'Sheet'.B2>

将数据data写入Excel：此处data是由元组组成的列表数据

rows = len(data)                           		#获取数据总行数
    columns = len(data[0])                 			#获取数据总列数
    for row in range(3, rows+3):  	   				#从第3行开始，前2行是表头
    	for column in range(1, columns+1):
    		zhi = data[row-3][column-1]
            ws.cell(row, column, value=zhi)    		#给单元格赋值

合并、取消合并单元格：

ws.merge_cells('B1:G1')							#合并B1-G1单元格
    ws.unmerge_cells('B1:G1')						#取消合并的B1-G1单元格

单元格样式：openpyxl的单元格样式由6种属性决定，每一种都是一个类。

font(字体类)：字号、字体颜色、下划线等
    fill(填充类)：颜色等
    border(边框类)：设置单元格边框
    alignment(位置类)：对齐方式
    number_format(格式类)：数据格式
    protection(保护类)：写保护

单元格默认样式如下：

>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
    >>> font = Font(name='Calibri',			      	#字体
    ...                 size=11,					#大小
    ...                 bold=False,					#加粗
    ...                 italic=False,				#斜体
    ...                 vertAlign=None,				#纵向对齐
    ...                 underline='none',			#下划线
    ...                 strike=False,				#删除线
    ...                 color='FF000000')			#颜色
    #下面2种填充方法均有可能不显示填充色：
    >>> fill = PatternFill(fill_type='solid',		#填充
    ...                 start_color='FFFFFFFF',		#前景色
    ...                 end_color='FF000000')		#背景色
    >>> fill = PatternFill(fill_type='solid', bgColor='FFFFFF', fgColor='33FF00')       #可显示背景色bgColor为33FF00(但此值为前景色fgColor的值)，无法显示前景色
    >>> border = Border(left=Side(border_style='thin',  
     ...                           color='FF000000'),	#边框，必须导入Side函数，且颜色必须是FF0000格式
    ...                 right=Side(border_style='thin',
    ...                            color='FF000000'),
    ...                 top=Side(border_style='thin',
    ...                          color='FF000000'),
    ...                 bottom=Side(border_style='thin',
    ...                             color='FF000000'),
    ...                 diagonal=Side(border_style='thin',
    ...                               color='FF000000'),
    ...                 diagonal_direction=0,
    ...                 outline=Side(border_style='thin',
    ...                              color='FF000000'),
    ...                 vertical=Side(border_style='thin',
    ...                               color='FF000000'),
    ...                 horizontal=Side(border_style='thin',
    ...                                color='FF000000')
    ...                )
    >>> alignment=Alignment(horizontal='center',		#水平方向，left、center、right等
    ...                     vertical='bottom',			#垂直方向，center、top、bottom等
    ...                     text_rotation=0,			#文本旋转角度？
    ...                     wrap_text=False,			#自动换行
    ...                     shrink_to_fit=False,		#缩小填充
    ...                     indent=0)					#缩进
    >>> number_format = 'General'						#数据格式
    >>> protection = Protection(locked=True,			#锁定
    ...                         hidden=False)			#隐藏

以上几种样式（字体、填充、边框、位置和保护）实例一旦被创建，实例的属性就不可更改，只能重新创建实例。

PatternFill填充类型：fill\_type若没有指定类型，则后续参数都无效。

'none'、'solid'、'darkDown'、'darkGray'、'darkGrid'、'darkHorizontal'、'darkTrellis'、'darkUp'、'darkVertical'、'gray0625'、'gray125'、'lightDown'、'lightGray'、'lightGrid'、'lightHorizontal'、'lightTrellis'、'lightUp'、'lightVertical'、'mediumGray'

border\_style类型：border\_style若没指定类型，则后续参数都无效。

'thin','dashDot','dashDotDot','dashed','dotted','double','hair','medium','mediumDashDot','mediumDashDotDot','mediumDashed','slantDashDot','thick'

>>> from openpyxl.styles import Font, Color
    >>> from openpyxl.styles import colors
    >>> from openpyxl import Workbook
    >>> wb = Workbook()
    >>> ws = wb.active
    >>> a1 = ws['A1']
    >>> d4 = ws['D4']
    >>> ft = Font(color=colors.RED)
    >>> a1.font = ft
    >>> d4.font = ft
    >>> a1.font.italic = True 							#不会生效，样式实例一旦创建，实例的属性就不可改变
    >>> # 若要改变样式，必须新建样式实例
    >>> a1.font = Font(color=colors.RED, italic=True) 	#创建字体实例（红色、斜体，其他属性默认）

复制样式：样式可以被复制

>>> from openpyxl.styles import Font
    >>> ft1 = Font(name='Arial', size=14)
    >>> ft2 = ft1.copy(name="Tahoma") 					#复制时指定字体为“Tahoma”，其他属性均复制自ft1
    >>> ft1.name
    'Arial'
    >>> ft2.name
    'Tahoma'
    >>> ft2.size
    14.0

基本字体颜色：字体颜色有一些颜色常量，可以直接调用

from openpyxl.styles import Font
    from openpyxl.styles.colors import RED
    font = Font(color=RED)
    font = Font(color="00FFBB00")

也可以按索引创建实例：

from openpyxl.styles.colors import Color
    c = Color(indexed=32)
    c = Color(theme=6, tint=0.5)

V2.3.5版本中颜色必须使用“00XXXXXX”格式，不能使用“\#XXXXXX”格式。如果颜色不生效请查看版本。

应用样式：可以直接应用到单元格:

from openpyxl.workbook import Workbook
    from openpyxl.styles import Font, Fill
    wb = Workbook()
    ws = wb.active
    c = ws['A1']
    c.font = Font(size=12)
    #可以对整行整列设置样式，前提是单元格已创建。
    col = ws.column_dimensions['A']
    col.font = Font(bold=True)
    row = ws.row_dimensions[1]
    row.font = Font(underline="single")

数据格式：属性number\_format的值是字符串类型，不为对象，直接赋值即可。openpyxl内置了一些数据格式，查看openpyxl.styles.numbers，也支持excel自定义格式，以下两种方式效果相同：

# 使用openpyxl内置的格式
    from openpyxl.styles import numbers
    ws.cell['D2'].number_format = numbers.FORMAT_GENERAL
    ws.cell(row=2, column=4).number_format = numbers.FORMAT_DATE_XLSX15
    # 直接使用字符串
    ws.cell['D2'].number_format = 'General'
    ws.cell(row=2, column=4).number_format = 'd-mmm-yy'

编辑页面设置：

from openpyxl.workbook import Workbook
    wb = Workbook()
    ws = wb.active
    ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE	#方向-竖屏（LANDSCAPE），横屏（PORTRAIT）
    ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID 			#纸张尺寸
    ws.page_setup.fitToHeight = 0                  			#自适应高度
    ws.page_setup.fitToWidth = 1                   			#自适应宽度

编辑打印选项：

from openpyxl.workbook import Workbook
    wb = Workbook()
    ws = wb.active
    ws.print_options.horizontalCentered = True				#水平居中
    ws.print_options.verticalCentered = True       			#垂直居中

**插入列：**

import openpyxl
    wb = openpyxl.load_workbook(r"test.xlsx")				#读取原表
    ws = wb.active
    lie = 2													#在第几列后面插入
    n = 5													#插入几列
    wb2 = openpyxl.Workbook()								#创建新表
    ws2 = wb2.active
    ws2.title = 'new'										#设置新表名
    for col in range(1, ws.max_column+1):
    	for ro in range(1, ws.max_row+1):
    		if col<=lie:
    			ws2.cell(row=ro, column=col).value=ws.cell(row=ro, column=col).value
    		else:
    			ws2.cell(row=ro, column=col+n).value=ws.cell(row=ro, column=col).value
    wb2.save(r"test.xlsx")									#勿忘保存

**插入行：**

wb = openpyxl.load_workbook(r"test.xlsx")				#读取原表
    ws = wb.active
    hang = 2												#在第几行后面插入
    n = 5													#插入几行
    wb2 = openpyxl.Workbook()								#创建新表
    ws2 = wb2.active
    ws2.title = 'new'										#设置新表名
    for ro in range(1, ws.max_row+1):
    	for col in range(1, ws.max_column+1):
    		if ro<=hang:
    			ws2.cell(row=ro, column=col).value=ws.cell(row=ro, column=col).value
    		else:
    			ws2.cell(row=ro+n, column=col).value=ws.cell(row=ro, column=col).value
    wb2.save(r"test.xlsx")									#勿忘保存

其它第三方库：针对电子表格的第三方库，除了openpyxl之外，还有xlsxwriter：针对Excel 2010格式，如.xlsx；xlrd、xlwt用来处理.xls格式的电子表格。