python标准库之openpyxl的常规操作

见贤思齐

目录openpyxl（Excel文件处理模块）读sheet读sheet中单元格合并单元格openpyxl模块基本用法安装方法基本使用读取Excel文档（一）获取工作表（二）获取单元格（三）获取行和列写入Excel文档（一）写入单元格（二）保存文件设置单元格样式（一）字体（二）对齐方式（三）边框（四）设置行高和列宽（五）合并和拆分单元格openpyxl(Excel文件处理模块)读sheet读取Excel文件中的工作表（sheet）也是重要的知识点。在使用openpyxl库时，可以通过以下方式来读取工作表：按名称读取工作表：你可以使用工作簿对象的sheetnames属性获取所有工作表的名称列表，然后通过工作簿对象的[sheet_name]或者get_sheet_by_name(sheet_name)方法来获取指定名称的工作表对象。workbook=openpyxl.load_workbook('p1.xlsx')sheet_names=workbook.sheetnamessheet=workbook[sheet_names[0]]#根据名称获取第一个工作表对象按索引读取工作表：除了按名称读取外，也可以直接使用索引来获取工作表对象。workbook=openpyxl.load_workbook('p1.xlsx')sheet=workbook.worksheets[0]#获取第一个工作表对象一旦获取了工作表对象，我们就可以使用工作表对象进行单元格数据的读取，遍历行和列等操作。实例fromopenpyxlimportload_workbookworkbook=load_workbook("p1.xlsx")#1、获取excel文件中所有shell名称print(workbook.sheetnames)#2、选择sheet,基础sheet名称"""sheet=workbook["人员"]cell=sheet.cell(2,3)print(cell.value)"""#3、选择sheet，基于索引位置"""sheet=workbook.worksheets[0]#worksheets索引函数cell=sheet.cell(2,3)print(cell.value)"""#4、循环所有的sheet，三种方式！"""fornameinworkbook.sheetnames:#sheetnames名称函数sheet=workbook[name]cell=sheet.cell(1,1)print(cell.value)""""""forsheetinworkbook.worksheets:#worksheets索引函数cell=sheet.cell(1,1)print(cell.value)""""""forsheetinworkbook:cell=sheet.cell(1,1)print(cell.value)"""读sheet中单元格fromopenpyxlimportload_workbookwb=load_workbook("p1.xlsx")sheet=wb.worksheets[0]#获取索引位置为0的sheet-->也就是第一个sheet"""#1、获取第N行第N列的单元格（位置从1开始）cell=sheet.cell(1,1)print(cell.value)#内容print(cell.style)#样式print(cell.font)#字体print(cell.alignment)#排列情况""""""#2、获取某个单元格v1=sheet["A2"]print(v1.value)v2=sheet["C2"]print(v2.value)"""#3、获取第N行所有的单元格"""forcellinsheet[1]:print(cell.value)"""#4、获取所有行的数据"""forrowinsheet.rows:print(row[0].value,row[1].value,row[2].value)"""#4、获取所有列的数据"""forcloinsheet.columns:print(clo[0].value,clo[1].value,clo[2].value)"""合并单元格importopenpyxlfromopenpyxlimportload_workbook#打开excel工作表workbook=openpyxl.load_workbook("p1.xlsx")#获取所有工作表的名称列表sheet_name=workbook.sheetnames#选择第一个工作表进行操作sheet=workbook[sheet_name[0]]#合并A1:A2sheet.merge_cells('A1:A2')#设置合并后的值sheet['A1']='OK!'#保存修改workbook.save('p4.xlsx')openpyxl模块基本用法openpyxl是一个用于处理xlsx格式Excel表格文件的第三方python库，其支持Excel表格绝大多数基本操作。安装方法使用pip或通过专门pythonIDE（如pyCharm）进行安装其中pip安装方法，命令行输入：pipinstallopenpyxl基本使用第一步先是要导入openpyxl模块importopenpyxl读取Excel文档通过调用方法load_workbook(filename)进行文件读取，该方法中还有一个read_only参数用于设置文件打开方式，默认为可读可写，该方法最终将返回一个workbook的数据对象。#文件必须是xlsx格式，如果是其他格式在执行前可利用win32辅助转化wb=openpyxl.load_workbook('p1.xlsx')（一）获取工作表每一个Excel表格中都会有很多张sheet工作表，在对表格操作前需要先选定一张工作表#获取所有工作表名(返回一个列表)sheets=wb.get_sheet_names()#获取某一特定的工作表sheet=wb.get_sheet_by_name('Sheet2')#获取工作表的表名sheet_name=sheet.title#一般来说，表格大多数用到的是打开时显示的工作表，这时可以用active来获取当前工作表sheet=wb.active（二）获取单元格对Excel表格的操作最终都落于对单元格的操作，获取单元格有两种获取方法：sheet[列行名]和sheet.cell(row,column)#通过sheet[列行名]获取a=sheet['A2']#通过sheet.cell(row,column)获取b=sheet.cell(1,2)#即sheet['B1']#获取单元格内容print(a.value)#获取单元格所在列和行print(‘ais’+str((a.column,a.row)))需要注意的是，sheet.cell(row,column)中参数分别是行和列，且必须为整数，如果列为英文字母，可以利用openpyxl.utils中的column_index_from_string(char)进行字母数字的转化。顺便一说，同理也可以利用get_column_letter(number)进行数字字母间的转化fromopenpyxl.utilsimportget_column_letter,column_index_from_string#对列进行字母/数字转化c_num=column_index_from_string('B')#c_num=2c_char=get_column_letter(5)#c_char='E‘（三）获取行和列在处理Excel表格有时可能需要对表格进行遍历查找，openpyxl中便提供了一个行和列的生成器(sheet.rows和sheet.columns),这两个生成器里面是每一行（或列）的数据，每一行（或列）又由一个tuple包裹，借此可以很方便地完成对行和列的遍历#对行进行遍历,输出A1,B1,C1forrowinsheet.rows:forcellinrow:print(cell.value)#对列进行遍历,输出A1,A2,A3forcolumninsheet.columns:forcellincolumn:print(cell.value)学习时还发现也可以通过list(sheet.rows)index 对某一行或列进行遍历，而在此值得注意的是，由于sheet.rows(或sheet.columns)是生成器类型，是不能直接调用的，需将其转化为一个list类型，然后再通过索引遍历#对某一特定的行进行遍历forcellinlist(sheet.rows)[0]:print(cell.value)同时，也可以通过使用sheet[行列值:行列值]来对给定单元格范围进行遍历#对某一单元格范围进行遍历forspacesinsheet['A1':'B2']:forcellinspaces:print(cell.value)另外，有时候我们还可能需要确定表格的大小，即获取表格行和列的最大值，可以用max_row和max_column来获取#获得最大列和最大行print(sheet.max_row)print(sheet.max_column)写入Excel文档在开头读取时已经介绍，默认的打开方式为可读可写，那么使用load_workbook(filename)读取Excel文档后也就可以直接写入了。另外，如果需要新建一个Excel文件，可以使用Workbook()方法，同时它会自动提供一个sheet工作表。对于删除一个工作表，则可以使用workbook对象的remove(sheet)方法删除#新建一个Excel文档wb=openpyxl.Workbook()#删除某个工作表wb.remove(sheet)（一）写入单元格获取工作表和之前一样，如果使用load_workbook(filename)读取，那么获取工作表后可以直接通过sheet[行列值]写入单元格。学习时，有资料介绍还可以传入Excel中的公式进行赋值，不过要注意，在读取文件时需要加上参数data_only=True，这样才能返回数字，否则将返回字符串，即公式本身。#直接赋值sheet['A1'].value=2#公式赋值sheet['A6'].value='=SUM(A1:A5)'另外，也可使用sheet.append(parameters)一行或多行写入#写入一行row=[1,2,3,4,5]sheet.append(row)#写入多行rows=[['ID','Name','Department'],['001','Lee','CS'],['002','John','MA'],['003','Amy','IS']]sheet.append(rows)（二）保存文件写完文件后，使用workbook.save(path+filename)进行保存，不过要注意文件扩展名一定要是xlsx格式#保存文件至当前目录wb.save('new_file.xlsx')设置单元格样式单元格样式主要包括字体、边框、颜色以及对齐方式等，这些均位于openpyxl.styles库中#导入字体、边框、颜色以及对齐方式相关库fromopenpyxl.stylesimportFont,Border,Side,PatternFill,colors,Alignment（一）字体通过sheet单元格font属性设置字体风格#设置字体风格为TimesNewRoman，大小为16，粗体、斜体，颜色蓝色sheet['A1'].font=Font(name='TimesNewRoman',size=16,bold=True,italic=True,color=colors.BLUE)（二）对齐方式通过sheet单元格alignment属性设置文本对齐风格#通过参数horizontal和vertical来设置文字在单元格里的对齐方式，此外设置值还可为left和rightsheet['B1'].alignment=Alignment(horizontal='center',vertical='center')（三）边框通过sheet单元格border属性设置字体风格#首先设置边框四个方向的线条种类left,right,top,bottom=[Side(style='thin',color='000000')]*4#再将各方向线条作为参数传入Border方法sheet['C1'].border=Border(left=left,right=right,top=top,bottom=bottom)（四）设置行高和列宽行和列的长度大小可以通过row_dimensions[序号].height和column_dimensions[标号].width来设置#设置行高sheet.row_dimensions[1].height=25#设置列宽sheet.column_dimensions['D'].width=15.5（五）合并和拆分单元格对单元格的合并与拆分，主要是通过sheet的merge_cells(args1:args2)和unmerge_cells(args1:args2)两个方法来实现的；当然，除了对角矩形区域化合并，也可以对一行或一列进行合并，只需相应修改参数即可。不过，这里要注意的是，合并后单元格显示的文本内容是合并前最左上角单元格的内容，而其他单元格内容则会自动清除。#合并单元格sheet.merge_cells('A1:B2')#拆分单元格sheet.unmerge_cells('A1:B2')