Skip to content

Commit e336c56

Browse files
yidao620cyidao620c
committed
mysql数据导出excel
1 parent 5f34875 commit e336c56

File tree

2 files changed

+301
-1
lines changed

2 files changed

+301
-1
lines changed

‎basic/samples/excel/excel_mysql.py renamed to ‎basic/samples/excel/excel_to_mysql.py

Lines changed: 31 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,36 @@
1414
import mysql.connector
1515
from mysql.connector import errorcode
1616

17+
class_map = {
18+
7: '事务所',
19+
5: '其他',
20+
1: '个人',
21+
2: '税务机关',
22+
3: '企业',
23+
4: '经销商',
24+
6: '集团客户',
25+
8: '公安'
26+
}
27+
enterprise_type_map = {
28+
1: '国有企业',
29+
2: '集体企业',
30+
3: '股份合作企业',
31+
4: '联营企业',
32+
5: '有限责任公司',
33+
6: '股份有限公司',
34+
7: '私营企业',
35+
8: '其他企业',
36+
9: '合资经营企业（港或澳、台资）',
37+
10: '合作经营企业（港或澳、台资）',
38+
11: '港、澳、台商独资经营企业',
39+
12: '港、澳、台商投资股份有限公司',
40+
13: '中外合资经营企业',
41+
14: '中外合作经营企业',
42+
15: '外资企业',
43+
16: '外商投资股份有限公司',
44+
17: '个体工商户'
45+
}
46+
1747
sql_create1 = """
1848
CREATE TABLE t_enterprise (
1949
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
@@ -197,7 +227,7 @@ def xlsx_to_table(xlsx_name):
197227
except:
198228
conn_.rollback()
199229
logging.exception('-------更新企业联系信息Exception,line={}-------'.format(i))
200-
handle_wrong_line(conn_, cursor,d2, ty=2)
230+
handle_wrong_line(conn_, cursor, d2, ty=2)
201231
if i % 50 == 0:
202232
conn_.commit()
203233
conn_.commit()

‎basic/samples/excel/mysql_to_excel.py

Lines changed: 270 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,270 @@
1+
#!/usr/bin/env python
2+
# -*- encoding: utf-8 -*-
3+
"""
4+
Topic: 从数据库中过滤出广州的企业，然后导出为excel格式
5+
Desc :
6+
"""
7+
8+
import sys
9+
import copy
10+
from openpyxl import Workbook
11+
from openpyxl import load_workbook
12+
from openpyxl.compat import range
13+
from openpyxl.cell import get_column_letter
14+
from openpyxl.drawing import Image
15+
from openpyxl.writer.dump_worksheet import WriteOnlyCell
16+
from openpyxl.comments import Comment
17+
from openpyxl.styles import Style, PatternFill, Border, Side, Alignment, Protection, Font, Color
18+
from openpyxl.styles import colors, borders, fills
19+
20+
import logging
21+
import datetime
22+
import mysql.connector
23+
from mysql.connector import errorcode
24+
25+
customer_type_map = {
26+
7: '事务所',
27+
5: '其他',
28+
1: '个人',
29+
2: '税务机关',
30+
3: '企业',
31+
4: '经销商',
32+
6: '集团客户',
33+
8: '公安'
34+
}
35+
enterprise_type_map = {
36+
1: '国有企业',
37+
2: '集体企业',
38+
3: '股份合作企业',
39+
4: '联营企业',
40+
5: '有限责任公司',
41+
6: '股份有限公司',
42+
7: '私营企业',
43+
8: '其他企业',
44+
9: '合资经营企业（港或澳、台资）',
45+
10: '合作经营企业（港或澳、台资）',
46+
11: '港、澳、台商独资经营企业',
47+
12: '港、澳、台商投资股份有限公司',
48+
13: '中外合资经营企业',
49+
14: '中外合作经营企业',
50+
15: '外资企业',
51+
16: '外商投资股份有限公司',
52+
17: '个体工商户'
53+
}
54+
55+
logging.basicConfig(level=logging.INFO,
56+
format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
57+
datefmt='%Y-%m-%d %H:%M:%S')
58+
_log = logging.getLogger('app.' + __name__)
59+
60+
SQL_ID_PARENTID = """
61+
DELIMITER $$
62+
CREATE FUNCTION getChildLst (rootId BIGINT)
63+
RETURNS VARCHAR (1000) DETERMINISTIC
64+
BEGIN
65+
DECLARE sTemp VARCHAR (1000) ;
66+
DECLARE sTempChd VARCHAR (1000) ;
67+
SET sTemp = '$' ;
68+
SET sTempChd = cast(rootId AS CHAR) ;
69+
WHILE sTempChd IS NOT NULL DO
70+
SET sTemp = concat(sTemp, ',', sTempChd) ;
71+
SELECT group_concat(id) INTO sTempChd
72+
FROM
73+
t_region
74+
WHERE
75+
FIND_IN_SET(parent_id, sTempChd) > 0 ;
76+
END WHILE ;
77+
RETURN sTemp ;
78+
END$$
79+
DELIMITER ;
80+
"""
81+
82+
SQL_SELECT_REGION_ID = """
83+
SELECT getChildLst(%s) as rid;
84+
"""
85+
86+
SQL_SELECT_GZ = """
87+
SELECT
88+
A.id AS id,
89+
A.name AS name,
90+
A.tax_code AS tax_code,
91+
A.region_id AS region_id,
92+
A.customer_type AS customer_type,
93+
A.enterprise_type AS enterprise_type,
94+
A.address AS address,
95+
A.postcode AS postcode,
96+
A.tel AS tel,
97+
A.contact AS contact,
98+
A.fax AS fax,
99+
A.mobile AS mobile,
100+
B.region_code as region_code,
101+
B.regian_name as regian_name,
102+
B.note as note,
103+
B.parent_id as parent_id
104+
FROM t_enterprise A
105+
LEFT OUTER JOIN t_region B ON A.region_id=B.id
106+
WHERE B.id IN ({})
107+
"""
108+
109+
110+
def _connect():
111+
config = {
112+
'user': 'root',
113+
'password': 'mysql',
114+
'host': '192.168.203.95',
115+
'database': 'hangxin',
116+
'raise_on_warnings': True,
117+
}
118+
cnx = None
119+
try:
120+
cnx = mysql.connector.connect(**config)
121+
except mysql.connector.Error as err:
122+
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
123+
print("Something is wrong with your user name or password")
124+
elif err.errno == errorcode.ER_BAD_DB_ERROR:
125+
print("Database does not exist")
126+
else:
127+
print(err)
128+
if cnx:
129+
cnx.close()
130+
return cnx
131+
132+
133+
def load_gz_data():
134+
_log.info('开始从数据库中加载数据')
135+
conn = _connect()
136+
cur = conn.cursor()
137+
# 广州的region_id为2152
138+
cur.execute(SQL_SELECT_REGION_ID, (2152,))
139+
rids = cur.fetchall()[0][0]
140+
print('rids----{}'.format(rids))
141+
cur.execute(SQL_SELECT_GZ.format(rids[2:]))
142+
result = [list(r) for r in cur.fetchall()]
143+
cur.close()
144+
conn.close()
145+
for v in result:
146+
if v[4]:
147+
v[4] = customer_type_map[int(v[4])]
148+
if v[5]:
149+
v[5] = enterprise_type_map[int(v[5])]
150+
_log.info('数据库中加载数据完毕')
151+
return result
152+
153+
154+
def export_to_excel(db_data, xlsx_name):
155+
"""导出到excel文件中"""
156+
_log.info('开始导出到excel文件中')
157+
border = Border(
158+
left=Side(border_style=borders.BORDER_THIN, color='FF000000'),
159+
right=Side(border_style=borders.BORDER_THIN, color='FF000000'),
160+
top=Side(border_style=borders.BORDER_THIN, color='FF000000'),
161+
bottom=Side(border_style=borders.BORDER_THIN, color='FF000000')
162+
)
163+
alignment = Alignment(horizontal='justify',
164+
vertical='bottom',
165+
text_rotation=0,
166+
wrap_text=False,
167+
shrink_to_fit=True,
168+
indent=0)
169+
fill = PatternFill(fill_type=None, start_color='FFFFFFFF')
170+
# 基本的样式
171+
basic_style = Style(font=Font(name='Microsoft YaHei')
172+
, border=border, alignment=alignment
173+
, fill=fill)
174+
header_style = basic_style.copy(
175+
font=Font(name='Microsoft YaHei', b=True, size=15, color='00215757'),
176+
fill=PatternFill(fill_type=fills.FILL_SOLID, start_color='00BAA87F'))
177+
common_style = basic_style.copy()
178+
wb = Workbook()
179+
ws = wb.create_sheet(index=0, title='enterprises-{}'.format(len(db_data)))
180+
181+
ws['A1'] = 'id'
182+
ws['A1'].style = common_style
183+
ws['B1'] = 'name'
184+
ws['B1'].style = common_style
185+
ws['C1'] = 'tax_code'
186+
ws['C1'].style = common_style
187+
ws['D1'] = 'region_id'
188+
ws['D1'].style = common_style
189+
ws['E1'] = 'customer_type'
190+
ws['E1'].style = common_style
191+
ws['F1'] = 'enterprise_type'
192+
ws['F1'].style = common_style
193+
ws['G1'] = 'address'
194+
ws['G1'].style = common_style
195+
ws['H1'] = 'postcode'
196+
ws['H1'].style = common_style
197+
ws['I1'] = 'tel'
198+
ws['I1'].style = common_style
199+
ws['J1'] = 'contact'
200+
ws['J1'].style = common_style
201+
ws['K1'] = 'fax'
202+
ws['K1'].style = common_style
203+
ws['L1'] = 'mobile'
204+
ws['L1'].style = common_style
205+
ws['M1'] = 'region_code'
206+
ws['M1'].style = common_style
207+
ws['N1'] = 'regian_name'
208+
ws['N1'].style = common_style
209+
ws['O1'] = 'note'
210+
ws['O1'].style = common_style
211+
ws['P1'] = 'parent_id'
212+
ws['P1'].style = common_style
213+
214+
ws.column_dimensions['A'].width = 20
215+
ws.column_dimensions['B'].width = 40
216+
ws.column_dimensions['C'].width = 20
217+
ws.column_dimensions['D'].width = 10
218+
ws.column_dimensions['E'].width = 20
219+
ws.column_dimensions['F'].width = 20
220+
ws.column_dimensions['G'].width = 80
221+
ws.column_dimensions['H'].width = 18
222+
ws.column_dimensions['I'].width = 40
223+
ws.column_dimensions['J'].width = 20
224+
ws.column_dimensions['K'].width = 20
225+
ws.column_dimensions['L'].width = 40
226+
ws.column_dimensions['M'].width = 20
227+
ws.column_dimensions['N'].width = 20
228+
229+
for i, row in enumerate(db_data):
230+
ws['A{}'.format(i + 2)] = row[0]
231+
ws['A{}'.format(i + 2)].style = common_style
232+
ws['B{}'.format(i + 2)] = row[1]
233+
ws['B{}'.format(i + 2)].style = common_style
234+
ws['C{}'.format(i + 2)] = row[2]
235+
ws['C{}'.format(i + 2)].style = common_style
236+
ws['D{}'.format(i + 2)] = row[3]
237+
ws['D{}'.format(i + 2)].style = common_style
238+
ws['E{}'.format(i + 2)] = row[4]
239+
ws['E{}'.format(i + 2)].style = common_style
240+
ws['F{}'.format(i + 2)] = row[5]
241+
ws['F{}'.format(i + 2)].style = common_style
242+
ws['G{}'.format(i + 2)] = row[6]
243+
ws['G{}'.format(i + 2)].style = common_style
244+
ws['H{}'.format(i + 2)] = row[7]
245+
ws['H{}'.format(i + 2)].style = common_style
246+
ws['I{}'.format(i + 2)] = row[8]
247+
ws['I{}'.format(i + 2)].style = common_style
248+
ws['J{}'.format(i + 2)] = row[9]
249+
ws['J{}'.format(i + 2)].style = common_style
250+
ws['K{}'.format(i + 2)] = row[10]
251+
ws['K{}'.format(i + 2)].style = common_style
252+
ws['L{}'.format(i + 2)] = row[11]
253+
ws['L{}'.format(i + 2)].style = common_style
254+
ws['M{}'.format(i + 2)] = row[12]
255+
ws['M{}'.format(i + 2)].style = common_style
256+
ws['N{}'.format(i + 2)] = row[13]
257+
ws['N{}'.format(i + 2)].style = common_style
258+
ws['O{}'.format(i + 2)] = row[14]
259+
ws['O{}'.format(i + 2)].style = common_style
260+
ws['P{}'.format(i + 2)] = row[15]
261+
ws['P{}'.format(i + 2)].style = common_style
262+
wb.save(filename=xlsx_name)
263+
_log.info('导出excel文件完成')
264+
265+
266+
if __name__ == '__main__':
267+
gz_data = load_gz_data()
268+
export_to_excel(gz_data, r'D:\download\20150505\gz_enterprises.xlsx')
269+
pass
270+

0 commit comments

Comments
 (0)