SpringBoot+Echarts实现请求后台数据显示饼状图，条形图+poi实现导入导出exec表
文章目录
前言
学习SpringBoot的一些基础东西
今天实现的功能是：SpringBoot+Echarts实现请求后台数据显示饼状图，条形图+poi实现导入导出exec表
提示：以下是本篇文章正文内容，下面案例可供参考
poi是什么？
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
Echarts官网: http://echarts.baidu.com/index.html
使用步骤
数据库
代码如下（示例）：
CREATE TABLE `spring`.`Untitled` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`price` double(10, 2) NULL DEFAULT NULL,
`count` int(11) NULL DEFAULT NULL,
`typeid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 180 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
ideajava代码
实体类
代码如下（示例）：
package com.domain;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.math.BigDecimal;
import lombok.Data;
/**
*
* @TableName goods
*/
@TableName(value ="goods")
@Data
public class Goods implements Serializable {
public Goods(Object[] args) {
/** DecimalFormat 用法
* https://www.jianshu.com/p/b3699d73142e
* Integer.valueOf 返回的时包装类 Integer.parseInt() 返回的是int
*/
//因为传进来的args 的赋值是从1开始的
this.id = new Double(args[0].toString()).intValue();
this.name = args[1].toString();
this.price = BigDecimal.valueOf(new Double(args[2].toString()).intValue());
this.count = Integer.parseInt(args[3].toString().substring(0,1));
this.typeid = args[4].toString();
}
public Goods() {
}
/**
*
*/
@TableId(type = IdType.AUTO)
private Integer id;
/**
*
*/
private String name;
/**
*
*/
private BigDecimal price;
/**
*
*/
private Integer count;
/**
*
*/
private String typeid;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
@Override
public boolean equals(Object that) {
if (this == that) {
return true;
}
if (that == null) {
return false;
}
if (getClass() != that.getClass()) {
return false;
}
Goods other = (Goods) that;
return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
&& (this.getName() == null ? other.getName() == null : this.getName().equals(other.getName()))
&& (this.getPrice() == null ? other.getPrice() == null : this.getPrice().equals(other.getPrice()))
&& (this.getCount() == null ? other.getCount() == null : this.getCount().equals(other.getCount()))
&& (this.getTypeid() == null ? other.getTypeid() == null : this.getTypeid().equals(other.getTypeid()));
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
result = prime * result + ((getName() == null) ? 0 : getName().hashCode());
result = prime * result + ((getPrice() == null) ? 0 : getPrice().hashCode());
result = prime * result + ((getCount() == null) ? 0 : getCount().hashCode());
result = prime * result + ((getTypeid() == null) ? 0 : getTypeid().hashCode());
return result;
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(getClass().getSimpleName());
sb.append(" [");
sb.append("Hash = ").append(hashCode());
sb.append(", id=").append(id);
sb.append(", name=").append(name);
sb.append(", price=").append(price);
sb.append(", count=").append(count);
sb.append(", typeid=").append(typeid);
sb.append(", serialVersionUID=").append(serialVersionUID);
sb.append("]");
return sb.toString();
}
}
Myapper层+service层
提示：这里使用的是mybatisPlus技术
package com.mapper;
import com.domain.Goods;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
/**
* @author liu
* @description 针对表【goods】的数据库操作Mapper
* @createDate 2022-11-29 09:38:23
* @Entity com.domain.Goods
*/
@Mapper
public interface GoodsMapper extends BaseMapper<Goods> {
}
package com.service;
import com.domain.Goods;
import com.baomidou.mybatisplus.extension.service.IService;
/**
* @author liu
* @description 针对表【goods】的数据库操作Service
* @createDate 2022-11-29 09:38:23
*/
public interface GoodsService extends IService<Goods> {
}
package com.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.domain.Goods;
import com.service.GoodsService;
import com.mapper.GoodsMapper;
import org.springframework.stereotype.Service;
/**
* @author liu
* @description 针对表【goods】的数据库操作Service实现
* @createDate 2022-11-29 09:38:23
*/
@Service
public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods>
implements GoodsService{
}
Controller层
package com.web;
import com.config.DownloadUtils;
import com.domain.Goods;
import com.service.GoodsService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* com.web
* liu
* 2022/11/29
* demo9
* 2022年11月29日09时25分
*/
@RestController
public class GoodsCollner {
@Autowired
GoodsService goodsService;
@RequestMapping("/index")
public ModelAndView index(ModelAndView modelAndView){
modelAndView.setViewName("index");
return modelAndView;
}
@RequestMapping("a")
public List a(){
List<Goods> list =goodsService.list();
return list;
}
@PostMapping("/upload")
@ResponseBody
public String upload(@RequestParam(name = "file") MultipartFile file) throws IOException {
//1.解析Excel
//1.1.根据Excel文件创建工作簿
Workbook wb = new XSSFWorkbook(file.getInputStream());
//1.2.获取Sheet
Sheet sheet = wb.getSheetAt(0);//参数：索引
//1.3.获取Sheet中的每一行，和每一个单元格
//2.获取用户数据列表
List<Goods> list = new ArrayList<>();
System.out.println("最后一行的下标 :" + sheet.getLastRowNum());
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);//根据索引获取每一个行
// System.out.println("列数"+row.getLastCellNum());
Object[] values = new Object[row.getLastCellNum()];
for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
Object value = getCellValue(cell);
values[cellNum] = value;
}
Goods user = new Goods(values);
user.setId(null);
list.add(user);
}
//3.批量保存用户
for (Goods userInfo : list) {
goodsService.save(userInfo);
}
return "SUCCESS";
}
public static Object getCellValue(Cell cell) {
//1.获取到单元格的属性类型
CellType cellType = cell.getCellType();
//2.根据单元格数据类型获取数据
Object value = null;
switch (cellType) {
case STRING:
value = cell.getStringCellValue();
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
//日期格式
value = cell.getDateCellValue();
} else {
//数字
value = cell.getNumericCellValue();
}
break;
case FORMULA: //公式
value = cell.getCellFormula();
break;
default:
break;
}
return value;
}
/**
* 导出数据 提前制作好的excel模板
*/
@GetMapping("exportTemplate")
public ModelAndView exportTemplate(HttpServletResponse response) throws IOException {
//1.获取数据
//人为构造的数据，实际是要从数据库中查的
List<Goods> users = goodsService.list();
//2.加载模板
Resource resource = new ClassPathResource("static/aa.xlsx");
FileInputStream fis = new FileInputStream(resource. getFile());
//3.根据模板创建工作簿
Workbook wb = new XSSFWorkbook(fis);
//4.读取工作表
Sheet sheet = wb.getSheetAt(0);
//5.抽取第2行的公共样式 , 因为第一行 为标题 第2行是数据 下标为1
Row row = sheet.getRow(0);
CellStyle styles[] = new CellStyle[row.getLastCellNum()];
Cell cell = null;
for (int i = 0; i < row.getLastCellNum(); i++) {
cell = row.getCell(i);
styles[i] = cell.getCellStyle();
}
//6.构造单元格
int rowIndex = 1;
for (Goods user : users) {
//创建每一行，同excel的第二行开始
row = sheet.createRow(rowIndex++);
//第一列
cell = row.createCell(0);
//设置单元格样式
cell.setCellStyle(styles[0]);
//写入数据 序号
cell.setCellValue(user.getId());
//第2列
cell = row.createCell(1);
cell.setCellStyle(styles[1]);
cell.setCellValue(user.getName());
cell = row.createCell(2);
cell.setCellStyle(styles[2]);
cell.setCellValue(String.valueOf(user.getPrice()));
cell = row.createCell(3);
cell.setCellStyle(styles[3]);
cell.setCellValue(user.getCount());
cell = row.createCell(4);
cell.setCellStyle(styles[4]);
cell.setCellValue(user.getTypeid());
}
//7.下载
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
String fileName = "用户信息-" + new Date().getTime() + ".xlsx";
new DownloadUtils().download(os, response, fileName);
ModelAndView modelAndView=new ModelAndView();
modelAndView.setViewName("index");
return modelAndView;
}
}
Html页面
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="/echarts.js"></script>
<script src="/jquery-3.3.1.min.js"></script>
</head>
<body>
<a th:href="@{/exportTemplate}">导出</a>
<form th:action="@{/upload}" method="post" enctype="multipart/form-data">
<input type="file" name="file">
<input type="submit" value="提交">
</form>
<!-- 为 ECharts 准备一个具备大小（宽高）的 DOM -->
<div id="main" style="width: 600px;height:400px;"></div>
<div id="main1" style="width: 600px;height:400px;"></div>
<script>
$(function (){
// 基于准备好的dom，初始化echarts实例
var myChart = echarts.init(document.getElementById('main'));
var myChart1 = echarts.init(document.getElementById('main1'));
// 指定图表的配置项和数据
var names = [];
var values = [];
$.ajax({
type: "get",
async: true, //异步请求（同步请求将会锁住浏览器，用户其他操作必须等待请求完成才可以执行）
url: "/a", //请求发送到dataActiont处
data: {},
dataType: "json", //返回数据形式为json
success: function (result) {
//请求成功时执行该函数内容，result即为服务器返回的json对象
if (result) {
console.log( (result))
for (var i = 0; i < result.length; i++) {
var obj = new Object();
obj.name=result[i].typeid;
obj.value=result[i].name;
names[i] = obj;
}
myChart.hideLoading(); //隐藏加载动画
myChart.setOption(
{
title: {
text: '商品',
x: 'center'
},
tooltip: {
trigger: 'item',
formatter: "{a} <br/>{b} : {c} ({d}%)"
},
legend: {
orient: 'vertical',
left: 'left'
},
series: [
{
name: '物料来源',
type: 'pie',
radius: '55%',
center: ['50%', '60%'],
data: names,
itemStyle: {
emphasis: {
shadowBlur: 10,
shadowOffsetX: 0,
shadowColor: 'rgba(0, 0, 0, 0.5)'
}
}
}
]
}
);
}
},
error: function (errorMsg) {
//请求失败时执行该函数
alert("图表请求数据失败!");
myChart.hideLoading();
}
});//end ajax
//条形图
$.ajax({
type: "get",
async: true, //异步请求（同步请求将会锁住浏览器，用户其他操作必须等待请求完成才可以执行）
url: "/a", //请求发送到dataActiont处
data: {},
dataType: "json", //返回数据形式为json
success: function (result) {
//请求成功时执行该函数内容，result即为服务器返回的json对象
if (result) {
console.log( (result))
for (var i = 0; i < result.length; i++) {
var obj = new Object();
obj.name=result[i].count;
obj.value=result[i].count;
names[i] = obj;
values[i] = obj;
}
myChart1.hideLoading(); //隐藏加载动画
myChart1.setOption(
{
title: {
text: '商品销量'
},
tooltip: {},
legend: {
data:['销量']
},
xAxis: {
data: names
},
yAxis: {},
series: [{
name: '销量',
type: 'bar',
data: values
}]
}
);
}
},
error : function(errorMsg) {
//请求失败时执行该函数
alert("图表请求数据失败!");
myChart.hideLoading();
}
});//end ajax
});//刷新方法结束
</script>
</body>
</html>
配置文件
server:
port: 8080
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/spring?characterEncoding=utf-8&&severTimezone=utc
username: root
password: 123456
thymeleaf:
mode: HTML5
cache: false
suffix: .html
prefix: classpath:/templates/
redis:
database: 0
host: 127.0.0.1
port: 6379
jedis:
pool:
max-wait: 1000ms
max-active: 8
min-idle: 5
max-idle: 8
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
附上结构图还有页面实现
总结
以上就是今天要讲的内容，本文仅仅简单介绍了SpringBoot+Echarts实现请求后台数据显示饼状图，条形图+poi实现导入导出exec表
一个基础的实现
版权声明：本文为qq_64688786原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。