需求
我们经常会遇到需要生成excel文件的需要，这个时候通常会使用apache提供的poi，但原生poi提供的api使用起来比较繁琐，所以我们实现利用注解的方式来快速封装
对象注入
@ExcelName 单页excel的名称
@ExcelRow 每行标题以及位置
@ExcelName("user list")
public class ReportData {
@ExcelRow(headerName = "User Name", columnIndex = 0)
private String username;
@ExcelRow(headerName = "Age", columnIndex = 1)
private int age;
@ExcelRow(headerName = "Sex", columnIndex = 2)
private String sex;
@ExcelRow(headerName = "User Id", columnIndex = 3)
private String userId;
}
调用封装方法
在封装的方法中传入对象的class，包含数据的list数组reportData，再输出到一个输出流就完成了
ExcelUtil.newSingleExcel(ReportData.class)
.fillData(reportData)
.flush(outputStream);
具体代码实现
定义两个我们需要的注解
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelName {
String value() default "";
}
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelRow {
String headerName() default "";
int columnIndex();
}
核心ExcelUtil类
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 newSingleExcel(Class<?> clazz) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(clazz.getAnnotation(ExcelName.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) {
ExcelRow cellAnnotation = field.getAnnotation(ExcelRow.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 的相应流
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);
ExcelRow excelCell = field.getAnnotation(ExcelRow.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;
}
}
}
网友评论