本文重点介绍根据时间段展示数据的功能，通过修改原生视图js内容实现导出Excel文件功能。使用MyBatis框架确定SQL语句，涉及实体类、Dao层、service层等代码实现。前端页面需准备相关js文件，最后给出ECharts的展示代码。
功能：
- 1.拥有(一)中的全部功能。
- 2.根据时间段展示（本篇文章的重点）。
最终运行图：
说明：这个视图表不能单独根据时间进行数据筛选，这是通过修改原生视图js内容，实现导出Excel文件功能，后续在改进。
实现：
<select id="queryAllByDate" resultType="io.yjkf.entity.EqdbEntity">
select * from eq_db1
<where>
<!-- 这里不要在添加stime!="",否则后面将会报String与Data数据类型转换错-->
<if test="stime!=null">
AND `ctime` <![CDATA[>=]]>#{stime}
</if>
<if test="etime!=null">
AND `ctime` <![CDATA[<=]]>#{etime}
</if>
</where>
</select>
- 实体类。
*先在这里说明一下，以下将展示两个实体类型，你别感到疑惑，一个是与数据库对应的，另一个是与前端ECharts折线图进行交互的容器实体类（可以理解为一个工具实体类）。*代码如下：
package io.yjkf.entity;
import java.io.Serializable;
import java.util.Date;
public class EqdbEntity implements Serializable{
/**
* 主实体类
*/
private static final long serialVersionUID = 1L;
private Date ctime;
private Long c1;
private Long c2;
private Long c3;
private Long c4;
private Long c5;
private Long c6;
public Date getCtime() {
return ctime;
}
public void setCtime(Date ctime) {
this.ctime = ctime;
}
public Long getC1() {
return c1;
}
public void setC1(Long c1) {
this.c1 = c1;
}
public Long getC2() {
return c2;
}
public void setC2(Long c2) {
this.c2 = c2;
}
public Long getC3() {
return c3;
}
public void setC3(Long c3) {
this.c3 = c3;
}
public Long getC4() {
return c4;
}
public void setC4(Long c4) {
this.c4 = c4;
}
public Long getC5() {
return c5;
}
public void setC5(Long c5) {
this.c5 = c5;
}
public Long getC6() {
return c6;
}
public void setC6(Long c6) {
this.c6 = c6;
}
}
package io.yjkf.entity;
//工具实体类
public class EqdbDateVo {
private String[] date;
private Long[] count;
private Long[] count2;
private Long[] count3;
private Long[] count4;
private Long[] count5;
private Long[] count6;
public Long[] getCount() {
return count;
}
public void setCount(Long[] count) {
this.count = count;
}
public String[] getDate() {
return date;
}
public void setDate(String[] date) {
this.date = date;
}
public Long[] getCount2() {
return count2;
}
public void setCount2(Long[] count2) {
this.count2 = count2;
}
public Long[] getCount3() {
return count3;
}
public void setCount3(Long[] count3) {
this.count3 = count3;
}
public Long[] getCount4() {
return count4;
}
public void setCount4(Long[] count4) {
this.count4 = count4;
}
public Long[] getCount5() {
return count5;
}
public void setCount5(Long[] count5) {
this.count5 = count5;
}
public Long[] getCount6() {
return count6;
}
public void setCount6(Long[] count6) {
this.count6 = count6;
}
}
- Dao层不啰嗦：
List<EqdbEntity> queryAllByDate(@Param("stime") Date stime,@Param("etime") Date etime);
-
service层和dao层一样
-
service实现类：
@Override
public List<EqdbEntity> queryAllByDate(Date stime, Date etime) {
List<EqdbEntity> list=new ArrayList<EqdbEntity>();
list=eqdbDao.queryAllByDate(stime, etime);
return list;
}
- 交互控制层（其实很简单，你能看懂的）
@ResponseBody
@RequestMapping(value="/eqtest",method=RequestMethod.POST)
@RequiresPermissions("sys:eqdb:test")
public EqdbDateVo toDataAnalysis(@RequestParam("stime") String stime, @RequestParam("etime") String etime){
List<EqdbEntity> list=new ArrayList<EqdbEntity>();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date date1;
Date date2;
if("".equals(stime)){
stime=null;
date1=simpleDateFormat.parse("2019-04-06 00:00:00");
}else{
date1=simpleDateFormat.parse(stime);
}
if("".equals(etime)){
etime=null;
date2=simpleDateFormat.parse("2019-05-06 00:00:00");
}else{
date2=simpleDateFormat.parse(etime);
}
list=eqdbService.queryAllByDate(date1, date2);
} catch (ParseException e) {
e.printStackTrace();
}
String[] date = new String[list.size()];
Long[] count = new Long[list.size()];
Long[] count2=new Long[list.size()];
Long[] count3 = new Long[list.size()];
Long[] count4 = new Long[list.size()];
Long[] count5 = new Long[list.size()];
Long[] count6 = new Long[list.size()];
int i=0;
for(EqdbEntity eq:list){
count[i] = eq.getC1();
count2[i] = eq.getC2();
count3[i] = eq.getC3();
count4[i] = eq.getC4();
count5[i] = eq.getC5();
count6[i] = eq.getC6();
date[i] = simpleDateFormat.format(eq.getCtime());//将list中的日期付给date
i++;
}
EqdbDateVo eqdbDateVo=new EqdbDateVo();
eqdbDateVo.setCount(count);
eqdbDateVo.setCount2(count2);
eqdbDateVo.setCount3(count3);
eqdbDateVo.setCount4(count4);
eqdbDateVo.setCount5(count5);
eqdbDateVo.setCount6(count6);
eqdbDateVo.setDate(date);
return eqdbDateVo;
}
- 前端页面：
- 准备条件：echarts-all.js、jquery.js、jquery.table2excel.js、WdatePicker.js（my97时间控件）。
这里有需要者，我会帮你们上传至我的gitHub。
普通html或jsp页面
<form id="time" action="../sys/echarts/eqtest">
<div id="rrapp">
<div style="text-align: center">
<input id="stime" name="stime" style="height:32px" class="Wdate" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss',readOnly:true,maxDate:'#F{$dp.$D(\'etime\')}'})" placeholder="开始时间"/>
-
<input id="etime" name="etime" style="height:32px" class="Wdate" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss',readOnly:true,minDate:'#F{$dp.$D(\'stime\')}',startDate:'#F{$dp.$D(\'stime\',{d:+1})}'})" placeholder="结束时间"/>
<input type="submit" class="btn btn-default" value="查询" onclick="return loadData()"/>
<br>
</div>
<div id="myDiv" ></div>
<div id="myLineDiv" style="height:500px;"></div>
在这里附上我的时间文本框样式：
<style type="text/css">
.Wdate{
box-sizing: border-box;
text-align:center;
font-size:1em;
border-radius:4px;
width:18%;
}
/* .Wdate:focus{
border:1px solid #ff7496;
} */
</style>
最后是ECharts的展示代码（花点时间，你能看懂的，加油，就快要成功了）：
<script type="text/javascript">
/*<!--这个是折现图-->*/
/* $("button[name='xvc']").on({
click: function () {
loadData(option);
}
}); */
/*<!--这个是折现图-->*/
var myChart = echarts.init(document.getElementById('myLineDiv'));
window.onload=loadData();
function loadData() {
$.ajax({
type : 'post', //传输类型
//async : false, //同步执行
url : '../sys/echarts/eqtest', //web.xml中注册的Servlet的url-pattern
data : $('#time').serialize(),
dataType : 'json', //返回数据形式为json
success:function(json){
var count=json.count;
var count2=json.count2;
var count3=json.count3;
var count4=json.count4;
var count5=json.count5;
var count6=json.count6;
var date=json.date;
var option = {
backgroundColor: '#FBFBFB',
tooltip: {
trigger: 'axis'
},
legend: {
orient:'vertical',
left:'left',
data: ['c1', 'c2','c3','c4','c5','c6']
},
calculable: true,
xAxis: [{
name:'日期',
type: 'category',
data: date
}],
yAxis: [{
name:'数值',
type: 'value',
axisLine: {
lineStyle: {
color: 'black'
}
}
}],
series: [{
name: 'c1',
type: 'line',
symbol: 'none',
smooth: 0.2,
data: count
}, {
name: 'c2',
type: 'line',
symbol: 'none',
smooth: 0.2,
data: count2
},{
name: 'c3',
type: 'line',
symbol: 'none',
smooth: 0.2,
data: count3
},{
name: 'c4',
type: 'line',
symbol: 'none',
smooth: 0.2,
data: count4
},{
name: 'c5',
type: 'line',
symbol: 'none',
smooth: 0.2,
data: count5
},{
name: 'c6',
type: 'line',
symbol: 'none',
smooth: 0.2,
data: count6
}],
toolbox: { //功能指标
feature: {
dataView:{
show:true, title:'数据视图',lang:['数据视图','关闭','导出Excel'],
contentToOption:function(opts){
$("#tableExcel_Day").table2excel({
exclude:".noExl", //过滤位置的CSS类名
filename:'测试数据'+".xls",
exclude_img:true,
exclude_links:true,
exclude_inputs:true
});
},
optionToContent:function(opt){
var axisData=opt.xAxis[0].data;
var series=opt.series;
var tdHeads='<td style="padding:0 10px">ctime</td>';
var tdBodys=''; // 表数据
//组装表头
var nameData=new Array('c1','c2','c3','c4','c5','c6');
for(var i=0;i<nameData.length;i++){
tdHeads+='<td style="padding: 0 10px">' + nameData[i] + '</td>';
}
var table = '<table id="tableExcel_Day" border="0" class="table-bordered table-striped" style="width:100%;text-align:center" ><tbody><tr>' + tdHeads + ' </tr>';
//组装表数据
for(var i = 0, l = axisData.length; i < l; i++){
for (var j = 0; j < series.length ; j++) {
var temp = series[j].data[i];
if (temp != null && temp != undefined) {
tdBodys += '<td>' + temp.toFixed(2) + '</td>';
} else {
tdBodys += '<td></td>';
}
}
table += '<tr><td style="padding: 0 10px">' + axisData[i] + '</td>' + tdBodys + '</tr>';
tdBodys = '';
}
table += '</tbody></table>';
return table;
}
},
dataZoom: {
yAxisIndex: 'none'
},
restore: {},
saveAsImage: {}
}
},
dataZoom:[ //这是定义横纵的伸缩
{
type: 'slider',
xAxisIndex: 0,
handleIcon: 'M10.7,11.9v-1.3H9.3v1.3c-4.9,0.3-8.8,4.4-8.8,9.4c0,5,3.9,9.1,8.8,9.4v1.3h1.3v-1.3c4.9-0.3,8.8-4.4,8.8-9.4C19.5,16.3,15.6,12.2,10.7,11.9z M13.3,24.4H6.7V23h6.6V24.4z M13.3,19.6H6.7v-1.4h6.6V19.6z',
handleSize: '80%',
handleStyle: {
color: '#fff',
borderColor: '#0e84de'
},
filterMode: 'empty'
},
{
type: 'slider',
yAxisIndex: 0,
handleIcon: 'M10.7,11.9v-1.3H9.3v1.3c-4.9,0.3-8.8,4.4-8.8,9.4c0,5,3.9,9.1,8.8,9.4v1.3h1.3v-1.3c4.9-0.3,8.8-4.4,8.8-9.4C19.5,16.3,15.6,12.2,10.7,11.9z M13.3,24.4H6.7V23h6.6V24.4z M13.3,19.6H6.7v-1.4h6.6V19.6z',
handleSize: '80%',
handleStyle: {
color: '#fff',
borderColor: '#0e84de'
},
filterMode: 'empty'
},
{
type: 'inside',
xAxisIndex: 0,
filterMode: 'empty'
},
{
type: 'inside',
yAxisIndex: 0,
filterMode: 'empty'
}
],
};
myChart.setOption(option);
}
});
return false;
}
</script>
参考过的文档：https://blog.youkuaiyun.com/weixin_39725742/article/details/81904488
很感谢他。
本博文由于在项目工程下进行的测试案列，不易分割，上面内容也已经很全了。
最后如果大家有什么问题或帮助，欢迎提问！
扫一扫
7061
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
