一文详解Python3处理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案例源码：
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技术小馆“，如果文章对您有帮助，欢迎关注我的公众号。