需求描述：
目前有一些報(bào)表類的excel文檔數(shù)據(jù)，數(shù)據(jù)量非常多，不方便查看和搜索。希望將這些所有的歷史數(shù)據(jù)存在數(shù)據(jù)庫(kù)，并在前端顯示，同時(shí)提供搜索和繪圖功能，方便查看。
實(shí)際的表涉及隱私且數(shù)據(jù)量大就不放出來了，簡(jiǎn)化版如下：
下表中除‘合計(jì)’一列不用讀取外，其它數(shù)據(jù)都需要讀取。
image.png
功能梳理如下：
- 設(shè)計(jì)數(shù)據(jù)庫(kù)（sqlite）
- 從現(xiàn)有的報(bào)表類的excel文檔記錄中讀取數(shù)據(jù)（openpyxl），存到數(shù)據(jù)庫(kù)
- 使用flask實(shí)現(xiàn)從數(shù)據(jù)庫(kù)拿所有數(shù)據(jù)，并顯示在前端
- 前端提供根據(jù)某幾個(gè)字段搜索的功能，顯示搜索后的條目
- 前端展示搜索后條目的折線圖（echarts）
實(shí)現(xiàn)步驟如下
一，設(shè)計(jì)數(shù)據(jù)庫(kù)
根據(jù)excel表，設(shè)計(jì)數(shù)據(jù)庫(kù)如下。projects存項(xiàng)目基本信息，entries存每個(gè)項(xiàng)目每月的數(shù)據(jù)。
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
);
創(chuàng)建一個(gè)訪問數(shù)據(jù)庫(kù)的類，可提供數(shù)據(jù)庫(kù)相關(guān)的操作，包括：
- 連接數(shù)據(jù)庫(kù)：connect_db()
- 初始化數(shù)據(jù)庫(kù)（新建表）：init_db()
- 保存項(xiàng)目相關(guān)信息到projects： save_project(data)
- 保存項(xiàng)目每月數(shù)據(jù)到entries：save_entry(data)
- 根據(jù)project的信息從數(shù)據(jù)獲取entries的數(shù)據(jù)：getEntries(cur)
二，openpyxl讀excel文檔，存數(shù)據(jù)庫(kù)
這里記錄一下openpyxl常用的命令：
excel = openpyxl.load_workbook(xlpath, data_only=True)
# 讀文檔，xlpath為文檔路徑，data_only為True表示只讀數(shù)據(jù)，比如有些單元格是公式算出來的，這里就會(huì)讀取算出來的值
excel.worksheets # 獲取文檔所有的表
sheet.sheet_state # sheet表的狀態(tài)，比如visible，hidden
excel.close() # 關(guān)閉文檔
mergeCells = xl.merged_cells.ranges # 獲取表xl中合并的單元格范圍
mergeCells[0].bounds # 返回min_col, min_row, max_col, max_row；也可直接.min_col這樣讀取
從excel讀數(shù)據(jù)并存到數(shù)據(jù)庫(kù)的代碼如下：
import openpyxl
import accessDB
def openExcel(xlpath):
# 讀取xlsx文檔，返回excel對(duì)象
excel = openpyxl.load_workbook(xlpath, data_only=True)
return excel
# 從表xl中獲取數(shù)據(jù)
def getData(xl):
projects = []
entries = [] # 按照數(shù)據(jù)庫(kù)設(shè)計(jì)定義這兩個(gè)變量
db = accessDB.MyDB().connect_db() # 連接數(shù)據(jù)庫(kù)
# 獲取數(shù)據(jù)庫(kù)中projects/entries表當(dāng)前的條數(shù)，+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中合并的單元格范圍
# 判斷第一個(gè)合并單元格的范圍（第一個(gè)合并的范圍一般是我們需要讀的‘公司’，比如test表中的‘北京’）。需讀取的最大行賦值給lines
# 如果這個(gè)范圍的min_col==max_col，則讀取1到max_row這些行數(shù)的數(shù)據(jù)即可；不是，則讀取1到min_row-1這些行數(shù)的數(shù)據(jù)。
if mergeCells[0].min_col == mergeCells[0].max_col:
lines = mergeCells[0].max_row
else:
lines = mergeCells[0].min_row - 1
# 對(duì)合并的單元格處理。比如：合并單元格范圍為A2:A4，值為aaa，直接讀A3和A4時(shí)會(huì)拿到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行道最大行讀數(shù)據(jù)
project = [project_id]
for i in range(0, 19): # 只讀19列的數(shù)據(jù)
entry = [entry_id]
if i < 6: # 前6列的數(shù)據(jù)存在project中
if row[i].value is None:
row[i].value = 'null'
project.append(row[i].value)
elif i > 6 & i < 19: # 從7到18列的數(shù)據(jù)存在entry中
if row[i].value is None:
row[i].value = 0
entry.append(project_id)
entry.append(i-6) # 存月份
entry.append(row[i].value) # 存月份對(duì)應(yīng)的值
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() # 初始化數(shù)據(jù)庫(kù)。后續(xù)如果是單純添加數(shù)據(jù)，不用這一步
for sheet in excel.worksheets:
if sheet.sheet_state == 'visible': # 只讀visible的表
projects, entries = getData(sheet)
for project in projects:
myDB.save_project(project) # 保存到數(shù)據(jù)庫(kù)中projects表
for entry in entries:
myDB.save_entry(entry) # 保存到數(shù)據(jù)庫(kù)中entries表
excel.close()
三，flask實(shí)現(xiàn)從數(shù)據(jù)庫(kù)拿數(shù)據(jù)，顯示在前端
flask的運(yùn)用是直接在官網(wǎng)實(shí)例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()
# 從數(shù)據(jù)庫(kù)獲取各搜索框需要顯示的內(nèi)容。從數(shù)據(jù)庫(kù)獲取時(shí)使用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)
對(duì)應(yīng)的在show_entries.html頁(yè)面上修改展示代碼
<table border="1" class="table table-bordered">
<tr><th>#</th><th>公司</th><th>分場(chǎng)</th><th>項(xiàng)目期</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" >
四，搜索功能
搜索功能，須在前端獲取搜索的文本，然后進(jìn)行post請(qǐng)求。
@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)
# 根據(jù)搜索出來的數(shù)據(jù)整理繪圖數(shù)據(jù)
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)
前端代碼中，下拉選項(xiàng)框中的數(shù)據(jù)從allCompany這些數(shù)據(jù)獲取就可以了。
（剛開始只簡(jiǎn)單的做了個(gè)文本框去搜索，會(huì)更簡(jiǎn)單，不需從數(shù)據(jù)庫(kù)獲取下拉選項(xiàng)框的內(nèi)容，不過當(dāng)數(shù)據(jù)量很大時(shí)，對(duì)用戶來說就很麻煩了）
五，折線圖
使用echarts實(shí)現(xiàn)，官方下載后包放在本地項(xiàng)目static文件夾即可。html中加入以下代碼。
<script src="{{ url_for('static', filename='echarts.min.js') }}"></script>
echarts代碼從官網(wǎng)實(shí)例中拷貝過來在改改就ok了。主要是折線圖的數(shù)據(jù)傳入。
{% autoescape false %}
{% if session.search_entry %}
<!-- 為ECharts準(zhǔn)備一個(gè)具備大小（寬高）的Dom -->
<div id="main" style="width: 1200px;height:400px;"></div>
<script type="text/javascript">
// 基于準(zhǔn)備好的dom，初始化echarts實(shí)例
var myChart = echarts.init(document.getElementById('main'));
// 折線圖的數(shù)據(jù)傳入
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]
});
}
// 指定圖表的配置項(xiàng)和數(shù)據(jù)
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
};
// 使用剛指定的配置項(xiàng)和數(shù)據(jù)顯示圖表。
myChart.setOption(option);
</script>
{% endif %}
{% endautoescape %}
因?yàn)閒lask中數(shù)據(jù)傳到前端會(huì)有一個(gè)自動(dòng)轉(zhuǎn)義的過程，導(dǎo)致單引號(hào)雙引號(hào)會(huì)被轉(zhuǎn)義成"和'，json格式的數(shù)據(jù)就處理不了了。
這里將代碼放在{% autoescape false %} {% endautoescape %}之間可取消自動(dòng)轉(zhuǎn)義，就可以處理json數(shù)據(jù)了。
六，最終效果
本地運(yùn)行后，訪問127.0.0.1:5000如下圖，和excel中的數(shù)據(jù)一致。
image.png
選擇下拉框，搜索：北京-分1-一期，點(diǎn)擊Search按鈕后顯示如下
image.png
選擇下拉框，搜索：北京-分2，點(diǎn)擊Search按鈕后顯示如下
image.png
另外加了個(gè)reset按鈕重置搜索，會(huì)重新顯示所有數(shù)據(jù)。
