常用模块 - 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, "张三", "男", "20"],
        [2, "李四", "女", "21"],
        [3, "王五", "男", "19"],
        [4, "麻六", "女", "24"],
        [5, "田七", "男", "23"],
    ]
    setexcelconopenpyxl(filename, con_list, field_list, book_name="名单")

注意：

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

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

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