1 简介
基于这几天使用excel模板上传到指定文件服务器的方法比较繁琐elementui隐藏表头elementui隐藏表头，所以决定使用apache.poi和注解来生成对应的excel模板。 具体实现方法如下
2.实现步骤2.1，添加maven依赖
org.apache.poi
poi-ooxml-schemas
4.1.2
org.apache.poi
poi
4.1.2
org.apache.poi
poi-ooxml
4.1.2
0ccb52f2079c96766d5586f60c797e48
修改实体对象数组上的注解，并设置对应的头名称。 如果没有描述，将使用实体对象的数组名称作为标题标题。 修改哪些数组，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
*/
@Component
public class ExcelTemplateUtil {
/**
* 根据实体对象的字段生成对应的excel模板
*
* @param sheetNameAndModelClasses 工作表名称集合和工作表表头对应的实体集合
* @param
*/
public ResponseEntity downloadExcelTemplate(Map<String, Class> sheetNameAndModelClasses,
String fileName) {
//工作簿
SXSSFWorkbook workbook = new SXSSFWorkbook();
//拿到所有的sheetName
if (Objects.isNull(sheetNameAndModelClasses) || sheetNameAndModelClasses.isEmpty()) {
throw new RuntimeException("sheet名称和对应表头实体不能为空!");
}
//获取map中所有的key=sheetName名称
Set sheetNames = sheetNameAndModelClasses.keySet();
for (String sheetName : sheetNames) {
//sheet表头标题对应的实体class
Class 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
*/
private 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 void buildRowHeader(Class firstData, Sheet sheet, SXSSFWorkbook workbook) {
//创建表头
Row headRow = sheet.createRow(0);
//获取对象所有的字段
Field[] fields = firstData.getDeclaredFields();
//获取被指定注解修饰的对象的字段个数
List 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
*/
private 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 exportExcelTemplate() throws Exception {
String fileName = "财务报表导出";
String sheetName = "财务报表导出-001";
String sheetName1 = "财务报表导出-002";
Map<String, Class> sheetNameAndModelClasses = new HashMap();
sheetNameAndModelClasses.put(sheetName, FncConfDefFmt.class);
sheetNameAndModelClasses.put(sheetName1, FncConfDefFmt.class);
ResponseEntity responseEntity = excelTemplateUtil.downloadExcelTemplate(sheetNameAndModelClasses,fileName);
return responseEntity;
}
检测结果
Excel 结果图
灵雨制作
