功能:
- 1.展示mysql中的数据
- 2.导出Echarts报表,生成.xls文件
最终运行图:
项目整体目录:
sqlyog内容:
实力类代码:
package com.zh.bean;
public class Cxx {
private String cxx;
private int c1;
private int c2;
private int c3;
private int c4;
private int c5;
private int c6;
public String getCxx() {
return cxx;
}
public void setCxx(String cxx) {
this.cxx = cxx;
}
public int getC1() {
return c1;
}
public void setC1(int c1) {
this.c1 = c1;
}
public int getC2() {
return c2;
}
public void setC2(int c2) {
this.c2 = c2;
}
public int getC3() {
return c3;
}
public void setC3(int c3) {
this.c3 = c3;
}
public int getC4() {
return c4;
}
public void setC4(int c4) {
this.c4 = c4;
}
public int getC5() {
return c5;
}
public void setC5(int c5) {
this.c5 = c5;
}
public int getC6() {
return c6;
}
public void setC6(int c6) {
this.c6 = c6;
}
}
持久层:
package com.zh.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import com.zh.bean.Cxx;
public class CxxDao {
public List<Cxx> query() {
ArrayList<Cxx> barArr = new ArrayList<Cxx>();
try {
//JDBC方式连接MySQL数据库
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test?characterEncoding=utf8", "root", "yj123456");
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM c1");
ResultSet rs = stmt.executeQuery();
//将test表中的数据存储到线性表中
while(rs.next()) {
Cxx bar = new Cxx();
bar.setCxx(rs.getString("cxx"));
bar.setC1(rs.getInt("a1"));
bar.setC2(rs.getInt("a2"));
bar.setC3(rs.getInt("a3"));
bar.setC4(rs.getInt("a4"));
bar.setC5(rs.getInt("a5"));
bar.setC6(rs.getInt("a6"));
barArr.add(bar);
}
//关闭连接
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return barArr;
}
}
控制层:
package com.zh.service;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONArray;
import com.zh.bean.Cxx;
import com.zh.dao.CxxDao;
public class CxxService extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
//创建DAO
CxxDao cxxDao = new CxxDao();
//从数据库里取数据
ArrayList<Cxx> cxxArr = (ArrayList<Cxx>) cxxDao.query();
//设置服务器响应时向JSP表示层传输数据的编码格式
resp.setContentType("text/html; charset=utf-8");
//ArrayList对象转化为JSON对象
JSONArray json = JSONArray.fromObject(cxxArr);
//控制台显示JSON
System.out.println(json.toString());
//返回到JSP
PrintWriter writer = resp.getWriter();
writer.println(json);
writer.flush();
//关闭输出流
writer.close();
}
}
视图层:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName()
+ ":" + request.getServerPort() + path + "/";
%>
<!DOCTYPE>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Hello ECharts</title>
<!-- 使用单文件引入的方式使用ECharts.JS -->
<script src="echarts-all.js"></script>
<script type="text/javascript" src="http://echarts.baidu.com/gallery/vendors/echarts/echarts.min.js"></script>
<script src="jquery.js"></script>
<script src="jquery.table2excel.js"></script>
</head>
<body style="height: 100%; margin: 0">
<!-- DOM用来设置展示ECharts图表的区域 -->
<div id="myDiv" ></div>
<div id="myLineDiv" style="height:400px;"></div>
<div id="myLineDiv2" style="height:400px"></div>
<input type="button" name="Button" value="打印" onclick="window.print()"/>
<script type="text/javascript">
<!--这个是折现图-->
function loadData(option) {
$.ajax({
type : 'post', //传输类型
async : false, //同步执行
url : 'cxx.do', //web.xml中注册的Servlet的url-pattern
data : {},
dataType : 'json', //返回数据形式为json
success : function(result) {
if (result) {
//初始化xAxis[0]的data
option.xAxis[0].data = [];
for (var i=0; i<result.length; i++) {
option.xAxis[0].data.push(result[i].cxx);
}
//初始化series[0]的data
option.series[0].data = [];
for (var i=0; i<result.length; i++) {
option.series[0].data.push(result[i].c1);
}
option.series[1].data = [];
for (var i=0; i<result.length; i++) {
option.series[1].data.push(result[i].c2);
}
option.series[2].data = [];
for (var i=0; i<result.length; i++) {
option.series[2].data.push(result[i].c3);
}
option.series[3].data = [];
for (var i=0; i<result.length; i++) {
option.series[3].data.push(result[i].c4);
}
option.series[4].data = [];
for (var i=0; i<result.length; i++) {
option.series[4].data.push(result[i].c5);
}
option.series[5].data = [];
for (var i=0; i<result.length; i++) {
option.series[5].data.push(result[i].c6);
}
}
},
error : function(errorMsg) {
alert("加载数据失败");
}
});//AJAX
}//loadData()
var myChart = echarts.init(document.getElementById('myLineDiv'));
var option = {
title: {
text: 'ECharts技术(折线图)'
},
tooltip : {
//show : true
trigger: 'axis'
},
legend : {
data : [ 'c1' ,'c2','c3','c4','c5','c6']
},
rid:{
left:'3%',
right:'4%',
bottom:'3%',
containLable:true
},
xAxis : [ {
type : 'category'
} ],
yAxis : [ {
type : 'value',
splitLine: {
show: false
}
} ],
toolbox: {
right:'20px',
feature: {
dataView:{
show:true,
title:'数据视图',
//readOnly:true, //设置只读,会隐藏刷新按钮
lang:['数据视图','关闭','导出Excel'],
contentToOption:function(opts){
$("#tableExcel_Day").table2excel({
exclude:".noExl", //过滤位置的css类名
filename:'测试数据'+".xls", //文件名称
name:"Excel Document Name.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">cxx</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>';
// console.log(table);
return table;
}
},
dataZoom: { show: true, title: { zoom: '区域缩放', back: '区域缩放还原' } },
restore: {},
saveAsImage: {}
}
},
dataZoom:[
{
type: 'slider',
xAxisIndex: 0,
/* start: 0,
end:5, */
filterMode: 'empty'
},
{
type: 'slider',
yAxisIndex: 0,
filterMode: 'empty'
}
],
series : [ {
name : 'c1',
type : 'line',
markLine: {
silent: true,
data: [{
yAxis :20
},{
yAxis :30
},{
yAxis :40
},{
yAxis :60
},{
yAxis :80
},{
yAxis :100
},
]
}
} ,
{
name : 'c2',
type : 'line',
markLine: {
silent: true,
data: [{
yAxis :20
},{
yAxis :30
},{
yAxis :40
},{
yAxis :60
},{
yAxis :80
},{
yAxis :100
},
]
}
},
{
name : 'c3',
type : 'line',
markLine: {
silent: true,
data: [{
yAxis :20
},{
yAxis :30
},{
yAxis :40
},{
yAxis :60
},{
yAxis :80
},{
yAxis :100
},
]
}
},
{
name : 'c4',
type : 'line',
markLine: {
silent: true,
data: [{
yAxis :20
},{
yAxis :30
},{
yAxis :40
},{
yAxis :60
},{
yAxis :80
},{
yAxis :100
},
]
}
},{
name : 'c5',
type : 'line',
markLine: {
silent: true,
data: [{
yAxis :20
},{
yAxis :30
},{
yAxis :40
},{
yAxis :60
},{
yAxis :80
},{
yAxis :100
},
]
}
},{
name : 'c6',
type : 'line',
markLine: {
silent: true,
data: [{
yAxis :20
},{
yAxis :30
},{
yAxis :40
},{
yAxis :60
},{
yAxis :80
},{
yAxis :100
},
]
}
}
]
};
//加载数据到option
loadData(option);
//设置option
myChart.setOption(option);
</script>
</body>
web.xml路径控制:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
<display-name>echarts_demo</display-name>
<welcome-file-list>
<welcome-file>test.jsp</welcome-file>
</welcome-file-list>
<!-- 注册servlet -->
<servlet>
<servlet-name>cxxService</servlet-name>
<servlet-class>com.zh.service.CxxService</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>cxxService</servlet-name>
<url-pattern>/cxx.do</url-pattern>
</servlet-mapping>
</web-app>
文章已发表源码:
https://download.csdn.net/download/qq_43251032/11151158
ECharts3官网实例:
https://echarts.baidu.com/
ECharts3.0与java的后端数据交互(二):
https://blog.csdn.net/qq_43251032/article/details/89924977