python - 写入数据到Excel - 写入多个sheet

 
import datetime
import time
import pymongo
import xlrd
import openpyxl
import pymysql
from DBUtils.PersistentDB import PersistentDB
from pymysql.cursors import DictCursor
import sys
 
# database_flag = sys.argv[1]
database_flag = "test"
print(f"%%% database_flag = {database_flag}")
 
DATABASE_CONFIG = {
    "test": {
        'user': 'xxx',
        'passwd': 'xxx',
        'host': '0.0.0.0',
        'database': 'xxx',
        'port': 3306
    }
}
 
db = pymysql.connect(host=DATABASE_CONFIG.get(database_flag).get("host"),
                     user=DATABASE_CONFIG.get(database_flag).get("user"),
                     password=DATABASE_CONFIG.get(database_flag).get("passwd"),
                     database=DATABASE_CONFIG.get(database_flag).get("database"),
                     port=DATABASE_CONFIG.get(database_flag).get("port"),
                     charset="utf8")
 
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
 
 
def link_mongo_database():
    myclient = pymongo.MongoClient('mongodb://xxxx:xxxxxxx@0.0.0.0:27017/')
    mydb = myclient["xxx"]
    return mydb
 
 
mydb = link_mongo_database()
 
 
def write_Excel_multi_sheets(file_name, excel_prepare_datas):
    """
    数据写入Excel表格，支持一个Excel文件写入多个sheet
    :param file_name: 文件名称
    :param excel_prepare_datas:
           example：
           excel_prepare_datas = [
        {"head": ["时间", "销量"], "data": [["2020-02-04", 1], ["2020-02-03", 2]], "title": "饼图"},
        {"head": ["彩虹", "彩虹桥"], "data": [["是", 1], ["否", 2]], "title": "折线图"}]
        "head":表头, "data":数据, "title": sheet_name
    :return:
    """
    outwb = openpyxl.Workbook()  # 打开一个将写的文件
    index = 0
    for origin_data in excel_prepare_datas:
        title = origin_data.get('title')
        data = origin_data.get('data')
        head = origin_data.get('head')
 
        outws = outwb.create_sheet(title=title, index=index)  # 在将写的文件创建sheet
        for row in range(1, len(data) + 2):
            for col in range(1, len(head) + 1):
                if row == 1:
                    outws.cell(row, col).value = head[col-1]  # 写文件
                else:
                    outws.cell(row, col).value = data[row - 2][col - 1]
        index += 1
    outwb.save(file_name)  # 一定要记得保存
    return file_name
 
 
if __name__ == '__main__':
    # 类目id列表
 
    head = ["年份", "时间"]
    excel_prepare_datas = [
        {
            "head": head,
            "data": [["2012", "2020-02-04"], ["2012", "2020-02-04"]],
            "title": "饼图"
        },
        {
            "head": head,
            "data": [["2012", "2020-02-04"], ["2012", "2020-02-04"]],
            "title": "折线图"
        },
 
    ]
 
    file_name = write_Excel_multi_sheets("multi_sheet_test02.xls", excel_prepare_datas)
    print(f"file_name = {file_name}")