1、前言

基于这几天在工作中发现使用excel模板上传到指定文件服务器的方式比较繁琐，我决定采用apache.poi和注解的方式生成对应的excel模板，具体的实现方式如下

2、实现步骤2.1、添加maven依赖

     <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml-schemas</artifactId>            <version>4.1.2</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>4.1.2</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>4.1.2</version>        </dependency>

将该注解修饰在实体对象的字段，并设置对应的表头名称，没有说明则取实体对象的字段名作为表头标题，哪些字段被修饰则excel模板的标题就有几个

package com.ljxy.score.excelHandler.annotation;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * @ClassName: ExcelHeaderAlias * @Description: TODO excel标题别名 * @Author: shenshixi * @Date 2023-07-25 20:16:53 * @Version 1.0.0 */@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface ExcelHeaderAlias {    /**     * excel表头的标题名称     *     * @return     */    String headerName() default "";    /**     * excel表头的标题是否需要进行特殊提示，默认不需要     *     * @return     */    boolean option() default false;    /**     * excel表头的标题提示信息     *     * @return     */    String optionMsg() default "";}

2.3、生成excel模板的实现类

package com.ljxy.score.excelHandler.handler;import com.ljxy.score.excelHandler.annotation.ExcelHeaderAlias;import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddressList;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.springframework.http.HttpHeaders;import org.springframework.http.MediaType;import org.springframework.http.ResponseEntity;import org.springframework.stereotype.Component;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.lang.reflect.Field;import java.net.URLEncoder;import java.nio.charset.StandardCharsets;import java.util.*;/** * @ClassName: ExcelTemplateUtil * @Description: TODO * @Author: shenshixi * @Date 2023-07-25 19:13:27 * @Version 1.0.0 */@Componentpublic class ExcelTemplateUtil {    /**     * 根据实体对象的字段生成对应的excel模板     *     * @param sheetNameAndModelClasses 工作表名称集合和工作表表头对应的实体集合     * @param <T>     */    public <T> ResponseEntity<byte[]> downloadExcelTemplate(Map<String, Class<T>> sheetNameAndModelClasses,                                                            String fileName) {        //工作簿        SXSSFWorkbook workbook = new SXSSFWorkbook();        //拿到所有的sheetName        if (Objects.isNull(sheetNameAndModelClasses) || sheetNameAndModelClasses.isEmpty()) {            throw new RuntimeException("sheet名称和对应表头实体不能为空!");        }        //获取map中所有的key=sheetName名称        Set<String> sheetNames = sheetNameAndModelClasses.keySet();        for (String sheetName : sheetNames) {            //sheet表头标题对应的实体class            Class<T> headerClass = sheetNameAndModelClasses.get(sheetName);            //生成工作表            SXSSFSheet sheet = workbook.createSheet(sheetName);            buildRowHeader(headerClass, sheet, workbook);            buildExcelStyle(sheet);        }        String tempFileName = fileName + ".xlsx";        ;        byte[] excelBytes;        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {            workbook.write(outputStream);            excelBytes = outputStream.toByteArray();            tempFileName = URLEncoder.encode(tempFileName, StandardCharsets.UTF_8.name());        } catch (IOException e) {            throw new RuntimeException(e);        }        //设置响应头        HttpHeaders headers = new HttpHeaders();        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);        headers.setContentDispositionFormData("attachment", tempFileName);        return ResponseEntity.ok()                .headers(headers)                .contentType(MediaType.parseMediaType("application/octet-stream"))                .body(excelBytes);    }    /**     * 给excel单元格创建下拉列表     *     * @param sheet     * @param field     * @param <T>     */    private <T> void excelDropdownList(SXSSFSheet sheet, Field field) {        // 创建一个单元格，并设置下拉列表        // 创建一个下拉列表的选项数组        String[] dropdownOptions = {"Option 1", "Option 2", "Option 3"};        // 这里设置下拉列表的单元格范围，这里是第一行第一列        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);        DataValidationHelper validationHelper = sheet.getDataValidationHelper();        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(dropdownOptions);        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);        sheet.addValidationData(dataValidation);    }    /**     * 构建表头信息     *     * @param firstData     * @param sheet     */    private <T> void buildRowHeader(Class<T> firstData, Sheet sheet, SXSSFWorkbook workbook) {        //创建表头        Row headRow = sheet.createRow(0);        //获取对象所有的字段        Field[] fields = firstData.getDeclaredFields();        //获取被指定注解修饰的对象的字段个数        List<Field> newFields = new ArrayList<>();        for (Field field : fields) {            field.setAccessible(true);            if (field.isAnnotationPresent(ExcelHeaderAlias.class)) {                newFields.add(field);            }        }        for (int i = 0; i < newFields.size(); i++) {            //获取字段            Field field = newFields.get(i);            //获取字段修饰的注解设置别名            ExcelHeaderAlias fieldAnnotation = field.getAnnotation(ExcelHeaderAlias.class);            if (Objects.isNull(fieldAnnotation)) {                continue;            }            //获取注解的值            String fieldAlias = fieldAnnotation.headerName();            //没有别名，则用字段名            String columName = StringUtils.isBlank(fieldAlias) ? field.getName() : fieldAlias;            //创建单元格            Cell cell = headRow.createCell(i);            //设置表头名称            cell.setCellValue(columName);            boolean option = fieldAnnotation.option();            if (option) {                Drawing<?> drawing = sheet.createDrawingPatriarch();                // 创建注释对象                CreationHelper creationHelper = workbook.getCreationHelper();                int columnIndex = cell.getColumnIndex();                int rowNum = headRow.getRowNum();                String optionMsg = fieldAnnotation.optionMsg();                excelCellOptMsg(drawing, creationHelper, cell, columnIndex, rowNum, optionMsg);            }        }    }    /**     * 给excel单元格创建提示信息     *     * @param drawing     * @param creationHelper     * @param cell     * @param <T>     */    private <T> void excelCellOptMsg(Drawing<?> drawing, CreationHelper creationHelper, Cell cell,                                     int ColumnIndex, int rowIndex, String optionMsg) {        ClientAnchor anchor = creationHelper.createClientAnchor();        anchor.setCol1(ColumnIndex);        anchor.setCol2(cell.getColumnIndex() + 1);        anchor.setRow1(rowIndex);        Comment comment = drawing.createCellComment(anchor);        RichTextString commentText = creationHelper.createRichTextString(optionMsg);        comment.setString(commentText);        cell.setCellComment(comment);    }    /**     * 构建excel表格的样式     *     * @param sheet     */    private void buildExcelStyle(Sheet sheet) {        //创建工作簿        Workbook workbook = sheet.getWorkbook();        //表头样式        CellStyle headerCellStyle = workbook.createCellStyle();        headerCellStyle.setFillBackgroundColor(IndexedColors.PINK1.getIndex());        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);        headerCellStyle.setBorderTop(BorderStyle.THIN);        headerCellStyle.setBorderBottom(BorderStyle.THIN);        headerCellStyle.setBorderRight(BorderStyle.THIN);        headerCellStyle.setBorderLeft(BorderStyle.THIN);        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);        //字体        Font headFont = workbook.createFont();        headFont.setBold(true);        headerCellStyle.setFont(headFont);        //excel表格内容样式        CellStyle cellStyle = workbook.createCellStyle();        cellStyle.setBorderTop(BorderStyle.THIN);        cellStyle.setBorderBottom(BorderStyle.THIN);        cellStyle.setBorderRight(BorderStyle.THIN);        cellStyle.setBorderLeft(BorderStyle.THIN);        int index = 0;        //设置表头和单元样式到单元格        for (Row row : sheet) {            for (Cell cell : row) {                if (row.getRowNum() == 0) {                    cell.setCellStyle(headerCellStyle);                } else {                    cell.setCellStyle(cellStyle);                }                // 计算内容的长度并设置列宽度                sheet.setColumnWidth(index,  5000);                index++;            }        }    }}

3、测试代码

测试代码如下

   @ApiOperation("导出excel模板")    @GetMapping(value = "/export-excel-template-test",produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)    public  ResponseEntity<byte[]> exportExcelTemplate() throws Exception {        String fileName = "财务报表导出";        String sheetName = "财务报表导出-001";        String sheetName1 = "财务报表导出-002";        Map<String, Class<FncConfDefFmt>> sheetNameAndModelClasses = new HashMap<>();        sheetNameAndModelClasses.put(sheetName, FncConfDefFmt.class);        sheetNameAndModelClasses.put(sheetName1, FncConfDefFmt.class);        ResponseEntity<byte[]> responseEntity = excelTemplateUtil.downloadExcelTemplate(sheetNameAndModelClasses,fileName);        return responseEntity;    }

测试结果

excel结果图

灵雨制作