基于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.合并、取消合并单元格

总结

以上是生活随笔为你收集整理的基于Python操作Excel实战案例的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。