用Python教你如何实现办公自动化,玩转Excel

用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,正在表格的下部

10 秒钟干完 2 时辰的活,用 Python 主动化办私有多爽?

读数据

翻开Excel表格并获取表格称号

上面读取表格的全体思绪就是:

  1. 读取一度表格

  2. 获取外面的这个sheet

  3. 获取sheet中单元格、某行、某列的数据

  4. 获取指名范畴的次序数据

  5. 案例:查找有空值的单元格

load_workbook(filename = 表格资料门路);workbook.sheetnames 获取表格资料内的sheet称号

留意:只能翻开具有的表格,没有能用该办法创立一度新表格资料

咱们经过读取丝芙兰的售卖货物来进修Excel操作,形式如次:

10 秒钟干完 2 时辰的活,用 Python 主动化办私有多爽?

from openpyxl import load_workbookworkbook = load_workbook(filename= 'cosmetics.xlsx')print(workbook.sheetnames)

能够看到有:

 ['cosmetics', 'Sheet1', 'Sheet2']

那样咱们就能够经过sheetname获取表格,然而假如Excel中只要一度sheet,那样能够间接运用.active

sheet = workbook['cosmetics']  # 前往的是workbook对于象:<Worksheet "cosmetics">

然而咱们的workbook是有多个sheet的,active的运用如次:

sheet = workbook.active

失掉Excel外面的sheet以后就能够对于表格的数据停止操作。比方:获取表格的分寸大小;第一溜儿第一列的数据,代码如次:

print(sheet.dimensions)  # 获取的表格的范畴:A1:K1473cell = sheet['B1']print(cell.value) # 获取B1表格的数据,后果:Brand

获取某一溜儿或者许某一列的形式

col_content = sheet['B']  # 示意获取B这一列row_content = sheet[3]  #获取第3行数据print(row_content)

留意前往的Cell单元格对于象

(<Cell 'cosmetics'.A3>, <Cell 'cosmetics'.B3>, <Cell 'cosmetics'.C3>, <Cell 'cosmetics'.D3>, <Cell 'cosmetics'.E3>, <Cell 'cosmetics'.F3>, <Cell 'cosmetics'.G3>, <Cell 'cosmetics'.H3>, <Cell 'cosmetics'.I3>, <Cell 'cosmetics'.J3>, <Cell 'cosmetics'.K3>)

假如想获取每个外面的值,能够联合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)

后果:

10 秒钟干完 2 时辰的活,用 Python 主动化办私有多爽?

某个获取的形式有点相似运用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)

后果:

10 秒钟干完 2 时辰的活,用 Python 主动化办私有多爽?

假如有需求获取表格的行或者许列能够运用,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 Workbookfrom openpyxl.utils import get_column_letterwb = Workbook()dest_filename = 'workbook.xlsx'# 激活默许任务薄ws1 = wb.active# 安装任务簿ws1.title = "numbers"# 向指存单元格写入形式ws1['C5'] = 3.14ws1['A2'] = 1.5# 创立第二个任务薄,名字为ws2 = wb.create_sheet(title="score")data = [    ['张三',100],    ['李四',98],    ['王五',83],    ['赵六',99],]for row in data: ws2.append(row)# 复制一度sheetwb.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')

后果:

10 秒钟干完 2 时辰的活,用 Python 主动化办私有多爽?

能够运用的公式是(能够经过下列代码获取):

from openpyxl.utils import FORMULAE print(FORMULAE)

比方说有SUM

10 秒钟干完 2 时辰的活,用 Python 主动化办私有多爽?

增添挑选

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 Workbookfrom openpyxl import load_workbookworkbook = load_workbook(filename = 'cosmetics.xlsx')sheet = workbook.activeworkbook_1 = Workbook()sheet_1 = workbook_1.activecells = sheet['D']data_list = []for cell in cells:    if isinstance(cell.value,int) and cell.value >100:        data_list.append(cell.row)print('输入满意环境的数据所外行数的列表:\n{}\n'.format(data_list))j = 1for 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 += 1workbook_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 osfrom openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.styles import Fontfrom openpyxl.styles import PatternFillworkbook = load_workbook(filename = 'cosmetics.xlsx')sheet = workbook.activeworkbook_1 = Workbook()sheet_1 = workbook_1.activedef 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('\n挑选后满意请求的数据次序表输入为:{}\n'.format(data_finial_row))data_finial_row.insert(0,1)#这一步的手段是将本来资料的标签写到新资料中去j = 1for 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 += 1print('\n\n数据已全副导出新Excel资料!上面给数据做标志......\n')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('\ncompleted!') 

后果:

10 秒钟干完 2 时辰的活,用 Python 主动化办私有多爽?

绘绘制形

from openpyxl import Workbookfrom 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 Workbookfrom openpyxl.chart import Series, Reference, BubbleChartwb = Workbook()ws = wb.activerows = [    ("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 dataxvalues = 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 secondxvalues = 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 E1ws.add_chart(chart, "E1")wb.save("bubble.xlsx")

10 秒钟干完 2 时辰的活,用 Python 主动化办私有多爽?

**本论坛部分作品是由网友自主投稿和发布、编辑整理上传,对此类作品本论坛仅供提供学习交流和参考,禁止用户用于商业行为,并请于下载后24小时内删除,若喜欢该作品请联系原作者购买正版。如果您发现论坛上有侵犯您的知识产权的作品,请与我们取得联系,我们会及时修改或删除。
158自学网 » 用Python教你如何实现办公自动化,玩转Excel
关于售后:
(1)、因部分资料含有敏感关键词,百度网盘无法分享链接,请联系客服进行发送;
(2)、所有资料在您未收到之前,都可以联系微信/QQ:406499404,无条件退款
(3)仅支持原渠道退回,微信支付,支付宝退回至您当初选择的付款方式
(4)不用担心不给资料,如果没有及时回复也不用担心,看到了都会发给您的,请放心!
(5)因部份资源来源互联网,本站不担保其完整性,请知悉!

发表评论

Hi, 如果你对本资源有疑问,可以跟我联系哦!

联系作者

提供最优质的资源集合

立即查看 了解详情
赞助VIP 享更多特权,建议使用 QQ 登录
喜欢我嘛?喜欢就按“ctrl+D”收藏我吧!♡