excel.py如下
# encoding:utf-8 from bson.objectid import ObjectId import pymongo import time import xlwt from operator import itemgetter import collections from tools import * client = pymongo.MongoClient(host='10.x.x.75', port=27017) # 统计ABCDE_db数据库里机构的基本信息 db = client.ABCDE_db collection = db.organizations result = collection.find() #org_nums = result.count() org_nums = collection.estimated_document_count() def gen_row_info(): for item in result[1:]: # 客户名称 org_name = item.get("name") # 创建者 creator = item.get("creator") # 邮箱 email = item.get("email") # 创建时间 create_time = timeFormat(item.get("createTime")) # 更新时间 update_time = timeFormat(item.get("updateTime")) # 客户数据库名 org_db_name = nameFormat(item.get("_id")) # 数据库大小 org_data_size = data_size(org_db_name) print(org_db_name) # 用户数量 user_num = count_simple(org_db_name, "users") # 现场数量 site_num = count_simple(org_db_name, "site") # 网关数量 device_num = count_simple(org_db_name, "device") # 在线设备量 online_device_num = count_complex(org_db_name, "device", {"online": 1}) # 是否大客户，网关数量大于20台 is_big_customer = "是" if device_num > 20 else "否" # 是否有权限分组 is_group = judge_simple(org_db_name, "groups") # 是否有自定义角色 is_userdefined_role = judge_complex(org_db_name, "roles", {'name': {'$nin': ["admin", "DeviceManager", "DeviceSense"]}}) # 是否有标签 is_tag = judge_simple(org_db_name, "tags") # 是否有流量预警 is_data_alarm = judge_complex(org_db_name, "alarm.config", {'emailList': {'$ne': ""}}) # 是否有告警 is_alarm = judge_simple(org_db_name, "alarms") # 是否有控制器 is_machine = judge_simple(org_db_name, "machine") # 是否有组态画面 is_scada = judge_simple(org_db_name, "scada") # 是否有远程维护 is_channel = judge_simple(org_db_name, "channel") # 是否有远程命令 is_task = judge_complex(org_db_name, "tasks", {"type": 2}) # 设备型号 device_model_details = device_model_detail(org_db_name, "device") row_info = collections.OrderedDict() row_info['org_name'] = org_name row_info["email"] = email row_info["creator"] = creator row_info["creater_time"] = create_time row_info["update_time"] = update_time row_info["org_data_size"] = org_data_size row_info["user_num"] = user_num row_info["site_num"] = site_num row_info["device_num"] = device_num row_info["online_device_num"] = online_device_num row_info["is_big_customer"] = is_big_customer row_info["is_group"] = is_group row_info["is_userdefined_role"] = is_userdefined_role row_info["is_tag"] = is_tag row_info["is_data_alarm"] = is_data_alarm row_info["is_alarm"] = is_alarm row_info["is_machine"] = is_machine row_info["is_scada"] = is_scada row_info["is_channel"] = is_channel row_info["is_task"] = is_task row_info["device_model_details"] = device_model_details yield row_info f = xlwt.Workbook() sheet1 = f.add_sheet("sheet1", cell_overwrite_ok=True) style = xlwt.XFStyle() style.alignment = get_alignment() # 写入标题, 合并单元格 title1 = ( "客户名称", "邮箱", "创建者", "创建时间", "更新时间", "数据库大小", "用户数量", "现场数量", "网关数量", "在线设备总量", "是否大客户" ) title2 = ( "权限分组", "自定义角色", "标签", "流量预警", "告警", "组态画面", "控制器", "远程维护", "远程命令" ) column1 = 0 for t in title1: sheet1.write_merge(10, 12, column1, column1, t, style) column1 += 1 sheet1.write_merge(10, 11, 11, 19, "功能使用情况", style) column2 = 11 for t in title2: sheet1.write(12, column2, t, style) column2 += 1 sheet1.write_merge(10, 12, 20, 60, "设备型号", style) # 设备型号数据单元格合并 j = 13 while j < org_nums+10: sheet1.write_merge(j, j, 20, 60) j += 1 # 写入数据 info = gen_row_info() row_list = list(info) rows_by_online = sorted(row_list, key=itemgetter('online_device_num'), reverse=True) #print(rows_by_online) # 从excle的第14行开始写入数据 row = 13 for d in rows_by_online: col = 0 for one in d.values(): sheet1.write(row, col, one) col += 1 row += 1 f.save('test2.xls')
自定义函数tools.py
import xlwt import time from bson.objectid import ObjectId import pymongo client = pymongo.MongoClient(host='10.x.x.75', port=27017) def get_alignment(): alignment = xlwt.Alignment() # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐) alignment.horz = 0x02 # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐) alignment.vert = 0x01 # 设置自动换行 # alignment.wrap = 1 return alignment def timeFormat(x): x = time.localtime(x) return time.strftime("%F", x) def nameFormat(x): x = str(x) x = x.upper()+"_db" return x def count_simple(db, coll): org_db = client[db] coll = org_db[coll] num = coll.estimated_document_count() return num def count_complex(db, coll, exp): org_db = client[db] coll = org_db[coll] num = coll.count_documents(exp) return num def judge_simple(db, coll): org_db = client[db] coll = org_db[coll] num = coll.estimated_document_count() y = "是" if num > 0 else "否" return y def judge_complex(db, coll, exp): org_db = client[db] coll = org_db[coll] num = coll.count_documents(exp) y = "是" if num > 0 else "否" return y def device_model_detail(db, coll): org_db = client[db] coll = org_db[coll] result = coll.aggregate([{'$group': {'_id': '$model', 'modelCount': {'$sum': 1}}}]) model_list = [] for i in result: model_dict_list = [(k, v) for k, v in i.items()] model_type = model_dict_list[0][1] model_num = str(model_dict_list[1][1]) model_count = "{}({})".format(model_type, model_num) model_list.append(model_count) device_model_details = ",".join(model_list) return device_model_details def data_size(db): org_db = client[db] stats = org_db.command("dbstats") size = stats.get("storageSize")/1024/1024 return round(size)