java-poi实现excel自定义注解生成数据并导出

        因为项目很多地方需要使用导出数据excel的功能，所以开发了一个简易的统一生成导出方法。

依赖

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

定义注解

标题栏注解 

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelTitle {/*** 标题名称* @return 默认空*/String titleName() ;/*** 标题背景* @return 默认空*/IndexedColors titleBack() default IndexedColors.WHITE;/*** 标题文字大小* @return 默认空*/short titleSize() default 14;/*** 标题文字颜色* @return 黑色*/HSSFColor.HSSFColorPredefined titleColor() default HSSFColor.HSSFColorPredefined.BLACK;/*** 边框格式* @return 细*/BorderStyle borderStyle() default BorderStyle.THIN;/*** 边框颜色* @return 默认*/IndexedColors borderColor() default IndexedColors.AUTOMATIC;/*** 标题文字加粗* @return 黑色*/boolean boldFont() default true;/*** 是否忽略* @return 黑色*/boolean ignore() default false;/*** 排序* @return 0*/int order() default 0;
}

数据栏注解

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** @description： excel导出结果配置*/
@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelProperty {/*** 背景* @return 默认空*/IndexedColors textBack() default IndexedColors.WHITE;/*** 内容类型,查看* @link org.apache.poi.ss.usermodel.BuiltinFormats* @return 默认TEXT*/String textType() default "TEXT";/*** 文字大小* @return 默认18*/short textSize() default 12;/*** 数据属于key:value时，可以自定义转换,配置格式为：key1=value;key2=value2;.....* @return 默认空*/String textKv() default "";/*** 文字颜色* @return 黑色*/HSSFColor.HSSFColorPredefined textColor() default HSSFColor.HSSFColorPredefined.BLACK;/*** 水平位置* @return 水平居中*/HorizontalAlignment horizontal() default HorizontalAlignment.CENTER;/*** 垂直位置* @return 垂直居中*/VerticalAlignment vertical() default VerticalAlignment.CENTER;/*** 文字加粗* @return 不加粗*/boolean boldFont() default false;
}

代码

 标题栏格式生成

        解析对象属性的@ExcelTitle注解的信息，并设置相关选项。

private CellStyle initTitleCellStyle(SXSSFWorkbook wb, Field titleField) {// 单元格样式XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();//水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);ExcelTitle excelTitle = titleField.getAnnotation(ExcelTitle.class);//背景颜色if(excelTitle != null && excelTitle.titleBack() != null){cellStyle.setFillForegroundColor(excelTitle.titleBack().getIndex());}  else {cellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());}//文字的设置字体颜色Font font = wb.createFont();if(excelTitle != null && excelTitle.titleColor() != null){font.setColor(excelTitle.titleColor().getIndex());}  else {font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());}font.setBold(excelTitle != null && excelTitle.boldFont());font.setFontHeightInPoints(excelTitle != null && excelTitle.titleSize() != 0 ? excelTitle.titleSize(): 12);cellStyle.setFont(font);//设置为文本格式cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat("TEXT"));cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//边框if(excelTitle != null && excelTitle.borderStyle() != null){cellStyle.setBorderTop(excelTitle.borderStyle());cellStyle.setBorderBottom(excelTitle.borderStyle());cellStyle.setBorderLeft(excelTitle.borderStyle());cellStyle.setBorderRight(excelTitle.borderStyle());} else {cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);}//边框if(excelTitle != null && excelTitle.borderColor() != null){cellStyle.setTopBorderColor(excelTitle.borderColor().getIndex());cellStyle.setBottomBorderColor(excelTitle.borderColor().getIndex());cellStyle.setLeftBorderColor(excelTitle.borderColor().getIndex());cellStyle.setRightBorderColor(excelTitle.borderColor().getIndex());} else {cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());cellStyle.setLeftBorderColor(IndexedColors.RED.getIndex());cellStyle.setRightBorderColor(IndexedColors.RED.getIndex());}return cellStyle;}

数据栏格式生成

  解析对象属性的@ExcelProperty注解的信息，并设置相关选项。

private CellStyle initCellStyle(SXSSFWorkbook wb, Field titleField) {// 单元格样式（垂直居中）XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();ExcelProperty excelProperty = titleField.getAnnotation(ExcelProperty.class);//水平居中if(excelProperty != null && excelProperty.horizontal() != null) {cellStyle.setAlignment(excelProperty.horizontal());}//垂直居中if(excelProperty != null && excelProperty.vertical() != null) {cellStyle.setVerticalAlignment(excelProperty.vertical());}//背景颜色if(excelProperty != null && excelProperty.textBack() != null){cellStyle.setFillForegroundColor(excelProperty.textBack().getIndex());}//文字的设置字体颜色Font font = wb.createFont();if(excelProperty != null && excelProperty.textColor() != null){font.setColor(excelProperty.textColor().getIndex());}font.setBold(excelProperty != null && excelProperty.boldFont());font.setFontHeightInPoints(excelProperty != null && excelProperty.textSize() != 0 ? excelProperty.textSize(): 12);cellStyle.setFont(font);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);//设置为文本格式cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat(excelProperty != null && excelProperty.textType() != null ? excelProperty.textType() : "TEXT"));cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);return cellStyle;}

同时提供一个默认的配置：

private CellStyle initDefaultCellStyle(SXSSFWorkbook wb) {// 单元格样式（垂直居中）XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();//水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置为文本格式cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat("TEXT"));//文字的设置Font font = wb.createFont();font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());   //设置字体颜色return cellStyle;}

解析数据，生成sheet

private SXSSFWorkbook creatBook(List<ExcelSheetVo> list) {//创建工作簿SXSSFWorkbook wb = new SXSSFWorkbook();for (ExcelSheetVo excelSheetVo : list) {createSXSSFSheet(excelSheetVo, wb);}return wb;}private SXSSFSheet createSXSSFSheet(ExcelSheetVo excelSheetVo, SXSSFWorkbook wb) {//创建工作簿SXSSFSheet sxssfSheet = wb.createSheet(excelSheetVo.getSheetName());//设置默认的行宽sxssfSheet.setDefaultColumnWidth(20);//设置morning的行高（不能设置太小，可以不设置）sxssfSheet.setDefaultRowHeight((short) 300);//设置morning的单元格格式//CellStyle style = initCellStyle(wb);//标题单元格格式//CellStyle titleStyle = initTitleCellStyle(wb, excelSheetVo.getDataClass());//初始化标题栏initTitle(wb, sxssfSheet, excelSheetVo.getDataClass());initSheetData(wb, sxssfSheet, excelSheetVo, 1);return sxssfSheet;}private void initSheetData(SXSSFWorkbook wb, SXSSFSheet sxssfSheet, ExcelSheetVo excelSheetVo, int dataStartLine) {if (CollectionUtils.isEmpty(excelSheetVo.getSheetData())) {return;}try {for (Object dataObject : excelSheetVo.getSheetData()) {SXSSFRow row = sxssfSheet.createRow(dataStartLine);Field[] fields = dataObject.getClass().getDeclaredFields();List<Field> fieldList = Arrays.stream(fields).filter(item -> item.getAnnotation(ExcelTitle.class) != null && !item.getAnnotation(ExcelTitle.class).ignore()).sorted(Comparator.comparingInt(item -> {ExcelTitle excelTitle = item.getAnnotation(ExcelTitle.class);return excelTitle.order();})).collect(Collectors.toList());for (int i = 0; i < fieldList.size(); i++) {//根据title的值对应的值SXSSFCell cell = row.createCell(i);Field field = fieldList.get(i);cell.setCellStyle(initCellStyle(wb, field));field.setAccessible(true);cell.setCellValue(dealValue(field, dataObject));}dataStartLine++;}} catch (Exception e){LOGGER.error("生成数据异常", e);}}

key:value数据解析

private String dealValue(Field field, Object dataObject) throws IllegalAccessException {ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);Object value = field.get(dataObject);if(value == null){return null;}if(excelProperty != null && StringUtils.isNotEmpty(excelProperty.textKv())){String[] kvs = excelProperty.textKv().split(";");Map<String, String> map = new HashMap<>();for(String str: kvs){map.put(str.split("=")[0], str.split("=")[1]);}return map.get(value.toString());}return value.toString();}

导出设置：

/*** 导出excel数据** @param response 亲求返回* @param list     每个sheet页的数据，一个elist表示一个sheet页* @param fileName 导出的名称* @return 结果*/public boolean creatExcel(HttpServletResponse response, List<ExcelSheetVo> list, String fileName) {SXSSFWorkbook wb = creatBook(list);//导出数据try {//设置Http响应头告诉浏览器下载这个附件response.reset();response.setCharacterEncoding("utf-8");response.setContentType("application/vnd.ms-excel");//名称要从新进行 ISO8859-1 编码否则会文件名称会乱码response.setHeader("Content-Disposition", "attachment;Filename=" + encodeFileName(fileName) + ".xlsx");OutputStream outputStream = response.getOutputStream();ByteArrayOutputStream baos = new ByteArrayOutputStream();wb.write(baos);outputStream.write(baos.toByteArray());baos.flush();baos.close();outputStream.close();} catch (Exception ex) {LOGGER.error("导出excel失败", ex);}return true;}private String encodeFileName(String fileName) {try {//fileName = java.net.URLEncoder.encode(fileName, "UTF-8");fileName = URLDecoder.decode(fileName, "UTF-8");return new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);} catch (UnsupportedEncodingException e) {return "未命名";}}

测试

定义数据实体

数据标题和数据定义

@Data
public class TestExcelVo {@ExcelTitle(titleName = "名称", titleColor = HSSFColor.HSSFColorPredefined.BLUE, borderStyle = BorderStyle.HAIR, titleSize = 12, titleBack = IndexedColors.YELLOW)@ExcelProperty()private String name;@ExcelTitle(titleName = "年龄", titleColor = HSSFColor.HSSFColorPredefined.GREEN, borderStyle = BorderStyle.HAIR, titleBack = IndexedColors.RED)@ExcelProperty(textType = "0", textColor = HSSFColor.HSSFColorPredefined.RED)private Integer age;@ExcelTitle(titleName = "性别", titleColor = HSSFColor.HSSFColorPredefined.BLUE, titleSize = 12, titleBack = IndexedColors.GREEN)@ExcelProperty(textKv = "0=男;1=女", textBack = IndexedColors.BROWN)private Integer sex;@ExcelTitle(titleName = "描述", titleColor = HSSFColor.HSSFColorPredefined.BLUE, titleBack = IndexedColors.GREEN)@ExcelProperty(boldFont = true, textSize = 18)private String des;
}

sheet的数据定义 

@Data
public class ExcelSheetVo<T> {/*** 每一个sheet页得名称*/private String sheetName;/*** 每个sheet里面得数据* 其中Object中得注解必须时包含 @ExcelTitle 和 @ExcelProperties*/private List<T> sheetData;/*** 数据对象得类型*/private Class dataClass;
}

测试代码

@RestController
@RequestMapping(value = "exceExport")
public class ExcelExportController {@GetMapping("testExport")public void testExport(HttpServletResponse response){String fileName = "测试sheet";List<ExcelSheetVo> sheetVoList = new ArrayList<>();for(int i = 0; i < 3; i++){sheetVoList.add(createSheetData(i));}new ExcelCreateUtil().creatExcel(response, sheetVoList, fileName);}private ExcelSheetVo<TestExcelVo> createSheetData(int index){ExcelSheetVo<TestExcelVo> excelSheetVo = new ExcelSheetVo<>();excelSheetVo.setDataClass(TestExcelVo.class);excelSheetVo.setSheetName("sheet" + index);List<TestExcelVo> testExcelVos = createTestExcelVo(new Random().nextInt(30) + 10, "sheet" + index);excelSheetVo.setSheetData(testExcelVos);return excelSheetVo;}private List<TestExcelVo> createTestExcelVo(int size, String sheetName){List<TestExcelVo> testExcelVos = new ArrayList<>();for(int i = 0; i < size; i++){TestExcelVo testExcelVo = new TestExcelVo();testExcelVo.setName(sheetName + "-" + i);testExcelVo.setAge(i);testExcelVo.setSex(i % 2);testExcelVo.setDes("哈哈哈哈哈" + i);testExcelVos.add(testExcelVo);}return testExcelVos;}
}

结果：