下载云盒历史Excel，反射机制获取数据

@GetMapping("/module/generator/fileUrl/{moduleGeneratorId}")
@Timed
@ApiOperation(value = "获取生成模组ID历史的下载地址",httpMethod = "GET",response = String.class ,notes = "获取生成模组ID历史的下载地址")
@ApiResponses(value = {
    @ApiResponse(code = 420 , message = "异常信息说明",response = ErrorResponseVM.class)
})
public ResponseEntity<?> getGeneratorHistoryFileUrl(@PathVariable("moduleGeneratorId")Integer moduleGeneratorId) throws URISyntaxException {
    HttpHeaders headers = HeaderUtil.createAlert(null, null);
    return new ResponseEntity<>(moduleGeneratorService.downLoadModuleIdAndSecretIdHistory(moduleGeneratorId), headers, HttpStatus.OK);
}

public String downLoadModuleIdAndSecretIdHistory(Integer moduleGeneratorId) {
    List<Module> moduleList=moduleRepository.findByGeneratorHistoryId(moduleGeneratorId);
    if (CollectionUtils.isEmpty(moduleList)){
        throw new RequestAlertException(JsonResultStatus.MODULE_GENERATOR_HISTORY_NOT_EXIST, this.getClass().getName());
    }
    List<String> moduleIdList= moduleList.stream().map(Module::getModuleId).collect(Collectors.toList());
    List<ModuleBox>  moduleBoxList=moduleBoxRepository.findAllByModuleIdInAndDelFlag(moduleIdList, "0");
    return getModuleIdFileUrl(moduleBoxList);
}

private String getModuleIdFileUrl(List<ModuleBox>  moduleBoxList) {
    SimpleDateFormat dft = new SimpleDateFormat("yyyy-MM-dd_HHmmss");
    String date = dft.format(new Date());
    String title[] = new String[]{"*云盒供应商","*云盒类型","*云盒型号","*云盒ID","*云盒秘钥","SIM卡运营商","IMSI","生成日期","操作员"};
    String sheetName = "云盒添加历史";
    String[] element = new String[]{"getCloudBoxSupplier", "getGoodsBoxType", "getSpecificType", "getModuleId", "getModuleSecretid", "getSimOperator", "getImsi", "getUpdateDate", "getUpdateBy"};
    try {
        String fileName = date + ".xls";
        Workbook wk = expExcel(title, sheetName, element, moduleBoxList);
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        wk.write(out);
        FileUploadDTO fileUploadDTO = new FileUploadDTO();
        fileUploadDTO.setFileBytesBase64(new String(Base64.encodeBase64(out.toByteArray())));
        fileUploadDTO.setFileName(fileName);
        FmFileDTO fmFileDTO = fileMgtManager.uploadfileBase64_new(fileUploadDTO).getBody();
        if (fmFileDTO != null) {
            log.debug("upload file content:" + JSON.toJSONString(fmFileDTO));
            return fmFileDTO.getCosPath();
        }
    } catch (Exception e) {
        log.error("ger excel file url fail:", e);
    }
    return null;
}

//动态导出Excel文件方法

@SuppressWarnings({"unchecked", "rawtypes"})
private Workbook expExcel(String[] title, String sheetName,
                          String[] element, Object data) {
    HSSFWorkbook wb = new HSSFWorkbook();
    SimpleDateFormat smf = new SimpleDateFormat("yyyy-MM-dd");
    Map <String, CellStyle> styles = createStyles(wb);
    Sheet sheet = wb.createSheet();
    wb.setSheetName(0, sheetName);
    sheet.setDisplayGridlines(false);
    sheet.setPrintGridlines(false);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 20);
    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    for (int i = 1; i <= title.length; i++) {
        Cell cell = headerRow.createCell(i - 1);
        cell.setCellValue(title[i - 1]);
        cell.setCellStyle(styles.get("header"));
    }

    sheet.createFreezePane(0, 1);
    sheet.setColumnWidth(0, 50 * 256);
    sheet.setColumnWidth(1, 50 * 256);
    sheet.setColumnWidth(2, 50 * 256);
    Row row;
    Cell cell;
    int rownum = 1;
    List <Object> list = (List <Object>) data;
    for (int i = 0; i < list.size(); i++) {
        Object ob = list.get(i);
        row = sheet.createRow(rownum);
        Class clazz = ob.getClass();
        for (int j = 0; j < element.length; j++) {
            cell = row.createCell(j);
            String styleName;
            boolean isHeader = i == 0
                || ((List <Object>) data).get(i - 1) == null;
            if (isHeader) {
                styleName = "cell_b";
            } else {
                styleName = "cell_normal";
            }
            String obj = "";
            try {
                if (clazz.getMethod(element[j]).invoke(ob) != null) {
                    if (clazz.getMethod(element[j]).getReturnType()
                        .equals(Date.class)) {
                        obj = smf.format(clazz.getMethod(element[j])
                            .invoke(ob));
                    } else {
                        obj = clazz.getMethod(element[j]).invoke(ob)
                            .toString();
                    }
                }
            } catch (Exception e) {
                log.error("exception:" + e.getMessage(), e);
            }
            cell.setCellValue(obj);
            cell.setCellStyle(styles.get(styleName));
        }
        rownum++;
    }
    return wb;
}

private static Map <String, CellStyle> createStyles(HSSFWorkbook wb) {
    Map <String, CellStyle> styles = new HashMap <>();
    DataFormat df = wb.createDataFormat();
    CellStyle style;
    Font headerFont = wb.createFont();
    style = createBorderedStyle(wb);
    style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    Font font1 = wb.createFont();
    style = createBorderedStyle(wb);
    style.setFont(font1);
    styles.put("cell_b", style);

    style = createBorderedStyle(wb);
    style.setWrapText(true);
    style.setDataFormat(df.getFormat("@"));
    styles.put("cell_normal", style);

    style = createBorderedStyle(wb);
    style.setWrapText(true);
    styles.put("cell_normal_centered", style);

    style = createBorderedStyle(wb);
    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    styles.put("cell_blue", style);

    return styles;
}

@SuppressWarnings("deprecation")
private static CellStyle createBorderedStyle(HSSFWorkbook wb) {
    HSSFCellStyle style = wb.createCellStyle();
    style.setWrapText(true);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setBorderTop(CellStyle.BORDER_THIN);
    return style;
}