一、介绍环境

        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/2022 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

4.0.0org.springframework.bootspring-boot-starter-parent2.7.5 com.gitkekiexcel0.0.1-SNAPSHOTexcelexcel1.8org.springframework.bootspring-boot-starter-thymeleaforg.springframework.bootspring-boot-starter-webcom.mysqlmysql-connector-jruntimeorg.projectlomboklomboktrueorg.springframework.bootspring-boot-starter-testtestcn.afterturneasypoi-spring-boot-starter4.1.2cn.afterturneasypoi-base4.1.0cn.afterturneasypoi-web4.1.0cn.afterturneasypoi-annotation4.1.0com.baomidoumybatis-plus-boot-starter3.4.2com.alibabafastjson1.2.83org.springframework.bootspring-boot-maven-pluginorg.projectlomboklombok

       

       
            cn.afterturn
            easypoi-spring-boot-starter
            4.1.2
       

       
       
            cn.afterturn
            easypoi-base
            4.1.0
       
       
            cn.afterturn
            easypoi-web
            4.1.0
       
       
            cn.afterturn
            easypoi-annotation
            4.1.0
       

        4.修改yml 

mybatis-plus:configuration:map-underscore-to-camel-case: truelog-impl: org.apache.ibatis.logging.stdout.StdOutImpl
spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/jpausername: rootpassword: rootthymeleaf:cache: false

        5.创建好相对应的包

        6.实体层

@Data
public 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层

@Mapper
public interface UserMapper extends BaseMapper {
}

public interface UserService extends IService {
}

@Service
public class UserServiceImpl extends ServiceImpl 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 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 list = userService.list();try {ExcelUtils.exportExcel(list, "用户信息", "用户信息", User.class, "用户信息", response);} catch (Exception e) {e.printStackTrace();}return "success";}//图表数据获取接口@PostMapping("/chart")@ResponseBodypublic String chart(){QueryWrapper userQueryWrapper = new QueryWrapper();userQueryWrapper.select("count(*) as count,sex");userQueryWrapper.groupBy("sex");List list = userService.list(userQueryWrapper);List userList = userService.list();userList.stream().forEach(item ->{list.add(item);});String jsonString = JSON.toJSONString(list);return jsonString;}
}

        10.前端页面

柱状图
  
饼状图
 

三、测试环节