Java实现excel导出

 导出功能其实是先获取导出表格的信息，然后将信息写入进模板，实现数据导出，实现起来还是很简单的

 @SneakyThrows
    @ResponseBody
    @ApiOperation(value = "CA序页清单导出", notes = "CA序页清单导出")
    @PostMapping("/exportCAListFile")
    public RtMsg exportCAListFile(HttpServletRequest req, HttpServletResponse response) {
        if (null == cano) {
            return RtMsg.ok().put("data","请先填写CA号系统才能支持导出");
        }
        List<DmCaContinuationPageListRespBody> list = caList;
        if (null == list) {
            return RtMsg.dataOk().put("data", "请先输入查询条件或查询结果为空");
        }
        List<DmCaContinuationPageListRespBody> queryList = list;
        Workbook workbook = null;
        Sheet sheet = null;
        try {
            // 读取源文件，模板数据存放位置，Linux环境下获取jar包classpath中的文件
            InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("ExcelModule/续页清单导出模板.xlsx");
            workbook = WorkbookFactory.create(inputStream);
            sheet = workbook.getSheetAt(0);
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (null != cano) {
            //cano
            sheet.getRow(0).getCell(11).setCellValue("CA号："+cano);
            //编制
            String prepareduserByCano = dmCaContinuationPageListMapper.getPrepareduserByCano(cano);
            String prepareduser = prepareduserByCano != null ? "编制：" + prepareduserByCano : "编制：";
            sheet.getRow(1).getCell(11).setCellValue(prepareduser);
        }


        //处理查询的结果集，对结果进行封装导出
        for (int i = 0; i < queryList.size(); i++) {
            DmCaContinuationPageListRespBody queryBody = queryList.get(i);
            //模板有五行，故从第六行开始录入数据
            Row row = sheet.createRow(i + 5);
            //1、id
            row.createCell(0).setCellValue(i + 1);
            //2、图样/文件编号
            row.createCell(1).setCellValue(Optional.ofNullable(queryBody.getDrawingfileno()).orElse("/"));
            //3、图样/文件名称
            row.createCell(2).setCellValue(Optional.ofNullable(queryBody.getDrawingfilename()).orElse("/"));
            //4、配置(梯型)
            row.createCell(3).setCellValue(Optional.ofNullable(queryBody.getConfigtx()).orElse("/"));
            //5、是否ODS层级
            row.createCell(4).setCellValue(Optional.ofNullable(queryBody.getIsods()).orElse("/"));
            //6、所属ODS
            row.createCell(5).setCellValue(Optional.ofNullable(queryBody.getBelongods()).orElse("/"));
            //7、更改页码
            row.createCell(6).setCellValue(Optional.ofNullable(queryBody.getChangepageno()).orElse("/"));
            //8、总页码
            row.createCell(7).setCellValue(Optional.ofNullable(queryBody.getTotalpageno()).orElse(1));
            //9、类别
            row.createCell(8).setCellValue(Optional.ofNullable(queryBody.getCategorytype()).orElse("/"));
            //10、更改类型
            row.createCell(9).setCellValue(Optional.ofNullable(queryBody.getChangetype()).orElse("/"));
            //11、件号变更
            row.createCell(10).setCellValue(Optional.ofNullable(queryBody.getPartnochange()).orElse("/"));
            //12、更改内容描述
            row.createCell(11).setCellValue(Optional.ofNullable(queryBody.getChangecontent()).orElse("/"));
            //13、自制
            row.createCell(12).setCellValue(Optional.ofNullable(queryBody.getSelfmade()).orElse("/"));
            //14、外协外购
            row.createCell(13).setCellValue(Optional.ofNullable(queryBody.getOutsourcing()).orElse("/"));
            //15、采购状态
            row.createCell(14).setCellValue(Optional.ofNullable(queryBody.getPurchasestatus()).orElse("/"));
            //16、设备线体
            row.createCell(15).setCellValue(Optional.ofNullable(queryBody.getEquipmentline()).orElse("/"));
            //17、库存量
            row.createCell(16).setCellValue(Optional.ofNullable(queryBody.getInventory()).orElse(0));

           

        }
        //对于结果进行刷新更改
        caList.clear();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
        String fileName = "";
        String fileLastStr = "续页清单.xlsx";
        Date date = new Date();
        fileName = sdf.format(date) + fileLastStr;

        //转码,否则前端显示乱码
        fileName = URLEncoder.encode(fileName, "UTF-8");

        // 设置输出的格式
        response.reset();
        //显示响应头,将文件名传给前端
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        // 解决前端访问跨域问题
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setContentType("application/msexcel");
        response.setCharacterEncoding("UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + fileName);
        OutputStream ouputStream = response.getOutputStream();
        workbook.write(ouputStream);
        ouputStream.flush();
        ouputStream.close();
        workbook.close();
        return null;
    }