Tip：本期使用的是SpringBoot框架整合MySQL跟Echarts使用案例和springboot+poi实现导出excel的实例，通过本次案例你能学到SpringBoot里面的Echarts如何获取MySQL里面的数据制作简单图形跟excel并导出导入的需求。
1、项目结构：
用Spring Initializr创建
添加 Lombok,Spring Web,Thymeleaf,MySQL Driver
最近springboot版本跟新了，注意要选3.0一下的版本，因为3.0的版本最低要求JDK17，目前用的是JDK1.8
先讲Echart数据展示
首先需要引入入echarts.min.js和jquery.js
<!-- 引入 echarts.js -->
<script src="https://cdn.staticfile.org/echarts/5.4.0/echarts.min.js"></script>
<script src="https://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.3.min.js"></script>再建两个div用来显示图形
<div id="main" style="width: 1000px;height:400px;"></div>
<br/>
<div id="pie" style="width: 1000px;height:400px;"></div>html界面和ajax
<!DOCTYPE html>
<html lang="zh" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="utf-8">
<title>ECharts</title>
<!-- 引入 echarts.js -->
<script src="https://cdn.staticfile.org/echarts/5.4.0/echarts.min.js"></script>
<script src="https://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.3.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
</head>
<body>
<table border="1">
<tr>
<th>编号</th>
<th>名称</th>
<th>价格</th>
<th>数量</th>
<th>类型</th>
<th>操作</th>
</tr>
<tr th:each="list:${list}">
<td th:text="${list.id}"></td>
<td th:text="${list.name}"></td>
<td th:text="${list.price}"></td>
<td th:text="${list.count}"></td>
<td th:text="${list.types.tyname}"></td>
<td><a th:href="@{'/byId/'+${list.id}}">修改</a> <a
th:href="@{'/deleteId/'+${list.id}+'/'+${list.type}}">删除</a></td>
</tr>
</table>
<a th:href="@{/downExcel}">导出数据</a>
<form th:action="@{/importExcel}" method="post" enctype="multipart/form-data">
<input type="file" name="excel">
<input type="submit" value="数据导入">
</form>
<!-- 为ECharts准备一个具备大小（宽高）的Dom -->
<div id="main" style="width: 1000px;height:400px;"></div>
<br/>
<div id="pie" style="width: 1000px;height:400px;"></div>
<script type="text/javascript">
//初始化echarts实例
var myChart1 = echarts.init(document.getElementById('main'));
myChart1.showLoading();
//指定图表的配置项和数据
var names = []; //类别数组（实际放x轴坐标值）
var nums = []; //库存数组（Y坐标值）
var price = []; //价格数组（Y坐标值）
$.ajax({
type: "get",
url: "/list",
dataType: "json",
async: false,
success: function (result) {
for (var i = 0; i < result.length; i++) {
names.push(result[i].name);
nums.push(result[i].count);
price.push(result[i].price);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(XMLHttpRequest.readyState);//当数据请求失败可以查看请求的状态
}
});
myChart1.hideLoading();//隐藏加载动画
// 指定图表的配置项和数据
var option = {
title: {
text: '超市'
},
tooltip: {},
legend: {
data: ['库存', '价格']
},
xAxis: {
data: names
},
yAxis: {},
series: [{
name: '库存',
type: 'bar',
data: nums
}
, {
name: '价格',
type: 'bar',
data: price
}]
};
// 使用刚指定的配置项和数据显示图表。
myChart1.setOption(option);
//饼图
var pieChart = echarts.init(document.getElementById("pie"));
//数据加载完之前先显示一段简单的loading动画
pieChart.showLoading();
//指定图表的配置项和数据
var list = [];
$.ajax({
type: "get",
async: false,
url: "/list",
dataType: "json",
success: function (result) {
for (var i = 0; i < result.length; i++) {
var obj = {};
obj.name = result[i].name;
obj.value = result[i].count;
list[i] = obj;
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(XMLHttpRequest.readyState);
}
});
pieChart.hideLoading();//隐藏加载动画
var option1 = {
legend: {
orient: 'vertical',
left: 'left'
},
tooltip: {},
toolbox: {
show: true,
feature: {
mark: {show: true},
dataView: {show: true, readOnly: false},
restore: {show: true},
saveAsImage: {show: true}
}
},
series: [
{
name: '库存',
type: 'pie',
radius: [25, 175],
center: ['50%', '50%'],
roseType: 'area',
itemStyle: {
borderRadius: 8
},
data: list
}
]
};
pieChart.setOption(option1);
</script>
</body>
</html>饼图需要用Object的name和value去接收值，还要定义一个list[] 接收
方法，
如果出现图像为灰色，没有显示数据原因可能是async：不是false
async: false,
从controller定义一个查询所有的方法返回到ajax
$.ajax({
type: "get",
async: false,
url: "/list",
dataType: "json",
success: function (result) {
for (var i = 0; i < result.length; i++) {
var obj = {};
obj.name = result[i].name;
obj.value = result[i].count;
list[i] = obj;
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(XMLHttpRequest.readyState);
}
});之后再把值放到
//饼图
var pieChart = echarts.init(document.getElementById("pie"));//获取div
//数据加载完之前先显示一段简单的loading动画
pieChart.showLoading();
//指定图表的配置项和数据
var list = [];
var option1 = {
legend: {
orient: 'vertical',
left: 'left'
},
tooltip: {},
toolbox: {//图形工具
show: true,
feature: {
mark: {show: true},
dataView: {show: true, readOnly: false},
restore: {show: true},
saveAsImage: {show: true}
}
},
series: [
{
name: '库存',
type: 'pie',
radius: [25, 175],
center: ['50%', '50%'],
roseType: 'area',
itemStyle: {
borderRadius: 8
},
data: list//传入数据
}
]
};
pieChart.setOption(option1); // 使用刚指定的配置项和数据显示图表。到这一步就完成了Echart数据展示，还有更多的图形再Echart的官网，大家可以去研究研究
Echarts网站：Examples - Apache ECharts
还有一个在线定制的Echarts网站：ECharts 在线构建
下面来讲poi 实现批量导入导出
引入poi依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
添加导入按钮，这里用form提交
<form th:action="@{/importExcel}" method="post" enctype="multipart/form-data">
<input type="file" name="excel">
<input type="submit" value="数据导入">
</form>
用a标签实现导出
<a th:href="@{/downExcel}">导出数据</a>工具类PageData，用来传递数据
package com.wnxy.wateraffair.util;
/**
* @author ：fengSir
* @date ：Created By 2022-08-30 10:07
* @description ：TODO
*/
import java.util.HashMap;
import java.util.Map;
public class PageData extends HashMap {
Map map = null;
public PageData() {
map = new HashMap();
}
public String getString(Object key) {
return String.valueOf( map.get(key));
}
@SuppressWarnings("unchecked")
@Override
public Object put(Object key, Object value) {
return map.put(key, value);
}
@Override
public Object remove(Object key) {
return map.remove(key);
}
public int size() {
// TODO Auto-generated method stub
return map.size();
}
}导出Excel核心处理类ObjectExcelView，继承自AbstractXlsView
package com.wnxy.wateraffair.util;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.servlet.view.document.AbstractXlsView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
public class ObjectExcelView extends AbstractXlsView {
@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
String filename = sdf.format(new Date());
//设置下载头部文件信息
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
//创建sheet，相当于一个excelsheel
Sheet sheet = workbook.createSheet("sheet1");
//得到excel标题内容
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
//设置单元格样式
CellStyle cellStyle = workbook.createCellStyle(); //标题样式
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置字体
Font headerFont = workbook.createFont(); //标题字体
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 11);
cellStyle.setFont(headerFont);
short width = 20, height = 25 * 20;
sheet.setDefaultColumnWidth(width);
//创建第一行，用来放标题
Row header = sheet.createRow(0);
for (int i = 0; i < len; i++) { //设置标题
String title = titles.get(i);
Cell cell = header.createCell(i);
cell.setCellValue(title);
cell.setCellStyle(cellStyle);
}
header.setHeight(height);
//设置内容样式
CellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
//得到controller传过来导出的数据，并填充到每一行中
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for (int i = 0; i < varCount; i++) {
Row userRow = sheet.createRow(i + 1);
PageData vpd = varList.get(i);
int mapLen = vpd.size();
for (int j = 0; j < mapLen; j++) {
String varStr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : "";
Cell cell = userRow.createCell(j);
cell.setCellValue(varStr);
cell.setCellStyle(contentStyle);
}
}
}
}
核心处理类
package com.wnxy.wateraffair.util;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
public class ObjectExcelRead {
/**
* @param filepath //文件路径
* @param filename //文件名
* @param startrow //开始行号
* @param startcol //开始列号
* @param sheetnum //sheet
* @return list
*/
public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
List<Object> varList = new ArrayList<Object>();
try {
File target = new File(filepath, filename);
FileInputStream fi = new FileInputStream(target);
HSSFWorkbook wb = new HSSFWorkbook(fi);
HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; //取得最后一行的行号
for (int i = startrow; i < rowNum; i++) { //行循环开始
PageData varpd = new PageData();
HSSFRow row = sheet.getRow(i); //行
int cellNum = row.getLastCellNum(); //每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { //列循环开始
HSSFCell cell = row.getCell(Short.parseShort(j + ""));
Object cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
case _NONE:
cellValue = String.valueOf((int) cell.getNumericCellValue());
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case NUMERIC:
cellValue = cell.getNumericCellValue() + "";
// cellValue = String.valueOf(cell.getDateCellValue());
break;
case BLANK:
cellValue = "";
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
varpd.put("var" + j, cellValue);
}
varList.add(varpd);
}
} catch (Exception e) {
e.printStackTrace();
}
return varList;
}
}上传工具类
package com.wnxy.wateraffair.util;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.IOException;
public class FileUpload {
/**
* @param file //文件对象
* @param path //上传路径
* @param name //文件名
* @return 文件名
*/
public static String fileUp(MultipartFile file, String path, String name){
//获取文件在服务器的储存位置
File filePath = new File(path);
if (!filePath.exists() && !filePath.isDirectory()) {
filePath.mkdir();
}
//获取原始文件名称(包含格式)
String originalFileName = file.getOriginalFilename();
//获取文件类型，以最后一个`.`为标识
String type = originalFileName.substring(originalFileName.lastIndexOf(".") + 1);
String fileName = name + "." + type;
//在指定路径下创建一个文件
File targetFile = new File(path, fileName);
//将文件保存到服务器指定位置
try {
file.transferTo(targetFile);
} catch (IOException e) {
e.printStackTrace();
}
return fileName;
}
}Controller功能实现
导出
@RequestMapping("downExcel")
public ModelAndView exportExcel() {
ModelAndView mv = new ModelAndView();
try {
Map<String, Object> dataMap = new HashMap<>();
List<String> titles = new ArrayList<>();
titles.add("编号");
titles.add("名称");
titles.add("价格");
titles.add("数量");
titles.add("类型");
dataMap.put("titles", titles);
List<Goods> varOList = goodsService.list();
List<PageData> varList = new ArrayList<>();
for (Goods goods : varOList) {
PageData vpd = new PageData();
vpd.put("var1", goods.getId());
vpd.put("var2", goods.getName());
vpd.put("var3", goods.getPrice());
vpd.put("var4", goods.getCount());
vpd.put("var5", goods.getType());
varList.add(vpd);
}
dataMap.put("varList", varList);
ObjectExcelView erv = new ObjectExcelView();
mv = new ModelAndView(erv, dataMap);
} catch (Exception e) {
e.printStackTrace();
}
return mv;
}导入
@RequestMapping("importExcel")
public String readExcel(
@RequestParam(value = "excel") MultipartFile file, HttpServletRequest request
) throws Exception {
System.out.println(file);
if (null != file && !file.isEmpty()) {
//文件上传路径
String filePath = request.getSession().getServletContext().getRealPath("/upload");
//执行上传
String fileName = FileUpload.fileUp(file, filePath, "userexcel");
// 执行读EXCEL操作,读出的数据导入List 2:从第2行开始；0:从第A列开始；0:第0个sheet
List<PageData> listPd = (List) ObjectExcelRead.readExcel(filePath, fileName, 1, 0, 0);
System.out.println(listPd + "--------------------------------");
for (int i = 0; i < listPd.size() - 1; i++) {
Goods user = new Goods();
String strId = listPd.get(i).getString("var3");
int id = 0;
if (strId.contains(".")) {
id = Integer.parseInt(strId.substring(0, strId.indexOf(".")));
} else {
id = Integer.parseInt(strId);
}
user.setId(null);
String var2 = listPd.get(i).getString("var1");
user.setName(var2);
BigDecimal var3 = new BigDecimal(listPd.get(i).getString("var2"));
user.setPrice(var3);
user.setCount(id);
String var4 = listPd.get(i).getString("var4");
int type = 0;
if (var4.contains(".")) {
type = Integer.parseInt(var4.substring(0, var4.indexOf(".")));
} else {
type = Integer.parseInt(var4);
}
user.setType(type);
jedisDao.delValue("select");
goodsService.save(user);
}
}
return "redirect:/index";
}（注意表格中数据格式的处理）
2685
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
