Python3如何详细操作Excel文件教程？

本文共计2304个文字，预计阅读时间需要10分钟。

最近工作中需要统计APP线上终端用户数量，包括手机品牌、手机型号与应用类型的用户量，并生成Excel表格汇总项目组数据。由于线上手机品牌、型号数量庞大，且手动操作耗时过长，因此需要搞定。

最近工作中需要统计APP线上终端用户 `手机品牌`、`手机型号`与`对应型号的用户量`，并生成Excel表格汇报项目组。因线上手机品牌、型号数量过于庞大，手动操作过于消耗时间，所以搞了一个 python3 的脚本来生成所需要的表格。

最近工作中需要统计APP线上终端用户 手机品牌、手机型号与对应型号的用户量，并生成Excel表格汇报项目组。因线上手机品牌、型号数量过于庞大，手动操作过于消耗时间，所以搞了一个 python3 的脚本来生成所需要的表格。

用 python3 操作Excel，第一件事是找可使用的依赖库，之前在网上找了一个 python3 依赖库的优劣对比图：

我选择的依赖库是openpyxl，因为我的需求是：

• 可以读写xlsx 新版本的Excel文件；
• 面对大文件的读写：速度够快；
• 除读写xlsx 外，具备以下功能：
  设置行高、列宽、文字大小、表格边框、合并单元格等

因为之前也尝试过 xlrd等其他依赖库，所以这篇文章我会将openpyxl和 xlrd的API使用方式均进行介绍，算是做一个学习笔记：

• python3使用openpyxl操作xlsx表格；
• python3使用xlrd操作xls表格；

一、openpyxl

这里对 python3 使用 openpyxl操作xlsx的相关代码片段做如下总结：

• 使用 openpyxl 读取 xlsx 文件；
• 使用 openpyxl 写 xlsx 文件；

1.1 读 xlsx 文件

• 打开Excel的xlsx文件，并读取sheet页：

import openpyxl # 导入模块openpyxl # 打开Excel表格 excel = openpyxl.load_workbook('refer/品牌型号映射表.xlsx') # 获取指定Sheet表单页 sheet = refer_excel['品牌型号对应关系']

• 读取单元格cell内容：读取第一行第一列的单元格内容；

import openpyxl # 导入模块openpyxl # 读取第一行第一列的单元格内容 cell_value = (sheet.cell(row=1, column=1)).value

读取Excel 可运行代码举例：

使用 openpyxl 读取Excel的xlsx文件，举例代码如下：

这里要读取的举例Excel表格如下：

代码举例如下：

import openpyxl # 导入模块openpyxl def get_brand_model_dict_from_refer(): """ 读取xlsx文件，获取xlsx文件中"品牌"、"型号"映射字典 """ # 打开Excel表格 refer_excel = openpyxl.load_workbook('refer/品牌型号映射表.xlsx') # 获取指定Sheet表单页 refer_sheet = refer_excel['品牌型号对应关系'] # 创建字典：创建一个以型号为key，以品牌为value的字典 brand_model_dict = {} # 行循环：从第二行开始循环，到最后一行截止 for row in range(2, refer_sheet.max_row + 1): # 读取cell单元格中的数据 brand = (refer_sheet.cell(row=row, column=1)).value # 品牌 model = (refer_sheet.cell(row=row, column=2)).value # 型号 # 以型号为key 以品牌为value brand_model_dict[model] = brand print("return brand_model_dict: ", brand_model_dict) return brand_model_dict # ~~~~~~~~~~~~~~~~main~~~~~~~~~~~~~~~~~~ # 读取对应关系表格： get_brand_model_dict_from_refer() 1.2 写 xlsx 文件

• 新建Excel并创建Sheet页：

# 创建输出表格Excel：创建工作表 excel = openpyxl.Workbook() # 创建sheet页：以demo为名字创建一个sheet页 sheet = excel.create_sheet('demo_sheet', 0)

• 设置Excel中表格的行高：

# 第一行的行高 sheet.row_dimensions[1].height = 22

• 设置Excel中表格的列宽：

# 设置A列的列宽 sheet.column_dimensions['A'].width = 25

• 为单元格赋值：

# 第一行第一列的单元格 cell11 = sheet.cell(row=1, column=1) # 单元格赋值 cell11.value = "demo table title"

• 单元格内容居中对齐：

# 居中对齐 cell11.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')

• 合并单元格：

# 合并单元格：合并第一行，1~3列的单元格 sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3)

• 保存Excel到指定目录：

# 保存excel文件 excel.save('output/demo_excel.xlsx')

完整的代码实现举例：

import os # 文件相关判断会用到 import openpyxl # 导入模块openpyxl # # ~~~~~~~~写~~~~~~~~ # 创建输出表格Excel：创建工作表 output_excel = openpyxl.Workbook() # 创建sheet页：以demo为名字创建一个sheet页 output_sheet = output_excel.create_sheet('demo_sheet', 0) # # 行高：第一行行高 22 output_sheet.row_dimensions[1].height = 22 # 列宽：列宽 25 (A、B、C) 三列宽度为 25 column_width_list = ['A', 'B', 'C'] for columnKey in column_width_list: output_sheet.column_dimensions[columnKey].width = 25 # # 设置第一行显示内容：第一行为表格的标题，需合并单元格 cell11 = output_sheet.cell(row=1, column=1) # 第一行第一列的单元格 cell11.value = "demo table title" # 单元格赋值 cell11.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center') # 居中对齐 # 合并单元格 output_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3) # # 设置第二行显示内容：第二行为 品牌、型号、数量 md_cell21 = output_sheet.cell(row=2, column=1) # 单元格 md_cell21.value = '品牌' # 赋值 md_cell21.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center') # 居中 md_cell21 = output_sheet.cell(row=2, column=2) # 单元格 md_cell21.value = '型号' # 赋值 md_cell21.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center') # 居中 md_cell21 = output_sheet.cell(row=2, column=3) # 单元格 md_cell21.value = '数量' # 赋值 md_cell21.alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center') # 居中 # 保存表格 # 如果存在已有的输出文件：则删除文件 if os.path.exists('output/demo_excel.xlsx'): os.remove('output/demo_excel.xlsx') # 保存输出文件 output_excel.save('output/demo_excel.xlsx')

生成的Excel文件如图所示：

二、xlrd

这里对 python3 使用 xlrd操作xls的相关代码片段做如下总结：

• 使用 xlrd 读取 xls 文件；
• 使用 xlutils.copy 写 xls 文件；

2.1 读 xls 文件

• 打开Excel的xls文件，并读取sheet页：

import xlrd # 读取excel # 打开Excel表格 excel = xlrd.open_workbook('input/品牌型号映射表.xls') # 获取指定Sheet表单页 sheet = excel.sheet_by_index(0)

• 读取单元格cell内容：读取第一行第一列的单元格内容；

import xlrd # 读取excel # 读取第一行第一列的单元格内容 value = sheet.cell_value(0, 0) # 品牌

读取 Excel 可运行代码举例：

使用 xlrd 读取Excel的xls文件，举例代码如下：

这里要读取的举例Excel表格如下：

代码举例如下：

import xlrd # 读取excel def get_brand_model_dict_from_refer(): """ 读取xlsx文件，获取xlsx文件中"品牌"、"型号"映射字典 """ # 打开Excel表格 excel = xlrd.open_workbook('input/品牌型号映射表.xls') # 获取指定Sheet表单页 sheet = excel.sheet_by_index(0) # 创建字典：创建一个以型号为key，以品牌为value的字典 brand_model_dict = {} # 行循环：从第二行开始循环，到最后一行截止 for row in range(1, sheet.nrows): # 读取cell单元格中的数据 brand = sheet.cell_value(row, 0) # 品牌 model = sheet.cell_value(row, 1) # 型号 # 以型号为key 以品牌为value brand_model_dict[model] = brand print("return brand_model_dict: ", brand_model_dict) return brand_model_dict # ~~~~~~~~~~~~~~~~main~~~~~~~~~~~~~~~~~~ # 读取对应关系表格： get_brand_model_dict_from_refer() 2.2 使用 xlutils.copy 写 xls 文件

因 xlrd 无更改Excel的功能，这里使用 xlutils.copy 创建一个新的Excel。

• 创建Excel：

# 因 xlrd 无更改Excel的功能，这里使用 xlutils.copy 创建一个新的Excel excel = copy(input_excel) sheet = output_excel.get_sheet(0)

• 为单元格赋值：

# 为单元格赋值 sheet.write(row, column, cell_value)

使用 xlutils.copy 写 Excel 可运行代码举例：

import xlrd # 读取excel # 导入copy模块 from xlutils.copy import copy # # ~~~~~~~~读~~~~~~~~ # 打开Excel表格 input_excel = xlrd.open_workbook('input/品牌型号映射表.xls') # 获取指定Sheet表单页 input_sheet = input_excel.sheet_by_index(0) # # ~~~~~~~~写~~~~~~~~ # 因 xlrd 无更改Excel的功能，这里使用 xlutils.copy 创建一个新的Excel output_excel = copy(input_excel) output_sheet = output_excel.get_sheet(0) # 行循环(参考文件) for row in range(input_sheet.nrows): # 忽略第0行 if row == 0: output_sheet.write(row, 2, '行号') else: # 修改：第row行 第2列的内容 output_sheet.write(row, 2, row) # 保存更改后的文件 output_excel.save('output/demo_excel.xls')

三、源码下载

给出源码之前，先说一下我的编译器环境：

• 环境：python 3.8
• 编译器：PyCharm

源码下载：

Python3使用openpyxl、xlrd依赖库操作Excel案例源码：
download.csdn.net/download/aiwusheng/85140982

Python3使用xlrd修改Excel数据映射关系：
download.csdn.net/download/aiwusheng/85066595

参考：

Python开发 之 Python3读写Excel文件：
blog.csdn.net/u014597198/article/details/83104653

= THE END =

文章首发于公众号”CODING技术小馆“，如果文章对您有帮助，欢迎关注我的公众号。