python第三方库openpyxl详解
前言
openpyxl是一个第三方库，可以处理xlsx格式的Excel文件。
openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，xls和xlsx之间转换容易， 注意：如果文字编码是“gb2312” 读取后就会显示乱码，请先转成Unicode。
openpyxl模块有三大类（首字母大写）：
Workbook 是对工作簿的抽象（工作簿，一个excel文件包含多个sheet。）
Worksheet 是对表格的抽象（工作表，一个workbook有多个，表名识别，如“sheet1”,“sheet2”等。）
Cell 是对单元格的抽象（单元格，存储数据对象）
安装
pip install openpyxl
基本操作
新建工作簿
workbook至少创建一个worksheet。
通过openpyxl.workbook.Workbook.active()得到worksheet。
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb) ws = wb.active # 获取worksheet对象 print(ws)
运行结果：
①
from openpyxl import Workbook #导入模块 wb = Workbook('abc.xlsx') #新建名为abc的工作簿 wb.save('abc.xlsx') #保存工作簿，完成新工作簿的建立(将覆盖同名文件且无警告）
运行结果：
②
from openpyxl import Workbook # 实例化 wb = Workbook() # 激活 worksheet ws = wb.active
加载已存在的工作簿
from openpyxl import load_workbook wb = load_workbook('文件名称.xlsx') # openpyxl第三方库只能处理.xlsx格式的Excel表格
获取当前所处工作表的sheet表名
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') ws2 = wb.active # 激活当前sheet表 print(ws2.title)
运行结果：
①当名为'sheet1'的sheet表格处于激活状态时，打印的当前所处工作表的表名为sheet1。
②当名为'sheet2'的sheet表格处于激活状态时，打印的当前所处工作表的表名为sheet2。
修改当前所处工作表名称（ ws = wb.active # 获取当前所在'激活状态下'工作表 ）
【注意】对工作簿修改后一定要记得执行保存操作！ wb.save('Excel文件名.xlsx')
from openpyxl import Workbook, load_workbook wb = load_workbook('./test.xlsx') ws = wb.active print(ws.title) ws.title = 'MS' wb.save('./test.xlsx')
运行结果：
创建和删除工作表
【注意】对工作簿修改后一定要记得执行保存操作！ wb.save('Excel文件名.xlsx')
from openpyxl import load_workbook ws1 = wb.create_sheet('Mysheet') #插入到最后（默认） ws2 = wb.create_sheet('Mysheet',0) #插入到最前 ws3 = wb.create_sheet('Mysheet',-1) #插入到倒数第二 wb.remove('Mysheet') #删除工作表(方法一) del wb('Mysheet') #删除工作表(方法二)
指定sheet工作表：sheet表名可以作为字典的key进行索引
from openpyxl import load_workbook wb = load_workbook('./test.xlsx')
ws = wb['Sheet2'] print(ws, '指定的工作表sheet名：', ws.title, end='\n\n') ws1 = wb.active print(ws1, '处于激活状态下的工作表sheet表名：', ws1.title)
运行结果：
【注意】三者区别：
wb.sheetnames # 获取文档所有工作表名称 wb['Sheet1'] # 获取指定的工作表 wb.active # 获取当前活跃的工作表
获取Excel中已存在的全部sheet表名
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb.sheetnames) # 工作簿中存在所有sheet表名（以列表格式返回） print(wb.worksheets) # 以列表形式返回所有sheet工作表对象
运行结果：
修改某一指定sheet表标签按钮颜色
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') ws = wb.active ws.sheet_properties.tabColor = '1072BA' # 默认为白色
判断某一指定Excel是否以只读方式打开（返回True或者False）
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb.read_only)
运行结果：
复制Sheet表
【注意】
①只有单元格（包括值、样式、超链接、备注）和一些工作表对象（包括尺寸、格式和参数）会被复制，其他属性不会被复制，如图片、图表；
②无法在两个文档中复制工作表。
③当文档处于只读或只写状态时也无法复制工作表。
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') source = wb.active # 获取当前活跃的工作表 target = wb.copy_worksheet(source) # 复制当前活跃的sheet表 print(target)
运行结果：
保存工作薄
wb.save('文件名.xlsx')
Excel属性操作
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb.encoding, end='\n\n') # 获取文档的字符集编码 print(wb.properties) # 获取文档的元数据如标题，创建者，创建日期等
运行结果：
获取某一指定sheet表中的最大行数以及最大列
ws.max_column
ws.max_row
选择单个单元格：获取指定位置的单元格对象
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb) ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws) # 选择单个单元格（获取指定位置的单元格对象） print(ws['A1']) print(ws.cell(1, 1)) # 先行后列，都是索引下标
运行结果：
单元格属性
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb) ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws) cell = ws['A1'] # 获取指定位置的单元格对象 # 单元格列索引 print(cell.col_idx) print(cell.column) # 单元格行索引 print(cell.row) # 单元格列名 print(cell.column_letter) # 单元格的坐标 print(cell.coordinate) # 单元格数字类型 # 默认是 # n：数值 # s：字符串 # d：日期时间 print(cell.data_type) # 单元格编码格式，默认 utf-8 print(cell.encoding) # 是否有样式 print(cell.has_style) # 默认样式是 Normal，如果是默认样式，返回False # 单元格样式 print(cell.style) # 单元格样式id print(cell.style_id)
运行结果：
单元格的样式属性
获取指定单元格的值
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb) ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws) cell = ws['A1'] # 获取指定位置的单元格对象 print(cell) # 获取指定单元格的值 print(ws['A1'].value) print(ws.cell(1, 1).value)
运行结果：
选择单元格
单元格赋值
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb) ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws) cell = ws['A1'] # 获取指定位置的单元格对象 print(cell) ws['A1'] = '第一行第一列修改过后的单元格值' ws.cell(1, 2).value = '第一行第二列修改过后的单元格值' wb.save(filename='./test.xlsx')
运行结果：
【注意】
①任何对于Excel文件的修改操作都需要进行保存之后才会生效。
②当单元格赋值使用 cell() 函数语法时，只能给单元格对象的value属性赋值，注意与 ws['A1'] = '值' 区分。 ws.cell(2,2) = 20 # 会报错
在指定sheet表最后空白行新增行数据
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb) ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws) cell = ws['A1'] # 获取指定位置的单元格对象 print(cell) ws.append([1, 2, 3]) wb.save(filename='./test.xlsx')
运行结果：
单元格遍历
① ws.values 返回的是生成器，是将一行数据作为一个元组单元组成的，是由值组成的。
② ws.values 获取的内容是从 “A1” 到 “最大行最大列”。
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb, end='\n\n') ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws, end='\n\n') cell = ws['A1'] # 获取指定位置的单元格对象 print(cell, end='\n\n') print(ws.values, end='\n\n') # 生成器对象，将一行单元格作为元组单元--》组成的生成器 print(list(ws.values), end='\n\n') # 将生成器对象转换为列表数据，列表中是生成器中的所有数据 for i in ws.values: print(i)
运行结果：
或者：
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb, end='\n\n') ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws, end='\n\n') cell = ws['A1'] # 获取指定位置的单元格对象 print(cell, end='\n\n') for i in ws.iter_rows(min_col=1, max_col=3, min_row=1, max_row=10): print(i) print(ws.iter_rows()) # 将每一个单元格对象（按行数据中的单元格）作为一个元组单元--》组成的生成器 print(ws.rows, end='\n\n') # 将每一个单元格对象（按行数据中的单元格）作为一个元组单元--》组成的生成器 print(list(ws.rows), end='\n\n') # 列表中存放的是生成器中的每一个单元格对象 for i in ws.rows: # 遍历以单元格对象为单位的元组单元组成的生成器 for j in i: # 遍历单元格对象中的属性 print(j, j.value)
运行结果：
或者：
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb, end='\n\n') ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws, end='\n\n') cell = ws['A1'] # 获取指定位置的单元格对象 print(cell, end='\n\n') print(ws.columns, end='\n\n') # 将每一个单元格对象（按照列数据）作为一个元组单元--》组成的生成器 print(ws.iter_cols(), end='\n\n') # 将每一个单元格对象（按照列数据）作为一个元组单元--》组成的生成器 print(list(ws.columns), end='\n\n') for i in ws.columns: print(i)
运行结果：
指定sheet表中删除行和删除列
【注意】删除行或者列后，后面的行或者列会自动往前填充，也就是说，删除第一列，原来的第二列就会变成第一列。
①
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb) ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws, end='\n\n') print(ws.cell(1, 2).value) ws.delete_cols(1) # 删除第一列，以此类推、n代表删除第n列 wb.save(filename='./test.xlsx') print(ws.cell(1, 2).value)
运行结果：
②
from openpyxl import load_workbook wb = load_workbook('./test.xlsx') print(wb) ws = wb.active # 获取当前活跃的worksheet对象（sheet表） print(ws, end='\n\n') print(ws.cell(1, 1).value) ws.delete_rows(1) # 删除第一行，以此类推、n代表删除第n行 wb.save(filename='./test.xlsx') print(ws.cell(1, 1).value)
运行结果：
转pandas
合并单元格
样式设置
颜色
Color(index=0) # 根据索引进行填充 # Color(rgb='00000000') # 根据rgb值进行填充 # index COLOR_INDEX = ( '00000000', '00FFFFFF', '00FF0000', '0000FF00', '000000FF', #0-4 '00FFFF00', '00FF00FF', '0000FFFF', '00000000', '00FFFFFF', #5-9 '00FF0000', '0000FF00', '000000FF', '00FFFF00', '00FF00FF', #10-14 '0000FFFF', '00800000', '00008000', '00000080', '00808000', #15-19 '00800080', '00008080', '00C0C0C0', '00808080', '009999FF', #20-24 '00993366', '00FFFFCC', '00CCFFFF', '00660066', '00FF8080', #25-29 '000066CC', '00CCCCFF', '00000080', '00FF00FF', '00FFFF00', #30-34 '0000FFFF', '00800080', '00800000', '00008080', '000000FF', #35-39 '0000CCFF', '00CCFFFF', '00CCFFCC', '00FFFF99', '0099CCFF', #40-44 '00FF99CC', '00CC99FF', '00FFCC99', '003366FF', '0033CCCC', #45-49 '0099CC00', '00FFCC00', '00FF9900', '00FF6600', '00666699', #50-54 '00969696', '00003366', '00339966', '00003300', '00333300', #55-59 '00993300', '00993366', '00333399', '00333333', #60-63 ) BLACK = COLOR_INDEX[0] WHITE = COLOR_INDEX[1] RED = COLOR_INDEX[2] DARKRED = COLOR_INDEX[8] BLUE = COLOR_INDEX[4] DARKBLUE = COLOR_INDEX[12] GREEN = COLOR_INDEX[3] DARKGREEN = COLOR_INDEX[9] YELLOW = COLOR_INDEX[5] DARKYELLOW = COLOR_INDEX[19]
字体
ws.cell(5,3).value='哈哈哈' ws.cell(5,3).font = Font(name='仿宋',size=12,color=Color(index=0),b=True,i=True) # size sz 字体大小 # b bold 是否粗体 # i italic 是否斜体 # name family 字体样式
边框
边
Side(style='thin',color=Color(index=0)) # style可选项 style = ('dashDot','dashDotDot', 'dashed','dotted', 'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'thick', 'thin') # 'medium' 中粗 # 'thin' 细 # 'thick' 粗 # 'dashed' 虚线 # 'dotted' 点线
填充
PatternFill(patternType='solid',fgColor=Color(), bgColor=Color()) # fgColor 前景色 # bgColor 后景色 # 参数可选项 patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical', 'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis', 'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp', 'lightHorizontal', 'darkTrellis', 'darkVertical'}
ws.cell(3,3).fill = PatternFill()
对齐
Alignment(horizontal='fill',vertical='center') # 参数可选项 horizontal = {'fill', 'distributed', 'centerContinuous', 'right', 'justify', 'center', 'left', 'general'} vertical = {'distributed', 'justify', 'center', 'bottom', 'top'} ws.cell(3,3).alignment= Alignment()
数字显示样式
链接
行高 列宽
去期待陌生，去拥抱惊喜。
