Excel动态表头模板下载

　　最近遇到表头字段不固定，需要下载Excel模板的场景，特意记录一下
　　表头数据动态生成，根据业务场景不同加载不同表头
　　Controller/**  * 下载动态模板  * @param response  * @return  */ @PostMapping(value = ＂/downloadTemplate＂, produces = ＂application/json;charset=UTF-8＂) @PreAuth(value = ＂buSuppDelMeritsDataMaintYW:download＂, project = ParamConstants.DEFAULT_PROJECT, connect = ParamConstants.DEFAULT_PERMISSION_CONNECT, logical = Logical.OR) @Log(title = TITLE_CONTENT + ＂清单下载＂, navName = NAV_NAME, actionUniqueKey = NEMU_NAME + ＂,＂ + ＂buSuppDelMeritsDataMaintYW:download＂, interfaceType = InterfaceType.INSIDE, logType = ＂2＂) public void downloadTemplate(@RequestBody SrmRecVO vo, HttpServletResponse response) {    buContractSignManageService.downloadTemplate(response,vo); }
　　ServiceImpl/**  * 下载动态模板  * @param response  * @return  */ @Override public void downloadTemplate(HttpServletResponse response,SrmRecVO vo) {      //获取动态表头     if (StringUtils.isEmpty(vo.getObjectID())&&StringUtils.isEmpty(vo.getObjectType())){     throw new BadRequestException(MsgConstants.DEFAULT_PARAMETERS_IS_NULL);  }     // 查询合同模板中制定表格类型变量  List variableList = buTemplateVariableService.list(new LambdaQueryWrapper()       .eq(BuNonProEcmVariableEntity::getTemplateCode, vo.getObjectID())       .eq(BuNonProEcmVariableEntity::getTableFlag, BuContractManageConstants.YES_OR_NO_YES)        .eq(BuNonProEcmVariableEntity::getVariableName, vo.getObjectType())    );    if (CollectionUtils.isNotEmpty(variableList)) {       // 查询表格的自定义变量       List tableVariableList = buTemplateTableVariableService.list(new LambdaQueryWrapper()          .eq(BuNonProEcmtableVariableEntity::getTemplateCode, vo.getObjectID())       );       if (CollectionUtils.isEmpty(tableVariableList)) {          throw new BadRequestException(MsgConstants.BU_NON_PRO_CONTRACT_TEMPLATE_TABLE_ERROR);       }        List headers = new ArrayList<>();       for (BuNonProEcmtableVariableEntity variableEntity : tableVariableList) {          headers.add(variableEntity.getVariableName());  //加载表头数据       }       //创建workBook       HSSFWorkbook hssfWorkbook = new HSSFWorkbook();       HSSFSheet sheet = hssfWorkbook.createSheet();            addHeaders(hssfWorkbook,sheet,headers);            String filename=vo.getObjectType()+System.currentTimeMillis();       ServletOutputStream fos = null;       try {          fos = response.getOutputStream();          //mime类型          response.setContentType(＂application/vnd.openxmlformats-officedocument.spreadsheetml.sheet＂);          response.setHeader(＂Content-disposition＂, ＂inline; filename = ＂ + filename + ＂.xls＂);          hssfWorkbook.write(fos);          fos.close();       } catch (Exception e) {          e.printStackTrace();       }    }  }/**  * 初始化表头  *  * @param wb  * @param mainSheet  * @param headers  */ private static void addHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List headers) {    //表头样式,居中加粗    HSSFCellStyle style = wb.createCellStyle();    HSSFFont font = wb.createFont();    font.setBold(true); //字体加粗    style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());//设置背景色    style.setFillPattern(FillPatternType.FINE_DOTS); //设置图案样式    style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());    style.setAlignment(HorizontalAlignment.CENTER); //水平居中    style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中    //生成sheet1内容，第一个sheet的第一行为标题    HSSFRow rowFirst = mainSheet.createRow(0);    //冻结第一行    mainSheet.createFreezePane(0, 1, 0, 1);    //写标题    for (int i = 0; i < headers.size(); i++) {       HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格       mainSheet.setColumnWidth(i, 4000); //设置每列的列宽       cell.setCellStyle(style); //加样式       cell.setCellValue(headers.get(i)); //往单元格里写数据    } }