8000 Létrehozva a Colaboratory használatával · klajosw/python@60a2942 · GitHub
[go: up one dir, main page]

Skip to content
6601

Commit 60a2942

Browse files
committed
Létrehozva a Colaboratory használatával
1 parent be3cbda commit 60a2942

File tree

1 file changed

+190
-0
lines changed

1 file changed

+190
-0
lines changed

kl_py_xls_rip_chart.ipynb

Lines changed: 190 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,190 @@
1+
{
2+
"cells": [
3+
{
4+
"cell_type": "markdown",
5+
"metadata": {
6+
"id": "view-in-github",
7+
"colab_type": "text"
8+
},
9+
"source": [
10+
"<a href=\"https://colab.research.google.com/github/klajosw/python/blob/master/kl_py_xls_rip_chart.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
11+
]
12+
},
13+
{
14+
"cell_type": "markdown",
15+
"metadata": {
16+
"id": "Dkz9CMUXYPny"
17+
},
18+
"source": [
19+
"<p align=\"left\"> \n",
20+
" <img src=\"https://raw.githubusercontent.com/klajosw/python/master/kl_mie_python_logo_250.jpg\" \n",
21+
" align=\"left\" width=\"251\" height=\"251\">\n",
22+
" \n",
23+
"</p>\n",
24+
"\n",
25+
"\n",
26+
"<p> </p>\n",
27+
"\n",
28+
"# Riportok Excelbe mentése és diagram készítés \n",
29+
"\n",
30+
"## Pandas és cx_Oracle csomag használatával\n",
31+
"\n",
32+
"https://klajosw.blogspot.com/\n",
33+
"\n",
34+
"https://github.com/klajosw/\n",
35+
"\n",
36+
"---"
37+
]
38+
},
39+
{
40+
"cell_type": "code",
41+
"execution_count": null,
42+
"metadata": {
43+
"id": "ttIRVVz5YPn3"
44+
},
45+
"outputs": [],
46+
"source": [
47+
"## diagram\n",
48+
"import cx_Oracle\n",
49+
"import pandas as pd\n",
50+
"import datetime\n",
51+
"us_nev = 'klajos'\n",
52+
"us_jsz = 'Ljs'\n",
53+
"us_db = 'aix'\n",
54+
"\n",
55+
"dt = datetime.datetime.now() - datetime.timedelta(days=1) ## tegnapi nap\n",
56+
"datum = '{:%Y-%m-%d}'.format(dt)\n",
57+
"\n",
58+
"conn = cx_Oracle.connect(us_nev ,us_jsz, us_db)\n",
59+
"\n",
60+
"### Excel filebe mentes\n",
61+
"# Excel írható objektum létrehozása (név tartalmazza a futás dátumot)\n",
62+
"writer = pd.ExcelWriter(r'c:\\Users\\kl\\Documents\\xls\\kiv_xml_riport_'+datum+'_kesz.xlsx')\n",
63+
"\n",
64+
"#------------------- riport eredmény lekérdezése és excel mumklaba helyezés kezdet\n",
65+
"#--- riport1\n",
66+
"sql_com = \"\"\"\n",
67+
"select to_char(END_DATE,'YYYYMMDD') as END_DATE, \n",
68+
"count(distinct XML_FILENAME) as XML_DB, count( distinct INTERNAL_KEY) as szl_db1, count(0) as SZL_DB0 \n",
69+
"from DB_STMT_STAT where END_DATE >= sysdate - 100 \n",
70+
"group by END_DATE \n",
71+
"order by END_DATE \"\"\"\n",
72+
"\n",
73+
"\n",
74+
"## lekérdezés futtatás\n",
75+
"df_ora = pd.read_sql(sql_com, conn)\n",
76+
"\n",
77+
"# Pandas dataframe excelbe írása\n",
78+
"df_ora.to_excel(writer, sheet_name='Stat_XML', engine='xlsxwriter', index=False)\n",
79+
"\n",
80+
"workbook = writer.book\n",
81+
"worksheet = writer.sheets['Stat_XML']\n",
82+
"(max_row, max_col) = df_ora.shape\n",
83+
"\n",
84+
"# auto szűrő\n",
85+
"worksheet.autofilter(0, 0, max_row, max_col-1)\n",
86+
"\n",
87+
"# oszlop szélesítése : pld. az első oszlop 25-re állítása\n",
88+
"writer.sheets['Stat_XML'].set_column(0, 0, 25)\n",
89+
"\n",
90+
"# diagram beszúrás\n",
91+
"chart = workbook.add_chart({'type': 'line'}) ## column, pie, line, area, scatter' // circle', 'size': 7}\n",
92+
"#chart.add_series({'values': ['Stat_XML', 0, 0, 2, 3 ]})\n",
93+
"#chart.add_series({'values': '=Stat_XML!$A$1:$C$8'})\n",
94+
"#chart.add_series({'values': ['Stat_XML', 1, 1, max_row, 1]})\n",
95+
"\"\"\"\n",
96+
"----------------1.\n",
97+
"chart.add_series({\n",
98+
"'name': ['Stat_XML', 0, 2],\n",
99+
"'categories': ['Stat_XML', 1, 0, max_row, 0], ## x tengely\n",
100+
"'values' : ['Stat_XML', 1, 2, max_row, 2], ## y tengely\n",
101+
"## 'values' : ['Stat_XML', 1, 3, max_row, 3],\n",
102+
"})\n",
103+
"chart.add_series({\n",
104+
"'name': ['Stat_XML', 0, 3],\n",
105+
"'categories': ['Stat_XML', 1, 0, max_row, 0], ## x tengely\n",
106+
"'values' : ['Stat_XML', 1, 3, max_row, 3], ## y tengely\n",
107+
"## 'values' : ['Stat_XML', 1, 3, max_row, 3],\n",
108+
"})\n",
109+
"--------------- 2.\n",
110+
"chart.add_series({\n",
111+
"'name': ['Stat_XML', 0, max_col - 1],\n",
112+
"'categories': ['Stat_XML', 1, 0, max_row , 0],\n",
113+
"'values': ['Stat_XML', 1, max_col - 1, max_row , max_col - 1],\n",
114+
"#'overlap':-10,\n",
115+
"})\n",
116+
"chart.add_series({\n",
117+
"'name': ['Stat_XML', 0, max_col - 2],\n",
118+
"'categories': ['Stat_XML', 1, 0, max_row , 0],\n",
119+
"'values': ['Stat_XML', 1, max_col - 2, max_row , max_col - 2],\n",
120+
"#'overlap':-10,\n",
121+
"})\n",
122+
"chart.add_series({\n",
123+
"'name': ['Stat_XML', 0, 1],\n",
124+
"'categories': ['Stat_XML', 1, 0, max_row , 0],\n",
125+
"'values': ['Stat_XML', 1, 1, max_row , 1],\n",
126+
"#'overlap':-10,\n",
127+
"})\n",
128+
"\n",
129+
"\"\"\"\n",
130+
"## első oszlop a kategoria, tobbi oszlop adatok és az értékek \n",
131+
"for i in range(max_col):\n",
132+
" if i != 0 :\n",
133+
" chart.add_series({\n",
134+
"'name': ['Stat_XML', 0, i],\n",
135+
"'categories': ['Stat_XML', 1, 0, max_row , 0],\n",
136+
"'values': ['Stat_XML', 1, i, max_row ,i],\n",
137+
"#'overlap':-10,\n",
138+
"})\n",
139+
"\n",
140+
"\n",
141+
"\n",
142+
"worksheet.insert_chart(1, max_col +1, chart)\n",
143+
"\n",
144+
"#------------------- riport eredmény lekérdezése és excel mumklaba helyezés vége\n",
145+
"\n",
146+
"# Az excel állomány mentése és lezárás\n",
147+
"writer.save()\n",
148+
"writer.close()\n",
149+
"conn.close()\n"
150+
]
151+
},
152+
{
153+
"cell_type": "code",
154+
"execution_count": null,
155+
"metadata": {
156+
"id": "UGX_4XuHYPn5"
157+
},
158+
"outputs": [],
159+
"source": [
160+
""
161+
]
162+
}
163+
],
164+
"metadata": {
165+
"kernelspec": {
166+
"display_name": "Python 3",
167+
"language": "python",
168+
"name": "python3"
169+
},
170+
"language_info": {
171+
"codemirror_mode": {
172+
"name": "ipython",
173+
"version": 3
174+
},
175+
"file_extension": ".py",
176+
"mimetype": "text/x-python",
177+
"name": "python",
178+
"nbconvert_exporter": "python",
179+
"pygments_lexer": "ipython3",
180+
"version": "3.8.8"
181+
},
182+
"colab": {
183+
"name": "kl_py_xls_rip_chart.ipynb",
184+
"provenance": [],
185+
"include_colab_link": true
186+
}
187+
},
188+
"nbformat": 4,
189+
"nbformat_minor": 0
190+
}

0 commit comments

Comments
 (0)
0