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}")