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)