需求描述
原生的 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 9 10 11 12 13 14 |
@Target({ElementType.FIELD}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ExcelCell { String headerName() default ""; int columnIndex(); String nullDescription() default ""; String emptyDescription() default ""; short alignment() default HSSFCellStyle.ALIGN_CENTER; } |
然后最核心的就是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 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 |
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.math.BigDecimal; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; public final class ExcelUtil { private ExcelUtil() { } /** * usage: * ExcelUtil.newSingleSheetExcel(SomeClass.class) * .fillDate(List<SomeClass> data) * .flush(outputStream); * * @param clazz this class must contains @ExcelSheet and @ExcelCell annotation * @return inner class */ 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); } /** * usage: * ExcelUtil.newMultipleSheetsExcel() * .newSheet(SomeClass.class) * .fillDate(List<SomeClass> data) * .newSheet(SomeClassTwo.class) * .fillDate(List<SomeClassTwo> data) * .flush(outputStream); * * @return inner class * @see ExcelUtil newSingleSheetExcel() */ public static InnerExcelClass newMultipleSheetsExcel() { return new InnerExcelClass(new XSSFWorkbook()); } 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 Class<?> clazz; private XSSFSheet sheet; private final XSSFCellStyle defaultCellStyle; private final Map<Short, XSSFCellStyle> customCellStyles; 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.defaultCellStyle = getDefaultCellStyle(); customCellStyles = new HashMap<>(); } private InnerExcelClass(XSSFWorkbook xssfWorkbook) { this.workbook = xssfWorkbook; this.defaultCellStyle = getDefaultCellStyle(); customCellStyles = new HashMap<>(); } public InnerExcelClass newSheet(Class<?> clazz) { this.clazz = clazz; this.sheet = workbook.createSheet(clazz.getAnnotation(ExcelSheet.class).value()); initHeader(sheet.createRow(0), clazz, getHeaderStyle(workbook)); return this; } 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; } 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, excelCell); XSSFCellStyle cellStyle = excelCell.alignment() == HSSFCellStyle.ALIGN_CENTER ? defaultCellStyle : getCustomCellStyle(excelCell.alignment()); cell.setCellStyle(cellStyle); field.setAccessible(accessible); } } private XSSFCellStyle getCustomCellStyle(short alignment) { if (!customCellStyles.containsKey(alignment)) { XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(alignment); customCellStyles.put(alignment, cellStyle); } return customCellStyles.get(alignment); } private void getAndFillCellValue(Object reportData, Field field, XSSFCell cell, ExcelCell excelCell) { 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.equals("") ? excelCell.emptyDescription() : 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 || fieldClass == boolean.class) { boolean value = field.getBoolean(reportData); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_BOOLEAN); } else if (fieldClass == Date.class) { String value = DateUtil.formatToDay((Date) field.get(reportData)); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_STRING); } else if (fieldClass == BigDecimal.class) { String value = field.get(reportData).toString(); cell.setCellValue(value); cell.setCellType(Cell.CELL_TYPE_STRING); } else { throw new RuntimeException(fieldClass + " is not supported."); } } catch (NullPointerException npe) { cell.setCellValue(excelCell.nullDescription()); 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 getDefaultCellStyle() { XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); return cellStyle; } } } |
好了，打完手工！
2020-09-21日更新，
加入了null和empty的时候自定义显示内容
加入了多页报表的生成