Skip to content

Commit dcd34ae

Browse files
yidao620cyidao620c
committed
添加一个操作excel xlsx的示例
1 parent 4109c7b commit dcd34ae

File tree

4 files changed

+310
-0
lines changed

4 files changed

+310
-0
lines changed

‎basic/myutils/excel/__init__.py

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
#!/usr/bin/env python
2+
# -*- encoding: utf-8 -*-
3+
"""
4+
Topic: python操作Excel 2007+ XLSX/XLSM
5+
Desc :
6+
使用OpenPyxl来操作Excel 2007 xlsx/xlsm files.
7+
OpenPyxl is a Python library to read/write Excel 2007 xlsx/xlsm files.
8+
XLSM文件XLSX文件都是excel2007文件，但前者是含有宏启用，Excel中默认情况下不自动启用宏
9+
10+
另外如果只是写文件Excel 2007+ XLSX的话，可以使用XlsxWriter库
11+
12+
如果要读/写老版本的excel文件xls，需要用xlrd这个库
13+
"""
14+

‎basic/myutils/excel/generate_schema.py

Lines changed: 128 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,128 @@
1+
#!/usr/bin/env python
2+
# -*- encoding: utf-8 -*-
3+
"""
4+
Topic: 通过一个schema.sql来生成excel表格的数据库设计文档
5+
Desc :
6+
"""
7+
from openpyxl import Workbook
8+
from openpyxl import load_workbook
9+
from openpyxl.compat import range
10+
from openpyxl.cell import get_column_letter
11+
from openpyxl.drawing import Image
12+
from openpyxl.writer.dump_worksheet import WriteOnlyCell
13+
from openpyxl.comments import Comment
14+
from openpyxl.styles import Style, PatternFill, Border, Side, Alignment, Protection, Font
15+
16+
17+
def load_xlsx():
18+
wb = Workbook()
19+
ws = wb.active
20+
ws.title = "首页列表"
21+
ws = wb['首页列表']
22+
print(wb.get_sheet_names())
23+
print(ws['D5'], ws.cell(row=5, column=4))
24+
cell_range = ws['A1':'C2']
25+
26+
wb2 = load_workbook('D:/work/MySQL数据库表.xlsx')
27+
print(wb2.get_sheet_names())
28+
29+
30+
def write_xlsx():
31+
wb = Workbook()
32+
dest_filename = 'empty_book.xlsx'
33+
ws = wb.active
34+
ws.title = "首页列表"
35+
for col_idx in range(1, 10):
36+
col = get_column_letter(col_idx)
37+
for row in range(1, 20):
38+
ws['%s%s' % (col, row)].value = '%s%s' % (col, row)
39+
ws.merge_cells('A1:B1') # 合并单元格
40+
ws.unmerge_cells('A1:B1')
41+
ws = wb.create_sheet()
42+
ws.title = 'Pi'
43+
ws['F5'] = 3.14
44+
# img = Image('logo.png')
45+
# img.drawing.top = 100
46+
# img.drawing.left = 150
47+
48+
wb.save(filename=dest_filename)
49+
50+
wb = load_workbook(filename='empty_book.xlsx')
51+
sheet_ranges = wb['首页列表']
52+
print(sheet_ranges['D18'].value)
53+
54+
55+
def write_only():
56+
wb = Workbook()
57+
ws = wb.create_sheet()
58+
ws.title = "首页列表"
59+
c = ws['A1']
60+
c.style = Style(font=Font(name='Courrier', size=36)
61+
, fill=PatternFill(fill_type=None, start_color='FFFFFFFF',
62+
end_color='FF000000')
63+
, protection=Protection(locked='inherit', hidden='inherit')
64+
, alignment=Alignment(horizontal='general', vertical='bottom',
65+
shrink_to_fit=True)
66+
, border=Border(left=Side(border_style=None, color='FF000000')))
67+
c.value = '姓名'
68+
# cell = WriteOnlyCell(ws, value="hello world")
69+
# cell.style = Style(font=Font(name='Courrier', size=36))
70+
# cell.comment = Comment(text="A comment", author="Author's Name")
71+
72+
# ws.header_footer.center_header.text = 'My Excel Page'
73+
# ws.header_footer.center_header.font_size = 14
74+
# ws.header_footer.center_header.font_name = "Tahoma,Bold"
75+
# ws.header_footer.center_header.font_color = "CC3366"
76+
wb.save(filename='empty_book.xlsx')
77+
78+
79+
def load_schema(filename):
80+
"""先加载schema.sql文件来获取所有建表语句"""
81+
result = []
82+
with open(filename, encoding='utf-8') as sqlfile:
83+
each_table = [] # 每张表定义
84+
for line in sqlfile:
85+
if line.startswith('--'):
86+
each_table.insert(0, line.split('--')[1].strip())
87+
elif ' COMMENT ' in line and 'ENGINE=' not in line:
88+
col_arr = line.split()
89+
col_name = col_arr[0]
90+
col_type = col_arr[1]
91+
if 'PRIMARY KEY' in line or 'NOT NULL' in line:
92+
col_null = 'NOT NULL'
93+
else:
94+
col_null = ''
95+
col_remark = col_arr[-1]
96+
each_table.append((col_name, col_type, col_null, col_remark))
97+
elif 'ENGINE=' in line:
98+
# 单个表定义结束
99+
result.append(list(each_table))
100+
each_table.clear()
101+
return result
102+
103+
104+
def write_dest(xlsx_name, schema_name):
105+
table_data = load_schema(schema_name)
106+
wb = Workbook()
107+
wb.active.title = "首页列表"
108+
for table in table_data:
109+
ws = wb.create_sheet(title=table[0])
110+
ws.merge_cells('E3:H3') # 合并单元格
111+
ws['E3'] = table[0]
112+
ws['E5'] = '列名'
113+
ws['F5'] = '类型'
114+
ws['G5'] = '空值约束'
115+
ws['H5'] = '备注'
116+
for idx, each_column in enumerate(table[1:]):
117+
ws['E{}'.format(idx + 6)] = each_column[0]
118+
ws['F{}'.format(idx + 6)] = each_column[1]
119+
ws['G{}'.format(idx + 6)] = each_column[2]
120+
ws['H{}'.format(idx + 6)] = each_column[3].strip().split('\'')[1]
121+
wb.save(filename=xlsx_name)
122+
123+
124+
if __name__ == '__main__':
125+
# write_xlsx()
126+
# write_only()
127+
write_dest('aaa.xlsx', 'schema.sql')
128+
pass

‎basic/myutils/excel/schema.sql

Lines changed: 167 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,167 @@
1+
-- 用户表
2+
DROP TABLE IF EXISTS t_user;
3+
CREATE TABLE t_user (
4+
id BIGINT PRIMARY KEY COMMENT '主键ID',
5+
username VARCHAR(32) NOT NULL COMMENT '用户名',
6+
password VARCHAR(32) NOT NULL COMMENT '密码',
7+
fullname VARCHAR(10) COMMENT '中文名',
8+
created_time DATETIME COMMENT '创建时间',
9+
updated_time DATETIME COMMENT '更新时间'
10+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';
11+
12+
-- 角色表
13+
DROP TABLE IF EXISTS t_role;
14+
CREATE TABLE t_role (
15+
id BIGINT PRIMARY KEY COMMENT '主键ID',
16+
name VARCHAR(30) NOT NULL COMMENT '角色名',
17+
created_time DATETIME COMMENT '创建时间',
18+
updated_time DATETIME COMMENT '更新时间'
19+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '角色表';
20+
21+
-- 用户角色表
22+
DROP TABLE IF EXISTS t_user_role;
23+
CREATE TABLE t_user_role (
24+
id BIGINT PRIMARY KEY COMMENT '主键ID',
25+
user_id BIGINT NOT NULL COMMENT '用户ID',
26+
role_id BIGINT NOT NULL COMMENT '角色ID',
27+
created_time DATETIME COMMENT '创建时间',
28+
updated_time DATETIME COMMENT '更新时间'
29+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户角色表';
30+
31+
-- 权限表
32+
DROP TABLE IF EXISTS t_privilege;
33+
CREATE TABLE t_privilege (
34+
id BIGINT PRIMARY KEY COMMENT '主键ID',
35+
name VARCHAR(30) NOT NULL COMMENT '权限名',
36+
created_time DATETIME COMMENT '创建时间',
37+
updated_time DATETIME COMMENT '更新时间'
38+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '权限表';
39+
40+
-- 角色权限表
41+
DROP TABLE IF EXISTS t_role_privilege;
42+
CREATE TABLE t_role_privilege (
43+
id BIGINT PRIMARY KEY COMMENT '主键ID',
44+
role_id BIGINT NOT NULL COMMENT '角色ID',
45+
privilege_id BIGINT NOT NULL COMMENT '权限ID',
46+
created_time DATETIME COMMENT '创建时间',
47+
updated_time DATETIME COMMENT '更新时间'
48+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '角色权限表';
49+
50+
-- 企业基本资料表
51+
DROP TABLE IF EXISTS t_company;
52+
CREATE TABLE t_company (
53+
id BIGINT PRIMARY KEY COMMENT '主键ID',
54+
name VARCHAR(80) NOT NULL COMMENT '企业名称',
55+
taxno VARCHAR(60) NOT NULL COMMENT '税号',
56+
reg_location VARCHAR(80) COMMENT '注册地',
57+
reg_time DATETIME COMMENT '注册时间',
58+
business_years INT COMMENT '经营时间，以年为单位',
59+
core_flag VARCHAR(2) COMMENT '是否核心企业：是/否',
60+
account_bank VARCHAR(30) COMMENT '开户银行',
61+
tax_amount INT COMMENT '该企业近一年的纳税额，以万为单位',
62+
comp_location VARCHAR(100) COMMENT '企业地址',
63+
area VARCHAR(30) COMMENT '所属片区',
64+
status VARCHAR(20) COMMENT '业务状态',
65+
created_time DATETIME COMMENT '创建时间',
66+
updated_time DATETIME COMMENT '更新时间'
67+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '企业基本资料表';
68+
69+
-- 企业联系方式表
70+
DROP TABLE IF EXISTS t_company_contact;
71+
CREATE TABLE t_company_contact (
72+
id BIGINT PRIMARY KEY COMMENT '主键ID',
73+
company_id BIGINT NOT NULL COMMENT '企业ID',
74+
name VARCHAR(10) COMMENT '联系人姓名',
75+
mobile_number VARCHAR(20) COMMENT '联系人电话',
76+
position VARCHAR(30) COMMENT '联系人职位',
77+
remark VARCHAR(100) COMMENT '备注',
78+
created_time DATETIME COMMENT '创建时间',
79+
updated_time DATETIME COMMENT '更新时间'
80+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '企业联系方式表';
81+
82+
-- 供应关系表
83+
DROP TABLE IF EXISTS t_supply_relation;
84+
CREATE TABLE t_supply_relation (
85+
id BIGINT PRIMARY KEY COMMENT '主键ID',
86+
company_id BIGINT NOT NULL COMMENT '企业ID',
87+
corecomp_id BIGINT NOT NULL COMMENT '所属核心企业ID',
88+
supply_years INT COMMENT '供应时间，以年为单位',
89+
start_time DATETIME COMMENT '供应开始时间',
90+
end_time DATETIME COMMENT '最近供应时间',
91+
year_amount INT COMMENT '年开票金额，以万为单位',
92+
total_amount INT COMMENT '历史开票总额，以万为单位',
93+
created_time DATETIME COMMENT '创建时间',
94+
updated_time DATETIME COMMENT '更新时间'
95+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '供应关系表';
96+
97+
-- 拜访记录表
98+
DROP TABLE IF EXISTS t_visit_record;
99+
CREATE TABLE t_visit_record (
100+
id BIGINT PRIMARY KEY COMMENT '主键ID',
101+
company_id BIGINT NOT NULL COMMENT '企业ID',
102+
corecomp_id BIGINT NOT NULL COMMENT '所属核心企业ID',
103+
status VARCHAR(10) COMMENT '拜访状态：未分配、已分配、接触中、有意向、没意向、已取数据。',
104+
customer_manager VARCHAR(10) COMMENT '客户经理名称',
105+
contact_phone VARCHAR(16) COMMENT '客户经理联系电话',
106+
no_intent_reason VARCHAR(120) COMMENT '没意向原因',
107+
created_time DATETIME COMMENT '创建时间',
108+
updated_time DATETIME COMMENT '更新时间'
109+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '拜访记录表';
110+
111+
-- 企业意向表
112+
DROP TABLE IF EXISTS t_company_intention;
113+
CREATE TABLE t_company_intention (
114+
id BIGINT PRIMARY KEY COMMENT '主键ID',
115+
company_id BIGINT NOT NULL COMMENT '企业ID',
116+
corecomp_id BIGINT NOT NULL COMMENT '所属核心企业ID',
117+
loan_usage VARCHAR(80) COMMENT '贷款用途',
118+
loan_amount INT COMMENT '贷款金额，以万为单位',
119+
loan_cycle VARCHAR(30) COMMENT '贷款周期，格式为年-月~年-月',
120+
repay_method VARCHAR(30) COMMENT '还款方式',
121+
suggest_amount INT COMMENT '建议额度，以万为单位',
122+
recommend_level VARCHAR(2) COMMENT '推荐等级，字段值为A、B、C、D',
123+
evaluation VARCHAR(120) COMMENT '评价',
124+
customer_manager VARCHAR(10) COMMENT '客户经理名称',
125+
contact_phone VARCHAR(16) COMMENT '客户经理联系电话',
126+
created_time DATETIME COMMENT '创建时间',
127+
updated_time DATETIME COMMENT '更新时间'
128+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '企业意向表';
129+
130+
-- 银行推荐表
131+
DROP TABLE IF EXISTS t_bank_recommend;
132+
CREATE TABLE t_bank_recommend (
133+
id BIGINT PRIMARY KEY COMMENT '主键ID',
134+
company_id BIGINT NOT NULL COMMENT '企业ID',
135+
corecomp_id BIGINT NOT NULL COMMENT '所属核心企业ID',
136+
accept_bank VARCHAR(30) COMMENT '受理银行',
137+
recommend_status VARCHAR(10) COMMENT '推荐状态',
138+
recommend_time DATETIME COMMENT '推荐时间',
139+
failure_reason VARCHAR(120) COMMENT '失败原因',
140+
credit_line INT COMMENT '授信额度，以万为单位',
141+
finish_time DATETIME COMMENT '完成时间',
142+
created_time DATETIME COMMENT '创建时间',
143+
updated_time DATETIME COMMENT '更新时间'
144+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '银行推荐表';
145+
146+
-- 银行认可表
147+
DROP TABLE IF EXISTS t_bank_recognize;
148+
CREATE TABLE t_bank_recognize (
149+
id BIGINT PRIMARY KEY COMMENT '主键ID',
150+
company_id BIGINT NOT NULL COMMENT '企业ID',
151+
recognize_bank VARCHAR(30) COMMENT '认可银行',
152+
created_time DATETIME COMMENT '创建时间',
153+
updated_time DATETIME COMMENT '更新时间'
154+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '银行认可表';
155+
156+
-- 上传文件记录表
157+
DROP TABLE IF EXISTS t_upload_record;
158+
CREATE TABLE t_upload_record (
159+
id BIGINT PRIMARY KEY COMMENT '主键ID',
160+
file_name VARCHAR(60) COMMENT '上传文件名称',
161+
store_location VARCHAR(80) COMMENT '上传文件的备份地址',
162+
uploader VARCHAR(10) COMMENT '上传者',
163+
upload_time DATETIME COMMENT '上传时间',
164+
created_time DATETIME COMMENT '创建时间',
165+
updated_time DATETIME COMMENT '更新时间'
166+
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '上传文件记录表';
167+

‎requirements.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,3 +12,4 @@ redis==2.10.3
1212
requests==2.4.3
1313
requests-oauthlib==0.4.2
1414
whitenoise==1.0.3
15+
openpyxl==2.2.0-b1

0 commit comments

Comments
 (0)