文件下载（导出excel文件）可自定义宽高样式字体大小

文件下载（导出excel文件）
（学习笔记仅供参考）
方式一：
controller层：

@GetMapping("/download")
    @ApiOperation(value = "下载")
    public void download(HttpServletResponse response){
        xiaZaiService.loading(response);
    }

service层：

@Override
    public void loading(HttpServletResponse response) {
        //查找企业数据
        list<EntApplyInfoListVo> list = xiaZaiMapper.selectEntApplyListByPage();
        if(list.size <= 0){
            return;
        }
        try(OutputStream out = response.getOutputStream()){
            //创建excel工作表对象
            XFFSheet sheet = ExcelUtils.create();
            //导出Excel文件名
            String fileName = "企业申办明细表".concat("(")
                                             .concat("yyy-MM-dd")
                                             .concat(")")
                                             .concat(".xlsx");
            //创建表头
            List<CoumnDTO> datelist = this.createExcelHeader(list);
            ExcrlUtils.createTitle(sheet,list);
            //创建内容按照参数合并
            ExcrlUtils.createContent(sheet,list);
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition","attachment;filename="+ java.net.URLEncoder.encode(fileName,"UTF-8"));
            sheet.getWorkbook().write(out);
            out.flush();
        }catch (IOException ex){
            log.error("构建文件失败",ex);
        }

    }

    /**
     * 创建表头
     */
    private List<ColumDTO> createExcelHeader(list<EntApplyInfoListVo> list){
        List<ColumnDTO> dateList = new ArrayList<>(7);
        String titles = "办事编码，企业名称，服务主题，服务事项，实施主体，服务状态";
        for (String title : titles.split(",")) {
            ColnmnDTO column = new ColumnDTO;
            column.setTitle(title);
            column.setValueList(new ArrayList<>());
            dateList.add(column);
        }
        //设置企业名称合并单元格
        dateList.get(0).setMerge(true);
        list.forEach(e->{
            dateList.get(0).getValueList().add(Optional.ofNullable(e.getProcessCode()).orElse(""));//Optional.ofNullable
            dateList.get(0).getValueList().add(e.getEntName());
            dateList.get(0).getValueList().add(e.getItemName());
            dateList.get(0).getValueList().add(e.getIndName());
            dateList.get(0).getValueList().add(e.getOrgName());
            dateList.get(0).getValueList().add(e.getWarnState());
        });
        return dateList;
    }

实体类：

@Date
public class ColimnDTO {
    
    @ApiModelProperty(value = "表格标题")
    private String title;
    
    @ApiModelProperty(value = "是否加粗",example = "true")
    private boolean bold = true;
    
    @ApiModelProperty(value = "字体颜色",example = "#fff")
    private String color;
    
    @ApiModelProperty(value = "合并单元格",example = "false")
    private boolean merge;
    
    @ApiModelProperty(value = "列值")
    List<String> valueList;
}

方式二（没有封装方法）：

@ApiOperation(value = "导出合同", notes = "导出选中的合同列表")
    @PostMapping(value = "export")
    void exportContract(HttpServletResponse response, @Validated @RequestBody ContractExportReqDTO reqDTO) {
        final List<ContractRspDTO> dtoList = contractService.queryByContractUuids(reqDTO.getContractUuids());
        try (final Workbook workbook = WorkbookFactory.create(false); OutputStream os = response.getOutputStream()) {
            //第一张表
            final Sheet sheet0 = workbook.createSheet("服务信息表");
            final Row row0 = sheet0.createRow(0);
            final Font font = workbook.createFont();
            final CellStyle style = workbook.createCellStyle();
            font.setFontHeightInPoints((short) 12);
            style.setFont(font);
            CellUtil.createCell(row0, 0, "流水号", style);
            CellUtil.createCell(row0, 1, "预约用户", style);
            CellUtil.createCell(row0, 2, "预约服务编号", style);
            CellUtil.createCell(row0, 3, "合同编号", style);
            CellUtil.createCell(row0, 4, "合同金额", style);
            CellUtil.createCell(row0, 5, "实际服务编号", style);
            final Font requireFont = workbook.createFont();
            requireFont.setColor(Font.COLOR_RED);
            requireFont.setFontHeightInPoints((short) 12);
            CellUtil.createCell(row0, 6, "*实际服务时间段/服务人员");
            RichTextString richTextString1 = new HSSFRichTextString("*实际服务时间段/服务人员");
            richTextString1.applyFont(0, 1, requireFont);
            richTextString1.applyFont(1, 12, font);
            final Cell cell6 = row0.createCell(6);
            cell6.setCellValue(richTextString1);
            RichTextString richTextString2 = new HSSFRichTextString("*仪器清单与实际机时（小时）");
            richTextString2.applyFont(0, 1, requireFont);
            richTextString2.applyFont(1, 14, font);
            final Cell cell7 = row0.createCell(7);
            cell7.setCellValue(richTextString2);
            final Row row1 = sheet0.createRow(1);
            CellUtil.createCell(row1, 0, "填写说明：", style);
            CellUtil.createCell(row1, 3, "最大支持50位", style);
            CellUtil.createCell(row1, 4, "最大输入10位，单位元", style);
            CellUtil.createCell(row1, 7, "不可增删改仪器编号，机时支持2位小数，未使用到的仪器填0", style);
            ContractRspDTO dto;
            List<ContractRspDTO> collect =
                    dtoList.stream().sorted(Comparator.comparing(ContractRspDTO::getSerialNumber).reversed())
                            .collect(Collectors.toList());
            for (int i = 0, len = collect.size(); i < len; i++) {
                StringBuilder realTimeSb = new StringBuilder();
                StringBuilder instructAndTimeSb = new StringBuilder();
                StringBuilder servicePerson = new StringBuilder();
                dto = collect.get(i);
                final Row row = sheet0.createRow(i + 2);
                CellUtil.createCell(row, 0, dto.getSerialNumber(), style);
                CellUtil.createCell(row, 1, dto.getAppointPerson(), style);
                CellUtil.createCell(row, 2, dto.getAppointIdentifier(), style);
                CellUtil.createCell(row, 3, dto.getIdentifier(), style);
                CellUtil.createCell(row, 4, dto.getContractAmount(), style);
                CellUtil.createCell(row, 5, dto.getServiceName(), style);
                // 实际服务时间
                dto.getRealTimeList().forEach(tmp -> {
                    realTimeSb.append(Objects.nonNull(tmp.getStartTime()) ?
                            LocalDateTime.ofInstant(tmp.getStartTime().toInstant(), ZoneId.systemDefault())
                                    .format(DateTimeFormatter.ofPattern(DateTimeConstants.DATETIME_PATTEN)) : "").append("~")
                            .append(Objects.nonNull(tmp.getEndTime()) ?
                                    LocalDateTime.ofInstant(tmp.getEndTime().toInstant(), ZoneId.systemDefault())
                                            .format(DateTimeFormatter.ofPattern(DateTimeConstants.DATETIME_PATTEN)) : "")
                            .append("/").append(Objects.nonNull(tmp.getPersonInfo()) ? tmp.getPersonInfo() : "")
                            .append("\n");
                });
                CellUtil.createCell(row, 6,
                        realTimeSb.length() > 0 ? realTimeSb.substring(0, realTimeSb.length() - 1) : realTimeSb.toString(), style);
                // 仪器清单与实际机时
                dto.getInstructmentAndTimeList().forEach(tmp -> {
                    instructAndTimeSb.append(Objects.nonNull(tmp.getIdentifier()) ? tmp.getIdentifier() : "")
                            .append("/").append(Objects.nonNull(tmp.getMeasurementUnit()) ? tmp.getMeasurementUnit() : "").
                            append("/").append(Objects.nonNull(tmp.getRealTime()) ? tmp.getRealTime() : "")
                            .append("\n");
                });
                CellUtil.createCell(row, 7,
                        instructAndTimeSb.length() > 0 ? instructAndTimeSb.substring(0, instructAndTimeSb.length() - 1) :
                                instructAndTimeSb.toString(), style);
            }
            //第一张表
            final Sheet sheet1 = workbook.createSheet("服务和仪器编号");
            final Row row2 = sheet1.createRow(0);
            final Font font2 = workbook.createFont();
            final CellStyle style2 = workbook.createCellStyle();
            font2.setFontHeightInPoints((short) 12);
            style.setFont(font2);
            CellUtil.createCell(row2, 0, "服务名称", style2);
            CellUtil.createCell(row2, 1, "服务编号", style2);
            CellUtil.createCell(row2, 2, "服务关联仪器", style2);
            CellUtil.createCell(row2, 3, "仪器编号", style2);
            List<ContractServiceDTO> contractServiceDTOList=new ArrayList<>();
            for (ContractRspDTO contractRspDTO : dtoList) {
                List<ContractServiceDTO> contractServiceDTOList1 = contractRspDTO.getContractServiceDTOList();
                for (ContractServiceDTO contractServiceDTO : contractServiceDTOList1) {
                    ContractServiceDTO contractServiceDTO1=new ContractServiceDTO();
                    contractServiceDTO1.setServiceName(contractServiceDTO.getServiceName());
                    contractServiceDTO1.setServiceIdentifier(contractServiceDTO.getServiceIdentifier());
                    contractServiceDTO1.setConstractIntrucmentDTOS(contractServiceDTO.getConstractIntrucmentDTOS());
                    contractServiceDTOList.add(contractServiceDTO1);
                }
            }
                ContractServiceDTO dto1;
                for (int i = 0, len = contractServiceDTOList.size(); i < len; i++) {
                    StringBuilder realTimeSb = new StringBuilder();
                    StringBuilder instructAndTimeSb = new StringBuilder();
                    StringBuilder servicePerson = new StringBuilder();
                    dto1 = contractServiceDTOList.get(i);
                    final Row row3 = sheet1.createRow(i + 2);
                    CellUtil.createCell(row3, 0, dto1.getServiceName(), style);
                    CellUtil.createCell(row3, 1, dto1.getServiceIdentifier(), style);
                    for (constractIntrucmentDTO constractIntrucmentDTO : dto1.getConstractIntrucmentDTOS()) {
                        CellUtil.createCell(row3, 2, constractIntrucmentDTO.getName(), style);
                        CellUtil.createCell(row3, 3, constractIntrucmentDTO.getIdentifier(), style);
                    }
                }
            String fileTempName = URLEncoder.encode("服务信息数据模板.xls", "UTF-8");
            //处理空格
            fileTempName = fileTempName.replaceAll("\\+", "%20");
            response.setContentType("application/x-www-form-urlencoded;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileTempName);
            workbook.write(os);
            os.flush();
        } catch (IOException e) {
            // ignore
        }
    }

方式三：数据导出excel，可自定义行宽高，字体大小，颜色等，简单易懂。

	 @Override
    public void logExport(HttpServletResponse response) throws IOException {
        List<ChangeLogVO> changeLogList = rotatePlanConfigDetailHistoryMapper.selectLists();
        // Excel表开始
        String sheetname = "变更日志记录"; 
        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        //添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetname);

        //设置宽度(我这里只有8列)
        int[] width = {30, 30, 30, 30, 30, 30, 30,70};
        for (int columnIndex = 0; columnIndex < 8; columnIndex++) {
            sheet.setColumnWidth(columnIndex, width[columnIndex] * 256);
        }
        //设置样式集合
        Map<String, HSSFCellStyle> styles = addStyle(wb);
        int rowIndex;
        int colIndex;
        HSSFRow row0 = sheet.createRow(0);
        for (colIndex = 0; colIndex < 9; colIndex++) {
            row0.createCell(colIndex);
            row0.getCell(colIndex).setCellStyle(styles.get("header_center"));
        }
        for (int i = 0; i < logHeade.size(); i++) {
            row0.getCell(i).setCellValue(logHeade.get(i));
            row0.getCell(i).setCellStyle(styles.get("vicetitle"));
        }
        //塞入数据
        for (int i = 0; i < changeLogList.size(); i++) {
            HSSFRow rowi = sheet.createRow(i + 1);
            for (int j = 0; j < logHeade.size(); j++) {
                rowi.createCell(j);
                rowi.getCell(j).setCellStyle(styles.get("header_center"));
            }
            rowi.getCell(0).setCellValue(changeLogList.get(i).getClmc());
            rowi.getCell(1).setCellValue(changeLogList.get(i).getClms());
            rowi.getCell(2).setCellValue(changeLogList.get(i).getQymc());
            rowi.getCell(3).setCellValue(changeLogList.get(i).getKdmc());
            rowi.getCell(4).setCellValue(changeLogList.get(i).getBgyy());
            rowi.getCell(5).setCellValue(changeLogList.get(i).getBgr());
            rowi.getCell(6).setCellValue(DateUtil.format(changeLogList.get(i).getBgsj(),"yyyy-MM-dd HH:mm:ss"));
            rowi.getCell(7).setCellValue(changeLogList.get(i).getBgnr());
        }
        //生成文件
        String fileName = "变更日志记录.xlsx";
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        OutputStream outputStream = response.getOutputStream();
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    private Map<String, HSSFCellStyle> addStyle(HSSFWorkbook wb) {
        @SuppressWarnings({"unchecked", "rawtypes"})
        Map<String, HSSFCellStyle> styles = new HashMap();

        //设置字体
        HSSFFont headFont = wb.createFont();
//        headFont.setFontName("微软雅黑");
        headFont.setFontHeightInPoints((short) 16);
        headFont.setBold(true);

        HSSFFont bodyFont = wb.createFont();
//        bodyFont.setFontName("微软雅黑");
        bodyFont.setFontHeightInPoints((short) 11);

        HSSFFont headFont1 = wb.createFont();
//        headFont1.setFontName("微软雅黑");
        headFont1.setFontHeightInPoints((short) 16);
        headFont1.setBold(true);

        //标题行样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(headFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styles.put("title", style);

        //副标题行样式
        HSSFCellStyle vicestyle = wb.createCellStyle();
        vicestyle.setFont(headFont1);
        vicestyle.setWrapText(true);
        vicestyle.setFillForegroundColor((short) 27);
        //居中对齐
        vicestyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
//        style.setVerticalAlignment(VERTICAL_CENTER);
        styles.put("vicetitle", vicestyle);

        //数据头居中样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("header_center", style);

        //数据行居中样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        styles.put("data_center", style);

        //数据行居中底色样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        styles.put("data_center_color", style);

        //数据行居中底色样式2
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
        styles.put("data_center_color1", style);

        //数据行居左样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        style.setFillForegroundColor((short) 27);
//        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styles.put("data_left", style);

        //数据行居右样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
//        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        styles.put("data_right", style);
        //无边框样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        styles.put("data_noborder", style);
        //无底边框样式
        style = wb.createCellStyle();
        style.setFont(bodyFont);
        style.setWrapText(true);
        styles.put("data_bottom", style);

        return styles;
    }