python处理Excel实现自动化办公教学(含实战)【二】
相关文章:
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')