def DB2xlsx(self): DBserver = pymongo.MongoClient('mongodb://localhost:27017/') DB = DBserver['DataDB'] collection = DB['usermsgs'] # 'username', 'mode', 'quiz_class', 'quiz_no', 'quiz_ans', 'Msgdate' # "編號", "執行順序", "遠1單雙", "遠2單雙", "吸管單雙", "寶特瓶單雙", "遠1得分", "遠2得分", "遠3得分", "吸管流暢", "吸管變通", "吸管獨創", "寶特瓶流暢", "寶特瓶變通", "寶特瓶獨創" output = {} for item in collection.find(): datestr = (item['Msgdate'] + datetime.timedelta(hours=8)).strftime("%Y/%m/%d, %H") if output.get(datestr, -1) == -1: output[datestr] = [] if ILLEGAL_CHARACTERS_RE.search(item['username']): print((item['Msgdate'] + datetime.timedelta(hours=8) ).strftime("%Y/%m/%d, %H:%M:%S")) print(item['username'], 'username', sep=';') if ILLEGAL_CHARACTERS_RE.search(item['quiz_ans']): print((item['Msgdate'] + datetime.timedelta(hours=8) ).strftime("%Y/%m/%d, %H:%M:%S")) print(item['quiz_ans'], 'quiz_ans', sep=';') data = [ item['username'], item['mode'], item['quiz_class'], item['quiz_no'], item['quiz_ans'], (item['Msgdate'] + datetime.timedelta(hours=8)).strftime("%Y/%m/%d, %H:%M:%S") ] output[datestr].append(data) if not os.path.exists('./output'): os.mkdir('output') if not os.path.exists('./output/Data ' + datetime.date.today().strftime("%Y.%m.%d")): os.mkdir('./output/Data ' + datetime.date.today().strftime("%Y.%m.%d")) for datestr in output.keys(): file = pd.DataFrame(output[datestr], columns=[ "UserName", "Single/Double Mode", "Quiz Class", "Quiz #", "Ans", "Time" ]) s = './output/Data ' + datetime.date.today().strftime("%Y.%m.%d") + '/' + \ (datestr.replace(", ", "-").replace("/", "-")) + '.xlsx' # print(s) file.to_excel(s, engine='xlsxwriter') print("done~")
def writeExcel(): outwb = openpyxl.Workbook() # 打开一个将写的文件 outws = outwb.create_sheet(index=0) # 在将写的文件创建sheet for row in range(1,70000): for col in range(1,4): outws.cell(row, col).value = ILLEGAL_CHARACTERS_RE.search("", "{}".format(row*2)) # 写文件 print(row) saveExcel = "D:\\test2.xlsx" outwb.save(saveExcel) # 一定要记得保存
DBserver = pymongo.MongoClient('mongodb://localhost:27017/') DB = DBserver['DataDB'] # 'DataDB' 'ir' collection = DB['usermsgs'] except Exception as e: print(e) print("DB連線失敗") os.system("pause") exit(1) # 'username', 'mode', 'quiz_class', 'quiz_no', 'quiz_ans', 'Msgdate' # "編號", "執行順序", "遠1單雙", "遠2單雙", "吸管單雙", "寶特瓶單雙", "遠1得分", "遠2得分", "遠3得分", "吸管流暢", "吸管變通", "吸管獨創", "寶特瓶流暢", "寶特瓶變通", "寶特瓶獨創" try: output = [] for item in collection.find(): if ILLEGAL_CHARACTERS_RE.search(item['username']): print((item['Msgdate'] + datetime.timedelta(hours=8)).strftime("%Y/%m/%d, %H:%M:%S")) print(item['username'], 'username', sep=';') if ILLEGAL_CHARACTERS_RE.search(item['quiz_ans']): print((item['Msgdate'] + datetime.timedelta(hours=8)).strftime("%Y/%m/%d, %H:%M:%S")) print(item['quiz_ans'], 'quiz_ans', sep=';') data = [item['username'], item['mode'], item['quiz_class'], item['quiz_no'], item['quiz_ans'], (item['Msgdate'] + datetime.timedelta(hours=8)).strftime("%Y/%m/%d, %H:%M:%S")] output.append(data) except Exception as e: print(e) print("DB讀取失敗") os.system("pause") exit(2)