需求描述：
目前有一些报表类的excel文档数据，数据量非常多，不方便查看和搜索。希望将这些所有的历史数据存在数据库，并在前端显示，同时提供搜索和绘图功能，方便查看。
实际的表涉及隐私且数据量大就不放出来了，简化版如下：
下表中除‘合计’一列不用读取外，其它数据都需要读取。
image.png
功能梳理如下：
- 设计数据库（sqlite）
- 从现有的报表类的excel文档记录中读取数据（openpyxl），存到数据库
- 使用flask实现从数据库拿所有数据，并显示在前端
- 前端提供根据某几个字段搜索的功能，显示搜索后的条目
- 前端展示搜索后条目的折线图（echarts）
实现步骤如下
一，设计数据库
根据excel表，设计数据库如下。projects存项目基本信息，entries存每个项目每月的数据。
create table projects (
project_id integer primary key autoincrement,
company string not null,
sites string not null,
phase string not null,
capacity real not null,
prod_date string not null,
t_year integer not null
);
create table entries (
entry_id integer primary key autoincrement,
project_id integer not null,
t_month string not null,
r_num REAL not null
);
创建一个访问数据库的类，可提供数据库相关的操作，包括：
- 连接数据库：connect_db()
- 初始化数据库（新建表）：init_db()
- 保存项目相关信息到projects： save_project(data)
- 保存项目每月数据到entries：save_entry(data)
- 根据project的信息从数据获取entries的数据：getEntries(cur)
二，openpyxl读excel文档，存数据库
这里记录一下openpyxl常用的命令：
excel = openpyxl.load_workbook(xlpath, data_only=True)
# 读文档，xlpath为文档路径，data_only为True表示只读数据，比如有些单元格是公式算出来的，这里就会读取算出来的值
excel.worksheets # 获取文档所有的表
sheet.sheet_state # sheet表的状态，比如visible，hidden
excel.close() # 关闭文档
mergeCells = xl.merged_cells.ranges # 获取表xl中合并的单元格范围
mergeCells[0].bounds # 返回min_col, min_row, max_col, max_row；也可直接.min_col这样读取
从excel读数据并存到数据库的代码如下：
import openpyxl
import accessDB
def openExcel(xlpath):
# 读取xlsx文档，返回excel对象
excel = openpyxl.load_workbook(xlpath, data_only=True)
return excel
# 从表xl中获取数据
def getData(xl):
projects = []
entries = [] # 按照数据库设计定义这两个变量
db = accessDB.MyDB().connect_db() # 连接数据库
# 获取数据库中projects/entries表当前的条数，+1后得到将要使用的id
project_id = db.cursor().execute('select count(*) from projects').fetchall()[0][0] + 1
entry_id = db.cursor().execute('select count(*) from entries').fetchall()[0][0] + 1
mergeCells = xl.merged_cells.ranges # 获取表xl中合并的单元格范围
# 判断第一个合并单元格的范围（第一个合并的范围一般是我们需要读的‘公司’，比如test表中的‘北京’）。需读取的最大行赋值给lines
# 如果这个范围的min_col==max_col，则读取1到max_row这些行数的数据即可；不是，则读取1到min_row-1这些行数的数据。
if mergeCells[0].min_col == mergeCells[0].max_col:
lines = mergeCells[0].max_row
else:
lines = mergeCells[0].min_row - 1
# 对合并的单元格处理。比如：合并单元格范围为A2:A4，值为aaa，直接读A3和A4时会拿到null；这里处理是将A2的值赋给A3和A4
for i in mergeCells:
# print(i.bounds) # min_col, min_row, max_col, max_row
for j in range(i.min_row, i.max_row):
xl.cell(row=j + 1, column=i.min_col).value = xl.cell(row=i.min_row, column=i.min_col).value
for row in list(xl.rows)[1: lines]: # 从第1行道最大行读数据
project = [project_id]
for i in range(0, 19): # 只读19列的数据
entry = [entry_id]
if i < 6: # 前6列的数据存在project中
if row[i].value is None:
row[i].value = 'null'
project.append(row[i].value)
elif i > 6 & i < 19: # 从7到18列的数据存在entry中
if row[i].value is None:
row[i].value = 0
entry.append(project_id)
entry.append(i-6) # 存月份
entry.append(row[i].value) # 存月份对应的值
entry_id = entry_id + 1
print(entry)
entries.append(entry)
project_id = project_id + 1
# print(project)
projects.append(project)
return projects, entries
excel = openExcel('test.xlsx')
myDB = accessDB.MyDB()
myDB.init_db() # 初始化数据库。后续如果是单纯添加数据，不用这一步
for sheet in excel.worksheets:
if sheet.sheet_state == 'visible': # 只读visible的表
projects, entries = getData(sheet)
for project in projects:
myDB.save_project(project) # 保存到数据库中projects表
for entry in entries:
myDB.save_entry(entry) # 保存到数据库中entries表
excel.close()
三，flask实现从数据库拿数据，显示在前端
flask的运用是直接在官网实例flaskr上修改的
# -*- coding: utf-8 -*-
# all the imports
from flask import Flask, request, session, g, redirect, url_for, abort, \
render_template, flash,jsonify
import accessDB
# create application
app = Flask(__name__)
app.config.from_pyfile('FLASKR_SETTINGS.py')
@app.before_request
def before_request():
g.myDB = accessDB.MyDB()
g.db = g.myDB.connect_db()
# 从数据库获取各搜索框需要显示的内容。从数据库获取时使用distinct去重
g.allCompany = g.myDB.allCompany()
g.allSites = g.myDB.allSites()
g.allPhase = g.myDB.allPhase()
g.allYear = g.myDB.allYear()
@app.route('/')
def show_entries():
session['search_entry'] = False
cur = g.db.execute('select * from projects order by project_id asc').fetchall()
entries = g.myDB.getEntries(cur)
return render_template('show_entries.html', entries=entries, allCompany=g.allCompany, allSites=g.allSites, \
allPhase=g.allPhase, allYear=g.allYear)
对应的在show_entries.html页面上修改展示代码
<table border="1" class="table table-bordered">
<tr><th>#</th><th>公司</th><th>分场</th><th>项目期</th><th>容量</th><th>日期</th><th>年</th>
{% for m in range(1,13) %}
<th>{{m}}月</th>
{% endfor %}
</tr>
{% for entry in entries %}
<tr>
{% for i in entry %}
<td>{{ i }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
为了使表格更好看，引入了bootstrap，在html的head里加入以下代码即可。也可下载bootstrap的包放在本地。
<link rel="stylesheet" href="//apps.bdimg.com/libs/bootstrap/3.3.0/css/bootstrap.min.css">
四，搜索功能
搜索功能，须在前端获取搜索的文本，然后进行post请求。
@app.route('/search', methods=['GET', 'POST'])
def search_entry(test=None):
session['search_entry'] = True
key_name = '%'+request.form['company']+'%'
key_site = '%'+request.form['sites']+'%'
key_phase = '%'+request.form['phase']+'%'
key_year = '%'+request.form['year']+'%'
cur = g.db.execute('select * from projects where company like ? and sites like ? and phase like ? \
and t_year like ? order by project_id asc', [key_name, key_site, key_phase, key_year]).fetchall()
entries = g.myDB.getEntries(cur)
# 根据搜索出来的数据整理绘图数据
name = []
data = []
for entry in entries:
na = entry[1]+entry[2]+str(entry[6])+entry[3]
name.append(na)
da = []
for i in range(7, 19):
da.append(entry[i])
data.append(da)
chartData = {'name':name, 'data':data}
return render_template('show_entries.html', chartData=chartData, entries=entries, allCompany=g.allCompany, allSites=g.allSites, \
allPhase=g.allPhase, allYear=g.allYear)
前端代码中，下拉选项框中的数据从allCompany这些数据获取就可以了。
（刚开始只简单的做了个文本框去搜索，会更简单，不需从数据库获取下拉选项框的内容，不过当数据量很大时，对用户来说就很麻烦了）
五，折线图
使用echarts实现，官方下载后包放在本地项目static文件夹即可。html中加入以下代码。
<script src="{{ url_for('static', filename='echarts.min.js') }}"></script>
echarts代码从官网实例中拷贝过来在改改就ok了。主要是折线图的数据传入。
{% autoescape false %}
{% if session.search_entry %}
<!-- 为ECharts准备一个具备大小（宽高）的Dom -->
<div id="main" style="width: 1200px;height:400px;"></div>
<script type="text/javascript">
// 基于准备好的dom，初始化echarts实例
var myChart = echarts.init(document.getElementById('main'));
// 折线图的数据传入
var result = {{chartData}};
var series = [];
for(var i=0;i<result['name'].length;i++){
series.push({
name: result['name'][i],
type: 'line',
data: result['data'][i]
});
}
// 指定图表的配置项和数据
var option = {
tooltip: {
trigger: 'axis'
},
legend: {
data:result['name']
},
grid: {
left: '3%',
right: '4%',
bottom: '3%',
containLabel: true
},
xAxis: {
type: 'category',
boundaryGap: false,
data: ['一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月']
},
yAxis: {
type: 'value'
},
series: series
};
// 使用刚指定的配置项和数据显示图表。
myChart.setOption(option);
</script>
{% endif %}
{% endautoescape %}
因为flask中数据传到前端会有一个自动转义的过程，导致单引号双引号会被转义成"和'，json格式的数据就处理不了了。
这里将代码放在{% autoescape false %} {% endautoescape %}之间可取消自动转义，就可以处理json数据了。
六，最终效果
本地运行后，访问127.0.0.1:5000如下图，和excel中的数据一致。
image.png
选择下拉框，搜索：北京-分1-一期，点击Search按钮后显示如下
image.png
选择下拉框，搜索：北京-分2，点击Search按钮后显示如下
image.png
另外加了个reset按钮重置搜索，会重新显示所有数据。
