常用模块 - openpyxl模块

一、简介

xlrd/xlwt

主要是针对Office 2003或更早版本的XLS文件格式

缺点：不支持XLSX文件格式

OpenPyXL

能读能写能修改

缺点：不支持XLS

Microsoft Excel API

强大无极限

缺点：速度慢

二、基本用法

首先介绍下Excel的一些基本概念，Workbook相当于是一个文件，WorkSheet就是文件里面的每个具体的表，比如新建Excel文件里面的“Sheet1”，一个Workbook里面有一个或多个WorkSheet。

2.1操作Workbook对象

获取Workbook对象的方法有两种，一种是创建一个新的，一种是导入一个已经存在的。

2.1.1获取Workbook对象

方法1：创建Workbook

# 导入模块

from openpyxl import Workbook

# 创建一个Workbook

wb = Workbook() // 默认生成一个名为'Sheet'的WorkSheet

方法2：导入Workbook

# 导入模块

from openpyxl import load_workbook

# 导入一个Workbook

wb = load_workbook(filename='test.xlsx')

2.1.2Workbook属性

sheetnames： 返回所有WorkSheet的名字列表，类型为list

worksheets： 返回所有WorkSheet的列表，类型为list

active： 返回当前默认选中的WorkSheet

2.1.3Workbook方法

get_sheet_names()： 同sheetnames

get_active_sheet()： 同active属性

get_sheet_by_name(name)： 根据名称获取WorkSheet

remove(worksheet)： 删除一个WorkSheet，注意是WorkSheet对象，不是名字

save(filename)： 保存到文件，记住有写入操作记得保存。

2.2操作WorkSheet

2.2.1获取WorkSheet对象

# 获取默认打开的(active)的WorkSheet

ws1 = wb.active

# 创建一个WorkSheet

ws2 = wb.create_sheet() # 可传title和index两个参数,不传生成的WorkSheet名在'Sheet'后面递增加数字

# 通过名称获取WorkSheet

ws3=wb['Sheet1']

2.2.2WorkSheet属性

rows： 返回所有有效数据行，有数据时类型为generator，无数据时为tuple

columns： 返回所有有效数据列，类型同rows

max_column： 有效数据最大列

max_row： 有效数据最大行

min_column： 有效数据最小列，起始为1

min_row： 有效数据最大行，起始为1

values： 返回所有单元格的值的列表，类型为tuple

title： WorkSheet的名称

2.2.3WorkSheet方法

cell(coordinate=None, row=None, column=None, value=None)： 获取指定单元格或设置单元格的值，具体使用在cell下面介绍

2.3操作Cell

2.3.1获取Cell对象

# 使用WorkSheet的Cell方法

c1 = ws.cell('A1')

c2 = ws.cell(row=1, column=1)  # 获取A1单元格

# 通过坐标获取Cell

c3 = ws['A1']

# 获取多个

c3 = ws['A1:E5']  # 返回多行数据,类型为tuple

2.3.2设置Cell的值

# 直接使用WorkSheet的cell方法设置

ws.cell(row=1, column=1, value=10)

# 设置Cell对象value属性

c1 = ws.cell('A1')

c1.value = 100

2.3.3Cell属性

column： 所在列，起始为1

row： 所在行，起始为1

coordinate：  所在坐标，如'A1'

parent： 所属的WorkSheet

value： 单元格的值

2.3.4Cell方法

offset(row=0, column=0)： 偏移

三、使用实例

#! /usr/bin/env python3

# -*- coding:utf-8 -*-

# Author   : mayi

# Blog     : http://www.cnblogs.com/mayi0312/

# Date     : 2019/04/25

# Name     : my_excel_model

# Software : PyCharm

# Note     : 功能：使用openpyxl模块处理Excel文件

import os

from openpyxl import load_workbook

from openpyxl import Workbook

def getExcelConOpenpyxl(file_name, book_name=None, data_type="list", index=1):

    """

    利用openpyxl模块获取Excel表中的内容

    :param file_name: Excel表文件名

    :param book_name: Sheet表名

    :param data_type: 列表中存储每行内容的方式（list或dict），默认为列表

    :param index: sheet的序号或者名字，默认处理第一个sheet

    :return: Excel表中的内容

    """

    # 打开一个xlsx文件

    wb = load_workbook(file_name)

    # 打开指定的sheet

    # sheet = wb.get_sheet_by_name("名单")

    # sheet = wb["名单"]

    if not book_name:

        sheet = wb.active

    else:

        sheet = wb.get_sheet_by_name(book_name)

    # 用于存储Excel表内容的列表

    excel_con_list = []

    # 获取所有内容

    line_list = sheet.rows

    # 第一行为表头

    field_list = next(line_list)

    for line in line_list:

        if data_type == "dict":

            # 字典

            temp_line_dict = {}

            for i, cell in enumerate(line):

                cell_key = field_list[i].value

                cell_value = cell.value

                temp_line_dict[cell_key] = cell_value

            excel_con_list.append(temp_line_dict)

        else:

            # 列表

            temp_line_list = []

            for i, cell in enumerate(line):

                cell_key = field_list[i].value

                cell_value = cell.value

                temp_line_list.append([cell_key, cell_value])

            excel_con_list.append(temp_line_list)

    # 关闭文档

    wb.close()

    return excel_con_list

def setExcelConOpenpyxl(file_name, con_list, field_list=None, book_name=None, model=None):

    """

    利用openpyxl模块将列表中的内容存储至Excel表中

    :param file_name: Excel表文件名

    :param con_list: 需要保存的列表信息

    :param field_list: 表头内容

    :param book_name: Sheet表名称

    :param model: 模板文件名（默认没有模板）

    :return: None

    """

    if model:

        # 如果有模板文件，则打开模板工作簿

        wb = load_workbook(model)

    else:

        # 否则，新建一个Excel工作簿

        wb = Workbook()

    # 激活工作表

    sheet = wb.active

    if not book_name:

        sheet.title = book_name

    if field_list:

        # 如果有设置表头的话

        # 列数

        n_col = len(field_list)

        for i_col in range(n_col):

            sheet.cell(row=1, column=i_col + 1, value=field_list[i_col])

    # 行数

    n_row = len(con_list)

    for i in range(n_row):

        # 列数

        n_col = len(con_list[i])

        for j in range(n_col):

            sheet.cell(row=2 + i, column=j + 1, value=con_list[i][j])

    if not os.path.exists(os.path.dirname(file_name)):

        # 如果文件所在目录不存在，则创建

        os.makedirs(os.path.dirname(file_name))

    # 文档另存为

    wb.save(file_name)

    # 关闭文档

    wb.close()

# 入口函数

if __name__ == '__main__':

    filename = "test.xlsx"

    con_list = getExcelConOpenpyxl(filename)

    print(con_list)

    filename = os.path.abspath("test_w.xlsx")

    field_list = ["序号", "姓名", "性别", "年龄"]

    con_list = [

        [1, "张三", "男", ""],

        [2, "李四", "女", ""],

        [3, "王五", "男", ""],

        [4, "麻六", "女", ""],

        [5, "田七", "男", ""],

    ]

    setExcelConOpenpyxl(filename, con_list, field_list, book_name="名单")

注意：

·Cell的row和column都是从1开始的

·文件操作完记得调用Workbook的save()方法

·最好记得调用Workbook的close()方法

巴特西