A riportoknál a körítés is fontos. Szépen formázott excel aktualizált diagrammal már szinte elvárás.
Hogyan is fogjunk hozzá pythonban?
Kódminta :
import cx_Oracle
import pandas as pd
import datetime
us_nev = 'klajos'
us_jsz = 'Ljs'
us_db = 'aix'
dt = datetime.datetime.now() - datetime.timedelta(days=1) ## tegnapi nap
datum = '{:%Y-%m-%d}'.format(dt)
conn = cx_Oracle.connect(us_nev ,us_jsz, us_db)
### Excel filebe mentes
# Excel írható objektum létrehozása (név tartalmazza a futás dátumot)
writer = pd.ExcelWriter(r'c:\Users\kl\Documents\xls\kiv_xml_riport_'+datum+'_kesz.xlsx')
#------------------- riport eredmény lekérdezése és excel mumklaba helyezés kezdet
#--- riport1
sql_com = """
select to_char(END_DATE,'YYYYMMDD') as END_DATE,
count(distinct XML_FILENAME) as XML_DB, count( distinct INTERNAL_KEY) as szl_db1, count(0) as SZL_DB0
from DB_STMT_STAT where END_DATE >= sysdate - 100
group by END_DATE
order by END_DATE """
## lekérdezés futtatás
df_ora = pd.read_sql(sql_com, conn)
# Pandas dataframe excelbe írása
df_ora.to_excel(writer, sheet_name='Stat_XML', engine='xlsxwriter', index=False)
workbook = writer.book
worksheet = writer.sheets['Stat_XML']
(max_row, max_col) = df_ora.shape
# auto szűrő
worksheet.autofilter(0, 0, max_row, max_col-1)
# oszlop szélesítése : pld. az első oszlop 25-re állítása
writer.sheets['Stat_XML'].set_column(0, 0, 25)
# diagram beszúrás
chart = workbook.add_chart({'type': 'line'}) ## column, pie, line, area, scatter' // circle', 'size': 7}
#chart.add_series({'values': ['Stat_XML', 0, 0, 2, 3 ]})
#chart.add_series({'values': '=Stat_XML!$A$1:$C$8'})
#chart.add_series({'values': ['Stat_XML', 1, 1, max_row, 1]})
"""
----------------1.
chart.add_series({
'name': ['Stat_XML', 0, 2],
'categories': ['Stat_XML', 1, 0, max_row, 0], ## x tengely
'values' : ['Stat_XML', 1, 2, max_row, 2], ## y tengely
## 'values' : ['Stat_XML', 1, 3, max_row, 3],
})
chart.add_series({
'name': ['Stat_XML', 0, 3],
'categories': ['Stat_XML', 1, 0, max_row, 0], ## x tengely
'values' : ['Stat_XML', 1, 3, max_row, 3], ## y tengely
## 'values' : ['Stat_XML', 1, 3, max_row, 3],
})
--------------- 2.
chart.add_series({
'name': ['Stat_XML', 0, max_col - 1],
'categories': ['Stat_XML', 1, 0, max_row , 0],
'values': ['Stat_XML', 1, max_col - 1, max_row , max_col - 1],
#'overlap':-10,
})
chart.add_series({
'name': ['Stat_XML', 0, max_col - 2],
'categories': ['Stat_XML', 1, 0, max_row , 0],
'values': ['Stat_XML', 1, max_col - 2, max_row , max_col - 2],
#'overlap':-10,
})
chart.add_series({
'name': ['Stat_XML', 0, 1],
'categories': ['Stat_XML', 1, 0, max_row , 0],
'values': ['Stat_XML', 1, 1, max_row , 1],
#'overlap':-10,
})
"""
## első oszlop a kategoria, tobbi oszlop adatok és az értékek
for i in range(max_col):
if i != 0 :
chart.add_series({
'name': ['Stat_XML', 0, i],
'categories': ['Stat_XML', 1, 0, max_row , 0],
'values': ['Stat_XML', 1, i, max_row ,i],
#'overlap':-10,
})
worksheet.insert_chart(1, max_col +1, chart)
#------------------- riport eredmény lekérdezése és excel mumklaba helyezés vége
# Az excel állomány mentése és lezárás
writer.save()
writer.close()
conn.close()
# import zipfile package
import zipfile
# import the python pandas package
import pandas as pd
# create data_frame1 by creating a dictionary
# in which values are stored as list
data_frame1 = pd.DataFrame({'Fruits': ['Appple', 'Banana', 'Mango',
'Sales in kg': [20, 30, 15, 10, 50, 40]})
# create data_frame2 by creating a dictionary
# in which values are stored as list
data_frame2 = pd.DataFrame({'Vegetables': ['tomato', 'Onion', 'ladies finger',
'Sales in kg': [200, 310, 115, 110, 55, 45]})
# create data_frame3 by creating a dictionary
# in which values are stored as list
data_frame3 = pd.DataFrame({'Baked Items': ['Cakes', 'biscuits', 'muffins',
'
# create data_frame3 by creating a dictionary
# in which values are stored as list
data_frame4 = pd.DataFrame({'Cool drinks': ['Pepsi', 'Coca-cola', 'Fanta',
'
'Sales in count': [1209, 1230, 1359, 3310, 2150, 1402]})
# specify the path in which the zip file has to be stored
with zipfile.ZipFile(r'c:\Users\
# in open function specify the name in which
# the excel file has to be stored
with zf.open(r'kl_filename.xlsx', "w") as buffer:
with pd.ExcelWriter(buffer) as writer:
# use to_excel function and specify the sheet_name and
# index to store the dataframe in specified sheet
data_frame1.to_excel(writer, sheet_name="Fruits", index=False)
data_frame2.to_excel(writer, sheet_name="Vegetables", index=False)
data_frame3.to_excel(writer, sheet_name="Baked Items", index=False)
data_frame4.to_excel(writer, sheet_name="Cool Drinks", index=False)
import cx_Oracle
import pandas as pd
import datetime
dt = datetime.datetime.now()
print('Elindult : ', dt)
us_korny = 'T3'
us_nev = 'Lajos_user'
if us_korny == 'T3':
us_jsz = 'jelszo22222'
us_db = r'fx.lajos.hu:1521/EB3'
dt = datetime.datetime.now() - datetime.timedelta(days=1) ## tegnapi nap
datum = '{:%Y-%m-%d}'.format(dt)
conn = cx_Oracle.connect(us_nev ,us_jsz, us_db)
### Excel filebe mentes
# Excel írható objektum létrehozása (név tartalmazza a futás dátumot)
writer = pd.ExcelWriter(r'c:\Users\
#------------------- riport eredmény lekérdezése és excel mumklaba helyezés kezdet
#--- riport1
sql_com = """
---- Státusz
select end_date, cl_type, cl_start, cl_end, CL_STATUS, ROUND((cl_end - cl_start)*24,2) as ZAR_ORA
from ebh_kiv.kiv_status
where END_DATE >= sysdate - 7 order by 1 desc
"""
## lekérdezés futtatás
df_ora = pd.read_sql(sql_com, conn)
# Pandas dataframe excelbe írása
df_ora.to_excel(writer, sheet_name='Havi_stat', index=False)
# auto szűrő
worksheet = writer.sheets['Havi_stat']
(max_row, max_col) = df_ora.shape
# worksheet.autofilter(0, 0, max_row, max_col - 1)
column_settings = [{'header': column} for column in df_ora.columns]
if max_row > 0 :
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
worksheet.set_column(0, max_col - 1, 45) ### 45 szélesre állítása az oszlop szélességnek
#--- riport2
sql_com = """
--- hiba1
select TIMESTAMP, CALL_STACK, ERROR_STACK
from EBH_KIV.KIV_ERROR_LOG_DTL where TIMESTAMP >= sysdate - 7 order by 1 desc
"""
## lekérdezés futtatás
df_ora = pd.read_sql(sql_com, conn)
# Pandas dataframe excelbe írása
df_ora.to_excel(writer, sheet_name='Hiba 1', index=False)
# auto szűrő
worksheet = writer.sheets['Hiba 1']
(max_row, max_col) = df_ora.shape
# worksheet.autofilter(0, 0, max_row, max_col - 1)
column_settings = [{'header': column} for column in df_ora.columns]
if max_row > 0 :
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
# worksheet.set_column(0, max_col - 1, 45)
worksheet.set_column(0, 0, 20) # Column A width set to 20.
worksheet.set_column(1, 1, 165) # Columns B width set to 165.
worksheet.set_column(2, 2, 60) # Column C width set to 60.
#--- riport3
sql_com = """
--- hiba2
select LOG_DATE, JOB_NAME, STATUS, ERROR# as HIBA, ACTUAL_START_DATE, ADDITIONAL_INFO
from EBH_KIV.KIV_SCHEDULER_JOB_RUN_
"""
## lekérdezés futtatás
df_ora = pd.read_sql(sql_com, conn)
# Pandas dataframe excelbe írása
df_ora.to_excel(writer, sheet_name='Hiba 2', index=False)
# auto szűrő
worksheet = writer.sheets['Hiba 2']
(max_row, max_col) = df_ora.shape
# worksheet.autofilter(0, 0, max_row, max_col - 1)
column_settings = [{'header': column} for column in df_ora.columns]
if max_row > 0 :
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
worksheet.set_column(0, max_col - 1, 25)
#------------------- riport eredmény lekérdezése és excel munkalapba helyezés vége
# Az excel állomány mentése
writer.save()
writer.close()
conn.close()
dt = datetime.datetime.now()
print('Elkészült: ', dt)
Megjegyzések
Megjegyzés küldése