Python自动化办公- 从 Excel 创建、数据操作到存储!

简介: 引入一个例子从 创建 Workbook 到存储到本地磁盘,涉及到的操作命令相对会更全面一点,内容概括如下:新建 Workbook,并保存;新建 sheet ,修改标题、tabColor;对 sheet 中的单元格进行赋值;删除 sheet 中指定行、列(可多行、多列);插入 sheet 中指定行、列(可多行、多列);sheet 中指定列添加筛选机制;合并单元行;设置单元格字体样式、填充样式、对齐样式;应用 Excel 表格中函数命令;

引入一个例子从 创建 Workbook 到存储到本地磁盘,涉及到的操作命令相对会更全面一点,内容概括如下:

  • 新建 Workbook,并保存;
  • 新建 sheet ,修改标题、tabColor;
  • 对 sheet 中的单元格进行赋值;
  • 删除 sheet 中指定行、列(可多行、多列);
  • 插入 sheet 中指定行、列(可多行、多列);
  • sheet 中指定列添加筛选机制;
  • 合并单元行;
  • 设置单元格字体样式、填充样式、对齐样式;
  • 应用 Excel 表格中函数命令;


1,创建一个新的 Excel 文件(Workbook)


from openpyxl import Workbook
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = "Empty_book.xlsx"
wb.save(dest_filename)


微信图片_20220520104430.jpg

2,更改 sheet 标题

ws1 = wb.active
ws1.title = 'range names'

微信图片_20220520104439.jpg

3,在第一个 sheet 40*600 个单元格中填充数据 **

for row in range(1,40):
    ws1.append(range(600))

微信图片_20220520104512.jpg


4,应用求和函数公式

openpyxl 还有一个强大的功能,可以使用 Excel 表格内置函数;函数语法与 Excel 中的基本相同,使用之前需要在 导入模块加入一行代码导入 FORMULAE 模块

from openpyxl.utils import FORMULAE

利用 SUM 函数求取每行各列值之和,并创建新的一列进行存储

for row in range(1,40):
    ws1["WC{}".format(row)] = '=SUM(A{}:WB{})'.format(row,row)


微信图片_20220520104516.jpg

5,表格首行加入筛选机制

在加入筛选机制之前,需要特别注意下先指定单元格范围,用到 auto_filter 模块中的 ref 参数

ws1.auto_filter.ref = "A1:WC39"

再对指定要添加筛选的列进行修改,这里我对表格中每一个列都加入了 筛选模块;

for row in range(600):
    ws1.auto_filter.add_filter_column(row,vals = ['1','25'])#对每一列加入过滤机制


add_filter_column()函数中 row 用于指定要添加的列索引,例如 0就是第一列,后面 vals 暂时还没发现有用处,

微信图片_20220520104520.jpg


另外这里吐槽一下,似乎 openpyxl 包中的 add_sort_condition 模块似乎没有什么用,暂不清楚自己是代码写错了还是什么原因,觉得就是个鸡肋,这里我加入了 排序功能,但代码运行完表格处理完之后还是处于无序状态

ws1.auto_filter.ref = "A1:WC39"
for row in range(600):
    ws1.auto_filter.add_filter_column(row,vals = ['1','25'])#对每一列加入过滤机制
for i in range(2,40):
    ws1['A{}'.format(i)] = random.choice(range(50))# 更改第一列数据
ws1.auto_filter.add_sort_condition("WC2:WC35") # 对Excel 第一列进行排序


微信图片_20220520104524.jpg


6,插入行、列

插入行方法

  • insert_rows(idx,amount),
  • 在指定行 idx 前面插入,amount 表示要插入 的行数,默认为1;

插入列方法

  • insert_cols(idx,amount)
  • 在指定列前面插入,amount 表示要插入的列数,默认为1;
# 插入行和列
ws1.insert_rows(2,amount = 2) #在第二行前面加入两空行
ws1.insert_cols(2,amount = 3) # 在第二列前面加入三个空列

微信图片_20220520104528.jpg


7,删除行、列(可多行、可单行)

删除列方法

  • delete_cols(idx,amount)
  • 删除以列索引为 idx 为起始,amount 指示删除列的数量;
  • delete_rows(idx,amount)
  • 删除以行索引为 idx 为起始,amount 表示删除行的数量;
# 删除行与列
ws1.delete_cols(2,amount = 3) # 删除 第 2 列为起始索引,共3列
ws1.delete_rows(4,amount = 5) # 删除 第 4 行为起始索引,共5行


微信图片_20220520104531.jpg


8,合并行

  • merge_cells(range_string,start_row,start_column,end_row,end_column)
  • range_string,表示单元格合并单元格范围,例如 "A2:F3",合并时只需设置这个参数即可;
  • start_row,起始行,例如 start_row = 2;
  • end_row,中止行,例end_row = 3;
  • start_column ,起始列,start_column = 1;
  • end_column,终止列,end_column = 5;
ws1.merge_cells(range_string = "A2:F3") # 合并多个单元格,A2-F3
# ws1.merge_cells(start_row=2, start_column=1, end_row=3, end_column=5) # 与上面效果等价;

微信图片_20220520104534.jpg


9,设置单元格样式

字体样式设置,这里需用到 openpyxl.styles.fonts 模块中的 Font 函数,

9.1,字体样式设置,用到 Font()函数模块

应用字体样式时,把设置好的 Font() 样式赋值为 单元格.font 属性即可,如下

from openpyxl.styles import colors, Font, Fill, NamedStyle
from openpyxl.styles import PatternFill, Border, Side, Alignment
ws1['A2'].font = Font(bold = True,color ='FFB6C1',sz = 15.0,i = True,outline = True)

微信图片_20220520104534.jpg


9.2, 设置对齐方式,需用到 openpyxl.styles.alignment 模块中的 Alignment 函数

应用时与字体方式相似,创建 Alignment 方法之后,赋值给 单元格.alignment 即可,格式如下

ws1['A2'].alignment = Alignment(horizontal = 'centerContinuous',shrinkToFit = True,textRotation = 20,vertical = 'bottom',wrapText = True)

微信图片_20220520104537.jpg

9.3,设置填充样式 ,用到 openpyxl.styles.fills 模块提供两种常用的填充方法, GrdaientFill -> 渐变填充 和  PatternFill -> 模式填充;

应用时,创建一个 GradientFill() 或 PatternFill() 方法,参数设置完之后,赋值给 单元格.fill 即可

ws1['A2'].fill = PatternFill(bgColor ='DC143C',fill_type = 'solid')

微信图片_20220520104542.jpg

**10,创建新的 sheet **

**10.1,**新建一个 sheet,修改标题 、指定单元格、 tabColor;

ws2 = wb.create_sheet(title = "Pi")
ws2['F5'] = 3.14 # 
ws2.sheet_properties.tabColor = "FF0000" # 修改颜色

微信图片_20220520104545.jpg

10.2, 新建 sheet ,对 10-20行,27-54列区域的单元格赋值(列名即可),

微信图片_20220520104548.jpg

ws3 = wb.create_sheet(title = "Data")
for row in range(10,20):
    for col in range(27,54):
        _ = ws3.cell(column = col,row =row,value = "{0}".format(get_column_letter(col)))


11,Excel 保存

最后,把修改之后的 Excel 表格存储在指定位置

wb.save(filename = dest_filename)

案例中的完整代码贴在下方,需要的小伙伴们自取

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils import FORMULAE
import random
wb =  Workbook()
dest_filename = "Empty_book.xlsx"
ws1 = wb.active
ws1.title = 'range names'
# 对第一个 sheet 操作
for row in range(1,40):
    ws1.append(range(600))
for row in range(1,40):
    ws1["WC{}".format(row)] = '=SUM(A{}:WB{})'.format(row,row)
ws1.auto_filter.ref = "A1:WC39"
for row in range(600):
    ws1.auto_filter.add_filter_column(row,vals = ['1','25'])#对每一列加入过滤机制
for i in range(2,40):
    ws1['A{}'.format(i)] = random.choice(range(50))# 更改第一列数据
ws1.auto_filter.add_sort_condition("WC2:WC35") # 对Excel 第一列进行排序
# 插入行和列
ws1.insert_rows(2,amount = 2) #在第二行前面加入两空行
ws1.insert_cols(2,amount = 3) # 在第二列前面加入三个空列
# 删除行与列
ws1.delete_cols(2,amount = 3) # 删除 第 2 列,3列
ws1.delete_rows(4,amount = 5) # 删除 第 4 行,5行
# 合并单元格
ws1.merge_cells(range_string = "A2:F3") # 合并多个单元格,A2-F3
# ws1.merge_cells(start_row=2, start_column=1, end_row=3, end_column=5) # 与上面效果等价;
from openpyxl.styles import colors, Font, Fill, NamedStyle
from openpyxl.styles import PatternFill, Border, Side, Alignment
ws1['A2'] = '合并后标题'
ws1['A2'].font = Font(bold = True,color ='FFB6C1',sz = 15.0,i = True,outline = True)
# 单元格对其设置
ws1['A2'].alignment = Alignment(horizontal = 'centerContinuous',shrinkToFit = True,textRotation = 20,vertical = 'bottom',wrapText = True)
# 单元格填充颜色设置
ws1['A2'].fill = PatternFill(bgColor ='DC143C',fill_type = 'solid')
# 对第二个 sheet 操作
ws2 = wb.create_sheet(title = "Pi")
ws2['F5'] = 3.14 # 
ws2.sheet_properties.tabColor = "FF0000" # 修改颜色
# 对第三个 sheet 操作
ws3 = wb.create_sheet(title = "Data")
for row in range(10,20):
    for col in range(27,54):
        _ = ws3.cell(column = col,row =row,value = "{0}".format(get_column_letter(col)))
# 保存Excel 表格
wb.save(dest_filename)

好了,以上就是本篇文章的所有内容啦,最后感谢大家的阅读!


相关文章
|
6天前
|
数据采集 数据安全/隐私保护 Python
从零开始:用Python爬取网站的汽车品牌和价格数据
在现代化办公室中,工程师小李和产品经理小张讨论如何获取懂车帝网站的汽车品牌和价格数据。小李提出使用Python编写爬虫,并通过亿牛云爬虫代理避免被封禁。代码实现包括设置代理、请求头、解析网页内容、多线程爬取等步骤,确保高效且稳定地抓取数据。小张表示理解并准备按照指导操作。
从零开始:用Python爬取网站的汽车品牌和价格数据
|
1天前
|
算法 Serverless 数据处理
从集思录可转债数据探秘:Python与C++实现的移动平均算法应用
本文探讨了如何利用移动平均算法分析集思录提供的可转债数据,帮助投资者把握价格趋势。通过Python和C++两种编程语言实现简单移动平均(SMA),展示了数据处理的具体方法。Python代码借助`pandas`库轻松计算5日SMA,而C++代码则通过高效的数据处理展示了SMA的计算过程。集思录平台提供了详尽且及时的可转债数据,助力投资者结合算法与社区讨论,做出更明智的投资决策。掌握这些工具和技术,有助于在复杂多变的金融市场中挖掘更多价值。
22 12
|
4天前
|
存储 数据采集 数据库
Python爬虫实战:股票分时数据抓取与存储
Python爬虫实战:股票分时数据抓取与存储
|
1月前
|
数据采集 Web App开发 数据可视化
Python用代理IP获取抖音电商达人主播数据
在当今数字化时代,电商直播成为重要的销售模式,抖音电商汇聚了众多达人主播。了解这些主播的数据对于品牌和商家至关重要。然而,直接从平台获取数据并非易事。本文介绍如何使用Python和代理IP高效抓取抖音电商达人主播的关键数据,包括主播昵称、ID、直播间链接、观看人数、点赞数和商品列表等。通过环境准备、代码实战及数据处理与可视化,最终实现定时任务自动化抓取,为企业决策提供有力支持。
|
2月前
|
存储 人工智能 人机交互
PC Agent:开源 AI 电脑智能体,自动收集人机交互数据,模拟认知过程实现办公自动化
PC Agent 是上海交通大学与 GAIR 实验室联合推出的智能 AI 系统,能够模拟人类认知过程,自动化执行复杂的数字任务,如组织研究材料、起草报告等,展现了卓越的数据效率和实际应用潜力。
223 1
PC Agent:开源 AI 电脑智能体,自动收集人机交互数据,模拟认知过程实现办公自动化
|
1月前
|
数据可视化 数据挖掘 大数据
1.1 学习Python操作Excel的必要性
学习Python操作Excel在当今数据驱动的商业环境中至关重要。Python能处理大规模数据集,突破Excel行数限制;提供丰富的库实现复杂数据分析和自动化任务,显著提高效率。掌握这项技能不仅能提升个人能力,还能为企业带来价值,减少人为错误,提高决策效率。推荐从基础语法、Excel操作库开始学习,逐步进阶到数据可视化和自动化报表系统。通过实际项目巩固知识,关注新技术,为职业发展奠定坚实基础。
|
2月前
|
数据采集 Web App开发 监控
Python爬虫:爱奇艺榜单数据的实时监控
Python爬虫:爱奇艺榜单数据的实时监控
|
2月前
|
数据采集 存储 XML
python实战——使用代理IP批量获取手机类电商数据
本文介绍了如何使用代理IP批量获取华为荣耀Magic7 Pro手机在电商网站的商品数据,包括名称、价格、销量和用户评价等。通过Python实现自动化采集,并存储到本地文件中。使用青果网络的代理IP服务,可以提高数据采集的安全性和效率,确保数据的多样性和准确性。文中详细描述了准备工作、API鉴权、代理授权及获取接口的过程,并提供了代码示例,帮助读者快速上手。手机数据来源为京东(item.jd.com),代理IP资源来自青果网络(qg.net)。
|
2月前
|
机器学习/深度学习 前端开发 数据处理
利用Python将Excel快速转换成HTML
本文介绍如何使用Python将Excel文件快速转换成HTML格式,以便在网页上展示或进行进一步的数据处理。通过pandas库,你可以轻松读取Excel文件并将其转换为HTML表格,最后保存为HTML文件。文中提供了详细的代码示例和注意事项,帮助你顺利完成这一任务。
105 0
|
9月前
|
存储 数据处理 索引
Python操作Excel常用方法汇总
Python操作Excel常用方法汇总
288 0

热门文章

最新文章

推荐镜像

更多