导出/下载EXCEL

weixin_47006244

已于 2022-03-21 14:52:56 修改

阅读量126

点赞数

分类专栏：笔记文章标签： java

于 2021-06-23 17:14:45 首次发布

本文链接：https://blog.csdn.net/weixin_47006244/article/details/118157485

版权

笔记专栏收录该内容

15 篇文章 0 订阅

订阅专栏

本文档详细描述了Controller中如何通过POST请求批量导出Excel的功能，涉及参数验证、Service层的接口定义及ServiceImpl中具体的数据处理和文件生成过程。重点展示了如何使用XSSFWorkbook创建Excel，以及数据填充和文件输出的细节。

摘要由CSDN通过智能技术生成

controller:

	/**
     * 批量导出Excel
     *
     * @param response response
     * @param sddIdDTO sddIdDTO
     */
    @PostMapping("/export/sddDetail")
    @WoAddUser
    @WoAuditLog(value = AuditLogTypeConstant.QUERY)
    public void exportSddDetail(HttpServletResponse response, @RequestBody @Validated SddIdBody sddIdDTO) {
        iSddDataManagementService.exportSddDetail(response, sddIdDTO);
    }

service:

/**
     * 批量导出Sdd Excel
     *
     * @param response response
     * @param sddIdDTO sddIdDTO
     */
    void exportSddDetail(HttpServletResponse response, SddIdBody sddIdDTO);

serviceImpl:

/**
     * 批量导出Sdd Excel
     *
     * @param response response
     * @param sddIdDTO sddIdDTO
     */
    @Override
    public void exportSddDetail(HttpServletResponse response, SddIdBody sddIdDTO) {
        List<String> sddIds = sddIdDTO.getSddIdList();
        String serviceInstanceId = sddIdDTO.getServiceInstanceId();
        // 限制导出1000条
        int count = sddDataManageMapper.selectCountSdd(sddIds, serviceInstanceId);
        if (count > 1000) {
            throw new CommonException(StatusCode.EXCEL_EXPORTED_IS_Limitations);
        }
        List<DataDevSddVO> dataDevSddVOS = sddDataManageMapper.selectTaskStatusAndInstanceId(sddIds, serviceInstanceId);
        if (dataDevSddVOS == null || dataDevSddVOS.isEmpty()) {
            throw new CommonException(StatusCode.EXCEL_EXPORT_NO_DATA);
        }
        OutputStream outputStream = null;
        // 创建Excel
         XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        exportTaskDataUtil.buildSddExcel(xssfWorkbook, dataDevSddVOS, sddIdDTO.getLanguage());
        LOG.info("buildSddExcel {}",  "buildSddExcel");
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=sdd.xlsx");
            response.flushBuffer();
            outputStream = response.getOutputStream();
            xssfWorkbook.write(outputStream);
        } catch (IOException e) {
            LOG.error("exportTaskDetail export excel failed IOException {}",
                ParamCheck.checkLoggerPrint(e.getMessage()));
        } finally {
            CommonUtils.close(outputStream);
            CommonUtils.close(xssfWorkbook);
        }
    }






/**
     * buildSddExcel
     *
     * @param xssfWorkbook xssfWorkbook
     * @param dataDevSddVOS dataDevSddVOS
     * @param language language
     * @return void
     */
    public void buildSddExcel(XSSFWorkbook xssfWorkbook, List<DataDevSddVO> dataDevSddVOS, String language) {
        // 创建Sheet
        XSSFSheet sddSheet = xssfWorkbook.createSheet(ExcelConstant.EXCEL_SHEET_NAME_SDDSHEET);
        createSddSheetBaseColumn(sddSheet, language);
 
        // sheet构建
        buildSddSheet(sddSheet, dataDevSddVOS, language);
    }




private void buildSddSheet(XSSFSheet sddSheet, List<DataDevSddVO> dataDevSddVOS, String language) {
        LOG.info("start buildSddSheet");
        // 行塞值
        for (int index = NumbersKey.ZERO; index < dataDevSddVOS.size(); index++) {
            DataDevSddVO dataDevSddVO = dataDevSddVOS.get(index);
            XSSFRow dataRow = sddSheet.createRow(index + NumbersKey.ONE);
            getSddData(dataRow, dataDevSddVO, language);
        }
        LOG.info("end buildSddSheet");
    }



private void createSddSheetBaseColumn(XSSFSheet sheet, String language) {
        String[] column = null;
        if (EN.equals(language)) {
            column = excelConfig.getSddColumnEn();
        } else {
            column = excelConfig.getSddColumn();
        }
        // 创建标题
        XSSFRow row = sheet.createRow(NumbersKey.ZERO);
        for (int index = NumbersKey.ZERO; index < column.length; index++) {
            XSSFCell cell = row.createCell(index);
            cell.setCellValue(column[index]);
        }
    }



private void getSddData(XSSFRow dataRow, DataDevSddVO dataDevSddVO, String language) {
        dataRow.createCell(NumbersKey.ZERO)
            .setCellValue(ExcelInjectFilter.filterExcelCellData(dataDevSddVO.getSddId()));
        dataRow.createCell(NumbersKey.ONE).setCellValue(ExcelInjectFilter.filterExcelCellData(dataDevSddVO.getTitle()));
        dataRow.createCell(NumbersKey.TWO)
            .setCellValue(ExcelInjectFilter.filterExcelCellData(dataDevSddVO.getDescription()));
        dataRow.createCell(NumbersKey.THREE)
            .setCellValue(ExcelInjectFilter.filterExcelCellData(dataDevSddVO.getCreated()));
        dataRow.createCell(NumbersKey.FOUR)
            .setCellValue(ExcelInjectFilter.filterExcelCellData(dataDevSddVO.getEstimatedWorkload()));
        dataRow.createCell(NumbersKey.FIVE)
            .setCellValue(SddPriorityEnmu.changeSddImpetence(dataDevSddVO.getUrgency(), language));
		dataRow.createCell(NumbersKey.SIX)
            .setCellValue(ExcelInjectFilter.filterExcelCellData(
                null == dataDevSddVO.getPlannedDate() ? "" : DateUtil.formatDate(dataDevSddVO.getPlannedDate())));
        dataRow.createCell(NumbersKey.SEVEN)
            .setCellValue(ExcelInjectFilter.filterExcelCellData(
                null == dataDevSddVO.getCreateTime() ? "" : DateUtil.formatDate(dataDevSddVO.getCreateTime())));
        dataRow.createCell(NumbersKey.EIGHT)
            .setCellValue(ExcelInjectFilter.filterExcelCellData(dataDevSddVO.getOwner()));
        dataRow.createCell(NumbersKey.NINE)
            .setCellValue(SddPriorityEnmu.changeSddStatus(dataDevSddVO.getTaskWorkStatus(), language));
    }
}



public static String changeSddImpetence(String impetence, String language) {
 
        if (EN.equals(language)) {
            switch (impetence) {
                case "0":
                    return "Very urgent.";
                case "1":
                    return "Urgent";
                case "2":
                    return "Common";
                case "3":
                    return "General";
            }
            return "Very urgent.";
        }else {
            switch (impetence) {
                case "0":
                    return "非常紧急";
                case "1":
                    return "紧急";
                case "2":
                    return "普通";
                case "3":
                    return "一般";
            }
            return "非常紧急";
        }
    }