[Python知识库]基于Python操作Excel实战案例

1.找出用户行为偏好.xlsx中 Sheet3 表中空着的格子，并输出这些格子的坐标。

# 获取当前工作目录
import os
print(f'当前工作目录为：{os.getcwd()} \n')

import warnings
warnings.filterwarnings('ignore')
path = './OpenPyXL_test/'

from openpyxl import load_workbook
exl = load_workbook(path+'用户行为偏好.xlsx')

# 查看excel 中表的名称
print(f'excel文件中的表包括：{exl.sheetnames}\n')

# sheet1 = exl.get_sheet_by_name('sheet3')
sheet3 = exl['Sheet3']

# 输出Sheet3 内容占据的大小
print(f'Sheet3内容大小：{sheet3.dimensions}\n')

# 直接通过sheet索引，sheet3.dimensions获取sheet数据区域
cells = sheet3[sheet3.dimensions]

# 遍历元组 判断每一个cell值是否为空
for rows in cells:
    for cell in rows:
        if not cell.value:
            # coordinate：单元格坐标
            print(f'{cell.coordinate} is None \n')

2.Excel 在原有工作簿中修改数据并保存。

（1）修改并保存数据

# 1、导入 openpyxl 中的 load_workbook 函数
from openpyxl import load_workbook
# 2、获取指定 excel 文件对象 Workbook
exl = load_workbook(filename=path+'用户行为偏好.xlsx')
# 3、通过指定 sheetname 从 Workbook 中获取 sheet 对象 Worksheet
sheet = exl.get_sheet_by_name("Sheet3")
# 4、通过索引方式获取指定 cell 值，将 A1 重新赋值
print(f"修改前 sheet['A1']：{sheet['A1'].value}")
sheet['A1'].value = 'hello world'
print(f"修改后 sheet['A1']：{sheet['A1'].value}")
# 5、保存修改后的内容
# 如果filename和原文件同名，则直接在源文件中修改；
# 否则会新建一个excel文件，并保存内容
exl.save(filename=path+'用户行为偏好_changed.xlsx')
# 我们选择保存在一个新文件 用户行为偏好_changed.xlsx 中

（2）验证是否修改成功

# 验证保存修改内容是否成功
exl_ = load_workbook(filename=path+'用户行为偏好_changed.xlsx')
a1 = exl_['Sheet3']['A1'].value
if a1 == 'hello world':
    print(f"修改成功！\n exl_['Sheet3']['A1'].value={a1}")
else:
    print(f"修改失败！\n exl_['Sheet3']['A1'].value={a1}")

3.创建新的表格写入数据并保存

# 1、导入 openpyxl 中的 Workbook 类
from openpyxl import Workbook

# 2、初始化一个 Workbook 对象
wb = Workbook()
print(f'默认sheet：{wb.sheetnames}')

# 3、通过 Workbook 对象的 create_sheet 函数创建一个 sheet
# title sheet 名称
# index sheet 位置，默认从0开始
sheet = wb.create_sheet(title='mysheet',index=0)
print(f'添加后sheet：{wb.sheetnames}')

# 4、在新建的 sheet 中写入数据
# 比如在 A1 单元格中写入‘test’
sheet['A1'].value='test'
print(f"sheet['A1'].value = {sheet['A1'].value}")

# 5、保存
wb.save(path+'create_sheet_test.xlsx')

4.将公式写入单元格保存

# 1、导入 openpyxl 中的 load_workbook 函数
from openpyxl import load_workbook

# 2、获取指定 excel 文件对象 Workbook
exl_1 = load_workbook(filename=path+'用户行为偏好_changed.xlsx')

# 3、通过指定 sheetname 从 Workbook 中获取 sheet 对象 Worksheet
sheet = exl_1['订单时长分布']

# 先查看原有表格的单元格范围，防止替代原有数据
print(f'订单时长分布值范围：{sheet.dimensions}\n')

# 单元格 A15 中写入 合计
sheet['A15'].value = '合计'

# 单元格 D15 中写入求和公式：SUM(D2:D14)
sheet['D15'] = '=SUM(D2:D14)'

# 保存
exl_1.save(filename='用户行为偏好_changed.xlsx')

# 使用 xlwing 打开 excel 文件然后保存，使写入的公式生效
import xlwings as xw

# 打开工作簿
app = xw.App(visible=False,add_book=False)
wb = app.books.open('用户行为偏好_changed.xlsx')
wb.save()

# 关闭工作簿
wb.close()
app.quit()

# 验证写入是否成功
# 1、获取指定 excel 文件对象 Workbook,并设置 data_only=True,
# 表示读取的时候如果单元格内是公式的话，以公式计算后的值的形式显示
exl_2 = load_workbook(filename='用户行为偏好_changed.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}")

注：即使设置了 data_only=True，也不能立即获取到刚刚添加的公式计算后的结果，需要自己 手动/添加代码 打开下 对应excel表格，然后 ctrl s保存下，再运行上面代码才能获取到对应公式计算后的值。

你可以使用下面代码自动打开指定 excel 文件然后保存使写入的公式生效，使用前你需要安装 xlwings，输入pip3 install xlwings即可，再后面我们也会学习这个模块。

5.插入空列/行

# 获取指定 sheet
sheet = exl_1['Sheet3']

# 插入列数据 insert_cols(idx,amount=1)
# idx是插入位置，amount是插入列数，默认是1
# idx=2表示第二列，即第二列前插入一列
sheet.insert_cols(idx=2)

# 第二列前插入5列
# sheet.insert_cols(idx=2,amount=5)

# 插入行数据 insert_rows(idx,amount=1)
# idx是插入位置，amount是插入行数，默认是1

# 在第二行前插入一行
sheet.insert_rows(idx=2)

# 在第2行前插入5行
# sheet.insert_rows(idx=2,amount=5)

exl_1.save(filename=path+'用户行为偏好_changed.xlsx')

6.删除和移动列和行

（1）删除

# 删除多列
sheet.delete_cols(idx=5,amount=2)

# 删除多行
sheet.delete_rows(idx=2,amount=5)

exl_1.save(filename=path+'用户行为偏好_changed.xlsx')

(2)移动

# 移动
# 当数字为正即向下或向右，为负即为向上或向左
sheet.move_range('B3:E16',rows=1,cols=-1)
exl_1.save(filename=path+'用户行为偏好_changed.xlsx')

7.Excel样式

（1）设置单个cell（单元格）字体样式

Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)。

# 1) 导入 openpyxl 中的 load_workbook 函数
#    导入 openpyxl 中的  styles 模块中的 Font 类
from openpyxl import load_workbook
from openpyxl.styles import Font

# 2) 获取指定 excel文件对象 Workbook
exl_1 = load_workbook(filename=root_path+'用户行为偏好_1.xlsx')
# 3) 通过指定 sheetname 从 Workbook 中获取 sheet 对象 Worksheet
sheet = exl_1['订单时长分布']
# 4) 获取到指定 cell 后，查看cell字体属性
cell = sheet['A1']
cell.font

# 5) 实例化一个 Font 对象，设置字体样式
#    字体改为：黑体  大小改为：20  设置为：加粗 斜体 红色
font = Font(name='黑体', size=20, bold=True, italic=True, color='FF0000')
cell.font = font
# 6) 保存修改 
exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')

（2）设置多个cell（单元格）字体样式

# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 中的 订单时长分布 工作表
# 我们处理了 单元格 A1 的字体样式，我们也可以通过遍历的形式，批量设置单元格字体样式

# 1) 获取要处理的单元格 
#    通过 sheet 索引获取第二行 cell
#    获取列可以用 字母索引，如 sheet['A'] 获取第一列 cell
cells = sheet[2]
# 2) 实例化一个 Font 对象，设置字体样式
#    字体改为：黑体  大小改为：10  设置为：加粗 斜体 红色
font = Font(name='黑体', size=10, bold=True, italic=True, color='FF0000')
# 3) 遍历给每一个 cell 都设置上对应字体样式
for cell in cells:
    cell.font = font
# 4) 保存修改
exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')

8.设置边框样式

（1）设置单元格边框样式

Side：边线样式设置类，边线颜色等

Side(style=None, color=None, border_style=None)

• style：边线的样式，有以下值可选：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick
• color：边线颜色
• border_style：style 的别名，必须设置，一般直接设置 border_style 就行，不用设置 style

Border：边框定位类，左右上下边线

Border常用参数解释：

• top bottom left right diagonal：上下左右和对角线的边线样式，为 Side 对象
• diagonalDown：对角线从左上角向右下角方向，默认为 False
• diagonalUp：对角线从右上角向左下角方向，默认为 False

# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 中的 订单时长分布 工作表 sheet
# 1) 导入 openpyxl 中的  styles 模块中的 Side, Border 类
from openpyxl.styles import Side, Border
# 2) 首先初始化一个边线对象（也可以设置多个）
side = Side(border_style='double', color='FF000000')
# 3) 通过 Border 去设置 整个单元格边框样式
border = Border(left=side, right=side, top=side, bottom=side, diagonal=side, diagonalDown=True, diagonalUp=True)

# 4) 查看目前单元格边框样式
# 获取第一行 cells
cells = sheet[1]
# 取出一个 cell 看边框样式
cells[0].border

# 5) 修改边框样式，并保存修改
for cell in cells:
    cell.border = border
exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')

9.设置单元格其他样式

（1） 设置单元格背景色

# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 中的 订单时长分布 工作表 sheet
# 1) 从 openpyxl.styles 中导入 背景颜色设置类 PatternFill, GradientFill
from openpyxl.styles import PatternFill, GradientFill

# 2) 实例化 PatternFill 对象，fill_type 参数必须指定
pattern_fill = PatternFill(fill_type='solid',fgColor="DDDDDD")
# 3) 实例化 GradientFill 对象，填充类型 type 默认为 linear
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff','000000'))

# 4) 获取指定 cells 遍历填充
# 对第三行 PatternFill 模式设置背景色
cells = sheet[3]
for cell in cells:
    cell.fill = pattern_fill

# 对第四行 GradientFill 模式设置背景色
cells = sheet[4]
for cell in cells:
    cell.fill = gradient_fill

# 5) 保存修改
exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')

（2）设置水平居中

openpyxl.styles 中的 Alignment 类常用参数介绍：

• horizontal：水平对齐，常见值 distributed, justify, center, left, fill, centerContinuous, right, general
• vertical：垂直对齐，常见值 bottom, distributed, justify, center, top
• textRotation：文字旋转角度，数值：0-180
• wrapText：是否自动换行，bool值，默认 False

# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 中的 订单时长分布 工作表 sheet
# 1) 从 openpyxl.styles 中导入 对齐方式设置类 Alignment
from openpyxl.styles import Alignment

# 2) 实例化一个 Alignment 对象，设置水平、垂直居中
alignment = Alignment(horizontal='center', vertical='center')

# 3) 获取指定 cells 遍历填充
# 对第五行数据设置上面的对齐方式
cells = sheet[5]
for cell in cells:
    cell.alignment = alignment
# 4) 保存修改
exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')

(3)设置行高与列高

# 1) 设置行高，通过 row_dimensions 和 column_dimensions 来获取行和列对象
# 2) 设置第1行行高为 30
sheet.row_dimensions[1].height = 30
# 3) 设置第3列列款为 24
sheet.column_dimensions['C'].width = 24
# 4) 保存修改
exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')

10.合并、取消合并单元格