需求描述：
目前有一些報(bào)表類的excel文檔數(shù)據(jù)漫贞，數(shù)據(jù)量非常多蔗牡，不方便查看和搜索西疤。希望將這些所有的歷史數(shù)據(jù)存在數(shù)據(jù)庫蔓腐，并在前端顯示蛇耀，同時提供搜索和繪圖功能，方便查看今膊。
實(shí)際的表涉及隱私且數(shù)據(jù)量大就不放出來了季二，簡化版如下：
下表中除‘合計(jì)’一列不用讀取外，其它數(shù)據(jù)都需要讀取拒啰。
image.png
功能梳理如下：
- 設(shè)計(jì)數(shù)據(jù)庫（sqlite）
- 從現(xiàn)有的報(bào)表類的excel文檔記錄中讀取數(shù)據(jù)（openpyxl）驯绎，存到數(shù)據(jù)庫
- 使用flask實(shí)現(xiàn)從數(shù)據(jù)庫拿所有數(shù)據(jù)，并顯示在前端
- 前端提供根據(jù)某幾個字段搜索的功能谋旦，顯示搜索后的條目
- 前端展示搜索后條目的折線圖（echarts）
實(shí)現(xiàn)步驟如下
一剩失，設(shè)計(jì)數(shù)據(jù)庫
根據(jù)excel表，設(shè)計(jì)數(shù)據(jù)庫如下册着。projects存項(xiàng)目基本信息拴孤，entries存每個項(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)建一個訪問數(shù)據(jù)庫的類甲捏，可提供數(shù)據(jù)庫相關(guān)的操作演熟，包括：
- 連接數(shù)據(jù)庫：connect_db()
- 初始化數(shù)據(jù)庫（新建表）：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ù)庫
這里記錄一下openpyxl常用的命令：
excel = openpyxl.load_workbook(xlpath, data_only=True)
# 讀文檔芒粹，xlpath為文檔路徑，data_only為True表示只讀數(shù)據(jù)免猾，比如有些單元格是公式算出來的是辕，這里就會讀取算出來的值
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ù)庫的代碼如下：
import openpyxl
import accessDB
def openExcel(xlpath):
# 讀取xlsx文檔，返回excel對象
excel = openpyxl.load_workbook(xlpath, data_only=True)
return excel
# 從表xl中獲取數(shù)據(jù)
def getData(xl):
projects = []
entries = [] # 按照數(shù)據(jù)庫設(shè)計(jì)定義這兩個變量
db = accessDB.MyDB().connect_db() # 連接數(shù)據(jù)庫
# 獲取數(shù)據(jù)庫中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中合并的單元格范圍
# 判斷第一個合并單元格的范圍（第一個合并的范圍一般是我們需要讀的‘公司’疙教，比如test表中的‘北京’）。需讀取的最大行賦值給lines
# 如果這個范圍的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
# 對合并的單元格處理裸弦。比如：合并單元格范圍為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行道最大行讀數(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) # 存月份對應(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ù)庫。后續(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ù)庫中projects表
for entry in entries:
myDB.save_entry(entry) # 保存到數(shù)據(jù)庫中entries表
excel.close()
三窖贤，flask實(shí)現(xiàn)從數(shù)據(jù)庫拿數(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ù)庫獲取各搜索框需要顯示的內(nèi)容。從數(shù)據(jù)庫獲取時使用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)
對應(yīng)的在show_entries.html頁面上修改展示代碼
<table border="1" class="table table-bordered">
<tr><th>#</th><th>公司</th><th>分場</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請求所森。
@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ù)獲取就可以了夯接。
（剛開始只簡單的做了個文本框去搜索，會更簡單纷妆，不需從數(shù)據(jù)庫獲取下拉選項(xiàng)框的內(nèi)容盔几，不過當(dāng)數(shù)據(jù)量很大時，對用戶來說就很麻煩了）
五掩幢，折線圖
使用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)備一個具備大惺涝（寬高）的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ù)傳到前端會有一個自動轉(zhuǎn)義的過程轮听，導(dǎo)致單引號雙引號會被轉(zhuǎn)義成"和'骗露，json格式的數(shù)據(jù)就處理不了了。
這里將代碼放在{% autoescape false %} {% endautoescape %}之間可取消自動轉(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
另外加了個reset按鈕重置搜索帆焕，會重新顯示所有數(shù)據(jù)。
