需求描述
原生的 apache poi 提供的 api 生成 excel 比较繁琐。我们今天要实现的是利用注解来快速封装 poi 生成 excel。
我们封装好的方法非常方便,而且兼容常见的数据类型。目标效果如下:
@ExcelSheet 提供生成的单页excel的名字
@ExcelCell 提供了每行的标题和位置
@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,最后输出到一个输出流就好了
ExcelUtil.newSingleSheetExcel(ReportData.class) .fillData(reportData) .flush(outputStream);
详细代码实现
首先定义2个注解
@Target({ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface ExcelSheet { String value() default ""; }
@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类了:
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的时候自定义显示内容
加入了多页报表的生成