需求描述
原生的 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的时候自定义显示内容
加入了多页报表的生成
