SpringBoot+Echarts实现请求后台数据显示饼状图，条形图+poi实现导入导出exec表

SpringBoot Echarts实现请求后台数据显示饼状图，条形图 poi实现导入导出exec表

前言

学习SpringBoot的一些基础东西

今天实现的功能是：SpringBoot Echarts实现请求后台数据显示饼状图，条形图 poi实现导入导出exec表

poi是什么？

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

Echarts官网: http://echarts.百度.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

附上结构图还有页面实现

总结

这篇好文章是转载于：学新通技术网