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

短命女 2022-12-08 01:43 44阅读 0赞

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZVdpc2VfbHp5_size_16_color_FFFFFF_t_70][]

### 前端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是其他图形的，） ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZVdpc2VfbHp5_size_16_color_FFFFFF_t_70 1][]

### 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" >
    
    <mapper namespace="com.example.charts.DataMapper">
    
        
        
        <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;
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZVdpc2VfbHp5_size_16_color_FFFFFF_t_70]: /images/20221123/7ea405bcc1ad4dd4ae6d784e9a96aa8c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1RoZVdpc2VfbHp5_size_16_color_FFFFFF_t_70 1]: /images/20221123/a3c2941edad64a40acbb0853de742544.png