一、介绍环境
EasyPOI:
现在我们就来介绍下EasyPoi,首先感谢EasyPoi 的开发者。EasyPoi开源
easypoi 是为了让开发者快速的实现excel，word,pdf的导入导出，基于Apache poi基础上的一个工具包。easypoi教程
Echarts:
echarts（Enterprise Charts，商业级数据图表）是一个使用 JavaScript 实现的开源可视化库，可以流畅的运行在 PC 和移动设备上，兼容当前绝大部分浏览器（IE8/9/10/11，Chrome，Firefox，Safari等），底层依赖轻量级的矢量图形库 ZRender，提供直观，交互丰富，可高度个性化定制的数据可视化图表。echarts 官网
二、代码环节
1.导入数据库
/*Navicat Premium Data TransferSource Server : localhostSource Server Type : MySQLSource Server Version : 80013Source Host : localhost:3306Source Schema : echartsTarget Server Type : MySQLTarget Server Version : 80013File Encoding : 65001Date: 29/11/ 17:14:00*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`age` int(11) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1016 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES (1, '张三', '123', '男', 19);INSERT INTO `user` VALUES (2, '李四', '123', '男', 21);INSERT INTO `user` VALUES (3, '王五', '123', '男', 24);INSERT INTO `user` VALUES (4, '赵六', '123456', '男', 34);INSERT INTO `user` VALUES (1002, '火狐', '123456', '男', 44);INSERT INTO `user` VALUES (1003, '微三', '123456', '男', 32);INSERT INTO `user` VALUES (1004, '以某', '123123', '其他', 56);INSERT INTO `user` VALUES (1005, '中某', '123456', '女', 43);INSERT INTO `user` VALUES (1006, '霍某', '123123', '女', 21);INSERT INTO `user` VALUES (1007, '赵莫', '123456', '女', 22);INSERT INTO `user` VALUES (1008, '阿里', '123123', '男', 45);INSERT INTO `user` VALUES (1009, '腾讯', '123456', '男', 67);INSERT INTO `user` VALUES (1010, '服务', '123123', '男', 33);INSERT INTO `user` VALUES (1011, '维子', '123456', '男', 25);SET FOREIGN_KEY_CHECKS = 1;
2.创建boot项目(内容过于简单，忽略...)
3.修改pom.xml
<?xml version="1.0" encoding="UTF-8"?><project xmlns="/POM/4.0.0" xmlns:xsi="/2001/XMLSchema-instance"xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.5</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.gitkeki</groupId><artifactId>excel</artifactId><version>0.0.1-SNAPSHOT</version><name>excel</name><description>excel</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.1.2</version></dependency><!--poi--><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.1.0</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.2</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.83</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><configuration><excludes><exclude><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></exclude></excludes></configuration></plugin></plugins></build></project>
<!--核心依赖-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.2</version>
</dependency>
<!--poi-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
4.修改yml
mybatis-plus:configuration:map-underscore-to-camel-case: truelog-impl: org.apache.ibatis.logging.stdout.StdOutImplspring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/jpausername: rootpassword: rootthymeleaf:cache: false
5.创建好相对应的包
6.实体层
@Datapublic class User {@TableId(value = "id", type = IdType.AUTO)private Integer id;@Excel(name = "用户名称")private String username;@Excel(name = "用户密码")private String password;@Excel(name = "用户性别")private String sex;@Excel(name = "用户年龄")private Integer age;@TableField(exist = false)private Integer count; //用于存储分组数据}
7.mapper层、service层
@Mapperpublic interface UserMapper extends BaseMapper<User> {}
public interface UserService extends IService<User> {}
@Servicepublic class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {}
8.工具类
public class ExcelUtils {/*** excel 导出** @param list数据* @param title标题* @param sheetName sheet名称* @param pojoClass pojo类型* @param fileName 文件名称* @param response*/public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));}/*** 默认的 excel 导出** @param list 数据* @param pojoClass pojo类型* @param fileName文件名称* @param response* @param exportParams 导出参数*/private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);downLoadExcel(fileName, response, workbook);}/*** 下载** @param fileName 文件名称* @param response* @param workbook excel数据*/private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));workbook.write(response.getOutputStream());} catch (Exception e) {throw new IOException(e.getMessage());}}/*** Excel 类型枚举*/enum ExcelTypeEnum {XLS("xls"), XLSX("xlsx");private String value;ExcelTypeEnum(String value) {this.value = value;}public String getValue() {return value;}}}
9.controller控制层
@Controller@RequestMapping("/excel")public class excelController {@Autowiredprivate UserService userService;//上传文件保存接口@PostMapping("/save")public String excel(@RequestParam("file") MultipartFile file, HttpServletRequest request){try {// 准备导入的参数ImportParams params = new ImportParams();params.setTitleRows(0); //标题列占几行params.setHeadRows(1); //header列占几行List<User> list = ExcelImportUtil.importExcel(file.getInputStream(), User.class, params);list.forEach(item -> {userService.save(item);});return "redirect:/index.html";} catch (Exception e) {e.printStackTrace();return "redirect:/err.html";}}//导出文件保存接口@PostMapping("/export")@ResponseBodypublic String export(HttpServletResponse response){List<User> list = userService.list();try {ExcelUtils.exportExcel(list, "用户信息", "用户信息", User.class, "用户信息", response);} catch (Exception e) {e.printStackTrace();}return "success";}//图表数据获取接口@PostMapping("/chart")@ResponseBodypublic String chart(){QueryWrapper<User> userQueryWrapper = new QueryWrapper<User>();userQueryWrapper.select("count(*) as count,sex");userQueryWrapper.groupBy("sex");List<User> list = userService.list(userQueryWrapper);List<User> userList = userService.list();userList.stream().forEach(item ->{list.add(item);});String jsonString = JSON.toJSONString(list);return jsonString;}}
10.前端页面
<!DOCTYPE html><html lang="en" xmlns:th=""><head><meta charset="UTF-8"><title>文件导入、导出、图形展示Demo</title></head><body><form th:action="@{/excel/save}" method="post" enctype="multipart/form-data"><input type="file" name="file"><input type="submit"></form><form th:action="@{/excel/export}" method="post"><input type="submit" value="导出"></form><h3>柱状图</h3><div id="main" style="width: 1000px;height:500px;"></div> <!--柱状图--><h3>饼状图</h3><div id="mains" style="width: 600px;height:400px;"></div> <!--饼状图--></body><!--导入Echarts--><script src="/echarts/4.3.0/echarts.min.js"></script><!--导入jquery--><script src="/jquery-latest.js"></script><script>window.onload = function() {$.ajax({type: 'post',url: '/excel/chart',dataType:'json',success: function (data) {console.log(data)//存放柱状图名称var chartName = [];//存放柱状图值var chartValue = [];//存放饼状图值var sex = [];for (let i = 0; i < data.length; i++) {if (data[i].count != null){ //后台使用stream 做了添加 因此可以得到分组后的countsex.push({"value": data[i].count,"name":data[i].sex})}else{chartName.push(data[i].username);chartValue.push(data[i].age);}}//初始化状图var myChart = echarts.init(document.getElementById('main'));var option = {tooltip: {},xAxis: {data: chartName //赋值},yAxis: {},series: [{name: '数量',type: 'bar',data: chartValue //赋值}]};myChart.setOption(option);//初始化饼图var myChart = echarts.init(document.getElementById('mains'));myChart.setOption({series : [{type: 'pie',radius: '55%',data: sex //赋值}]})}})}</script></html>
