SpringBoot、EasyPoi、Echarts 实现文档导入、出、图表显示 (饼状图、柱状图) 保姆级教程

一、介绍环境

        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 Transfer Source Server         : localhost Source Server Type    : MySQL Source Server Version : 80013 Source Host           : localhost:3306 Source Schema         : echarts Target Server Type    : MySQL Target Server Version : 80013 File Encoding         : 65001 Date: 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

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/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: true    log-impl: org.apache.ibatis.logging.stdout.StdOutImplspring:  datasource:    driver-class-name: com.mysql.cj.jdbc.Driver    url: jdbc:mysql://localhost:3306/jpa    username: root    password: root  thymeleaf:    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 {    @Autowired    private 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")    @ResponseBody    public 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")    @ResponseBody    public 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="http://www.thymeleaf.org"><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="https://cdn.staticfile.org/echarts/4.3.0/echarts.min.js"></script><!--导入jquery--><script src="http://code.jquery.com/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 做了添加 因此可以得到分组后的count                        sex.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>

三、测试环节