使用hutool库，对excel进行一些导出、导入操作

所用到的各种类，依赖的话百度一下吧，最后再给上全部代码

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.domain.MagBackAmountInfo;
import com.domain.ReturnResult;
import com.example.service.impl.MagBackAmountInfoServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

导出数据为excel的接口代码

    @GetMapping("/testExport")
 public void test(HttpServletRequest request, HttpServletResponse response){
     int pageStart = Integer.parseInt(request.getParameter("Pagenumber"));
     int pageSize = Integer.parseInt(request.getParameter("number"));
     /** 这里写你自己获取数据的方法*/
        List<MagBackAmountInfo> magBackAmountInfos = backAmountInfoService.selectByPrimarybyid(null);

        ExcelWriter writer = ExcelUtil.getWriter(true);
        /** 这里的作用是将你的字段与excel展示名称做匹配 */
     Map<String,String> map = new HashMap<>();
     map.put("backAmountId","VIN");
     map.put("makeInvocId","ICCID");
     map.put("customerName","客户名称");
     map.put("vehiclesName","车系名称");
     map.put("vehicleModel","车型名称");
     map.put("vehicleConf","配置名称");
     map.put("customerType","客户类型");
     map.put("activedTime","实销通过日期");
     map.put("realStatus","实名状态");
     writer.setHeaderAlias(map);
     /** 这里的作用时，写出之后不展示你的字段名，只展示你设置的别名，客户类型，实销通过日期。这些 */
     writer.setOnlyAlias(true);
     writer.write(magBackAmountInfos,true);
     response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
     response.setHeader("Content-Disposition","attachment;filename=RealnameRechargeBlack.xlsx");
     ServletOutputStream out= null;
     try {
         out = response.getOutputStream();
         writer.flush(out, true);
         // 关闭writer，释放内存
         writer.close();
         //此处记得关闭输出Servlet流
         IoUtil.close(out);
     } catch (IOException e) {
         e.printStackTrace();
     }
 }

导入时提供模板的接口代码

  @ResponseBody
    @RequestMapping(value = "/moban", method = RequestMethod.GET)
    public void moban(HttpServletRequest request, HttpServletResponse response){

        ExcelWriter writer = ExcelUtil.getWriter(true);
        List<String> row = CollUtil.newArrayList("backAmountId", "makeInvocId", "customerName", "vehiclesName","vehicleModel","vehicleConf","customerType","activedTime","realStatus");
        List<String> secRow = CollUtil.newArrayList("VIN", "ICCID", "客户名称", "车系名称","车型名称","配置名称","客户类型","实销通过日期-(yyyy-MM-dd HH:mm:ss)","实名状态");
        writer.writeHeadRow(row);
        writer.writeRow(secRow);
        /** 这里说是隐藏，但是其实就是将高度置为0，有点鸡肋 */
        writer.getSheet().getRow(0).setZeroHeight(true);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=RealnameRechargeBlack.xlsx");
        ServletOutputStream out= null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

导入接口代码

    //导入excel
    @PostMapping("/import")
    public ReturnResult importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        String originalFilename = file.getOriginalFilename();
        String suffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
        if (file==null|| file.isEmpty()){
            return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "上传文件为空，请重新上传");
        }
        else if (!suffix.equals("xls")&&!suffix.equals("xlsx")){
            return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "文件格式错误，请上传excel文件");
        }
        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
        /** 因为模板中有两行为提示信息，所以这儿设置从0，2之后读取 */
        List<MagBackAmountInfo> blackDtos = reader.read(0, 2, MagBackAmountInfo.class);
        if (blackDtos.size() == 0) {
            return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "文件数据为空，请重新上传");
        } else {
            /** 可以直接批量插入，也可以在service层做一些关键值的判断 */
             backAmountInfoService.batchInsert(blackDtos);
          return   ReturnResult.ok();
        }
    }

完整class代码

package com.example.demo;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.domain.MagBackAmountInfo;
import com.domain.ReturnResult;
import com.example.service.impl.MagBackAmountInfoServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
//允许跨域访问
@CrossOrigin
@RequestMapping("/member")
public class ExcelController {

    @Autowired
    MagBackAmountInfoServiceImpl backAmountInfoService;

    @GetMapping("/testExport")
 public void test(HttpServletRequest request, HttpServletResponse response){
     int pageStart = Integer.parseInt(request.getParameter("Pagenumber"));
     int pageSize = Integer.parseInt(request.getParameter("number"));
     /** 这里写你自己获取数据的方法*/
        List<MagBackAmountInfo> magBackAmountInfos = backAmountInfoService.selectByPrimarybyid(null);

        ExcelWriter writer = ExcelUtil.getWriter(true);
        /** 这里的作用是将你的字段与excel展示名称做匹配 */
     Map<String,String> map = new HashMap<>();
     map.put("backAmountId","VIN");
     map.put("makeInvocId","ICCID");
     map.put("customerName","客户名称");
     map.put("vehiclesName","车系名称");
     map.put("vehicleModel","车型名称");
     map.put("vehicleConf","配置名称");
     map.put("customerType","客户类型");
     map.put("activedTime","实销通过日期");
     map.put("realStatus","实名状态");
     writer.setHeaderAlias(map);
     /** 这里的作用时，写出之后不展示你的字段名，只展示你设置的别名，客户类型，实销通过日期。这些 */
     writer.setOnlyAlias(true);
     writer.write(magBackAmountInfos,true);
     response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
     response.setHeader("Content-Disposition","attachment;filename=RealnameRechargeBlack.xlsx");
     ServletOutputStream out= null;
     try {
         out = response.getOutputStream();
         writer.flush(out, true);
         // 关闭writer，释放内存
         writer.close();
         //此处记得关闭输出Servlet流
         IoUtil.close(out);
     } catch (IOException e) {
         e.printStackTrace();
     }
 }


    @ResponseBody
    @RequestMapping(value = "/moban", method = RequestMethod.GET)
    public void moban(HttpServletRequest request, HttpServletResponse response){

        ExcelWriter writer = ExcelUtil.getWriter(true);
        List<String> row = CollUtil.newArrayList("backAmountId", "makeInvocId", "customerName", "vehiclesName","vehicleModel","vehicleConf","customerType","activedTime","realStatus");
        List<String> secRow = CollUtil.newArrayList("VIN", "ICCID", "客户名称", "车系名称","车型名称","配置名称","客户类型","实销通过日期-(yyyy-MM-dd HH:mm:ss)","实名状态");
        writer.writeHeadRow(row);
        writer.writeRow(secRow);
        /** 这里说是隐藏，但是其实就是将高度置为0，有点鸡肋 */
        writer.getSheet().getRow(0).setZeroHeight(true);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename=RealnameRechargeBlack.xlsx");
        ServletOutputStream out= null;
        try {
            out = response.getOutputStream();
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    //导入excel
    @PostMapping("/import")
    public ReturnResult importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        String originalFilename = file.getOriginalFilename();
        String suffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
        if (file==null|| file.isEmpty()){
            return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "上传文件为空，请重新上传");
        }
        else if (!suffix.equals("xls")&&!suffix.equals("xlsx")){
            return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "文件格式错误，请上传excel文件");
        }
        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
        /** 因为模板中有两行为提示信息，所以这儿设置从0，2之后读取 */
        List<MagBackAmountInfo> blackDtos = reader.read(0, 2, MagBackAmountInfo.class);
        if (blackDtos.size() == 0) {
            return ReturnResult.failed(HttpStatus.BAD_REQUEST.value(), "文件数据为空，请重新上传");
        } else {
            /** 可以直接批量插入，也可以在service层做一些关键值的判断 */
             backAmountInfoService.batchInsert(blackDtos);
          return   ReturnResult.ok();
        }
    }

}