python第三方库openpyxl详解（openpyxl使用）

python第三方库openpyxl详解（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_workbookwb = 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()# 激活 worksheetws = wb.active

加载已存在的工作簿

from openpyxl import load_workbookwb = load_workbook('文件名称.xlsx') # openpyxl第三方库只能处理.xlsx格式的Excel表格

获取当前所处工作表的sheet表名

from openpyxl import load_workbookwb = 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_workbookwb = load_workbook('./test.xlsx')ws = wb.activeprint(ws.title)ws.title = 'MS'wb.save('./test.xlsx')

运行结果：

创建和删除工作表

【注意】对工作簿修改后一定要记得执行保存操作！ wb.save('Excel文件名.xlsx')

from openpyxl import load_workbookws1 = 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_workbookwb = load_workbook('./test.xlsx')ws = wb['Sheet2']print(ws, '指定的工作表sheet名：', ws.title, end='\n\n')ws1 = wb.activeprint(ws1, '处于激活状态下的工作表sheet表名：', ws1.title)

运行结果：

【注意】三者区别：

wb.sheetnames # 获取文档所有工作表名称wb['Sheet1'] # 获取指定的工作表wb.active # 获取当前活跃的工作表

获取Excel中已存在的全部sheet表名

from openpyxl import load_workbookwb = load_workbook('./test.xlsx')print(wb.sheetnames) # 工作簿中存在所有sheet表名（以列表格式返回）print(wb.worksheets) # 以列表形式返回所有sheet工作表对象

运行结果：

修改某一指定sheet表标签按钮颜色

from openpyxl import load_workbookwb = load_workbook('./test.xlsx')ws = wb.activews.sheet_properties.tabColor = '1072BA' # 默认为白色

判断某一指定Excel是否以只读方式打开（返回True或者False）

from openpyxl import load_workbookwb = load_workbook('./test.xlsx')print(wb.read_only)

运行结果：

复制Sheet表

【注意】

①只有单元格（包括值、样式、超链接、备注）和一些工作表对象（包括尺寸、格式和参数）会被复制，其他属性不会被复制，如图片、图表；

②无法在两个文档中复制工作表。

③当文档处于只读或只写状态时也无法复制工作表。

from openpyxl import load_workbookwb = load_workbook('./test.xlsx')source = wb.active # 获取当前活跃的工作表target = wb.copy_worksheet(source) # 复制当前活跃的sheet表print(target)

运行结果：

保存工作薄

wb.save('文件名.xlsx')

Excel属性操作

from openpyxl import load_workbookwb = load_workbook('./test.xlsx')print(wb.encoding, end='\n\n') # 获取文档的字符集编码print(wb.properties) # 获取文档的元数据如标题，创建者，创建日期等

运行结果：

获取某一指定sheet表中的最大行数以及最大列

ws.max_column

ws.max_row

选择单个单元格：获取指定位置的单元格对象

from openpyxl import load_workbookwb = 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_workbookwb = 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-8print(cell.encoding)# 是否有样式print(cell.has_style) # 默认样式是 Normal，如果是默认样式，返回False# 单元格样式print(cell.style)# 单元格样式idprint(cell.style_id)

运行结果：

单元格的样式属性

获取指定单元格的值

from openpyxl import load_workbookwb = 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_workbookwb = 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_workbookwb = 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_workbookwb = 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_workbookwb = 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_workbookwb = 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_workbookwb = 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_workbookwb = 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()

数字显示样式

链接

行高 列宽

去期待陌生，去拥抱惊喜。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。