10分钟干完2小时的活，用Python自动化办公有多爽？

　　模块的介绍和安装
　　Python专栏中有对所有可以操作Excel的模块进行了对比分析，感兴趣的同学可以前去围观。
　　推荐阅读：Python杀死Excel？众多模块哪家强
　　openpyxl模块是一个可以读取和写入Excel文件的模块，可以处理Excel数据、公式、样式，在表格里面插入图表等；需要单独安装不包含在python标准库里；
　　安装：pipinstallopenpyxlihttps：pypi。tuna。tsinghua。edu。cnsimple
　　在windows系统下使用此条指令进行第三方库的安装省时间，Mac端直接使用pip3install模块名
　　具体openpyxl的官方文档可以参照https：openpyxl。readthedocs。ioenstable
　　Excel表格术语
　　列：column，以字母表示，在表格的上方
　　行：row，以数字表示，但是注意这里是从1开始，在表格的左侧
　　表单：sheet，在表格的下部
　　读数据打开Excel表格并获取表格名称
　　下面读取表格的整体思路就是：读取一个表格获取里面的某个sheet获取sheet中单元格、某行、某列的数据获取指定范围的行列数据案例：查找有空值的单元格
　　loadworkbook（filename表格文件路径）；workbook。sheetnames获取表格文件内的sheet名称
　　注意：只能打开存在的表格，不能用该方法创建一个新表格文件
　　我们通过读取丝芙兰的售卖商品来学习Excel操作，内容如下：
　　fromopenpyxlimportloadworkbookworkbookloadworkbook（filenamecosmetics。xlsx）print（workbook。sheetnames）
　　可以看到有：〔cosmetics，Sheet1，Sheet2〕
　　那么我们就可以通过sheetname获取表格，但是如果Excel中只有一个sheet，那么可以直接使用。activesheetworkbook〔cosmetics〕返回的是workbook对象：Worksheetcosmetics
　　但是我们的workbook是有多个sheet的，active的使用如下：sheetworkbook。active
　　获得Excel里面的sheet之后就可以对表格的数据进行操作。比如：获取表格的尺寸大小；第一行第一列的数据，代码如下：print（sheet。dimensions）获取的表格的范围：A1：K1473cellsheet〔B1〕print（cell。value）获取B1表格的数据，结果：Brand获取某一行或者某一列的内容colcontentsheet〔B〕表示获取B这一列rowcontentsheet〔3〕获取第3行数据print（rowcontent）
　　注意返回的Cell单元格对象（Cellcosmetics。A3，Cellcosmetics。B3，Cellcosmetics。C3，Cellcosmetics。D3，Cellcosmetics。E3，Cellcosmetics。F3，Cellcosmetics。G3，Cellcosmetics。H3，Cellcosmetics。I3，Cellcosmetics。J3，Cellcosmetics。K3）
　　如果想获取每个里面的值，可以结合value属性接上代码forrowinrowcontent：print（row。value）获取多行或者多列内容
　　。iterrows（minrow最低行数，maxrow最高行数，mincol最低列数，maxcol最高列数）按行获取
　　。itercols（minrow最低行数，maxrow最高行数，mincol最低列数，maxcol最高列数）按列获取
　　注意：这两个演示任一个都可以，两个案例的区别就是第一个是每次获取一行内容，第二个是按照列获取内容。
　　每次获取一行数据forrowinsheet。iterrows（minrow2，maxrow5，mincol1，maxcol4）：涵盖范围的所有单元格都会显示print（row）每行的内容是一个元组的内容，如果想看到数据还需要继续遍历forcellinrow：print（cell。value）print（50）
　　结果：
　　这个获取的内容有点类似使用pandas的loc获取第几行几列的情况。
　　同样列的获取也是一样道理，就是每次获取一列数据forcolinsheet。itercols（minrow2，maxrow5，mincol1，maxcol4）：print（col）forcellinrow：print（cell。value）print（50）
　　结果：
　　如果有需要获取表格的行或者列可以使用，sheet。rows或者sheet。columns
　　案例：
　　编写一个python程序，要求：
　　（1）打开文件丝芙兰化妆品表格cosmetics。xlsx
　　（2）找到其中空的单元格
　　（3）输出这些空单元格的坐标（如A1，B5，C6）fromopenpyxlimportloadworkbook1。加载cosmetics。xlsx表格workbookloadworkbook（filenamecosmetics。xlsx）2。得到cosmetics工作簿sheetworkbook〔cosmetics〕3。获取工作簿的范围并切割范围：A1：K1473使用字符串的split分隔得到：〔A1，K1473〕sizelssheet。dimensions。split（：）4。从而可以得到行和列的最大和最小值colmin，rowmin，colmax，rowmaxsizels〔0〕〔0〕，sizels〔0〕〔1〕，sizels〔1〕〔0〕，sizels〔1〕〔1：〕print（colmin，rowmin，colmax，rowmax）打印结果是：A，1，K，1473即最小列是A，最大列是K，最小行是1，最大行是14735。声明一个空的列表存放有空值的单元格坐标nonelist〔〕6。遍历行和列遍历列，但是需要注意的是列是字母，所以要使用ord将字母转成数字才可以使用range范围forcolinrange（ord（colmin），ord（colmax）1）：7。遍历行，将字符串的行转成整型forrowinrange（int（rowmin），int（rowmax）1）：8。通过chr（col）str（row）获取单元格坐标，再通过chr将数字转成字母比如65就是A，所以chr（col）str（row）的结果类似是：A3ifsheet〔chr（col）str（row）〕。valueNone：9。如果某个单元格没有值则将单元格坐标保存到列表：nonelist中nonelist。append（chr（col）str（row））10。打印查看nonelist里面的内容foriinnonelist：print（i）
　　综合对比openpyxl在读取获取数据上没有pandas有优势，但是pandas不能修改表格，但是openpyxl是可以的。
　　写数据
　　向某个格子写入数据并保存
　　sheet〔‘A1’〕‘你好啊’
　　用Python列表插入行数据
　　sheet。append（Python列表）插入的数据会接在表格内已有数据后面
　　复制一个sheet
　　workbook。copyworksheet（sheet实例）
　　案例：fromopenpyxlimportWorkbookfromopenpyxl。utilsimportgetcolumnletterwbWorkbook（）destfilenameworkbook。xlsx激活默认工作薄ws1wb。active设置工作簿ws1。titlenumbers向指定单元格写入内容ws1〔C5〕3。14ws1〔A2〕1。5创建第二个工作薄，名字为ws2wb。createsheet（titlescore）data〔〔张三，100〕，〔李四，98〕，〔王五，83〕，〔赵六，99〕，〕forrowindata：ws2。append（row）复制一个sheetwb。copyworksheet（ws2）最后将内容保存wb。save（filenamedestfilename）使用Excel公式workbookloadworkbook（filenameworkbook。xlsx）sheetworkbook〔score〕sheet〔B5〕AVERAGE（B1：B4）sheet〔B6〕SUM（B1：B4）workbook。save（filenameworkbook。xlsx）
　　如果你对Excel足够熟悉也可以使用过复杂的公式：workbookloadworkbook（filenameworkbook。xlsx）sheetworkbook〔Sheet1〕sheet〔D1〕标准身高Excel中的公式：IF（RIGHT（C2，2）cm，C2，SUBSTITUTE（C2，m，）100cm）foriinrange（2，9）：sheet〔D｛｝。format（i）〕fIF（RIGHT（C｛i｝，2）cm，C｛i｝，SUBSTITUTE（C｛i｝，m，）100cm）workbook。save（filenameworkbook。xlsx）
　　结果：
　　可以使用的公式是（可以通过下列代码获取）：fromopenpyxl。utilsimportFORMULAEprint（FORMULAE）
　　比如说有SUM
　　添加筛选
　　sheet。autofilter。ref：给表格添加筛选器
　　。autofilter。refsheet。dimension给所有字段添加筛选器；
　　。autofilter。refA1给A1这个格子添加筛选器，就是给第一列添加筛选器；冻结窗格
　　sheet。freezepanes‘G2’
　　冻结的结果是，在这个窗格的左上都是不动的，当移动滑块时，变化的只有窗格的右下方数据。也就是当运行此程序代码后，Excel表格里面会在G2窗格（左上方）处出现’十字’坐标线，在第二象限的数据没有办法移动，改变的只有其它象限的数据fromopenpyxlimportloadworkbook1。加载cosmetics。xlsx表格workbookloadworkbook（filenamecosmetics。xlsx）2。得到cosmetics工作簿sheetworkbook〔cosmetics〕3。设置筛选器sheet。autofilter。refA14。冻结C100窗口sheet。freezepanesC1005。保存设置workbook。save（filenamecosmetics。xlsx）
　　当然如果不需要也可以删除行、列、表，分别使用：
　　。remove（sheet名）：删除某个sheet表；
　　。deleterows（idx数字编号，amount要插入的列数）删除行，从idx这一列开始，包括idx这一列
　　。deletecols（idx数字编号，amount要插入的列数）删除列，从idx这一行开始，包括idx这一行
　　也可以添加空行
　　插入多行空行
　　。insertrows（idx数字编号，amount要插入的列数）在idx数字编号的行上边插入几行
　　或者多个空列
　　。insertcols（idx数字编号，amount要插入的列数）在idx数字编号的列左边插入几列案例
　　编写一个Python程序，要求：
　　（1）打开文件丝芙兰化妆品表格cosmetics。xlsx
　　（2）找到Price这一列
　　（3）找到Price中大于100的数据
　　（4）将这些数据所在行复制到一个新的Excel文件中fromopenpyxlimportWorkbookfromopenpyxlimportloadworkbookworkbookloadworkbook（filenamecosmetics。xlsx）sheetworkbook。activeworkbook1Workbook（）sheet1workbook1。activecellssheet〔D〕datalist〔〕forcellincells：ifisinstance（cell。value，int）andcell。value100：datalist。append（cell。row）print（输出满足条件的数据所在行数的列表：｛｝。format（datalist））j1forrowindatalist：forcolinrange（ord（A），ord（G）1）：sheet1〔chr（col）str（j）〕sheet〔chr（col）str（row）〕。valueprint（正在写入第｛｝行数据。format（j），end）j1workbook1。save（cosmeticsother。xlsx）
　　样式
　　修改字体样式
　　Font（name字体名称，size字体大小，bold是否加粗，italic是否斜体，color字体颜色）
　　获取表格中字体的样式
　　cell。font。属性
　　设置对齐样式
　　Alignment（horizontal水平对齐模式，vertical垂直对齐模式，textrotation旋转角度，wraptext是否自动换行）
　　设置边框样式
　　Side（style边线样式，color边线颜色）Border（left左边线样式，right右边线样式，top上边线样式，bottom下边线样式）
　　填充
　　PatternFill（filltype填充样式，fgColor填充颜色）GradientFill（stop（渐变颜色1，渐变颜色2，））
　　设置行高和列宽
　　。rowdimensions〔行编号〕。height行高。columndimensions〔列编号〕。width列宽
　　合并单元格
　　。mergecells（待合并的格子编号）。mergecells（startrow起始行号，startcolumn起始列号，endrow结束行号，endcolumn结束列号）
　　取消合并单元格
　　。unmergecells（待合并的格子编号）。unmergecells（startrow起始行号，startcolumn起始列号，endrow结束行号，endcolumn结束列号）
　　案例综合应用
　　编写一个python程序，要求
　　（1）打开文件丝芙兰化妆品表格cosmetics。xlsx
　　（2）找到Rank在4。5年以上的，Price价格大于100的数据
　　（3）将其他数据删除，最后不要在中间留空行
　　（4）将price数据背景标为红色，字体标为白色
　　（5）保存该Excel文件importosfromopenpyxlimportWorkbookfromopenpyxlimportloadworkbookfromopenpyxl。stylesimportFontfromopenpyxl。stylesimportPatternFillworkbookloadworkbook（filenamecosmetics。xlsx）sheetworkbook。activeworkbook1Workbook（）sheet1workbook1。activedefreturncolorrow（content）：函数功能：根据输入的content，筛选出窗格中含有这个content的所有的col（列）和row（行）返回两个的列表，第一个是所在列的数据，第二个是所在行的数据datasizesheet。dimensionssizelsdatasize。split（：）colmin，rowmin，colmax，rowmaxsizels〔0〕〔0〕，sizels〔0〕〔1〕，sizels〔1〕〔0〕，sizels〔1〕〔1：〕rowls〔〕colls〔〕forcolinrange（ord（colmin），ord（colmax）1）：forrowinrange（int（rowmin），int（rowmax）1）：ifsheet〔chr（col）str（row）〕。valuecontent：colcontentchr（col）rowcontentstr（row）colls。append（colcontent）rowls。append（rowcontent）return（colls，rowls）colbyRankreturncolorrow（Rank）〔0〕〔0〕colPricereturncolorrow（Price）〔0〕〔0〕datacolbyRanksheet〔colbyRank〕datacolPricesheet〔colPrice〕datafinialrow〔〕foriinrange（len（datacolbyRank））：ifdatacolbyRank〔i〕。valueRank：continueifdatacolPrice〔i〕。valuePrice：continueifisinstance（float（datacolbyRank〔i〕。value），float）andfloat（datacolbyRank〔i〕。value）4。5andisinstance（float（datacolPrice〔i〕。value），float）andfloat（datacolPrice〔i〕。value）100：print（Rank的数值为｛｝，对应的价格是｛｝。format（datacolbyRank〔i〕。value，datacolPrice〔i〕。value））datafinialrow。append（datacolbyRank〔i〕。row）print（筛选后满足要求的数据行列表输出为：｛｝。format（datafinialrow））datafinialrow。insert（0，1）这一步的目的是将原来文件的标签写到新文件中去j1forrowindatafinialrow：forcolinrange（ord（A），ord（K）1）：print（sheet〔chr（col）str（row）〕。value）sheet1〔chr（col）str（j）〕sheet〔chr（col）str（row）〕。valueprint（正在写入第｛｝行数据。format（j），end）j1print（数据已全部导入新Excel文件！下面给数据做标记。。。。。。）dataafterPricesheet1〔returncolorrow（Price）〔0〕〔0〕〕forcellindataafterPrice：cell。fillPatternFill（filltypesolid，fgColorFF0000）cell。fontFont（colorFFFFFF）print（数据标记完成）workbook1。save（filename筛选数据后的表格。xlsx）print（completed！）
　　结果：
　　绘制图形fromopenpyxlimportWorkbookfromopenpyxl。chartimportBarChart，Series，Reference，BarChart3DwbWorkbook（）wswb。activerows〔（None，2020，2021），（Apples，6，9），（Oranges，5，2），（Pears，8，3）〕forrowinrows：ws。append（row）dataReference（ws，mincol2，minrow1，maxcol3，maxrow4）titlesReference（ws，mincol1，minrow2，maxrow4）chartBarChart3D（）chart。title3DBarChartchart。adddata（datadata，titlesfromdataTrue）chart。setcategories（titles）ws。addchart（chart，E5）wb。save（bar3d。xlsx）
　　或者fromopenpyxlimportWorkbookfromopenpyxl。chartimportSeries，Reference，BubbleChartwbWorkbook（）wswb。activerows〔（NumberofProducts，SalesinUSD，Marketshare），（14，12200，15），（20，60000，33），（18，24400，10），（22，32000，42），（），（12，8200，18），（15，50000，30），（19，22400，15），（25，25000，50），〕forrowinrows：ws。append（row）chartBubbleChart（）chart。style18useapresetstyleaddthefirstseriesofdataxvaluesReference（ws，mincol1，minrow2，maxrow5）yvaluesReference（ws，mincol2，minrow2，maxrow5）sizeReference（ws，mincol3，minrow2，maxrow5）seriesSeries（valuesyvalues，xvaluesxvalues，zvaluessize，title2013）chart。series。append（series）addthesecondxvaluesReference（ws，mincol1，minrow7，maxrow10）yvaluesReference（ws，mincol2，minrow7，maxrow10）sizeReference（ws，mincol3，minrow7，maxrow10）seriesSeries（valuesyvalues，xvaluesxvalues，zvaluessize，title2014）chart。series。append（series）placethechartstartingincellE1ws。addchart（chart，E1）wb。save（bubble。xlsx）
　　Python全栈人工智能、网络安全等热门学科，正在火热报名中，想从事互联网IT行业，想高薪就业的同学，欢迎咨询免费体验，线上部分学科学费最高减免3000元。
　　以下文章来源于Python专栏，作者宋宋
　　来源：https：mp。weixin。qq。comsKrHejrgOXKIXg9hJ30PhA