Python对excel的基本操作[TOC]一、前言本文基于Python的第三方库openpyxl，据官方介绍支持Excel2010xlsx/xlsm/xltx/xltm格式文件，应支持这些格式的哪个版本。作为一名网络攻城狮，我们可以通过python来掌握excel的基本操作技巧。当然，最好是精通。那么我们用openpyxl做什么呢？后面要给大家分享一篇关于网络设备配置批量备份的文章，里面会涉及到excel的操作，所以提前给大家分享一下如何操作excel基础，顺便巩固下自己的知识。来，我们来看下图，这是一张所有网络设备的管理地址表。通过python的openpyxl库，可以读取ip地址信息、巡检命令等信息，可以批量备份网络。设备已配置。之前用python结合txt文本，觉得不方便，改成python结合excel。Excel编辑起来非常方便。二、实验环境windowdown10Python3.69PycharmPython第三方库openpyxlexcel2013说明：请随意组合实验环境，python版本3.x以上。3.基本操作下面一步步教大家如何操作...3.1安装openpyxl第三方库首先我们要安装第三方库`openpyxl`，使用如下命令安装.C:\>pipinstallopenpyxl3.2Createanewworkbook3.2.1CreateanewworkbookfromopenpyxlimportWorkbook#类实例化wb=Workbook()#保存生成文件wb.save('simple_excel.xlsx')说明：运行代码后，会生成一个excel文件：simple_excel.xlsx，暂无内容。3.2.2DefaultworksheetfromopenpyxlimportWorkbook#Classinstantiationwb=Workbook()#激活并默认创建第一个工作表：sheetws1=wb.active#命名第一个工作表：1_sheetws1.title='1_sheet'#保存并生成filewb.save('simple_excel.xlsx')效果如下：3.2.3CreateaworksheetfromopenpyxlimportWorkbook#类实例化wb=Workbook()#默认激活创建第一个Worksheetws1=wb.active#将第一个工作表命名为ws1.title='1_sheet'#创建工作表3ws3=wb.create_sheet(title='3_sheet',index=2)#创建工作表2ws2=wb.create_sheet('2_sheet',1)#创建工作表4ws4=wb.copy_worksheet(ws3)#保存并生成文件wb.save('simple_excel.xlsx')参数说明：属性title：给工作表命名；methodcreate_sheet：新建一个工作表，其中index为工作表的顺序索引，如0表示第一个工作表……；methodcopy_worksheet：复制工作表；methodsave：保存并生成一个文件，每次运行都会覆盖同名文件；3.2.4DeletetheworksheetfromopenpyxlimportWorkbook#classinstantiationwb=Workbook()#activateandcreatethefirstworksheetdefault#...省略中间代码...ws4=wb.copy_worksheet(ws3)#删除工作Tablewb.remove(ws4)#保存并生成文件wb.save('simple_excel.xlsx')注意：我不会运行这一步。3.2.5SetworksheetcolorfromopenpyxlimportWorkbook#classinstantiationwb=Workbook()#...省略中间代码...#setworksheetbackgroundcolorws1.sheet_properties.tabColor='90EE90'ws2.sheet_properties.tabColor='1E90FF'ws3.sheet_properties.tabColor='90EE90'ws4.sheet_properties.tabColor='1E90FF'#保存生成文件wb.save('simple_excel.xlsx')参数说明：属性tabColor：设置工作表的背景颜色，RGB可以使用颜色。效果如下：3.2.6Cell写入数据####WriteasingledatafromopenpyxlimportWorkbook#类实例化wb=Workbook()#...省略中间代码...#Cell写入数据#方法一:ws1['A1']='Example:'#方法二：ws1.cell(row=1,column=1,value='Example:')#保存生成文件wb.save('simple_excel.xlsx')batchwritedatafromopenpyxlimportWorkbook#类实例化wb=Workbook()#...省略中间代码...#单元格写入数据data=[["device_name","device_ip","vendor","model","sn","os","version","update_time"],['switch-01',"192.168.1.1","cisco",'WS-C3560G-24TS',"FOC00000000","cisco_ios","12.2(50)SE5","1周,1分钟"],['switch-02',"192.168.1.2","cisco",'WS-C3560G-24TS',"FOC00000000","cisco_ios","12.2(50)SE5","1周,1分钟"],['switch-03',"192.168.1.3","cisco",'WS-C3560G-24TS',"FOC00000000","cisco_ios","12.2(50)SE5","1weeks,1minutes"],]forrowindata:ws1.append(row)#saveandgeneratefilewb.save('simple_excel.xlsx')参数说明：append：传入一个可迭代对象（字符串，列表，元组...），迭代写入单元格；效果如下：3.2.7设置背景颜色来自openpyxlimportWorkbookf的单元格romopenpyxl.stylesimportPatternFill,Border,Side,Font,colors#classinstantiationwb=Workbook()#...省略中间代码...#cellfillbackgroundcolorbackground_color=PatternFill(start_color='00BFFF',fill_type='solid')#setborderborder=Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))font_type=Font(color=colors.WHITE,size=12,b=True,bold=True)#设置字体居中Align=Alignment(horizo??ntal='center',vertical='center')#loopws1.iter_rows(min_row=2,max_row=2)中迭代单元格并设置行样式：forcellinrow:cell.fill,cell.font,cell.alignment=background_color,font_type,Align参数说明：classPatternFill:start_color、end_color分别为背景色、图案颜色、图案样式；classBorder：设置边框线条样式，如线宽样式、对角线等；类Font：设置字体颜色、字号、下划线等；classAlignment:设置文本对齐方式，水平对齐，垂直对齐；效果如下：3.2.8合并单元格#...省略代码...#合并单元格ws1.merge_cells('A1:H1')ws1.unmerge_cells('A1:H1')#...省略代码...参数说明：merge_cells：合并后的单元格;unmerge_cells:取消合并单元格；效果如下：3.2.9自动调整列单元格宽度fromopenpyxlimportWorkbookfromopenpyxl.stylesimportPatternFill,Border,Side,Font,colors,Alignmentfromopenpyxl.utilsimportget_column_letter#classinstantiationwb=Workbook()#...省略中间代码...#自动调整单元格宽度#过滤出每列单元格的最大长度，作为调整列宽的值all_ws=wb.sheetnamesforwsinall_ws:dims={}ws=wb[ws]forrowinws.rows:forcellinrow:ifcell.value:dims[cell.column]=max(dims.get(cell.column,0),len(str(cell.value)))forcol,valueindims.items():ws.column_dimensions[get_column_letter(col)].width=value+3dims.clear()思路解读：首先求出列中所有数据的最大长度，然后根据这个长度自动调整单元格的宽度。首先定义一个空字典dims来存储键值对，column（列）：value（单元格长度）；逐一比较每一列的单元格值长度，得到最大值，最后得到最大值作为每一列宽度的宽度值；methodget_column_letter()：将cell.column整数值1、2、3……转换为列字符串‘A’、‘B’、‘C’……；methodcolumn_dimensions:passwidth设置列的宽度，建议增加一点；效果如下：3.2.10chartfromopenpyxl.chartimportBarChart3D,Reference#...省略中间代码...#向单元格写入数据firstdata=[["Fruit","2017","2018","2019","2020"],['Apples',10000,5000,8000,6000],['Pears',2000,3000,4000,5000],['Bananas',6000,6000,6500,6000],['Oranges',500,300,200,700],]forrowindata:ws2.append(row)#开始绘制3D直方图chart=BarChart3D()chart.type='col'chart.style=10chart.title='SalesBarChart'chart.x_axis.title='Fruit'chart.y_axis.title='Sales'#set_categories()X轴设置数据，add_data()y轴设置数据data=Reference(ws2,min_col=2,min_row=1,max_col=5,max_row=5)series=Reference(ws2,min_col=1,min_row=2、max_row=5)chart.add_data(data=data,titles_from_data=True)chart.set_categories(series)ws2.add_chart(chart,'A7')参数表示说明：属性类型：可以通过两种方式设置columncol和horizo??ntalbar；属性style：设置样式，为1~48之间的整数值；属性title：设置标题；属性x_axis.title：x轴的标题；属性y_axis。title：y轴的标题；classReference：指单元格区域的数据；methodadd_data：设置Y轴数据；methodset_categories:设置X轴数据；效果如下：3.3加载工作簿通过load_workbook方法文件加载已有的excel，以read_only方式读取内容，不能编辑load_workbook方法参数：filename：文件路径或文件对象；read_only：是否只读，优化阅读，不能编辑内容；keep_vba：是否保留在vba中（不代表可以使用），默认保留；data_only：单元格是保留公式还是结果；keep_links：是否保留外部链接，默认保留；3.3.1Gettheworksheetfromopenpyxlimportload_workbookasopen#classinstantiationwb=open('simple_excel.xlsx',read_only=True)#Getallworksheetsprint('Allworksheets:',wb.sheetnames)#关闭工作簿wb.close()#回显结果如下：Allworksheets:['1_sheet','2_sheet','3_sheet','3_sheetCopy']参数说明：参数read_only=True：表示以read方式打开工作簿只有模式；methodsheetnames：返回工作表名称列表；methodclose():只在只读和只写模式下使用，下同，不再多次解释；3.3.2遍历工作表fromopenpyxlimportload_workbookasopen#类实例化wb=open('simple_excel.xlsx',read_only=True)#获取单个工作表print('1stworksheet:',wb.sheetnames[0])print('第二张工作表:',wb.sheetnames[1])print('第三张工作表:',wb.sheetnames[2])print('第四张工作表:',wb.sheetnames[3])#循环遍历工作表forwsinwb.sheetnames:print(ws)#closetheworkbookwb.close()#echo结果如下：1stworksheet:1_sheet2ndworksheet:2_sheet3rdworksheet:3_sheet4thworksheet:3_sheetCopy1_sheet2_sheet3_sheet3_sheetCopy3.3.3Getcell来自openpyxl导入加载的数据_workbookasopen#classinstantiationwb=open('simple_excel.xlsx',read_only=True)#firstworksheetobjectws1=wb[wb.sheetnames[0]]#or#ws1=wb['1_sheet']#获取单元格print(f"Getcelldata:{ws1['A3'].value}")#Selectarangetogetcelldataforrowinws1['A3:H3']:forcellinrow:print(f"Getcelldatabyrange:{cell.value}")#关闭工作簿wb.close()#回显结果如下：Getcelldata:switch-01Getcelldatabyrange:switch-01Getcelldatabyrange:192.168.1.1按范围获取单元数据：cisco按范围获取单元数据：WS-C3560G-24TS按范围获取单元数据：FOC00000000按范围获取单元数据：cisco_ios获取单元数据：12.2(50)SE5按范围获取单元数据:1weeks,1minutes3.3.4Traverserowspecifiedrowfromopenpyxlimportload_workbookasopen#类实例化wb=open('simple_excel.xlsx',read_only=True)#第一个工作表对象ws1=wb[wb.sheetnames[0]]#为ws1['2']中的单元格指定第二行：print(cell.value)#关闭workbookwb.close()#回显结果如下：device_namedevice_ipvendormodelsnosversionupdate_timespecifyingtherowrange#...省略代码...#specifiedtherowrangeforrowinws1['2:3']:forcellinrow:print(cell.value)#...省略代码...#回显结果如下：device_namedevice_ipvendormodelsnosversionupdate_timeswitch-01192.168.1.1ciscoWS-C3560G-24TSFOC00000000cisco_ios12.2(50)SE51周，1分钟方法iter_rows，遍历行fromopenpyxlimportload_workbookasopen#classinstantiationwb=open('simple_excel.xlsx',read_only=True)Aworksheetobjectws1=wb[wb.sheetnames[0]]#遍历行forrowinws1.iter_rows(min_row=2,max_row=2,min_col=1,max_col=8):forcellinrow:print(f"Celldata:{cell.value}")#关闭工作簿wb.close()#回显结果如下：celldata:device_namecelldata:device_ipcelldata:vendorcelldata:modelunitCelldata:sncelldata:oscelldata:versioncelldata:update_time参数说明：方法iter_rows：通过该方法可以遍历每一行数据，是一个元组，再次循环.value可以得到cell数据；3.3.5遍历列指定列fromopenpyxlimportload_workbookasopen#类实例化wb=open('simple_excel.xlsx',read_only=True)#第一个工作表对象ws1=wb[wb.sheetnames[0]]#指定第一个columnforcellinws1['A']:print(cell.value)#关闭工作簿wb.close()#回显结果如下：Example:device_nameswitch-01switch-02switch-03Specifythecolumnrange#...省略代码...#Specifythecolumnrangeforcolinws1['A:B']:forcellincol:print(cell.value)#...省略代码...#回显结果如下如下：示例：device_nameswitch-01switch-02switch-03Nonedevice_ip192.168.1.1192.168.1.2192.168.1.3方法iter_cols，遍历列从openpyxlimportload_workbookasopen#classinstantiationwb=open('simple_excel.xlsx')#firstworksheetObjectws1=wb[wb.sheetnames[0]]#遍历列forcolinws1.iter_cols(min_row=3,max_row=5,min_col=1,max_col=1):forcellincol:print(f"unitCelldata:{cell.value}")#关闭工作簿wb.close()#回显结果如下：celldata:switch-01celldata:switch-02celldata:switch-03参数说明：methoditer_cols:通过这个方法，可以遍历每一列数据，是一个元组，可以再次通过.value获取单元格数据。与iter_rows的另一个区别是load_workbook不能使用read_only=True；附录openpyxl官方文档openpyxlRGB颜色参考RGB颜色参考如果喜欢我的文章，欢迎关注我的公众号：滴滴科技，扫码关注，不定期分享
