-
Notifications
You must be signed in to change notification settings - Fork 2
/
bbSaveXls.py
177 lines (172 loc) · 6.98 KB
/
bbSaveXls.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
# Benzín Brno - bbSaveXls.py - vypise ceny jednotlivych benzinek - bbSaveXls.py
# Ulozi ceny benzinek do Xls souboru
# 18.05.2022 - zmena na asyncio, trio nelze s playwright
import asyncio
import time
from datetime import datetime
import pytz # $ pip install pytz
import pandas as pd
import dataframe_image as dfi
from bbCena import F2f, tF
from bbCFG import bbCsvFlNm, bbDateDMY, bbDateMsk, bbLogFlNm, bbXlsFlNm, bbXlsShNm, brint, bbTimeZone, bbDnu, bbPngFxls, bbFmt2
from bbGlobus import tGlobu
from bbLST import bbBenzinky, bbHlavaUrl, bbHlavCena, bbHlavDate, bbHlavDlta, bbHLAVICKA, bbHlavOldC, bbNoUrl, s
from bbMakro import tMakro
from bbMapy import tMappy
from bbmBenzin import tmBenz
from bbTankONO import tTankO
from bbEurobit import tEuroB
# asnyc verze Eval
async def aEval(sStr, sFce, n):
# n je pole v formatu bbBenzinky; sloupce 0 - 3
start = time.perf_counter()
s = sStr # 3. sloupec - Url je jako parametr s funkce
brint("aEval: ", n[0])
try:
Cena = await eval(sFce) # nazev promenne v promenne
except Exception as ex: # pylint: disable=broad-except
Cena = 0
print(f"ERROR: bbSaveXls {n[0]} Url:{s} Error message: {ex}")
# eval result
brint("aEval: ", n[0], ' Cena', Cena, ' type(Cena)', type(Cena))
# n - 4. sloupec nova cena
n.append(Cena)
# n - 5. sloupec cas behu
exec_time = time.perf_counter() - start
n.append(exec_time)
return n
# zjisti ceny a vrati je v Listu
def DejNoveCeny():
# start_time = time.time()
loop = asyncio.get_event_loop()
tasks = []
# pres Benzinky
for i, n in enumerate(bbBenzinky):
# Zjisti cenu - pomoci eval, s - je url
s = n[3] # 3. sloupec - Url je jako parametr s funkce
# Task
# Cena = await aEval(s, n[1]) # 1. sloupec string s nazvem funkce
task = asyncio.ensure_future(aEval(s, n[1], n))
tasks.append(task)
# Vysledek
Ceny = loop.run_until_complete(asyncio.gather(*tasks))
# print('Ceny', Ceny)
# print("Total time:", time.time() - start_time)
return Ceny
# SaveXls
def SaveXls(Dump=False):
# Zmeny cen
zmena = []
# Xls file
# dfXls = pd.read_excel(bbXlsFlNm, sheet_name=bbXlsShNm)
# df1 = pd.read_excel(file, converters= {'COLUMN': pd.to_datetime}) - https://bit.ly/3nsSsrL
dfXls = pd.read_excel(bbXlsFlNm, sheet_name=bbXlsShNm, converters={bbHLAVICKA[bbHlavDate]: pd.to_datetime})
# print(dfXls)
# Benzinky - Now date
# NowDate = 'Last status check on: ' + str(time.strftime(bbDateDMY))
NowDate = 'Last status check on: ' + str(datetime.now(pytz.timezone(bbTimeZone)).strftime(bbDateDMY))
# Hlavicka tabulky - ['Název', 'Cena', 'Old Cena', 'Delta Cena', 'Old Datum', 'Url']
Hlava = bbHLAVICKA[:]
Hlava[bbHlavaUrl] = NowDate
# pandas Excel
df = pd.DataFrame(columns=Hlava)
# pole benzinek: Nazev, Fce, Url
for i, n in enumerate(DejNoveCeny()):
# for i, n in enumerate(bbBenzinky):
# Zjisti cenu - pomoci eval, s - je url
# s = n[3] # 3. sloupec - Url je jako parametr s funkce
# Task
# Cena = await aEval(s, n[1]) # 1. sloupec string s nazvem funkce
Cena = n[4] # cena je pridana jako dalsi sloupec
brint(' su tu n[1]:', n[1], 'Cena', Cena, ' type(Cena)', type(Cena))
# Nazev: cena
brint('#', i, ': Nazev:', n[0], ' Fce:', n[1], ' Cena:', Cena, ' 2:', n[2], ' Url:', n[3])
# Udaje Old, Cena - 2. sloupec
OldCena = dfXls.iloc[i, bbHlavCena]
OldDelt = dfXls.iloc[i, bbHlavDlta]
OldDate = dfXls.iloc[i, bbHlavDate]
brint('Cena1', Cena, ' type(Cena)', type(Cena), ' OldDelt:', OldDelt, ' type(OldDelt)', type(OldDelt), ' OldDate:', OldDate)
# import time - strftime - https://bit.ly/3Edt2np
# NowDate = time.strftime(bbDateMsk)
NowDate = datetime.now(pytz.timezone(bbTimeZone)).strftime(bbDateMsk)
# zmena ceny string
zc = '' # dlouhy text o zmene ceny
zz = '' # jen +- tj. zmeny pro graf
# Kdyz neni Zjistena cena
if Cena == 0:
Cena = OldCena
print('Cena nezjistena - ', ' Nazev:', n[0], ' Fce:', n[1], ' Url:', n[3], '!')
# Je Zmena Ceny
if Cena != OldCena:
# Zmena ceny - datum
OldDate = NowDate
# Zmena ceny - rozdil
OldDelt = F2f(Cena - float(OldCena))
# pridani +-
if OldDelt > 0:
OldDelt = '+' + str(OldDelt)
else:
OldDelt = str(OldDelt)
zc = ' ' + str((OldDelt)) + ' Cena: ' + str(float(Cena)) + ' Old: ' + str(float(OldCena)) + ' ' + str(NowDate) + ' - zmena ceny '
# Vypis zmenu kdyz neni dump
txt = n[0] + ': ' + str(Cena) + zc
print(txt) if not (Dump) else None
# Log protokol zmen - append to file - https://bit.ly/3mXdyhz
with open(bbLogFlNm, "a", encoding='UTF-8') as LogF:
LogF.write(txt+'\n')
else:
OldCena = dfXls.iloc[i, bbHlavOldC]
# pocet dni od zmeny ceny
DeltaDni = pd.to_datetime(NowDate, format=bbDateMsk)-pd.to_datetime(OldDate, format=bbDateMsk)
# Pandas Timedelta in Days - https://bit.ly/3MxImlV
DeltaDni = DeltaDni.days
brint('DeltaDni', DeltaDni, type(DeltaDni))
# zmena zapisuji 3 dny
# print('bbDnu', bbDnu, type(bbDnu),'\n','OldDate', OldDate, type(OldDate),'\n','NowDate', NowDate, type(NowDate))
if DeltaDni <= bbDnu:
# zmena za aktualni - 3 dny
# zz = OldDelt
zz = ' +'+str(OldDelt) if float(OldDelt) > 0 else str(OldDelt)
brint('platna zmena OldDelt', OldDelt, type(OldDelt))
# zmeny
zmena.append(zz)
# DataSet
brint('#', i, ': Nazev:', n[0], ' Cena:', Cena, ' OldCena:', OldCena, ' OldDelt:', OldDelt, ' n[3]:', n[3])
# url - https://bit.ly/3qJlRjq
# lnk = '=HYPERLINK("http://www.someurl.com", "some website")'
lnk = n[3]
# lnk = '=HYPERLINK("' + lnk + '", "' + lnk + '")'
df.loc[i] = [n[0], Cena, OldCena, OldDelt, OldDate, lnk]
# Vypisuj?
if Dump:
if zc == '':
# info o case behu pouze kdyz neni zmena ceny
Cas = n[5] # Cas je sloupec # 5
zc = '\t\tElapsed time: ' + bbFmt2.format(Cas)
# Zjisti cenu
print(n[0], ':', Cena, zc)
# Save Xls
# df.style.set_precision(2).background_gradient().hide_index().to_excel('styled.xlsx', engine='openpyxl')
df.to_excel(bbXlsFlNm, index=False, sheet_name=bbXlsShNm)
# Save CSV
df.to_csv(bbCsvFlNm, index=False)
# save xls to png - soubor bbPngFxls, ber jen sloupec Cena
df_styled = df.style.background_gradient(axis=0).hide(axis=0)
# How do I style a subset of a pandas dataframe? - https://bit.ly/3Ovn6i5
# df_styled = df.style.background_gradient(axis=None,subset=[bbHLAVICKA[bbHlavCena]])
dfi.export(df_styled, bbPngFxls)
# print('zmena', zmena)
return zmena
# main
def bbSaveXls_main():
print()
start_time = time.perf_counter()
# ceny = DejNoveCeny()
# print('ceny', ceny)
zmena = SaveXls(True)
print("SaveXls(): zmena", zmena)
print("Total time:", bbFmt2.format(time.perf_counter() - start_time), 'seconds.')
print('OkDone.')
# __name__
if __name__ == '__main__':
bbSaveXls_main()