当前位置 : 主页 > 编程语言 > 其它开发 >

一文详解Python3处理Excel文件

来源:互联网 收集:自由互联 发布时间:2022-05-30
最近工作中需要统计APP线上终端用户 `手机品牌`、`手机型号`与`对应型号的用户量`,并生成Excel表格汇报项目组。因线上手机品牌、型号数量过于庞大,手动操作过于消耗时间,所以搞
最近工作中需要统计APP线上终端用户 `手机品牌`、`手机型号`与`对应型号的用户量`,并生成Excel表格汇报项目组。因线上手机品牌、型号数量过于庞大,手动操作过于消耗时间,所以搞了一个 python3 的脚本来生成所需要的表格。

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

用 python3 操作Excel,第一件事是找可使用的依赖库,之前在网上找了一个 python3 依赖库的优劣对比图:
Excel 处理相关依赖包优劣对比

我选择的依赖库是openpyxl,因为我的需求是:

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

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

  • python3使用openpyxl操作xlsx表格;
  • python3使用xlrd操作xls表格;
一、openpyxl

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

  • 使用 openpyxl 读取 xlsx 文件;
  • 使用 openpyxlxlsx 文件;
1.1 读 xlsx 文件
  • 打开Excelxlsx文件,并读取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 读取Excelxlsx文件,举例代码如下:

这里要读取的举例Excel表格如下:
要读取的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文件如图所示:
生成的Excel文件如图所示

二、xlrd

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

  • 使用 xlrd 读取 xls 文件;
  • 使用 xlutils.copyxls 文件;
2.1 读 xls 文件
  • 打开Excelxls文件,并读取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 读取Excelxls文件,举例代码如下:

这里要读取的举例Excel表格如下:
要读取的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')

生成的Excel文件如图所示

三、源码下载

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

  • 环境: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技术小馆“,如果文章对您有帮助,欢迎关注我的公众号。

欢迎关注我的公众号

上一篇:八小时工作制,做八小时or坐八小时?
下一篇:没有了
网友评论