openpyxl模块安装及使用

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格式的电子表格。