openpyxl模块

获取文件对象

import openpyxl
file = '1.xlsx'  #文件路径
wb = openpyxl.load_workbook(file, data_only=True) # 打开文件

获取 工作簿中所有工作表名的列表

wb.get_sheet_names()

获取工作表

#按名字：
wb.get_sheet_by_name('要操作的工作表名')

#索引：
details_sheet = wb.worksheets[0] #按wb.get_sheet_names() 列表顺序读取

读写

#读：
head = details_sheet.cell(row=1, column= 1).value  #row 第几行  column 第几列

#写：
details_sheet.cell(row=1, column= 1).value='插入的数据'


#按大写字母操作
head = details_sheet.cell(A, A).value # A = 1

保存

# 保存的文件名
template_file="2.xlsx"
wb.save(template_file)

一键导入excel

# Copyright (c) 2010-2021 openpyxl

from openpyxl.compat.numbers import NUMPY
from openpyxl.xml import DEFUSEDXML, LXML
from openpyxl.workbook import Workbook
from openpyxl.reader.excel import load_workbook as open
from openpyxl.reader.excel import load_workbook
import openpyxl._constants as constants

# Expose constants especially the version number

__author__ = constants.__author__
__author_email__ = constants.__author_email__
__license__ = constants.__license__
__maintainer_email__ = constants.__maintainer_email__
__url__ = constants.__url__
__version__ = constants.__version__


def insert_excel_parallel(file_path, data_list, keys_list, head_list=None):
    """
    :param file_path: str 操作的文件路径
    :param data_list: list sql 数据
    :param keys_list: list 作为key读取data_list并按顺序插入表中
    :param head_list: list 第一行的表头，全为str
    :param read_only: 只读
    :param data_only: 只写
    :param keep_links:是否应保留指向外部工作簿的链接
    :return: str 操作的文件路径
    """
    wb = load_workbook(file_path, data_only=True)
    ws = wb.active
    h = 0
    if head_list:
        # 插入表头
        h = 1
        for head_key in range(len(head_list)):
            ws.cell(1, head_key + 1).value = head_list[head_key]

    # 插入数据
    row_count = len(data_list)
    for row in range(row_count):
        account_data = data_list[row]
        for column in range(len(keys_list)):
            key = keys_list[column]
            value = account_data[key]
            ws.cell(row + h + 1, column + 1).value = value

    wb.save(file_path)
    return file_path

测试

from openpyxl_sql import insert_excel_parallel as insert_excel

file_path = '../static/1.xlsx'
data_list = [
    {"Tag_ID": 1, "Tag_Name": "食品", "count": 100},
    {"Tag_ID": 2, "Tag_Name": "衣服", "count": 200},
    {"Tag_ID": 3, "Tag_Name": "日常用品", "count": 300}
]
keys_list = ['Tag_ID', 'Tag_Name', 'count']
head_list = ["标签ID", "标签名字", "数量"]
insert_excel(file_path, data_list, keys_list, head_list)