最近工作中需要统计APP线上终端用户 手机品牌
、手机型号
与对应型号的用户量
,并生成Excel表格汇报项目组。因线上手机品牌、型号数量过于庞大,手动操作过于消耗时间,所以搞了一个 python3 的脚本来生成所需要的表格。
用 python3 操作Excel,第一件事是找可使用的依赖库,之前在网上找了一个 python3 依赖库的优劣对比图:
我选择的依赖库是openpyxl
,因为我的需求是:
xlsx
新版本的Excel文件;xlsx
外,具备以下功能:因为之前也尝试过 xlrd
等其他依赖库,所以这篇文章我会将openpyxl
和 xlrd
的API使用方式均进行介绍,算是做一个学习笔记:
openpyxl
操作xlsx
表格;xlrd
操作xls
表格;这里对 python3
使用 openpyxl
操作xlsx
的相关代码片段做如下总结:
openpyxl
读取 xlsx
文件;openpyxl
写 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()
新建Excel
并创建Sheet
页:# 创建输出表格Excel:创建工作表 excel = openpyxl.Workbook() # 创建sheet页:以demo为名字创建一个sheet页 sheet = excel.create_sheet('demo_sheet', 0)
行高
:# 第一行的行高 sheet.row_dimensions[1].height = 22
列宽
:# 设置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文件如图所示:
这里对 python3
使用 xlrd
操作xls
的相关代码片段做如下总结:
xlrd
读取 xls
文件;xlutils.copy
写 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()
因 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
文章首发于公众号”CODING技术小馆“,如果文章对您有帮助,欢迎关注我的公众号。