【Echarts】上传excle存入数据库，并画出对应柱状图。

最新推荐文章于 2022-11-29 16:47:03 发布

TheWise_lzy

最新推荐文章于 2022-11-29 16:47:03 发布

阅读量617

点赞数

分类专栏： Echarts 文章标签： Echarts ajax mybatis

本文链接：https://blog.csdn.net/TheWise_lzy/article/details/108634942

版权

Echarts 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

效果图：选择excel文件上传->存入数据库->返回List->显示对应图表

前端demo.html:

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>图表上传与显示</title>
    <script src="https://cdn.staticfile.org/echarts/4.3.0/echarts.min.js"></script>
    <script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.js"></script>
    <script src="https://unpkg.com/xlsx@0.13.3/dist/xlsx.full.min.js"></script>
    <script src="https://cdn.bootcss.com/FileSaver.js/2014-11-29/FileSaver.js"></script>
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
</head>
<body>
    <form method="POST" action="/file/excel" enctype="multipart/form-data">
        <input name="file" type="file" />
        <input type="submit" value="上传">
    </form>

    <div id="main" style="width: 600px;height:400px;"></div>
    <script type="text/javascript">
        var myChart = echarts.init(document.getElementById('main'));
        myChart.showLoading();  // 开启 loading 效果
        myChart.setOption({
            title: {
                text: '异步数据加载示例'
            },
            tooltip: {},
            legend: {
                data:['销量']
            },
            xAxis: {
                data: []
            },
            yAxis: {},
            series: [{
                name: '销量',
                type: 'bar',
                data: []
            }]
        });

        myChart.showLoading();    //数据加载完之前先显示一段简单的loading动画

        var names=[];    //类别数组（实际用来盛放X轴坐标值）
        var nums=[];    //销量数组（实际用来盛放Y坐标值）

        $.ajax({
            type : "post",
            async : true,            //异步请求（同步请求将会锁住浏览器，用户其他操作必须等待请求完成才可以执行）
            url : "/list",    //请求发送到TestServlet处
            data : {},
            dataType : "json",        //返回数据形式为json
            success : function(result) {
                //请求成功时执行该函数内容，result即为服务器返回的json对象
                if (result) {
                    for(var i=0;i<result.length;i++){
                        names.push(result[i].name);    //挨个取出类别并填入类别数组
                    }
                    for(var i=0;i<result.length;i++){
                        nums.push(result[i].sale);    //挨个取出销量并填入销量数组
                    }
                    myChart.hideLoading();    //隐藏加载动画
                    myChart.setOption({        //加载数据图表
                        xAxis: {
                            data: names
                        },
                        series: [{
                            // 根据名字对应到相应的系列
                            name: '销量',
                            data: nums
                        }]
                    });

                }

            },
            error : function(errorMsg) {
                //请求失败时执行该函数
                alert("图表请求数据失败!");
                myChart.hideLoading();
            }
        })
    </script>

</body>
</html>

文件目录：（有些没有用到，如bean-ResponseBean是标准响应格式，因为这相应的不多我就没用,然后index.html是其他图形的，）

1.数据库设计：mybatis

1.1对象sales：

package com.example.charts.bean;

public class sales {
    private int sid;
    private int year;
    private String name;
    private int sale;

    public sales() {}

    public sales(String name, int sale) {
        this.name = name;
        this.sale = sale;
    }

    public sales(int year, String name, int sale) {
        this.year = year;
        this.name = name;
        this.sale = sale;
    }

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getSale() {
        return sale;
    }

    public void setSale(int sale) {
        this.sale = sale;
    }
}

1.2 dbMapper.xml: 数据库映射

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!-- 映射文件，映射到对应的SQL接口 -->
<mapper namespace="com.example.charts.DataMapper">

    <!--返回的结果集，用于关联实体类属性和数据库字段 -->
    <!--如果实体类属性和数据库属性名保持一致，就不需要javaType和jdbcType（必须大写）属性 -->
    <resultMap id="sales_resultMap" type="com.example.charts.bean.sales">
        <result column="sid" property="sid" javaType="java.lang.Integer" jdbcType="INTEGER" />
        <result column="name" property="name" javaType="java.lang.String" jdbcType="VARCHAR" />
        <result column="sale" property="sale" javaType="java.lang.Integer" jdbcType="INTEGER" />
    </resultMap>

    <!-- 显示数据 -->
    <select id="listALL" resultMap="sales_resultMap">
        select * from sales order by sid
    </select>


    <!-- 插入数据 -->
    <insert id="Insert" parameterType="com.example.charts.bean.sales">
		insert into sales (name,sale)
		values (#{name}, #{sale})
	</insert>

</mapper>

1.3 DataMapper：映射的接口

package com.example.charts;

import com.example.charts.bean.sales;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;

import java.util.List;

//映射Sql，定义接口
@Mapper
@Component
public interface DataMapper {

    //显示
    List<sales> listALL();

    //插入
    void Insert(sales sa);
}

1.4 salesService：显示&插入

package com.example.charts.service;

import com.example.charts.DataMapper;
import com.example.charts.bean.sales;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class salesService {
    @Autowired
    DataMapper dataMapper;

    public List<sales> listALL(){
        return dataMapper.listALL();
    }

    public void ruleInsert(sales sale){
        dataMapper.Insert(sale);
    }
}

1.5 DataSourceConfig：数据库配置文件

package com.example.charts;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
//指定扫描的mapper接口所在的包
@MapperScan(basePackages = "com.example.charts", sqlSessionFactoryRef = "DBDataSqlSessionFactory")
public class DataSourceConfig {
    @Bean(name = "DBDataSource")
    @ConfigurationProperties(prefix="spring.datasource") //告诉自动加载配置的属性
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "DBDataSqlSessionFactory")
    public SqlSessionFactory  sqlSessionFactory(@Qualifier("DBDataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/dbMapper.xml"));
        return bean.getObject();
    }

    @Bean(name = "DBDataTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("DBDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "DBDataSqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(
            @Qualifier("DBDataSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

2.FileService：处理Excel文件，读取表格内容，调用DataMapper存入数据库。

package com.example.charts.service;

import com.example.charts.DataMapper;
import com.example.charts.bean.sales;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

@Service
public class FileService {

    @Autowired
    DataMapper dataMapper;

    public void insql(String filename) {
        try {
            // 构造 Workbook 对象，execelFile 是传入文件路径(获得Excel工作区)
            Workbook book = null;
            try {
                // Excel 2007 + WPS的excle
                book = new XSSFWorkbook(new FileInputStream(filename));
            } catch (Exception ex) {
                // Excel 2003
                book = new HSSFWorkbook(new FileInputStream(filename));
            }
            // 读取表格的第一个sheet页
            Sheet sheet = book.getSheetAt(0);
            // 总行数,从0开始
            int totalRows = sheet.getLastRowNum();
            // 循环输出表格中的内容,首先循环取出行,再根据行循环取出列
            for (int i = 1; i <= totalRows; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {    // 处理空行
                    continue;
                }
                sales sa=new sales(row.getCell(0).toString(),
                        (int)Math.ceil(Double.valueOf(row.getCell(1).toString())));//转成int类型
                dataMapper.Insert(sa); //插入数据
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

3.后端

3.1FileUploadController ：将上传的excel存到项目中（根据上传日期新建文件夹），并调用FileService。

package com.example.charts.controller;

import com.example.charts.bean.ResponseBean;
import com.example.charts.service.FileService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.UUID;


@RestController()
@RequestMapping("/file")
public class FileUploadController {

    @Autowired
    private FileService fileService;
    /**
     * 实现文件上传
     * */
    @PostMapping("/excel")
    public ResponseBean fileUpload(@RequestParam("file") MultipartFile uploadfile) {
        if (uploadfile.isEmpty()) {
            return new ResponseBean(500,"error","文件上传失败");
        }

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd/");
        String format = sdf.format(new Date());
        File file = new File(format);

        String oldName = uploadfile.getOriginalFilename();
        assert oldName != null;
        String newName = UUID.randomUUID().toString() + oldName.substring(oldName.lastIndexOf("."),oldName.length());

        String filename=file.getAbsolutePath() + File.separator + newName;
        File dest = new File(filename);

        if(!dest.isDirectory()){
            dest.mkdirs();//递归生成文件夹
        }
        try {
            uploadfile.transferTo(dest); //保存文件
            fileService.insql(filename); //调用自定义方法，把文件传给service处理
            return new ResponseBean(200,"succ",null);
        } catch (IllegalStateException | IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return new ResponseBean(500,"error","文件上传失败");
        }
    }


}

3.2 chart：也是一个controller

package com.example.charts.controller;

import com.example.charts.bean.sales;
import com.example.charts.service.salesService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;

@Controller
public class chart {

    @Autowired
    private salesService saleService;

    @RequestMapping("/show")
    public String show(){
        return "demo";
    }

    @ResponseBody
    @RequestMapping("/list")
    public List<sales> listAll() {
        List<sales> sale = saleService.listALL();
        return sale;
    }
}

TheWise_lzy

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
【Echarts】上传excle存入数据库，并画出对应柱状图。

效果图：选择excel文件上传->存入数据库->返回List->显示对应图表前端demo.html:<!DOCTYPE html><html lang="en" xmlns:th="http://www.thymeleaf.org"><head> <meta charset="UTF-8"> <title>图表上传与显示</title> <script src="http
复制链接

扫一扫