springboot2.x利用poi生成Excel

springboot2.x利用poi和freemarker通过前端请求生成word并直接下载在浏览器上的续博客

之前是word;
今天来look look Excel
直接上代码

控制器里的方法：

@PostMapping(value = "/print/finDataExport")
    public ResponseEntity<byte[]> finDataExport(@RequestParam("printLabelIds") String printLabelIds, @RequestParam("fileType") String fileType,
                                                              @RequestParam("exportRemark") String exportRemark,@RequestParam("currentUserId") String currentUserId
                                                ,@RequestParam("exportType") Integer exportType, HttpServletRequest request) throws Exception {


        String exportRoot = "exportRoot";
//        String path = request.getServletContext().getRealPath("/");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        Configuration configuration = new Configuration();
        configuration.setDefaultEncoding("utf-8");

        String exportRootPath =   request.getServletContext().getRealPath(exportRoot);
        String rootDir =   request.getServletContext().getRealPath(exportRoot+"/"+sdf.format(new Date()));
        FileUtil.delAllFile(exportRootPath); //每次调用此方法先删除服务器上的这个目录下的文件及文件夹
        File exportDir = new File(rootDir); //创建数据导出的 以时间命名 的目录
        if(!exportDir.exists()){
            exportDir.mkdirs();
        }

        SimpleDateFormat year = new SimpleDateFormat("yyyy");
        SimpleDateFormat timeSdf = new SimpleDateFormat("yyyy-MM-dd");
        List<FinishedFileDto> finList = originalService.getFinByIds(printLabelIds);

        String[] dimTypes = {"SaveDate"};
        List<Dim> saveDateDim = dimMapper.getListByDataTypes(dimTypes);

        List<ProjectInfoDto> projectInfoList = projectService.getProjectByIds(finList.stream().map(FinishedFileDto::getProjectId).collect(Collectors.toList()));

        List<VolumeFileDto> volumeList = originalService.getScienceVolumeListByFinIds(printLabelIds);

        List<Attachment> attachmentList =  originalService.getAttachmentListByVolId(volumeList.stream().map(VolumeFileDto::getId).collect(Collectors.toList()));

        finList.forEach(item->{
            if(null!=item.getSaveDays()){
                saveDateDim.forEach(dim -> {
                    if (String.valueOf(item.getSaveDays()).equals(String.valueOf(dim.getDataValue()))) {
                        item.setSaveName(dim.getDataName());
                    }
                });
            }
            if (null != item.getBackFileTime()) {
                item.setBackFileYear(year.format(item.getBackFileTime()));
            }

            if (null != item.getProjectId() && !"".equals(item.getProjectId())) {
                projectInfoList.forEach(p->{
                    if (item.getProjectId().equals(p.getId())) {
                        item.setOwnerCompanyNo(p.getOwnerCompanyNo());
                        item.setExportDir(String.format("%s %s",p.getEngineeringIndex(),p.getProjectName()));
                    }
                });

            }
            List<VolumeFileDto> vlist = volumeList.stream().filter(v->item.getId().equals(v.getFinishedFileId())).collect(Collectors.toList());
            Integer vPage = vlist.stream().map(VolumeFileDto::getPage).reduce(0,Integer::sum);
            item.setPage(vPage);
            List<Integer>  sortList = vlist.stream().filter(o -> null!=o.getVolumeSort()).map(VolumeFileDto::getVolumeSort).distinct().collect(Collectors.toList());
            if (sortList.size() == 0) {
                item.setSort(0);

            }else{
                Integer  max = sortList.stream().max((e1, e2) -> e1.compareTo(e2)).get();
                item.setSort(max);
            }

        });
        //卷内文件
        volumeList.forEach(item->{
            if (null != item.getFinishedFileId() && !"".equals(item.getFinishedFileId())) {
                finList.forEach(f->{
                    if (item.getFinishedFileId().equals(f.getId())) {
                        item.setBackFileYear(f.getBackFileYear());
                        item.setOwnerCompanyNo(f.getOwnerCompanyNo());
                        // 文件夹名称： 工程检索号 工程名/卷册检索号 卷册名
                        // 351-Q009S 武夷山电力培训中心翻修配套工程/351-Q009S-T0003 大门传达室土建图
                        item.setExportDir(String.format("%s/%s %s",f.getExportDir(),f.getFileNumber(),f.getFileName()));
                        item.setFileNumber(String.format("%s-%s",f.getFileNumber(),item.getFileNumber())); //卷册检索号-卷内文件编号
                    }
                });
                if (null != item.getOrganizationTime()) {
                    item.setOrgTime(timeSdf.format(item.getOrganizationTime()));
                }
            }
        });

        List<VolumeFileDto> list = new ArrayList<>();
        this.settingExportFileUrl(list,volumeList,attachmentList); //设置导出的文件路径

        list.forEach(item->{
            File  export = new File(request.getServletContext().getRealPath(exportRoot+"/"+sdf.format(new Date()))+"/"+item.getExportDir());
            if(!export.exists()){ //创建文件夹
                export.mkdirs();
            }
            //下载 文件 到服务器上
            this.download2Server(fileType,item,export);

        });

        List<VolumeFileDto> handleList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            if (fileType.contains("1")) { // 需要体现dwg文件
                if (StringUtils.endsWithIgnoreCase(list.get(i).getExportFileName(), ".dwg")) {
                    handleList.add(list.get(i));
                }
            }
            if (fileType.contains("2")) { //
                if (StringUtils.endsWithIgnoreCase(list.get(i).getExportFileName(), ".tif")) {
                    handleList.add(list.get(i));
                }
            }
            if (fileType.contains("3")) { //
                if (StringUtils.endsWithIgnoreCase(list.get(i).getExportFileName(), ".office")) {
                    handleList.add(list.get(i));
                }
            }
            if (fileType.contains("4")) { //
                if (StringUtils.endsWithIgnoreCase(list.get(i).getExportFileName(), ".pdf")) {
                    handleList.add(list.get(i));
                }
            }
        }

        this.addExportRecord(finList,handleList.size(),exportRemark,currentUserId,exportType); //插入导出记录表
        // 这一段就是生成Excel文件的代码，里面有多个sheet
        this.writeExcell(rootDir,projectInfoList,finList,volumeList,handleList);//生成 导出的文件目录

        FileOutputStream outputStream = new FileOutputStream(new File(exportRootPath+"/"+sdf.format(new Date())+".zip"));
        FileZipUtil.toZip(rootDir,outputStream,true);//压缩文件夹

        //浏览器 下载压缩文件
        String filename = sdf.format(new Date())+".zip";
        File file = new File(exportRootPath+"/"+ filename);
        HttpHeaders headers = new HttpHeaders();
        String downloadFileName = new String(filename.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        headers.setContentDispositionFormData("attachment", downloadFileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.CREATED);
    }

    private void addExportRecord(List<FinishedFileDto> finList, int size, String exportRemark,String currentUserId,int exportType) {
        List<ExportFinishedRecord> exportFinishedRecordList = new ArrayList<>();
        ExportFinishedRecord exportFinishedRecord;
        for (FinishedFileDto dto : finList) {
            exportFinishedRecord = new ExportFinishedRecord();
            exportFinishedRecord.setFinishedId(dto.getId());
            exportFinishedRecord.setExportPersonId(currentUserId);
            exportFinishedRecord.setExportFileNum(size);
            exportFinishedRecord.setExportType(exportType);
            exportFinishedRecord.setExportRemark(exportRemark);
            exportFinishedRecordList.add(exportFinishedRecord);
        }
        this.exportFinishedRecordMapper.addPatch(exportFinishedRecordList);
    }

    private void download2Server(String fileType, VolumeFileDto item, File export) {
        String ftpIp = FtpUtil.FTP_IP;
        if (fileType.contains("1")) { //dwg
            if (StringUtils.endsWithIgnoreCase(item.getExportFileName(), ".dwg")) {
                item.setFileSize(FtpUtil.downloadOneFile(item.getExportFileUrl().substring(item.getExportFileUrl().indexOf(ftpIp) + ftpIp.length() + 1), export.toString() + "/" + item.getExportFileName()));
            }
        }
        if (fileType.contains("2")) { // tif
            if (StringUtils.endsWithIgnoreCase(item.getExportFileName(), ".tif")) {
                item.setFileSize(FtpUtil.downloadOneFile(item.getExportFileUrl().substring(item.getExportFileUrl().indexOf(ftpIp) + ftpIp.length() + 1), export.toString() + "/" + item.getExportFileName()));
            }
        }
        if (fileType.contains("3")) { //office
            if (StringUtils.endsWithIgnoreCase(item.getExportFileName(), ".office")) {
                item.setFileSize(FtpUtil.downloadOneFile(item.getExportFileUrl().substring(item.getExportFileUrl().indexOf(ftpIp) + ftpIp.length() + 1), export.toString() + "/" + item.getExportFileName()));
            }
        }
        if (fileType.contains("4")) { //pdf
            if (StringUtils.endsWithIgnoreCase(item.getExportFileName(), ".pdf")) {
                item.setFileSize(FtpUtil.downloadOneFile(item.getExportFileUrl().substring(item.getExportFileUrl().indexOf(ftpIp) + ftpIp.length() + 1), export.toString() + "/" + item.getExportFileName()));
            }
        }
    }

    private void settingExportFileUrl(List<VolumeFileDto> list, List<VolumeFileDto> volumeList, List<Attachment> attachmentList) {
        VolumeFileDto d ;
        for (VolumeFileDto dto : volumeList) {
            if (null != dto.getVolumeName() && !"".equals(dto.getVolumeName()) && null != dto.getFileUrl() && !"".equals(dto.getFileUrl())) { //原文件
                 d = new VolumeFileDto();
                BeanUtils.copyProperties(dto,d);
                d.setExportFileName(String.format("%s %s",dto.getFileNumber(),dto.getVolumeName()));
                d.setExportFileUrl(dto.getFileUrl());
                d.setFileType("1");
                list.add(d);

            }
            if (null != dto.getScanName() && !"".equals(dto.getScanName()) && null != dto.getScanUrl() && !"".equals(dto.getScanUrl())) {//扫描件
                d = new VolumeFileDto();
                BeanUtils.copyProperties(dto,d);
                d.setExportFileName(String.format("%s %s",dto.getFileNumber(),dto.getScanName()));
                d.setExportFileUrl(dto.getScanUrl());
                d.setFileType("2");
                list.add(d);
            }
            for (Attachment attachment : attachmentList) {
                if (dto.getId().equals(attachment.getVolumeId())) { //附件
                    if (null != attachment.getUrl() && !"".equals(attachment.getUrl()) && null != attachment.getName() && !"".equals(attachment.getName())) {
                        d = new VolumeFileDto();
                        BeanUtils.copyProperties(dto,d);
                        d.setExportFileUrl(attachment.getUrl());
                        d.setExportFileName(String.format("%s %s",dto.getFileNumber(),attachment.getName()));
                        d.setFileType("3");
                        list.add(d);
                    }
                }
            }

        }
    }

    private void writeExcell(String path, List<ProjectInfoDto> projectInfoList, List<FinishedFileDto> finList, List<VolumeFileDto> volumeList,List<VolumeFileDto> list) {


        String archiveType = "73";
        // 创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        //  创建工作表
        XSSFSheet sheetProject = workbook.createSheet("项目表");
        sheetProject.setColumnWidth((short)0,(short)35.7*122);
        sheetProject.setColumnWidth((short)1,(short)35.7*194);
        sheetProject.setColumnWidth((short)2,(short)35.7*296);
        sheetProject.setColumnWidth((short)3,(short)35.7*260);
        sheetProject.setColumnWidth((short)4,(short)35.7*260);
        sheetProject.setColumnWidth((short)5,(short)35.7*116);
        sheetProject.setColumnWidth((short)6,(short)35.7*116);

        XSSFSheet sheetFin = workbook.createSheet("案卷表");
        sheetFin.setColumnWidth((short)4,(short)35.7*116);
        sheetFin.setColumnWidth((short)5,(short)35.7*116);


        XSSFSheet sheetFile = workbook.createSheet("文件表");
        sheetFile.setColumnWidth((short)0,(short)35.7*122);
        sheetFile.setColumnWidth((short)2,(short)35.7*194);
        sheetFile.setColumnWidth((short)3,(short)35.7*122);
        sheetFile.setColumnWidth((short)4,(short)35.7*122);
        sheetFile.setColumnWidth((short)5,(short)35.7*116);


        XSSFSheet sheetFileDir = workbook.createSheet("电子原文件");
        sheetFileDir.setColumnWidth((short)0,(short)35.7*860);
        sheetFileDir.setColumnWidth((short)1,(short)35.7*122);
        sheetFileDir.setColumnWidth((short)2,(short)35.7*260);
        sheetFileDir.setColumnWidth((short)3,(short)35.7*116);
        sheetFileDir.setColumnWidth((short)4,(short)35.7*116);

        //写入数据
        Map<String,Object[]> data = new TreeMap<>();
        data.put("1", new Object[] {"目录代号", "项目类别", "项目名称","建设单位","设计单位","移交单位","对应的档案类型"});
        for (int i = 0; i < projectInfoList.size(); i++) {

            data.put("2"+i, new Object[] {projectInfoList.get(i).getEngineeringIndex(), projectInfoList.get(i).getTypeName(), projectInfoList.get(i).getProjectName(), projectInfoList.get(i).getOwnerCompanyName(),
                    projectInfoList.get(i).getDesignCompanyName(), projectInfoList.get(i).getOwnerCompanyNo(), archiveType});
        }
        Map<String,Object[]> dataFin = new TreeMap<>();
        dataFin.put("1", new Object[] {"保管期限", "总件数", "总页数","归档年度","移交单位","对应的档案类型"});
        for (int i = 0; i < finList.size(); i++) {
            dataFin.put("2"+i, new Object[] {finList.get(i).getSaveName(), finList.get(i).getSort(), finList.get(i).getPage(),
                    finList.get(i).getBackFileYear(), finList.get(i).getOwnerCompanyNo(), archiveType});
        }


        Map<String,Object[]> dataFile = new TreeMap<>();
        dataFile.put("1", new Object[] {"形成日期", "页数", "文件编号","归档年度","移交单位","对应的档案类型"});
        volumeList = volumeList.stream().sorted(Comparator.comparing(VolumeFileDto::getFinishedFileId)).collect(Collectors.toList());
        for (int i = 0; i < volumeList.size(); i++) {
            dataFile.put("2"+i, new Object[] {volumeList.get(i).getOrgTime(), volumeList.get(i).getPage(),volumeList.get(i).getFileNumber(), volumeList.get(i).getBackFileYear(),
                    volumeList.get(i).getOwnerCompanyNo(), archiveType});
        }

        Map<String,Object[]> dataFileDir = new TreeMap<>();
        dataFileDir.put("1", new Object[] {"电子文件名称", "文件类型", "文件大小","移交单位","对应的档案类型"});


        List<VolumeFileDto> sortList = ( list.stream().filter(a->"1".equals(a.getFileType())).collect(Collectors.toList())) //目录中只体现源文件
                .stream().sorted(Comparator.comparing(VolumeFileDto::getFinishedFileId)).collect(Collectors.toList());
        for (int i = 0; i < sortList.size(); i++) {
            dataFileDir.put("2"+i, new Object[] {sortList.get(i).getExportDir()+"/"+sortList.get(i).getExportFileName(), "正文",String.valueOf(sortList.get(i).getFileSize()), sortList.get(i).getOwnerCompanyNo(), archiveType});
        }

        // 遍历数据写入表中
        this.createCellData(data,sheetProject);
        this.createCellData(dataFin,sheetFin);
        this.createCellData(dataFile,sheetFile);
        this.createCellData(dataFileDir,sheetFileDir);

        try {
            File file = new File(String.format("%s/%s",path,"/科技档案数据导出.xlsx"));
            FileOutputStream out = new FileOutputStream(file);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private void createCellData(Map<String, Object[]> data, XSSFSheet sheet) {

        Set<String> keySet = data.keySet();
        int rowNum = 0;
        for (String key : keySet){
            Row row = sheet.createRow(rowNum++);
            Object [] objArr = data.get(key);
            int cellNum = 0;
            for (Object obj: objArr){
                Cell cell  = row.createCell(cellNum++);
                if (obj instanceof String){
                    cell.setCellValue((String)obj);
                }else if(obj instanceof Integer){
                    cell.setCellValue((Integer)obj);
                }
            }
        }
    }