Python 自动化办公之操作Excel

Python excel 操作

1.1 常用的openpyxl

1.1.1 有一个xlsx表用户行为偏好，查看有哪些活动表并查看活跃活动表的名称

import openpyxl
wb = openpyxl.load_workbook('.\\task2_2\\用户行为偏好.xlsx')#这种在同一目录下还能进行操作的路径叫做相对路径
# type(wb)
print(wb.sheetnames)
active_sheet = wb.active
# print(f'active_sheet对象: {active_sheet}')
print(f'活跃活动表的名称: {active_sheet.title}')

运行结果:

1.1.2针对单元格的操作

sheet = wb['订单时长分布']
print(f'wb[订单时长分布]: {sheet}')
a=sheet['B1']
# print(f'此处单元格对象为{a}')
print(f"B1处的值为: {a.value}")
print(f'B1处内容 所在行{a.row},a 所在列{a.column}')

运行结果：

1.1.3打印前14行奇数行的值以及确定此表格有多少行多少列

for i in range(1,14,2):
    print(f'第{i}行的值为 {sheet.cell(row=i,column=2).value}')
#确定表格的最大行数与最大列数
print(f'此表格有{sheet.max_row},此表格有{sheet.max_column}列')
print(f'sheet.max_column: {sheet.max_column}')

运行结果

1.1.4 读出多个单元格的值

cells = sheet['A1:C8']
# print(cells)
for i in cells:
    # print(i)
    for j in i:
        print(j.value,end = ',')
    print()

运行结果

1.1.5 将sum求和公式写入单元格中

# 1) 导⼊ openpyxl 中的 load_workbook 函数
from openpyxl import load_workbook
# 2) 获取指定 excel⽂件对象 Workbook
exl_1 = load_workbook(filename='用户行为偏好.xlsx')
# 3) 通过指定 sheetname 从 Workbook 中获取 sheet 对象 Worksheet
sheet = exl_1['订单时长分布']
print(f'订单时⻓分布 值范围: {sheet.dimensions}') #先查看原有表格的单元格范围，防⽌替代原有数据订单时⻓分布 值范围: A1:D14
# 单元格 A15 中写⼊ 合计
sheet['A15'].value = '合计'
# 单元格 D15 中写⼊求和公式：SUM(D2:D14)
sheet['D15'] = '=SUM(D2:D14)'
exl_1.save(filename='用户行为偏好.xlsx')
# 使⽤ xlwings 打开 excel ⽂件然后保存 使写⼊的 公式⽣效
import xlwings as xw
# 打开⼯作簿
app = xw.App(visible=False, add_book=False)
wb = app.books.open('用户行为偏好.xlsx') 
wb.save()
# 关闭⼯作簿
wb.close()
app.quit()
# 验证写⼊是否成功
# 1) 获取指定 excel⽂件对象 Workbook，
# 并设置 data_only=True，表示读取的时候如果单元格内是公式的话，以公式计算后的值的形式显示
exl_2 = load_workbook(filename = '用户行为偏好.xlsx', data_only=True)
# 2) 打印相关信息
sheet = exl_2['订单时长分布']
print(f"sheet['A15']={sheet['A15'].value},sheet['D15']={sheet['D15'].value}")
print(f"{sheet['D1'].value} 求和值为SUM(D2:D14)={sheet['D15'].value}")

#自动打开相应的文件进行保存
# 使⽤ xlwings 打开 excel ⽂件然后保存 使写⼊的 公式⽣效
import xlwings as xw
# 打开⼯作簿
app = xw.App(visible=False, add_book=False)
wb = app.books.open('用户行为偏好.xlsx') 
wb.save()
# 关闭⼯作簿
wb.close()
app.quit()

运行结果：

1.2 常用的XLWings

1.2.1 一次简单的xlsx表格的创建

# 导⼊xlwings，并起⼀个别名 xw，⽅便操作
import xlwings as xw
# 1、创建⼀个app应⽤，打开Excel程序
# visible=True 表示打开操作Excel过程可⻅ 初次接触可以设置为True，了解其过程
# add_book=False 表示启动app后不⽤新建个⼯作簿
app = xw.App(visible=True, add_book=False)
# 2、新建⼀个⼯作簿
wb = app.books.add()
# 3、新建⼀个sheet，并操作
# 3.1 新建sheet 起名为first_sht
sht = wb.sheets.add('first_sht')
# 3.2 在新建的sheet表中A1位置插⼊⼀个值：Datawhale
sht.range('A1').value = 'Datawhale'
# 3.3 保存新建的⼯作簿，并起⼀个名字
wb.save('xlwings_wb.xlsx')
# 4、关闭⼯作簿
wb.close()
# 5、程序运⾏结束，退出Excel程序
app.quit()

运行结果：

1.2.2 剩下的需要自己去拓展与记忆

1、有对sheet(工作表)的基本操作，有创建的操作，有对单元格赋值，删除，更新的操作，有统计工作表行数与列数的操作等。
2、对单元格样式进行相应的设置，其中印象最为深刻的为对目标单元格进行超链接的设置
3、还可以向目标单元格进行图片插入的操作，用到了pandas，numpy,matplotlib 等模块。