Python3之excel操作-openpyxl模块

1：openpyxl模块介绍 

  openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到其它库（如：xlrd、xlwt等），这是openpyxl比较其他模块的不足之处。openpyxl是一款比较综合的工具，不仅能够同时读取和修改Excel文档，而且可以对Excel文件内单元格进行详细设置，包括单元格样式等内容，甚至还支持图表插入、打印设置等内容，使用openpyxl可以读写xltm, xltx, xlsm, xlsx等类型的文件，且可以处理数据量较大的Excel文件，跨平台处理大量数据是其它模块没法相比的。因此，openpyxl成为处理Excel复杂问题的首选库函数。

在使用openpyxl前先要掌握三个对象:

1:Workbook(工作簿，一个包含多个Sheet的Excel文件)。

2:Worksheet（工作表，一个Workbook有多个Worksheet，表名识别，如“Sheet1”,“Sheet2”等）。

3:Cell（单元格，存储具体的数据对象）三个对象。

2：openpyxl模块安装

pip install openpyxl

3：openpyxl模块常用操作

3.1：获取工作表

# encoding=gbkimport  openpyxl# 打开Book工作簿workBook = openpyxl.load_workbook('../test.xlsx') # 查看工作簿对象中所有的属性与方法# print(help(workBook)) # 获取所有的工作表名print(workBook.sheetnames) # ['Sheet1', 'Sheet2', 'Sheet3']print(workBook.get_sheet_names()) # ['Sheet1', 'Sheet2', 'Sheet3'] # 获取所有的工作表print(workBook.worksheets) # 返回一个列表# 获取某个工作表# 1:获取当前激活的工作表workSheet = workBook.active# 2：通过下标获取工作表workSheet = workBook.worksheets[0]# 获取工作表的索引# print(workBook.index(workSheet))# print(workBook.get_index(workSheet))# 3:通过工作表名获取workSheet = workBook.get_sheet_by_name('Sheet1')

3.2：行操作

# encoding=gbk import openpyxl # 打开Book工作簿workBook = openpyxl.load_workbook('../test.xlsx') # 查看工作簿对象中所有的属性与方法# print(help(workBook))# 获取某个工作表# 1:获取当前激活的工作表# workSheet = workBook.active# 2：通过下标获取工作表# workSheet = workBook.worksheets[0]# 3:通过工作表名获取workSheet = workBook.get_sheet_by_name('Sheet1') # 查看工作表中的方法与属性print(help(workSheet)) print(workSheet.min_row) # 有数据的最小行索引print(workSheet.max_row) # 有数据的最大行索引 # 获取所有的行:从1行1列开始，到有数据的最大行(workSheet.max_row)，最大列(workSheet.max_column)；print(workSheet.rows) # generator objectfor row in workSheet.rows: print(row[0],row[0].value) # row[0]: 某行对应的第一列的单元格，row[0].value 单元格中的值 # 可以通过指定开始行列，结束行列的范围来获取所需要的行for row in workSheet.iter_rows(min_row=3, min_col=1,max_col = workSheet.max_column, max_row = workSheet.max_row): print(row[0], row[0].value) # row[0] 行对应的第一列的单元格，row[0].value 单元格中的值

3.3：列操作

# encoding=gbk import openpyxl # 打开Book工作簿workBook = openpyxl.load_workbook('../test.xlsx') # 查看工作簿对象中所有的属性与方法# print(help(workBook))# 获取某个工作表# 1:获取当前激活的工作表# workSheet = workBook.active# 2：通过下标获取工作表# workSheet = workBook.worksheets[0]# 3:通过工作表名获取workSheet = workBook.get_sheet_by_name('Sheet1') # 查看工作表中的方法与属性print(help(workSheet))print(workSheet.min_column) # 有数据的最小列索引print(workSheet.max_column) # 有数据的最大列索引 # 获取所有的行:从1行1列开始，到有数据的最大行(workSheet.max_row)，最大列(workSheet.max_column)；print(workSheet.columns) # generator objectfor col in workSheet.columns: print(col[0],col[0].value) # col[0]: 某列对应的第一行的单元格，col[0].value 单元格中的值 print('*'*60)# 可以通过指定开始行列，结束行列的范围来获取所需要的列for col in workSheet.iter_cols(min_row=3, min_col=2,max_col = workSheet.max_column, max_row = workSheet.max_row): print(col[0], col[0].value) # col[0] 列对应的第一行的单元格，col[0].value 单元格中的值

3.4：单元格操作

# encoding=gbkimport  openpyxl# 打开Book工作簿workBook = openpyxl.load_workbook('../test.xlsx')# 查看工作簿对象中所有的属性与方法# print(help(workBook))# 获取某个工作表# 1:获取当前激活的工作表# workSheet = workBook.active# 2：通过下标获取工作表# workSheet = workBook.worksheets[0]# 3:通过工作表名获取workSheet = workBook.get_sheet_by_name('Sheet1') # 查看工作表中的方法与属性# print(help(workSheet)) # 1：可以通过上面的行操作，列操作，获取单元格的内容，及重新设置单元格的内容# 2：通过如下方式获取，重新设置单元格的内容print(workSheet['A1'].value)workSheet['A1'].value = '1234';# 3: 通过 cell(self, row, column, value=None) 获取，设置print(workSheet.cell(1,1).value) # 不提供value，返回单元格print(workSheet.cell(1,1,200).value) # 提供value，首先重置单元格内容，再返回单元格 workBook.save('test_1.xlsx');

3.5：写excel文件，合并单元格，样式处理，设置公式等

# encoding=gbkimport openpyxl # 一：打开Book工作簿，或创建工作簿workBook = openpyxl.load_workbook('../test.xlsx') # 在已有的excel文件基础上进行操作# workBook = openpyxl.Workbook() # 创建新的文件来操作 # 查看工作簿对象中所有的属性与方法# print(help(workBook)) # 二：获取某个工作表，或新创建工作表# 1:获取当前激活的工作表# workSheet = workBook.active# 2：通过下标获取工作表# workSheet = workBook.worksheets[0]# 3:通过工作表名获取# workSheet = workBook.get_sheet_by_name('Sheet1') # 在已有的工作表上进行操作workSheet = workBook.create_sheet('Sheet_test',0) # 创建新的工作表，第二个参数为插入的位置 # 查看工作表中的方法与属性# print(help(workSheet)) # 三：给单元格赋值,设置公式# 1：可以通过上面的行操作，列操作，获取单元格的内容，及重新设置单元格的内容# 2：通过如下方式获取，重新设置单元格的内容print(workSheet['A1'].value)workSheet['A1'].value = '1234';# 3: 通过 cell(self, row, column, value=None) 获取，设置print(workSheet.cell(1,1).value) # 不提供value，返回单元格print(workSheet.cell(1,1,300).value) # 提供value，首先重置单元格内容，再返回单元格print(workSheet.cell(2,1,200).value) # 给单元格设置公式：为单元格设置公式的方式与设置普通值是一样的。workSheet['A3'] = '=SUM(A1:A2)'# 四：合并单元格# merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)print(workSheet.merge_cells(start_row=2,start_column=2,end_row=5,end_column=4))workSheet.cell(2, 2).value = 'hello' # 给合并之后的单元格赋值 workSheet.merge_cells('A8:C10')workSheet['A8'].value = 'A8-88'; # 取消合并# unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)# 五：样式# 导入库from openpyxl.styles import Font, colors, Alignment# 样式--->字体bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)workSheet['A8'].font = bold_itatic_24_font # 样式--->对齐方式:也是直接使用cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等参数# 设置B1中的数据垂直居中和水平居中workSheet['A8'].alignment = Alignment(horizontal='center', vertical='center') # 样式--->设置行高和列宽# 第2行行高workSheet.row_dimensions[2].height = 40# C列列宽workSheet.column_dimensions['C'].width = 30 # 样式--->改变 sheet 标签按钮颜色workSheet.sheet_properties.tabColor = "0000ff" workBook.save('test_1.xlsx');