config包下创建一个类访问的资源到指定目录
public class WebConfig implements WebMvcConfigurer {
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
registry.addResourceHandler("/js").addResourceLocations("/js/**");
registry.addResourceHandler("/echarts").addResourceLocations("/echarts/**");
}
}
controller层
package com.itcast.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.itcast.domain.SysRole;
import com.itcast.domain.SysUser;
import com.itcast.domain.SysUserRole;
import com.itcast.dto.PieDto;
import com.itcast.dto.SysUserRoleDto;
import com.itcast.service.SysRoleService;
import com.itcast.service.SysUserRoleService;
import com.itcast.service.SysUserService;
import com.itcast.utils.UserListener;
import lombok.SneakyThrows;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* @BelongsProject: SpringBoot_poi_echarts
* @BelongsPackage: com.itcast.controller
* @Author: cp
* @CreateTime: 2022-11-26 11:20
* @Description: TODO
* @Version: 1.0
*/
@RestController
public class RoleController {
@Autowired
private SysRoleService sysRoleService;
@Autowired
private SysUserRoleService sysUserRoleService;
@Autowired
private SysUserRoleDto sysUserRoleDto;
@Autowired
private SysUserService sysUserService;
@RequestMapping("/echarts")
public SysUserRoleDto getCount(){
List<SysRole> list = sysRoleService.list();
List<Long> count = new ArrayList<>();
List<String> roleName = new ArrayList<>();
for (SysRole sysRole : list) {
roleName.add(sysRole.getRolename());
count.add(sysUserRoleService.count(new LambdaQueryWrapper<SysUserRole>().
eq(SysUserRole::getRoleid,sysRole.getId()))
);
}
sysUserRoleDto.setCount(count);
sysUserRoleDto.setRoleName(roleName);
return sysUserRoleDto;
}
@RequestMapping("/pieEcharts")
public List<PieDto> getPie(){
List<PieDto> pieDtoList = new ArrayList<>();
//查询所有角色
List<SysRole> list = sysRoleService.list();
for (SysRole sysRole : list) {
PieDto pieDto = new PieDto();
pieDto.setName(sysRole.getRolename());
pieDto.setValue(sysUserRoleService.count(new LambdaQueryWrapper<SysUserRole>().
eq(SysUserRole::getRoleid,sysRole.getId())));
pieDtoList.add(pieDto);
}
return pieDtoList;
}
/**
* 导出excel
* @param response
*/
@SneakyThrows(IOException.class)
@RequestMapping("/easyExcel")
public void easyExcel(HttpServletResponse response){
String operName = "fengzihao"; // 操作人
exportJuLiData(response, operName);
}
/**
* 导出
* @param response
* @param operName 操作人
* @return 是否成功
* @throws IOException
*/
public void exportJuLiData(HttpServletResponse response, String operName) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //设置响应内容类型
response.setCharacterEncoding("utf-8");//编码
// 设置文件名, ps:把字符串中所有的'+'替换成'%20',在URL中%20代表空格
String fileName = URLEncoder.encode("用户信息表", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//设置响应头
List<SysUser> applicantByPage = sysUserService.list();// 获取全部用户数据
ExcelWriter writer = EasyExcel.write(response.getOutputStream(), SysUser.class).build();//获取写出流
WriteSheet sheet = EasyExcel.writerSheet("用户").build();//创建表格,设置表格页名称
writer.write(applicantByPage, sheet);//读出
writer.finish();//关闭流
}
/**
* 导入本地用户userId与聚力对应userId
* @param file 导入文件
* @return 是否导入成功
* @throws Exception
*/
@PostMapping("/upload")
public String upload(@RequestBody MultipartFile file) throws Exception {
String name = file.getName();
System.out.println(name);
EasyExcel.read(file.getInputStream(), SysUser.class, new UserListener(sysUserService)).sheet().doRead();
return "success";
}
/**
* 导入
* @param file 导入文件
* @return 是否成功
* @throws IOException
*/
public String importJuLiData(MultipartFile file) throws IOException {
UserListener excelListener = new UserListener(sysUserService);
System.out.println(excelListener);
EasyExcel.read(file.getInputStream(), SysUser.class, new UserListener(sysUserService)).sheet().doRead();
return "success";
}
}
以下是三个实体类
package com.itcast.domain;
import java.io.Serializable;
import lombok.Data;
/**
*
* @TableName sys_role
*/
@Data
public class SysRole implements Serializable {
/**
*
*/
private Long id;
/**
*
*/
private String rolename;
/**
*
*/
private String roledesc;
private static final long serialVersionUID = 1L;
}
package com.itcast.domain;
import java.io.Serializable;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
/**
*
* @TableName sys_user
*/
@Data
@ContentRowHeight(18)//内容行高
@HeadRowHeight(25)//标题行高
@ColumnWidth(20)//列宽,可设置成员变量上
public class SysUser implements Serializable {
/**
*
*/
@ExcelProperty("用户编号")
private Long id;
/**
*
*/
@ExcelProperty("用户姓名")
private String username;
/**
*
*/
@ExcelProperty("用户邮箱")
private String email;
/**
*
*/
@ExcelProperty("用户密码")
private String password;
/**
*
*/
@ExcelProperty("用户手机号")
private String phonenum;
private static final long serialVersionUID = 1L;
注意这个实体类中的注解,生成表格中的列名
package com.itcast.domain;
import java.io.Serializable;
import lombok.Data;
/**
*
* @TableName sys_user_role
*/
@Data
public class SysUserRole implements Serializable {
/**
*
*/
private Long userid;
/**
*
*/
private Long roleid;
private static final long serialVersionUID = 1L;
}
mapper以及service层用的都是mybatispuls
前端页面
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<script src="../js/echarts.js" type="text/javascript"></script>
<script src="../js/axios-0.18.0.js" type="text/javascript"></script>
<style rel="stylesheet">
#main{
position: relative;
height: 400px;
width: 400px;
overflow: hidden;
}
</style>
</head>
<body>
<div id="main"></div>
<script type="text/javascript">
var chartDom = document.getElementById('main');
var myChart = echarts.init(chartDom);
var option;
option = {
title: {
text: 'Referer of a Website',
subtext: 'Fake Data',
left: 'center'
},
tooltip: {
trigger: 'item'
},
legend: {
orient: 'vertical',
left: 'left'
},
series: [
{
name: 'Access From',
type: 'pie',
radius: '50%',
data: [
{ value: 1048, name: 'Search Engine' },
{ value: 735, name: 'Direct' },
{ value: 580, name: 'Email' },
{ value: 484, name: 'Union Ads' },
{ value: 300, name: 'Video Ads' }
],
emphasis: {
itemStyle: {
shadowBlur: 10,
shadowOffsetX: 0,
shadowColor: 'rgba(0, 0, 0, 0.5)'
}
}
}
]
};
option && myChart.setOption(option);
</script>
</body>
</html>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport"
content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<style rel="stylesheet">
*{
margin: 0;
padding: 0;
}
#main{
position: relative;
height: 400px;
overflow: hidden;
}
#pie{
position: absolute;
margin-top: 20px;
overflow: hidden;
height: 400px;
width: 400px;
}
</style>
<script src="../js/echarts.js" type="text/javascript"></script>
<script src="../js/axios-0.18.0.js" type="text/javascript"></script>
</head>
<body>
<div id="main"></div>
<div id="pie"></div>
<form method="post" action="/upload" enctype="multipart/form-data">
<input type="file" name="file" value="请选择文件">
<input type="submit" value="上传">
<a href="http://localhost:8080/easyExcel">导出</a>
</form>
<script>
var myChart = echarts.init(document.getElementById("main"));
axios({
method: "post",
url: "/echarts",
}).then(resp => {
console.log(resp.data.roleName)
console.log(resp.data.count)
myChart.setOption({
title:{
text:'职位分布柱状图',
},
//x轴的数据
xAxis: {
type: 'category',
// data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
data: resp.data.roleName
},
yAxis: {
type: 'value'
},
//y轴的数据
series: [
{
// data: [150, 230, 224, 218, 135, 147, 260],
data: resp.data.count,
type: 'bar'
}
]
})
})
var pieChart = echarts.init(document.getElementById("pie"));
axios({
method: "post",
url: "/pieEcharts",
}).then(resp => {
console.log(resp.data)
console.log(resp.data)
pieChart.setOption({
title: {
text: '职位分布饼状图',
subtext: '职位',
left: 'center'
},
tooltip: {
trigger: 'item'
},
legend: {
orient: 'vertical',
left: 'left'
},
series: [
{
name: '职位',
type: 'pie',
radius: '50%',
data: resp.data
/*[
{ value: 1048, name: 'Search Engine' },
{ value: 735, name: 'Direct' },
{ value: 580, name: 'Email' },
{ value: 484, name: 'Union Ads' },
{ value: 300, name: 'Video Ads' }
]*/,
emphasis: {
itemStyle: {
shadowBlur: 10,
shadowOffsetX: 0,
shadowColor: 'rgba(0, 0, 0, 0.5)'
}
}
}
]
})
})
</script>
</body>
</html>
完事
效果展示