ECharts与Excel的结合实战
引言：本文是一篇ECharts和Excel实战的记录。将Excel与ECharts产生火花，从Excel读取数据然后在ECharts上展示。
1.柱状图前端代码
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>echarts-bar</title><script src="https://cdn.bootcss.com/jquery/3.1.1/jquery.min.js"></script><script src="js/echarts.min.js"></script><script src="../../plugins/element-ui/index.js"></script>
</head>
<body>
<!-- 为ECharts准备一个具备大小（宽高）的Dom -->
<div id="main" style="width:2000px; height:700px;"></div><button onclick="generateExcel()">导出Excel文件</button>
<script type="text/javascript">function generateExcel() {fetch('http://localhost:8080/backend/page/eChart/excel').then(response => response.json()).then(data => {const url = window.URL.createObjectURL(new Blob([data]));const a = document.createElement('a');a.href = url;a.download = '菜品销量.xlsx'; // 你可以根据需要设置文件名a.click();});}// 基于准备好的dom，初始化echarts实例var myChart = echarts.init(document.getElementById('main'));// 指定图表的配置项和数据myChart.setOption({title: {text: '菜品销售情况柱形图'},tooltip: {},legend: {data: ['销量']},xAxis: {data: [],axisLabel: {interval: 0}},yAxis: {},series: [{name: '销量',type: 'bar',data: [],itemStyle: {color: '#91cc75',shadowColor: '#91cc75',borderType: 'dashed',opacity: 0.5,borderWidth:1},}]});myChart.showLoading();var names = []; //类别数组（实际用来盛放X轴坐标值）var nums = []; //销量数组（实际用来盛放Y坐标值）$.ajax({type: "get",async: false, //异步请求（同步请求将会锁住浏览器，用户其他操作必须等待请求完成才可以执行）url: "list", //请求发送到TestServlet处data: {},dataType: "json", //返回数据形式为jsonsuccess: function (result) {//请求成功时执行该函数内容，result即为服务器返回的json对象var data = result.data;console.log(data);if (data) {for (var i = 0; i < data.length; i++) {names.push(data[i].name); //挨个取出类别并填入类别数组}for (var i = 0; i < data.length; i++) {nums.push(data[i].count); //挨个取出销量并填入销量数组}myChart.hideLoading(); //隐藏加载动画myChart.setOption({ //加载数据图表xAxis: {data: names},series: [{// 根据名字对应到相应的系列name: '销量',data: nums}]});}},error: function () {//请求失败时执行该函数alert("图表请求数据失败!");myChart.hideLoading();}})
</script>
</body>
</html>
2.饼图前端代码
<!DOCTYPE html>
<html lang="zh-CN" xmlns:th="http://www.thymeleaf.org" style="height: 100%">
<head><meta charset="utf-8"><title>echarts-pie</title><script src="https://cdn.bootcss.com/jquery/3.1.1/jquery.min.js"></script><script src="js/echarts.min.js"></script>
</head>
<body style="height: 100%; margin: 0">
<div id="main" style="height: 100%"></div>
<button onclick="generateExcel()">导出Excel文件</button><script type="text/javascript">function generateExcel() {fetch('http://localhost:8080/backend/page/eChart/excel').then(response => response.json()).then(data => {const url = window.URL.createObjectURL(new Blob([data]));const a = document.createElement('a');a.href = url;a.download = '菜品销量.xlsx'; // 你可以根据需要设置文件名a.click();});}var mychart1 = echarts.init(document.getElementById('main'));mychart1.setOption({title: {text: '菜品销量统计饼状图',left: 'center'},tooltip: {trigger: 'item'},legend: {orient: 'vertical',left: 'left'},series: [{name: 'Access From',type: 'pie',radius: '50%',data: []}],emphasis: {itemStyle: {shadowBlur: 10,shadowOffsetX: 0,shadowColor: 'rgba(0, 0, 0, 0.5)'}}});mychart1.showLoading();var names=[];var nums=[];$.ajax({type : "get",async : false, //异步请求（同步请求将会锁住浏览器，用户其他操作必须等待请求完成才可以执行）url : "list", //请求发送到TestServlet处data : {},dataType : "json", //返回数据形式为jsonsuccess : function(result) {//请求成功时执行该函数内容，result即为服务器返回的json对象var data = result.data;if (data) {for(var i=0;i<data.length;i++){names.push(data[i].name); //挨个取出类别并填入类别数组nums[i] = {value: data[i].count,name:data[i].name};}/* for(var i=0;i<data.length;i++){nums.push(data[i].count); //挨个取出销量并填入销量数组}*/mychart1.hideLoading(); //隐藏加载动画mychart1.setOption({ //加载数据图表series: {type: 'pie',radius: '55%',center: ['50%','60%'],data: nums,emphasis: {itemStyle: {shadowBlur: 10,shadowOffsetX: 0,shadowColor: 'rgb(0,0,0,0.5)'}}},});}},error : function() {//请求失败时执行该函数alert("图表请求数据失败!");mychart1.hideLoading();}})window.addEventListener('resize', mychart1.resize);
</script>
</body>
</html>
3.后端通用代码
3.1生成Excel表格
@RequestMapping(value = "excel", method = RequestMethod.GET)public R<String> getExcel(){File file = new File(path);QueryWrapper<Dish> wrapper = new QueryWrapper<>();wrapper.select("name", "count");List<Dish> list = dishService.list(wrapper);ArrayList<Map<String, Object>> rows = new ArrayList<>();for (Dish dish : list) {Map<String, Object> row = new LinkedHashMap<>();row.put("菜品", dish.getName());row.put("销量", dish.getCount());rows.add(row);}// 通过工具类创建writerExcelWriter writer = ExcelUtil.getWriter(path);// 合并单元格后的标题行，使用默认标题样式writer.merge(1 ,"菜品销量统计");// 一次性写出内容，使用默认样式，强制输出标题writer.write(rows,true);// 关闭writer，释放内存writer.close();r.setCode(1);r.setData("文件导出成功");return r;}3.2Excel表格数据导入ECharts
public static final String path = "D:\\java\\excel\\test.xlsx"; File file = new File(path);FileInputStream input = new FileInputStream(file);MultipartFile multipartFile = new MockMultipartFile("file", file.getName(), "text/plain", IOUtils.toByteArray(input));// 1.获取上传文件输入流InputStream inputStream = null;inputStream = multipartFile.getInputStream();// 2.应用HUtool ExcelUtil获取ExcelReader指定输入流和sheetExcelReader excelReader = ExcelUtil.getReader(inputStream, "sheet1");// 可以加上表头验证// 3.读取第二行到最后一行数据//List<List<Object>> read = excelReader.read(1, excelReader.getRowCount());return excelReader.readAll();