ExcelExcel GitHub地址

需求

通过浏览器下载Excel模版，要求指定的单元格有下拉选项，指定的表头有批注。

实现

pom.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.0.3.RELEASE</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.9</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
    <scope>compile</scope>
</dependency>

Excel表头

@Data
public class DownloadData {
    @ExcelProperty("标题1")
    @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
    private String title1;
    @ExcelProperty("标题2")
    private String title2;
    @ExcelProperty("标题3")
    private String title3;
}

单元格设置下拉框

public class CustomSheetWriteHandler extends AbstractCellWriteHandler {

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        // 设置单元格下拉框
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 100, 0, 0);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 数据可从数据库查询
        String[] array = {"测试1", "测试2", "测试3", "测试4"};
        DataValidationConstraint constraint = helper.createExplicitListConstraint(array);
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        sheet.addValidationData(dataValidation);
    }
}

表头设置批注

public class CommentWriteHandler extends AbstractRowWriteHandler {

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                Integer relativeRowIndex, Boolean isHead) {
        if (isHead) {
            Sheet sheet = writeSheetHolder.getSheet();
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
            // 在第一行 第二列创建一个批注
            Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 1, 0, (short) 2, 1));
            // 输入批注信息
            comment.setString(new XSSFRichTextString("测试批注!"));
            // 将批注添加到单元格对象中
            sheet.getRow(0).getCell(1).setCellComment(comment);
        }
    }
}

Http接口

浏览器直接请求该接口，即可下载Excel模版。

@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), DownloadData.class)
            .inMemory(Boolean.TRUE)
            .registerWriteHandler(new CommentWriteHandler())
            .registerWriteHandler(new CustomSheetWriteHandler())
            .sheet("测试模板")
            .doWrite(new ArrayList());
}