第一步:创建springboot项目:(创建项目省略)
第二步配置:pom依赖包
<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency><!-- easyexcel 依赖 --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.7</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.xmlbeans</groupId><artifactId>xmlbeans</artifactId><version>3.1.0</version></dependency><dependency><groupId>mons</groupId><artifactId>commons-collections4</artifactId><version>4.1</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.2</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.2</version></dependency><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><scope>runtime</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies>
第三步配置application.yml文件:
server:port: 8080spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/computs?characterEncoding=utf-8&&severTimezone=utcusername: rootpassword: rootthymeleaf:prefix: classpath:/templates/ #th模板运行路径suffix: .htmlmode: HTML5encoding: UTF-8cache: falsemybatis:mapperLocations: classpath:mapper/**/*.xmlconfiguration:map-underscore-to-camel-case: truepagehelper:helper-dialect: mysqloffset-as-page-num: trueparams: count=countSqlreasonable: truerow-bounds-with-count: truesupport-methods-arguments: true
第三步 写:controller service pojo mapper util (我用的是自动装配)
Controller:
package com.poi.springboot_poi.controller;import com.poi.puterroom;import com.poi.puterroomServiceImpl;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;import java.util.List;@Slf4j@RestControllerpublic class ExcelController {@Autowiredprivate ComputerroomServiceImpl computerroomService;@GetMapping("/export")public String exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {fileName = "D:/sss.xlsx";if (fileName == null || "".equals(fileName)) {return "文件名不能为空!";} else {if (fileName.endsWith("xlsx")) {Boolean isOk = computerroomService.exportExcel(response, fileName, 1, 10);if (isOk) {return "index!";} else {return "导出失败!";}}return "文件格式有误!";}}@GetMapping("/import")public String importExcel() {String fileName = "D:/sss.xlsx";if (fileName == null && "".equals(fileName)) {return "文件名不能为空!";} else {if (fileName.endsWith("xls") || fileName.endsWith("xlsx")) {Boolean isOk = computerroomService.importExcel(fileName);if (isOk) {return "index";} else {return "导入失败!";}}return "文件格式错误!";}}//饼状图的数据查询@RequestMapping("/pojos_bing")public List<Computerroom> findAll(){List<Computerroom> all = computerroomService.findAll();System.err.println(all.toString());return all;}}
Mapper层:
package com.poi.springboot_poi.mapper;import com.poi.puterroom;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Select;import java.util.List;@Mapperpublic interface ComputerroomMapper {@Select("select * from computerroom")public List<Computerroom> find();@Insert("insert into computerroom ( roomName, computerNumber, manager,locations) value ( #{roomName}, #{computerNumber}, #{manager}, #{locations})")public int add(Computerroom computerroom);}
pojo层:(数据库的类型跟实体类一样 ,所以数据库就自行创建了)
package com.poi.springboot_poi.pojo;import com.alibaba.excel.annotation.ExcelProperty;import lombok.Data;import java.io.Serializable;@Datapublic class Computerroom implements Serializable {@ExcelProperty(index = 0,value = "机房编号")private Integer roomId;@ExcelProperty(index = 1,value = "机房名称")private String roomName;@ExcelProperty(index = 2,value = "计算机数量")private String computerNumber;@ExcelProperty(index = 3,value = "机房管理员")private String manager;@ExcelProperty(index = 4,value = "机房地址")private String locations;}
ExcelData类:
package com.poi.springboot_poi.pojo;import lombok.Data;import java.io.Serializable;import java.util.List;@Datapublic class ExcelData implements Serializable {//文件名称private String fileName;//表头数据private String[] head;//数据private List<String[]> data;}
service层:
package com.poi.springboot_poi.service;import com.poi.puterroom;import javax.servlet.http.HttpServletResponse;import java.util.List;public interface ComputerroomService {public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize);public Boolean importExcel(String fileName);public List<Computerroom> findAll();}
serviceImpl:
package com.poi.springboot_poi.service;import com.poi.puterroomMapper;import com.poi.puterroom;import com.poi.springboot_poi.pojo.ExcelData;import com.poi.springboot_poi.util.ExcelUtil;import lombok.extern.slf4j.Slf4j;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import javax.servlet.http.HttpServletResponse;import java.util.ArrayList;import java.util.List;@Slf4j@Servicepublic class ComputerroomServiceImpl implements ComputerroomService{@Autowiredprivate ComputerroomMapper computerroomMapper;@Overridepublic Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {log.info("导出数据开始。。。。。。");//查询数据并赋值给ExcelDataList<Computerroom> computerroomList = computerroomMapper.find();List<String[]> list = new ArrayList<String[]>();for (Computerroom computerroom : computerroomList) {String[] arrs = new String[computerroomList.size()];arrs[0] = String.valueOf(computerroom.getRoomId());arrs[1] = String.valueOf(computerroom.getRoomName());arrs[2] = String.valueOf(computerroom.getComputerNumber());arrs[3] = String.valueOf(computerroom.getManager());arrs[4] = String.valueOf(computerroom.getLocations());list.add(arrs);}//表头赋值String[] head = {"机房编号", "机房名称", "计算机数量", "机房管理员","机房地址"};ExcelData data = new ExcelData();data.setHead(head);data.setData(list);data.setFileName(fileName);//实现导出try {ExcelUtil.exportExcel(response, data);log.info("导出数据结束。。。。。。");return true;} catch (Exception e) {log.info("导出数据失败。。。。。。");return false;}}/*** 修改时间:*/@Overridepublic Boolean importExcel(String fileName) {log.info("导入数据开始。。。。。。");try {List<Object[]> list = ExcelUtil.importExcel(fileName);for (int i = 0; i < list.size(); i++) {Computerroom computerroom = new Computerroom();// computerroom.setRoomId((Integer) list.get(i)[0]);computerroom.setRoomName((String) list.get(i)[0]);computerroom.setComputerNumber((String) list.get(i)[1]);computerroom.setManager((String) list.get(i)[2]);computerroom.setLocations((String) list.get(i)[3]);computerroomMapper.add(computerroom);}log.info("导入数据结束。。。。。。");return true;} catch (Exception e) {log.info("导入数据失败。。。。。。");e.printStackTrace();}return false;}@Overridepublic List<Computerroom> findAll() {return computerroomMapper.find();}}
util工具类:
PageController类:
package com.poi.springboot_poi.util;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;@Controllerpublic class PageController {@RequestMapping("/{page}.html")public String toPage(@PathVariable("page") String page){return page;}}
ExcelUtil类:
package com.poi.springboot_poi.util;import com.poi.puterroom;import com.poi.springboot_poi.pojo.ExcelData;import lombok.extern.slf4j.Slf4j;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import javax.servlet.http.HttpServletResponse;import java.io.BufferedOutputStream;import java.io.FileInputStream;import java.io.InputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;@Slf4jpublic class ExcelUtil {public static void exportExcel(HttpServletResponse response, ExcelData data) {log.info("导出解析开始,fileName:{}",data.getFileName());try {//实例化HSSFWorkbookHSSFWorkbook workbook = new HSSFWorkbook();//创建一个Excel表单,参数为sheet的名字HSSFSheet sheet = workbook.createSheet("sheet");//设置表头setTitle(workbook, sheet, data.getHead());//设置单元格并赋值setData(sheet, data.getData());//设置浏览器下载setBrowser(response, workbook, data.getFileName());log.info("导出解析成功!");} catch (Exception e) {log.info("导出解析失败!");e.printStackTrace();}}private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {try {HSSFRow row = sheet.createRow(0);//设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度for (int i = 0; i < str.length; i++) {sheet.setColumnWidth(i, 15 * 256);}//设置为居中加粗,格式化时间格式HSSFCellStyle style = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setBold(true);style.setFont(font);style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//创建表头名称HSSFCell cell;for (int j = 0; j <str.length; j++) {cell = row.createCell(j);cell.setCellValue(str[j]);cell.setCellStyle(style);}} catch (Exception e) {log.info("导出时设置表头失败!");e.printStackTrace();}}/*** 方法名:setData*/private static void setData(HSSFSheet sheet, List<String[]> data) {try{int rowNum = 1;for (int i = 0; i <data.size(); i++) {HSSFRow row = sheet.createRow(rowNum);for (int j = 0; j < data.get(i).length; j++) {row.createCell(j).setCellValue(data.get(i)[j]);}rowNum++;}log.info("表格赋值成功!");}catch (Exception e){log.info("表格赋值失败!");e.printStackTrace();}}/*** 方法名:setBrowser* 功能:使用浏览器下载* 描述:* 创建人:typ* 创建时间:/10/19 16:20* 修改人:* 修改描述:* 修改时间:*/private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {try {//清空responseresponse.reset();//设置response的Headerresponse.addHeader("Content-Disposition", "attachment;filename=" + fileName);OutputStream os = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/vnd.ms-excel;charset=gb2312");//将excel写入到输出流中workbook.write(os);os.flush();os.close();log.info("设置浏览器下载成功!");} catch (Exception e) {log.info("设置浏览器下载失败!");e.printStackTrace();}}/*** 方法名:importExcel* 功能:导入*/public static List<Object[]> importExcel(String fileName) {log.info("导入解析开始,fileName:{}",fileName);try {List<Object[]> list = new ArrayList<>();InputStream inputStream = new FileInputStream(fileName);Workbook workbook = WorkbookFactory.create(inputStream);Sheet sheet = workbook.getSheetAt(0);//获取sheet的行数int rows = sheet.getPhysicalNumberOfRows();for (int i = 0; i < rows; i++) {//过滤表头行if (i == 0) {continue;}//获取当前行的数据Row row = sheet.getRow(i);Object[] objects = new Object[row.getPhysicalNumberOfCells()];int index = 0;for (Cell cell : row) {if (cell.getCellType().equals(CellType.NUMERIC)) {objects[index] = (int) cell.getNumericCellValue();}if (cell.getCellType().equals(CellType.STRING)) {objects[index] = cell.getStringCellValue();}if (cell.getCellType().equals(CellType.BOOLEAN)) {objects[index] = cell.getBooleanCellValue();}if (cell.getCellType().equals(CellType.ERROR)) {objects[index] = cell.getErrorCellValue();}index++;}list.add(objects);}log.info("导入文件解析成功!");return list;}catch (Exception e){log.info("导入文件解析失败!");e.printStackTrace();}return null;}//测试导入public static void main(String[] args) {try {String fileName = "D:/sss.xlsx";List<Object[]> list = importExcel(fileName);for (int i = 0; i <list.size(); i++) {Computerroom computerroom = new Computerroom();// computerroom.setRoomId((Integer) list.get(i)[0]);computerroom.setRoomName((String) list.get(i)[0]);computerroom.setComputerNumber((String) list.get(i)[1]);computerroom.setManager((String) list.get(i)[2]);computerroom.setLocations((String) list.get(i)[3]);System.out.println(computerroom.toString());}} catch (Exception e) {e.printStackTrace();}}}
index.html页面:
<!DOCTYPE html><html lang="en" xmlns:th=""><head><meta charset="UTF-8"><title>index</title></head><body><div><a th:href="@{'/export'}">导出</a><a th:href="@{'/import'}">导入</a></div><!--饼状图--><div id="pie" style="width:800px;height:600px;"></div><!--柱状图--><div style="height: 50px;"></div><div id="bar" style="width: 1000px;height: 800px;"></div><script th:src="@{/ajax/libs/echarts/5.4.0/echarts.min.js}"></script><script>option = {title: {text:'饼图',subtext:'纯属虚构',left:'center'},legend: {top: 'bottom'},tooltip:{trigger:'item'},toolbox: {show: true,feature: {mark: { show: true },dataView: { show: true, readOnly: false },restore: { show: true },saveAsImage: { show: true }}},series: [{name: 'Nightingale Chart',type: 'pie',radius: [50, 250],center: ['50%', '50%'],roseType: 'area',itemStyle: {borderRadius: 8},data: []}]};var chartDom = document.getElementById('pie');var myChart = echarts.init(chartDom);fetch("/pojos_bing").then(response => response.json()).then(res => { //这里的 /pojos_bing 就是换成自己刚刚Controller层写好的查询数据的接口res.forEach(item => {option.series[0].data.push({name: item.roomName,value: puterNumber}) //这里遍历x和y就是数据库数据})myChart.setOption(option);})</script><script type="text/javascript">barOption = {title: {text: '柱状图'},legend: {top: 'top'},tooltip: {trigger: 'axis'},xAxis: {type: 'category',data: []},yAxis: {type: 'value'},series: [{name: '11',data: [],type: 'bar'},{name: '22',data: [],type: 'bar'}]};var barDom = document.getElementById('bar');var barChart = echarts.init(barDom);fetch("/pojos_bing").then(response => response.json()).then(res => {//这里的 /pojos_bing 就是换成自己刚刚Controller层写好的查询数据的接口const x= res.map(v => v.roomName);//这里拿到数据库中的 xbarOption.xAxis.data = xconst y= res.map(v => puterNumber);//这里拿到数据库中的 ybarOption.series[0].data = ybarChart.setOption(barOption)})</script></body></html>
大概的项目结构
大概流程以及代码就是上述这样子 ,但是 由于我的 poi的导入导出 和树状图以及饼状图是写在一起的所以 看上去可能会有点乱, 这里 我建议大家先看 导入导出,然后在写 echart 做图形。