SpringBoot导入导出数据

今天给大家带来的是一个 SpringBoot导入导出数据

首先我们先创建项目 注意：创建SpringBoot项目时一定要联网不然会报错

项目创建好后我们首先对 application.yml 进行编译

server:   port: 8081  # mysql spring:   datasource:     driver-class-name: com.mysql.cj.jdbc.Driver     url: jdbc:mysql://127.0.0.1:3306/dvd?characterEncoding=utf-8&&severTimezone=utc     username: root     password: root    thymeleaf:     mode: HTML5     cache: false     suffix: .html     prefix: classpath:/  mybatis:   mapperLocations: classpath:mapper/**/*.xml   configuration:     map-underscore-to-camel-case: true  pagehelper:   helper-dialect: mysql   offset-as-page-num: true   params: count=countSql   reasonable: true   row-bounds-with-count: true   support-methods-arguments: true

注意：在 ：后一定要空格，这是他的语法，不空格就会运行报错

接下来我们进行对项目的构建 创建好如下几个包 可根据自己实际需要创建其他的工具包之类的

        mapper：用于存放dao层接口

        pojo：用于存放实体类

        service：用于存放service层接口，以及service层实现类

        controller：用于存放controller控制层       

接下来我们开始编写代码

首先是实体类

package com.bdqn.springbootexcel.pojo;  import lombok.Data;  import java.io.Serializable; import java.util.List;  @Data public class ExcelData implements Serializable{      //文件名称     private String fileName;     //表头数据     private String[] head;     //数据     private List<String[]> data;  } 

 然后是service层

package com.bdqn.springbootexcel.service;  import com.bdqn.springbootexcel.pojo.User; import org.apache.ibatis.annotations.Select;  import javax.servlet.http.HttpServletResponse; import java.util.List;  public interface ExcelService {      Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize);      Boolean importExcel(String fileName);      List<User> find(); } 

package com.bdqn.springbootexcel.service;  import com.bdqn.springbootexcel.mapper.UserMapper; import com.bdqn.springbootexcel.pojo.ExcelData; import com.bdqn.springbootexcel.pojo.User; import com.bdqn.springbootexcel.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 @Service public class ExcelServiceImpl implements ExcelService {      @Autowired     private UserMapper userMapper;      @Override     public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {         log.info("导出数据开始。。。。。。");         //查询数据并赋值给ExcelData         List<User> userList = userMapper.find();         List<String[]> list = new ArrayList<String[]>();         for (User user : userList) {             String[] arrs = new String[userList.size()];             arrs[0] = String.valueOf(user.getId());             arrs[1] = String.valueOf(user.getName());             arrs[2] = String.valueOf(user.getAge());             arrs[3] = String.valueOf(user.getSex());             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;         }     }      @Override     public Boolean importExcel(String fileName) {         log.info("导入数据开始。。。。。。");         try {             List<Object[]> list = ExcelUtil.importExcel(fileName);             System.out.println(list.toString());             for (int i = 0; i < list.size(); i++) {                 User user = new User();                 user.setName((String) list.get(i)[0]);                 user.setAge((String) list.get(i)[1]);                 user.setSex((String) list.get(i)[2]);                 userMapper.add(user);             }             log.info("导入数据结束。。。。。。");             return true;         } catch (Exception e) {             log.info("导入数据失败。。。。。。");             e.printStackTrace();         }         return false;     }      @Override     public List<User> find() {         return userMapper.find();     } } 

工具类

package com.bdqn.springbootexcel.util;  import com.bdqn.springbootexcel.pojo.ExcelData; import com.bdqn.springbootexcel.pojo.User; 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;  import static org.apache.poi.ss.usermodel.CellType.*;  @Slf4j public class ExcelUtil {     public static void exportExcel(HttpServletResponse response, ExcelData data) {         log.info("导出解析开始，fileName:{}",data.getFileName());         try {             //实例化HSSFWorkbook             HSSFWorkbook 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();         }     }      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();         }     }      private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {         try {             //清空response             response.reset();             //设置response的Header             response.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();         }      }      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(NUMERIC)) {                         objects[index] = (int) cell.getNumericCellValue();                     }                     if (cell.getCellType().equals(STRING)) {                         objects[index] = cell.getStringCellValue();                     }                     if (cell.getCellType().equals(BOOLEAN)) {                         objects[index] = cell.getBooleanCellValue();                     }                     if (cell.getCellType().equals(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 = "G:/test.xlsx";             List<Object[]> list = importExcel(fileName);             for (int i = 0; i < list.size(); i++) {                 User user = new User();                 user.setName((String) list.get(i)[0]);                 user.setAge((String) list.get(i)[1]);                 user.setSex((String) list.get(i)[2]);                 System.out.println(user.toString());             }         } catch (Exception e) {             e.printStackTrace();         }     }  } 

最后是controller层

package com.bdqn.springbootexcel.controller;  import com.bdqn.springbootexcel.pojo.User; import com.bdqn.springbootexcel.service.ExcelService; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; 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 @RestController public class ExcelController {      @Autowired     private ExcelService excelService;      @GetMapping("/export")     public String exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {         fileName = "test.xlsx";         if (fileName == null || "".equals(fileName)) {             return "文件名不能为空！";         } else {             if (fileName.endsWith("xls") || fileName.endsWith("xlsx")) {                 Boolean isOk = excelService.exportExcel(response, fileName, 1, 10);                 if (isOk) {                     return "导出成功！";                 } else {                     return "导出失败！";                 }             }             return "文件格式有误！";         }     }      @GetMapping("/import")     public String importExcel(String fileName) {         fileName = "G:/test.xlsx";         if (fileName == null && "".equals(fileName)) {             return "文件名不能为空！";         } else {             if (fileName.endsWith("xls") || fileName.endsWith("xlsx")) {                 Boolean isOk = excelService.importExcel(fileName);                 if (isOk) {                     return "导入成功！";                 } else {                     return "导入失败！";                 }             }             return "文件格式错误！";         }     }      //饼状图的数据查询     //@ResponseBody     @RequestMapping("/pojos_bing")     public List<User> gotoIndex() {         List<User> pojos = excelService.find();         return pojos;     }  } 

到现在为止我们的后端代码就已经完全搞定了，前端页面如下

写了一个简单前端用于测试

index.html

<!DOCTYPE html> <html lang="en" xmlns:th="http://www.thymeleaf.org"> <html lang="en"> <head>     <meta charset="UTF-8">     <title>Title</title> </head> <body>     <div align="center">         <a th:href="@{'/export'}">导出</a>         <a th:href="@{'/import'}">导入</a>     </div> </body> </html>

当我们点击导出按钮时浏览器会自动下载

当我们点击导入按钮时会往数据库中添加表格数据

今天给大家带来的就是这些内容，感谢观看！

有问题可以在评论区指正，我会及时修改！