前言：
而今咱们对“apachepoi循环写数据库”可能比较看重，朋友们都想要知道一些“apachepoi循环写数据库”的相关资讯。那么小编在网络上搜集了一些关于“apachepoi循环写数据库””的相关内容，希望朋友们能喜欢，各位老铁们一起来学习一下吧！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>2.2、修饰实体对象的注解
将该注解修饰在实体对象的字段，并设置对应的表头名称，没有说明则取实体对象的字段名作为表头标题，哪些字段被修饰则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结果图
灵雨制作
版权声明：
本站文章均来自互联网搜集，如有侵犯您的权益，请联系我们删除，谢谢。
