python处理Excel实现自动化办公教学（含实战）【二】

汀丶人工智能

发布于 2022-12-21 16:00:25

2.3K0

文章被收录于专栏：NLP/KGNLP/KG

python处理Excel实现自动化办公教学（含实战）【一】

python处理Excel实现自动化办公教学（含实战）【二】

python处理Excel实现自动化办公教学（数据筛选、公式操作、单元格拆分合并、冻结窗口、图表绘制等）【三】

相关码源：B站python资料.zip-数据挖掘文档类资源-CSDN下载

1.写入Excel文档

OpenPyXL 也提供了一些方法写入数据，这意味着你的程序可以创建和编辑电子表格文件。利用Python创建一个包含几千行数据的电子表格是非常简单的。
调用 openpyxl.Workbook()函数，创建一个新的空 Workbook 对象

import openpyxl

#创建一个新的工作簿对象
wb = openpyxl.Workbook()

#获取工作表名称 两种写法
wb.get_sheet_names()
wb.sheetsnames
#给工作表设置名称

sheet = wb.get_active_sheet()
sheet = wb.active
sheet.title = '跟进记录表'


wb.get_sheet_names()
#['跟进记录表']

#保存工作表
wb.save('./data/第一个工作簿.xlsx')

2.创建和删除工作表

利用 create_sheet() and del方法，可以在工作簿中添加或删除工作表
- create_sheet()方法返回一个新的 Worksheet 对象，名为 SheetX，它默认是工作簿的最后一个工作表。可以利用 index 和 title 关键字参数，指定新工作表的索引或名称。
- remove_sheet()方法接受一个 Worksheet 对象作为其参数，而不是工作表名称的字符串。如果你只知道要删除的工作表的名称，就调用 get_sheet_by_name()，将它的返回值传入 remove_sheet()。[2.x版本]
- 在工作簿中添加或删除工作表之后，记得调用 save()方法来保存变更。
创建sheet

wb = openpyxl.Workbook()

wb.create_sheet(title='销售记录')
wb.create_sheet(index=1,title='养殖技术')#index决定放在的位置

<Worksheet "养殖技术">

wb.sheetnames
['Sheet', '养殖技术', '销售记录']

删除sheet

del wb['养殖技术']
wb.sheetnames

['Sheet', '销售记录']

将值写入单元格
- 将值写入单元格，很像将值写入字典中的键
- 如果你有单元格坐标的字符串，可以像字典的键一样，将它用于 Worksheet 对象，指定要写入的单元格。

sheet = wb["销售记录"]

sheet['A1'] = 'hello'
sheet['B2'] = 'world'
wb.save('./data/第二个工作簿.xlsx')

3.项目:更新一个电子表格

这个项目需要编写一个程序，更新产品销售电子表格中的单元格。程序将遍历这个电子表格，找到特定类型的产品，并更新它们的价格
数据说明：
- 每一行代表一次单独的销售。列分别是销售产品的类型(A)、产品每磅的价格(B)、销售的磅数(C)，以及这次销售的总收入（D）。TOTAL列已经设置为 Excel公式，将每磅的成本乘以销售的磅数，并将结果取整到分。有了这个公式，如果列 B 或 C 发生变化，TOTAL 列中的单元格将自动更新。
现在假设 Garlic、Celery 和 Lemons 的价格输入的不正确。这让你面对一项无聊的任务:遍历这个电子表格中的几万行，更新所有 garlic、celery 和 lemon 行中每磅的价格。你不能简单地对价格查找替换，因为可能有其他的产品价格一样，你不希望错误地“更正”。对于几万行数据，手工操作可能要几小时。但你可以编写程序，几秒钟内完成这个任务。
你的程序做下面的事情:
- 循环遍历所有行。
- 如果该行是 Garlic、Celery 或 Lemons，更新价格。
这意味着代码需要做下面的事情:
- 打开电子表格文件。
- 针对每一行，检查列 A 的值是不是 Celery、Garlic 或 Lemon。如果是，更新列 B 中的价格。将该电子表格保存为一个新文件(这样就不会丢失原来的电子表格，以防万一)。
需要更新的价格如下:
- Celery 1.19
- Garlic 3.07
- Lemon 1.27
利用更新信息建立数据结构

这样硬编码产品和更新的价格有点不优雅。如果你需要用不同的价格，或针对不同的产品，再次更新这个电子表格，就必须修改很多代码。每次修改代码，都有引入缺陷的风险。
更灵活的解决方案，是将正确的价格信息保存在字典中，在编写代码时，利用这个数据结构,如果需要再次更新这个电子表格，只需要更新 PRICE_UPDATES字典，不用修改其他代码。

#2.x版本
import openpyxl
wb = openpyxl.load_workbook('./data/produceSales.xlsx')
sheet = wb.get_sheet_by_name('Sheet') #获取工作表
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27}
for rowNum in range(2, sheet.get_highest_row()+1): 
    produceName = sheet.cell(row=rowNum, column=1).value #遍历
    if produceName in PRICE_UPDATES: #包含在字典中
        sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]
wb.save('./data/updatedProduceSales.xlsx')

#3.x版本 推荐
import openpyxl
wb = openpyxl.load_workbook('./data/produceSales.xlsx')
sheet = wb["sheet"] #获取工作表
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27}
for rowNum in range(2, sheet.max_row()+1): 
    produceName = sheet.cell(row=rowNum, column=1).value #遍历
    if produceName in PRICE_UPDATES: #包含在字典中
        sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]
wb.save('./data/updatedProduceSales.xlsx')

4.设置单元格风格样式

4.1设置字体

设置某些单元格行或列的字体风格，可以帮助你强调电子表格中重点的区域。例如，在这个产品电子表格中，程序可以对产品的名称等行使用粗体。或者也许你希望对每公斤价格超过5元的行使用斜体。手工为大型电子表格的某些部分设置字体风格非常令人厌烦，但程序可以马上完成。【即：凸显】
为了定义单元格的字体风格，需要从 openpyxl.styles 模块导入 Font和 Style。
Font参数：

from openpyxl.styles import Font,Style
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.activate
sheet['A3'] = 'hello bobo'
sheet['A3'].font=Font(name="楷体")

#Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
"""
font.name 字体名称
font.size 字体大小
font.bold 是否加粗 返回的是布尔值
font.italic 是否斜体 返回的是布尔值
font.color 字体颜色 返回颜色类型和相关参数
"""

wb.save('./data/Styles.xlsx')

sheet['A3'].style #返回当前单元格的默认风格

Style(number_format='General')

可以调用 Font()来创建一个 Font 对象，并将这个 Font 对象保存在一个变量中。然后将它传递给 Style()，得到的 Style 对象保存在一个变量中，并将该变量赋给 Cell 对象的 style 属性。

f = Font(sz=50,b=True,i=True)
s = Style(font=f)
sheet['A3'].style = s
wb.save('./data/Styles.xlsx')

4.2设置填充色

from openpyxl.styles import Font,PatternFil
import openpyxl
wb = openpyx1.Workbook()
sheet = wb.active
sheet['A4'] = '背景填充色'
sheet[ 'A4'].fill = PatternFill (patternType= 'solid' , fgColor= ' 8470FF' ) #RGB值:需要填写16进制
wb.save(' ./data/ Styles.xlsx' )

4.3 设置cell的边框样式

需要使用Side, Boder类步骤:。

1.创建Side对象，通过构造方法参数style和color设置其边的样式和颜色。
2.设置ell的border属性，给其赋值为Border对象，且设置其上下左右边框为哪一个Side

from openpyx1.styles import Side,Border
import openpyxl
wb = openpyx1.Workbook( )
sheet = wb.active
sheet[ 'F4'] = ' 效果1'
sheet[ 'F5'] = ' 效果2'
#常见的style值: dashDot, dashDotDot, dashed, dotted, sigle......
s1 = Side(style= 'thin' ,color=' 8470FF' )
s2 = Side (style= ' double ' ,color=' ff0000')
sheet[ 'F4' ] .border = Border (top=s1 )
sheet[ 'F5' ] .border = Border ( top=s2 , bottom=s1，left=s2, right=s1)
wb.save( ' . /data/ cellBorder . xlsx' )

4.4 单元格对齐方式

from openpyxl. styles import Alignment
import openpyxl
wb = openpyxl.load_workbook( ' . /data/ cellBorder .xlsx')
sheet = wb[ ' Sheet']
c1 = sheet[ 'C1'].alignment = Alignment( horizontal= ' right' )
c2 = sheet['C2' ].alignment = Alignment(vertical= ' center ' )
c3 = sheet[ 'C3'].alignment = Alignment(vertical='top')
wb.save(' ./data/cel1Border. xlsx')