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