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