Python – Write data to Excel – write multiple sheets

 
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('mongo: / / XXXX: [email protected]:27017 /')
    mydb = myclient["xxx"]
    return mydb
 
 
mydb = link_mongo_database()
 
 
def write_Excel_multi_sheets(file_name, excel_prepare_datas):
    """Data is written to an Excel table, supporting multiple sheets written to an Excel file :param file_name: file name: param excel_prepare_datas: Example: excel_prepare_datas = [{"head": ["time","sales"]."data": [["2020-02-04", 1], ["2020-02-03", 2]]."title":"The pie chart"}, {"head": ["The colours of the rainbow","Rainbow bridge"]."data": [["is", 1], ["no", 2]]."title":"The line chart"}]"head": header,"data": data,"title": sheet_name :return: """Outwb = OpenPyxl.workbook () # open a file to be written to 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) # create sheet in the file to be writtenfor 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] # write fileelse:
                    outws.cell(row, col).value = data[row - 2][col - 1]
        index += 1Outwb. save(file_name) # remember to savereturn file_name
 
 
if __name__ == '__main__'Head = ["Year"."Time"]
    excel_prepare_datas = [
        {
            "head": head,
            "data": [["2012"."2020-02-04"], ["2012"."2020-02-04"]],
            "title": "Pie"
        },
        {
            "head": head,
            "data": [["2012"."2020-02-04"], ["2012"."2020-02-04"]],
            "title": "Line chart"
        },
 
    ]
 
    file_name = write_Excel_multi_sheets("multi_sheet_test02.xls", excel_prepare_datas)
    print(f"file_name = {file_name}")
Copy the code