10分钟干完2小时的活,用Python自动化办公有多爽?
模块的介绍和安装
Python专栏中有对所有可以操作Excel的模块进行了对比分析,感兴趣的同学可以前去围观。
推荐阅读:Python杀死Excel?众多模块哪家强
openpyxl模块是一个可以读取和写入Excel文件的模块,可以处理Excel数据、公式、样式,在表格里面插入图表等;需要单独安装不包含在python标准库里;
安装:pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple
在windows系统下使用此条指令进行第三方库的安装省时间,Mac端直接使用pip3 install 模块名
具体openpyxl的官方文档可以参照https://openpyxl.readthedocs.io/en/stable/
Excel表格术语
列:column,以字母表示,在表格的上方
行:row,以数字表示,但是注意这里是从1开始,在表格的左侧
表单:sheet,在表格的下部
读数据 打开Excel表格并获取表格名称
下面读取表格的 整体思路就是: 读取一个表格 获取里面的某个sheet 获取sheet中单元格、某行、某列的数据 获取指定范围的行列数据 案例:查找有空值的单元格
load_workbook(filename = 表格文件路径);workbook.sheetnames 获取表格文件内的sheet名称
注意:只能打开存在的表格,不能用该方法创建一个新表格文件
我们通过读取丝芙兰的售卖商品来学习Excel操作,内容如下:
from openpyxl import load_workbook workbook = load_workbook(filename= "cosmetics.xlsx") print(workbook.sheetnames)
可以看到有: ["cosmetics", "Sheet1", "Sheet2"]
那么我们就可以通过sheetname获取表格,但是如果Excel中只有一个sheet,那么可以直接使用.active sheet = workbook["cosmetics"] # 返回的是workbook对象:
但是我们的workbook是有多个sheet的,active的使用如下: sheet = workbook.active
获得Excel里面的sheet之后就可以对表格的数据进行操作。比如:获取表格的尺寸大小;第一行第一列的数据,代码如下: print(sheet.dimensions) # 获取的表格的范围:A1:K1473 cell = sheet["B1"] print(cell.value) # 获取B1表格的数据,结果:Brand 获取某一行或者某一列的内容col_content = sheet["B"] # 表示获取B这一列 row_content = sheet[3] #获取第3行数据 print(row_content)
注意返回的Cell单元格对象 (, , , , , , , , , , )
如果想获取每个里面的值,可以结合value属性 # 接上代码 for row in row_content: print(row.value) 获取多行或者多列内容
.iter_rows(min_row = 最低行数,max_row = 最高行数,min_col = 最低列数,max_col = 最高列数) 按行获取
.iter_cols(min_row = 最低行数,max_row = 最高行数,min_col = 最低列数,max_col = 最高列数) 按列获取
注意:这两个演示任一个都可以,两个案例的区别就是第一个是每次获取一行内容,第二个是按照列获取内容。
每次获取一行数据 for row in sheet.iter_rows(min_row=2, max_row=5, min_col= 1,max_col=4): # 涵盖范围的所有单元格都会显示 # print(row) # 每行的内容是一个元组的内容,如果想看到数据还需要继续遍历 for cell in row: print(cell.value) print("-"*50)
结果:
这个获取的内容有点类似使用pandas的loc获取第几行几列的情况。
同样列的获取也是一样道理,就是每次获取一列数据 for col in sheet.iter_cols(min_row=2, max_row=5, min_col= 1,max_col=4): print(col) for cell in row: print(cell.value) print("-"*50)
结果:
如果有需要获取表格的行或者列可以使用,sheet.rows或者sheet.columns
案例:
编写一个python程序,要求:
(1)打开文件丝芙兰化妆品表格cosmetics.xlsx
(2)找到其中空的单元格
(3)输出这些空单元格的坐标(如A1,B5,C6) from openpyxl import load_workbook # 1. 加载cosmetics.xlsx表格 workbook = load_workbook(filename= "cosmetics.xlsx") # 2. 得到cosmetics工作簿 sheet = workbook["cosmetics"] # 3. 获取工作簿的范围并切割 范围:"A1:K1473" ---->使用字符串的split分隔得到:["A1","K1473"] size_ls=sheet.dimensions.split(":") # 4. 从而可以得到行和列的最大和最小值 col_min,row_min,col_max,row_max = size_ls[0][0],size_ls[0][1],size_ls[1][0],size_ls[1][1:] # print(col_min,row_min,col_max,row_max) # 打印结果是:A,1,K,1473 即最小列是A,最大列是K,最小行是1,最大行是1473 # 5. 声明一个空的列表存放有空值的单元格坐标 none_list = [] # 6. 遍历行和列 # 遍历列,但是需要注意的是列是字母,所以要使用ord将字母转成数字才可以使用range范围 for col in range(ord(col_min),ord(col_max)+1): # 7. 遍历行,将字符串的行转成整型 for row in range(int(row_min), int(row_max)+1): # 8. 通过chr(col)+str(row)获取单元格坐标,再通过chr将数字转成字母比如65就是A,所以chr(col)+str(row)的结果类似是:A3 if sheet[chr(col)+str(row)].value == None: # 9. 如果某个单元格没有值则将单元格坐标保存到列表:none_list中 none_list.append(chr(col)+str(row)) # 10. 打印查看none_list里面的内容 for i in none_list: print(i)
综合对比openpyxl在读取获取数据上没有pandas有优势,但是pandas不能修改表格,但是openpyxl是可以的。
写数据
向某个格子写入数据并保存
sheet[‘A1’] = ‘你好啊’
用Python列表插入行数据
sheet.append(Python列表) 插入的数据会接在表格内已有数据后面
复制一个sheet
workbook.copy_worksheet(sheet实例)
案例 : from openpyxl import Workbook from openpyxl.utils import get_column_letter wb = Workbook() dest_filename = "workbook.xlsx" # 激活默认工作薄 ws1 = wb.active # 设置工作簿 ws1.title = "numbers" # 向指定单元格写入内容 ws1["C5"] = 3.14 ws1["A2"] = 1.5 # 创建第二个工作薄,名字为 ws2 = wb.create_sheet(title="score") data = [ ["张三",100], ["李四",98], ["王五",83], ["赵六",99], ] for row in data: ws2.append(row) # 复制一个sheet wb.copy_worksheet(ws2) # 最后将内容保存 wb.save(filename = dest_filename)使用Excel公式workbook = load_workbook(filename= "workbook.xlsx") sheet = workbook["score"] sheet["B5"] = "=AVERAGE(B1:B4)" sheet["B6"] = "=SUM(B1:B4)" workbook.save(filename="workbook.xlsx")
如果你对Excel足够熟悉也可以使用过复杂的公式: workbook = load_workbook(filename= "workbook.xlsx") sheet = workbook["Sheet1"] sheet["D1"] = "标准身高" # Excel中的公式: =IF(RIGHT(C2,2)="cm",C2,SUBSTITUTE(C2,"m","")*100&"cm") for i in range(2,9): sheet["D{}".format(i)] = f"=IF(RIGHT(C{i},2)="cm",C{i},SUBSTITUTE(C{i},"m","")*100&"cm")" workbook.save(filename="workbook.xlsx")
结果:
可以使用的公式是(可以通过下列代码获取): from openpyxl.utils import FORMULAE print(FORMULAE)
比如说有SUM
添加筛选
sheet.auto_filter.ref:给表格添加"筛选器"
.auto_filter.ref = sheet.dimension 给所有字段添加筛选器;
.auto_filter.ref = "A1" 给A1这个格子添加"筛选器",就是给第一列添加"筛选器"; 冻结窗格
sheet.freeze_panes = ‘G2’
冻结的结果是,在这个窗格的左上都是不动的,当移动滑块时,变化的只有窗格的右下方数据。也就是当运行此程序代码后,Excel表格里面会在G2窗格(左上方)处出现’十字’坐标线,在第二象限的数据没有办法移动,改变的只有其它象限的数据 from openpyxl import load_workbook # 1. 加载cosmetics.xlsx表格 workbook = load_workbook(filename= "cosmetics.xlsx") # 2. 得到cosmetics工作簿 sheet = workbook["cosmetics"] # 3. 设置筛选器 sheet.auto_filter.ref = "A1" # 4. 冻结C100窗口 sheet.freeze_panes ="C100" # 5. 保存设置 workbook.save(filename = "cosmetics.xlsx")
当然如果不需要也可以删除行、列、表,分别使用:
.remove("sheet名"):删除某个sheet表;
.delete_rows(idx = 数字编号,amount = 要插入的列数) 删除行, 从idx这一列开始,包括idx这一列
.delete_cols(idx = 数字编号,amount = 要插入的列数) 删除列, 从idx这一行开始,包括idx这一行
也可以添加空行
插入多行空行
.insert_rows(idx = 数字编号,amount = 要插入的列数) 在idx数字编号的行上边插入几行
或者多个空列
.insert_cols(idx = 数字编号,amount = 要插入的列数) 在idx数字编号的列左边插入几列 案例
编写一个Python程序,要求:
(1)打开文件丝芙兰化妆品表格cosmetics.xlsx
(2)找到Price这一列
(3)找到Price中大于100的数据
(4)将这些数据所在行复制到一个新的Excel文件中 from openpyxl import Workbook from openpyxl import load_workbook workbook = load_workbook(filename = "cosmetics.xlsx") sheet = workbook.active workbook_1 = Workbook() sheet_1 = workbook_1.active cells = sheet["D"] data_list = [] for cell in cells: if isinstance(cell.value,int) and cell.value >100: data_list.append(cell.row) print("输出满足条件的数据所在行数的列表: {} ".format(data_list)) j = 1 for row in data_list: for col in range(ord("A"),ord("G")+1): sheet_1[chr(col)+str(j)] = sheet[chr(col)+str(row)].value print("正在写入第{}行数据".format(j),end = " ") j += 1 workbook_1.save("cosmetics_other.xlsx")
样式
修改字体样式
Font(name=字体名称,size=字体大小, bold=是否加粗,italic=是否斜体,color=字体颜色)
获取表格中字体的样式
cell.font.属性
设置对齐样式
Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
设置边框样式
Side(style=边线样式,color=边线颜色) Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)
填充
PatternFill(fill_type=填充样式, fgColor=填充颜色) GradientFill(stop=(渐变颜色1,渐变颜色2,…))
设置行高和列宽
.row_dimensions[行编号].height = 行高 .column_dimensions[列编号].width = 列宽
合并单元格
.merge_cells(待合并的格子编号) .merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
取消合并单元格
.unmerge_cells(待合并的格子编号) .unmerge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
案例综合应用
编写一个python程序,要求
(1)打开文件丝芙兰化妆品表格cosmetics.xlsx
(2)找到Rank在4.5年以上的,Price价格大于100的数据
(3)将其他数据删除,最后不要在中间留空行
(4)将price数据背景标为红色,字体标为白色
(5)保存该Excel文件 import os from openpyxl import Workbook from openpyxl import load_workbook from openpyxl.styles import Font from openpyxl.styles import PatternFill workbook = load_workbook(filename = "cosmetics.xlsx") sheet = workbook.active workbook_1 = Workbook() sheet_1 = workbook_1.active def return_col_or_row(content): """函数功能:根据输入的content, 筛选出窗格中含有这个content的所有的col(列)和row(行) 返回两个的列表,第一个是所在列的数据,第二个是所在行的数据 """ data_size = sheet.dimensions size_ls = data_size.split(":") col_min,row_min,col_max,row_max = size_ls[0][0],size_ls[0][1],size_ls[1][0],size_ls[1][1:] row_ls = [] col_ls = [] for col in range(ord(col_min),ord(col_max)+1): for row in range(int(row_min), int(row_max)+1): if sheet[chr(col)+str(row)].value == content: col_content = chr(col) row_content = str(row) col_ls.append(col_content) row_ls.append(row_content) return(col_ls,row_ls) col_by_Rank = return_col_or_row("Rank")[0][0] col_Price = return_col_or_row("Price")[0][0] data_col_by_Rank = sheet[col_by_Rank] data_col_Price = sheet[col_Price] data_finial_row = [] for i in range(len(data_col_by_Rank)): if data_col_by_Rank[i].value=="Rank": continue if data_col_Price[i].value=="Price": continue if isinstance(float(data_col_by_Rank[i].value), float) and float(data_col_by_Rank[i].value) >4.5 and isinstance(float(data_col_Price[i].value), float) and float(data_col_Price[i].value)>100: print("Rank的数值为{},对应的价格是{}".format(data_col_by_Rank[i].value,data_col_Price[i].value)) data_finial_row.append(data_col_by_Rank[i].row) print(" 筛选后满足要求的数据行列表输出为:{} ".format(data_finial_row)) data_finial_row.insert(0,1) #这一步的目的是将原来文件的标签写到新文件中去 j = 1 for row in data_finial_row: for col in range(ord("A"),ord("K")+1): #print(sheet[chr(col)+str(row)].value) sheet_1[chr(col)+str(j)] = sheet[chr(col)+str(row)].value print("正在写入第{}行数据".format(j),end = " ") j += 1 print(" 数据已全部导入新Excel文件!下面给数据做标记...... ") data_after_Price = sheet_1[return_col_or_row("Price")[0][0]] for cell in data_after_Price: cell.fill = PatternFill(fill_type="solid",fgColor="FF0000") cell.font = Font(color="FFFFFF") print("数据标记完成") workbook_1.save(filename="筛选数据后的表格.xlsx") print(" completed!")
结果:
绘制图形 from openpyxl import Workbook from openpyxl.chart import BarChart, Series, Reference,BarChart3D wb = Workbook() ws = wb.active rows = [ (None, 2020, 2021), ("Apples", 6, 9), ("Oranges", 5, 2), ("Pears", 8, 3) ] for row in rows: ws.append(row) data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4) titles = Reference(ws, min_col=1, min_row=2, max_row=4) chart = BarChart3D() chart.title = "3D Bar Chart" chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) ws.add_chart(chart, "E5") wb.save("bar3d.xlsx")
或者 from openpyxl import Workbook from openpyxl.chart import Series, Reference, BubbleChart wb = Workbook() ws = wb.active rows = [ ("Number of Products", "Sales in USD", "Market share"), (14, 12200, 15), (20, 60000, 33), (18, 24400, 10), (22, 32000, 42), (), (12, 8200, 18), (15, 50000, 30), (19, 22400, 15), (25, 25000, 50), ] for row in rows: ws.append(row) chart = BubbleChart() chart.style = 18 # use a preset style # add the first series of data xvalues = Reference(ws, min_col=1, min_row=2, max_row=5) yvalues = Reference(ws, min_col=2, min_row=2, max_row=5) size = Reference(ws, min_col=3, min_row=2, max_row=5) series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013") chart.series.append(series) # add the second xvalues = Reference(ws, min_col=1, min_row=7, max_row=10) yvalues = Reference(ws, min_col=2, min_row=7, max_row=10) size = Reference(ws, min_col=3, min_row=7, max_row=10) series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014") chart.series.append(series) # place the chart starting in cell E1 ws.add_chart(chart, "E1") wb.save("bubble.xlsx")
Python全栈+人工智能、网络安全等热门学科,正在火热报名中,想从事互联网IT行业,想高薪就业的同学,欢迎咨询免费体验~, 线上部分学科学费最高减免3000元。
以下文章来源于Python专栏 ,作者宋宋
来源:https://mp.weixin.qq.com/s/K_rHejrgOXKIXg9hJ30PhA | | | | | | | | | | |
年中盘点小间距LED大屏指挥中心应用经典案例文章转载自数字音视工程网现如今,是信息化高速发展的时代。政府部门各个企业都在加速自身信息化建设的步伐。在信息化基础建设中,指挥中心是的重要组成部分,必须具备集中数据采集数据分析策略
涨价后遗症原材料普涨致LED显示应用行业陷入内卷?文章来源自屏显世界从去年以来,国际大宗商品开启疯涨模式,带动大部分原材料价格纷纷走高,某些关键原材料更是一天一个价,甚至出现断货现象。加之人民币升值国际货运成本提升,导致企业运营成
LED产业回暖利亚德产品提价MicroLED如期扩产作为LED显示上市公司,利亚德(300296)预计上半年业绩向好,公司高管日前在接受机构调研中指出,公司已经对LED显示产品提价315,另外,MicroLED直显产品目前如期扩产。
资讯丨大尺寸MicroLED挺进家用总投资267亿显示产业项目签约大尺寸MicroLED挺进家用!LED显示第三次破局前行如果说小间距LED显示屏的发明是产业的应用创新,那么MicroLED的出现,是产业的革命性突破,它通吃大中小尺寸,没有应用边
捐款100亿!曹德旺突然宣布,整个福建都沸腾了一hr一百年,一甲子,一个世纪,一个轮回。福建人的爱国之心,一代传一代,永远流传!昨天!福建人曹德旺,正式宣布!出资100亿,建立一所公办大学!主要用于专门研发攻克理工科,以及国家
LED显示能否霸屏百吋以上市场?在显示技术领域,小间距LED近年来无论在技术创新还是市场开拓方面,无疑都是速度最快跨度最大的技术种类。特别是伴随超微间距技术的深入,LED显示开始逐步从专业显示领域迈向更加广阔的天
太突然!马斯克跑了一hr一石激起千层浪。马斯克,突然从比特币跑了!5月13日凌晨,特斯拉CEO马斯克突然在社交媒体表示,因比特币挖矿对化石燃料使用量增加的担心,公司已暂停接受比特币购买其车辆。马斯克
MiniMicroLED快步前进芯片封装显示屏集中披露最新进度虽然MiniMicroLED市场正在发展之中,但增速惊人,成长很快,各产品表现亮眼。包括产业链上游的芯片企业华灿光电,中游封装模组厂隆利科技等,下游显示屏面板厂利亚德洲明京东方等,
对比度,为什么不同LED显示屏之间差异那么大?众所周知,对于LED显示屏而言,对比度是重要参数之一。对比度越大,图像越清晰,色彩越艳丽。市面上LED显示屏的对比度差异巨大,数值上动辄就差了好几个位数。市面上某SMDLED显示屏
C罗闪耀欧洲杯,LED显示屏助力精彩赛事欧洲杯F组第3轮,葡萄牙对阵法国。比赛过后,C罗在欧洲杯的总进球数达到了13个,成功超越了普拉蒂尼成为了新的欧洲杯历史射手王。本次欧洲杯也是C罗参加的第五届欧洲杯,因此他也成为了历
惨烈!又一大佬,坠落了一hrToshiba,Toshiba,新时代的东芝!多么熟悉的声音,走过了多少的风和雨!然而,这句广告语的标的物东芝公司,却已是物是人非!今天,一个重大消息传来东芝将被英国私募企业