springboot 基于poi的execl文件导入，导出，模板下载实现

pom：

        <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>

controller：

public class ConfigCenterController {

    @Autowired
    ConfigCenterService configCenterService;

    @ApiOperation(httpMethod = "GET", value = "ExcelP导入信息配置-模板下载", notes = "aric")
    @GetMapping("download/MessageExcel")
    public ResponseVO<String> downloadPnDrawingMessageExcel(HttpServletResponse response) throws IOException {
        return configCenterService.downloadPnDrawingMessageExcel(response, "excelData/MessageHis.xlsx", "MessageHis.xlsx");
    }

    @ApiOperation(httpMethod = "POST", value = "Excel导入信息配置", notes = "aric")
    @PostMapping("upload/pnDrawingMessageExcel")
    public ResponseVO<String> uploadPnDrawingMessageExcel(@ApiParam(value = "料号图位-料号图位信息Excel文件", required = true) @RequestParam("excel") MultipartFile excel) throws IOException {
        if (excel == null) {
            return ResponseVO.error(ResponseEnum.DATA_NULL, "Empty File!");
        }
        return configCenterService.uploadPnDrawingMessageExcel(excel);
    }

    @ApiOperation(httpMethod = "GET", value = "Excel导出息配置", notes = "aric")
    @GetMapping("download/MessageHisExcel")
    public ResponseVO<String> getPnDrawingMessageHisExcel(HttpServletResponse response) throws IOException {
        return configCenterService.getPnDrawingMessageHisExcel(response);
    }
}

service：

@Service
@Transactional(transactionManager = "mysqlTransactionManager")
public class ConfigCenterService {

    @Autowired
    ConfigCenterMapper configCenterMapper;

    private static final String SUFFIX_2003 = ".xls";
    private static final String SUFFIX_2007 = ".xlsx";

    public ResponseVO<String> downloadPnDrawingMessageExcel(HttpServletResponse response, String path, String fileName) throws IOException {
        ClassPathResource resource = new ClassPathResource(path);
        InputStream fis = resource.getInputStream();
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
        byte[] buffer = new byte[1024];
        BufferedInputStream bis = null;
        OutputStream os = null;  //输出流
        try {
            os = response.getOutputStream();
            bis = new BufferedInputStream(fis);
            int i = bis.read(buffer);
            while (i != -1) {
                os.write(buffer);
                i = bis.read(buffer);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return ResponseVO.error(ResponseEnum.MASHINE_ERROR, e.toString());
        }
        try {
            bis.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return ResponseVO.successByCommonData("success");
    }

    public ResponseVO<String> uploadPnDrawingMessageExcel(MultipartFile file) throws IOException {
        List<PNtoDrawingConfigPO> list = new ArrayList<>();
        Workbook workbook = getWookbook(file);
        if (workbook == null) {
            return ResponseVO.error(ResponseEnum.DATA_NULL);
        } else {
            //获取一个sheet也就是一个工作簿
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum <= 0) {
                return ResponseVO.error(ResponseEnum.DATA_NULL);
            }
            //从第一行开始第一行一般就是标题
            for (int j = 1; j <= lastRowNum; j++) {
                Row row = sheet.getRow(j);
                PNtoDrawingConfigPO bean = new PNtoDrawingConfigPO();
                bean.setId(j);
                bean.setSubject_id(subjectId);
                if (row.getCell(0).getCellType() != CellType.STRING) {
                    return ResponseVO.error(ResponseEnum.PARAMS_ERROR, "读取出错：pn type error");
                }
                String pn = row.getCell(0).getStringCellValue();
                bean.setPn(pn);
                if (row.getCell(1).getCellType() != CellType.STRING) {
                    return ResponseVO.error(ResponseEnum.PARAMS_ERROR, "读取出错：testPoint type error");
                }
                String testPoint = row.getCell(1).getStringCellValue();
                bean.setTest_point(testPoint);
                if (row.getCell(2).getCellType() != CellType.STRING) {
                    return ResponseVO.error(ResponseEnum.PARAMS_ERROR, "读取出错：drawing type error");
                }
                String drawing = row.getCell(2).getStringCellValue();
                bean.setDrawing(drawing);
                list.add(bean);
            }
            try {
                int i = configCenterMapper.deletePNtoDrawingConfigHisData(subjectId);
                for (PNtoDrawingConfigPO bean : list) {
                    int j = configCenterMapper.insertPNtoDrawingConfig(bean);
                    if (j <= 0) {
                        return ResponseVO.error(ResponseEnum.INSERT_ERROR, "插入数据出错");
                    }
                }
                return ResponseVO.successByCommonData("success");
            } catch (Exception e) {
                return ResponseVO.error(ResponseEnum.INSERT_ERROR);
            }
        }
    }

    /**
    * 解析excel文件获得workbook
    */
    private Workbook getWookbook(MultipartFile file) throws IOException {
        //获取文件名称
        String originalFilename = file.getOriginalFilename();
        Workbook workbook = null;
        if (originalFilename.endsWith(SUFFIX_2003)) {
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if (originalFilename.endsWith(SUFFIX_2007)) {
            workbook = new XSSFWorkbook(file.getInputStream());
        } else {
            throw new selfHandle();
        }
        return workbook;
    }

    public ResponseVO<String> getPnDrawingMessageHisExcel(HttpServletResponse response) throws IOException {
        //查询要下载的数据
        List<PNtoDrawingConfigVO> list = configCenterMapper.getMessageExcel();
        //设置文件名称
        String fileName = "MessageHis.xlsx";
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sheet = null; //表
        Row row = null;  //行
        Cell cell = null;  //列
        int rowNo = 0;  //总行号
        int pageRowNo = 0;  //页行号
        for (int i = 0; i < list.size(); i++) {
            //写入300000条后切换到下个工作表
            if (rowNo % 300000 == 0) {
                wb.createSheet("料号图位表" + (rowNo / 300000));  //创建新的sheet对象
                sheet = wb.getSheetAt(rowNo / 300000);  //动态指定当前工作表
                pageRowNo = 0;  //新建工作表，重置工作表的行号为0
                //定义表头
                row = sheet.createRow(pageRowNo++);
                //列数
                row.createCell(0).setCellValue("料号");
                row.createCell(1).setCellValue("测点");
                row.createCell(2).setCellValue("图位");
                rowNo++;
            }
            rowNo++;
            row = sheet.createRow(pageRowNo++);  //新建行对象

            //行，获取cell值
            row.createCell(0).setCellValue(list.get(i).getPn());
            row.createCell(1).setCellValue(list.get(i).getTestPoint());
            row.createCell(2).setCellValue(list.get(i).getDrawing());
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
        return ResponseVO.successByCommonData("success");
    }
}