最近工作中需要统计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案例源码：
https://download.csdn.net/download/aiwusheng/85140982
Python3使用xlrd修改Excel数据映射关系：
https://download.csdn.net/download/aiwusheng/85066595
参考：
Python开发 之 Python3读写Excel文件：
https://blog.csdn.net/u014597198/article/details/83104653
= THE END =
文章首发于公众号”CODING技术小馆“，如果文章对您有帮助，欢迎关注我的公众号。
原创文章，作者：Carrie001128，如若转载，请注明出处：https://blog.ytso.com/244359.html