本文主要是介绍【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="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", //返回数据形式为jsonsuccess : 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 {@AutowiredDataMapper 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 {@AutowiredDataMapper dataMapper;public void insql(String filename) {try {// 构造 Workbook 对象，execelFile 是传入文件路径(获得Excel工作区)Workbook book = null;try {// Excel 2007 + WPS的exclebook = new XSSFWorkbook(new FileInputStream(filename));} catch (Exception ex) {// Excel 2003book = 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 {@Autowiredprivate 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 blocke.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 {@Autowiredprivate salesService saleService;@RequestMapping("/show")public String show(){return "demo";}@ResponseBody@RequestMapping("/list")public List<sales> listAll() {List<sales> sale = saleService.listALL();return sale;}
}
这篇关于【Echarts】上传excle存入数据库，并画出对应柱状图。的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
