需求描述
原生的 apache poi 提供的 api 生成 excel 比较繁琐。我们今天要实现的是利用注解来快速封装 poi 生成 excel。
我们封装好的方法非常方便，而且兼容常见的数据类型。目标效果如下：
@ExcelSheet 提供生成的单页excel的名字
@ExcelCell 提供了每行的标题和位置
|
1 2 3 4 5 6 7 8 9 10 11 |
@ExcelSheet("My Report") public class ReportData { @ExcelCell(headerName = "User Name", columnIndex = 0) private String username; @ExcelCell(headerName = "Age", columnIndex = 1) private int age; @ExcelCell(headerName = "Birthday", columnIndex = 2) private Date birthday; @ExcelCell(headerName = "Identity Id", columnIndex = 3) private long identityId; } |
这是封装好的生成方法：
生成一个单页excel，传入数据结构的class，传入list数组的reportData，最后输出到一个输出流就好了
|
1 2 3 |
ExcelUtil.newSingleSheetExcel(ReportData.class) .fillData(reportData) .flush(outputStream); |
详细代码实现
首先定义2个注解
|
1 2 3 4 5 6 |
@Target({ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ExcelSheet { String value() default ""; } |
|
1 2 3 4 5 6 7 8 |
@Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ExcelCell { String headerName() default ""; int columnIndex(); } |
然后最核心的就是ExcelUtil类了：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 |
package com.anz.moolah.common.util; import com.anz.moolah.common.annotation.ExcelCell; import com.anz.moolah.common.annotation.ExcelSheet; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.*; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; public final class ExcelUtil { public static InnerExcelClass newSingleSheetExcel(Class<?> clazz) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(clazz.getAnnotation(ExcelSheet.class).value()); initHeader(sheet.createRow(0), clazz, getHeaderStyle(workbook)); return new InnerExcelClass(workbook, clazz); } // 这里先把excel的第一行设置为头，同时把样式设置好，字体加粗 private static void initHeader(XSSFRow row, Class<?> clazz, XSSFCellStyle headerStyle) { Field[] fields = clazz.getDeclaredFields(); for (Field field : fields) { ExcelCell cellAnnotation = field.getAnnotation(ExcelCell.class); XSSFCell cell = row.createCell(cellAnnotation.columnIndex()); cell.setCellStyle(headerStyle); cell.setCellValue(cellAnnotation.headerName()); } } private static XSSFCellStyle getHeaderStyle(XSSFWorkbook workbook) { XSSFCellStyle headerStyle = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(font); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); return headerStyle; } public static class InnerExcelClass { private final XSSFWorkbook workbook; private final Class<?> clazz; private final XSSFSheet sheet; private final XSSFCellStyle cellStyle; private final static SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy/MM/dd"); private final Logger logger = LogManager.getLogger(ExcelUtil.class); private InnerExcelClass(XSSFWorkbook xssfWorkbook, Class<?> clazz) { this.workbook = xssfWorkbook; this.clazz = clazz; this.sheet = workbook.getSheetAt(0); this.cellStyle = getCellStyle(); } public InnerExcelClass fillData(List<?> reportData) { for (int index = 0; index < reportData.size(); index++) { XSSFRow row = sheet.createRow(index + 1); fillRowData(reportData.get(index), row); } // 填充完所有数据后， 把所有数据表格的宽度自适应一下 autoSizeColumn(); return this; } // 你可以输出到文件流，也可以是 httpresponse 的相应流 public void flush(OutputStream outputStream) throws IOException { workbook.write(outputStream); } private void fillRowData(Object reportData, XSSFRow row) { Field[] fields = clazz.getDeclaredFields(); for (Field field : fields) { boolean accessible = field.isAccessible(); field.setAccessible(true); ExcelCell excelCell = field.getAnnotation(ExcelCell.class); XSSFCell cell = row.createCell(excelCell.columnIndex()); getAndFillCellValue(reportData, field, cell); cell.setCellStyle(cellStyle); field.setAccessible(accessible); } } // 支持基础数据类型和 Date 时间类型，可以继续添加自定义类型 private void getAndFillCellValue(Object reportData, Field field, XSSFCell cell) { Class<?> fieldClass = field.getType(); try { if (fieldClass == Integer.class || fieldClass == int.class) { int value = field.getInt(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (fieldClass == Short.class || fieldClass == short.class) { short value = field.getShort(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (fieldClass == Long.class || fieldClass == long.class) { long value = field.getShort(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (fieldClass == String.class) { String value = field.get(reportData).toString(); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_STRING); } else if (fieldClass == Double.class || fieldClass == double.class) { double value = field.getDouble(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (fieldClass == Float.class || fieldClass == float.class) { float value = field.getFloat(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (fieldClass == Byte.class || fieldClass == byte.class) { byte value = field.getByte(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (fieldClass == Character.class || fieldClass == char.class) { char value = field.getChar(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_STRING); } else if (fieldClass == Boolean.class) { boolean value = field.getBoolean(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_BOOLEAN); } else if (fieldClass == Date.class) { String value = DATE_FORMAT.format((Date) field.get(reportData)); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_STRING); } else { throw new RuntimeException(fieldClass + " is not supported."); } } catch (NullPointerException npe) { // 如果没有拿到数据，报NPE，我们设置为空 cell.setCellValue(""); cell.setCellType(Cell.CELL_TYPE_STRING); } catch (IllegalAccessException e) { logger.error(e.getMessage()); } } private void autoSizeColumn() { int columnCount = clazz.getDeclaredFields().length; while (columnCount > 0) { sheet.autoSizeColumn(--columnCount); } } private XSSFCellStyle getCellStyle() { XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); return cellStyle; } } } |
好了，代码感兴趣的可以直接拷贝到项目慢慢研究。有多页excel需求的小伙伴可以自己实现一个ExcelUtil.newMultipleSheetExcel()方法哦！：）