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表头
@Datapublic 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 {@Overridepublic 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 {@Overridepublic 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());}
