本文记录一种通用的导出Excel文件的通用类。
1.在pom.xml中添加POI相关依赖
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
2.Excel字段注解类
package com.example.study.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField {
/**
* 排序,该字段值越小,排序越前
*/
int ordinal() default 0;
/**
* 列宽,取值范围0-255,默认16,当列宽<0或>255时,使用默认列宽
*/
int columnWidth() default 16;
/**
* 字段名
*/
String columnName();
/**
* 时间类型的格式化,目前支持Date, LocalDate, LocalTime, LocalDateTime四种时间类型
*/
String format() default "yyyy-MM-dd HH:mm:ss";
}
3.Excel导出类
package com.example.study.util;
import com.example.study.annotation.ExcelField;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Optional;
/**
* 导出Excel
*/
@Slf4j
public class ExcelUtil {
/**
* 导出excel文件
*
* @param collection 数据集合
* @param <T> 数据实体类
* @return workbook对象
*/
public static <T> XSSFWorkbook write(Collection<T> collection) {
return write(collection, null, null);
}
/**
* 导出excel文件
*
* @param collection 数据集合
* @param clazz 数据实体类的class
* @param <T> 数据实体类
* @return workbook对象
*/
public static <T> XSSFWorkbook write(Collection<T> collection, Class<?> clazz) {
return write(collection, clazz, null);
}
/**
* 导出excel文件
*
* @param collection 数据集合
* @param sheetName 工作表表名
* @param <T> 数据实体类
* @return workbook对象
*/
public static <T> XSSFWorkbook write(Collection<T> collection, String sheetName) {
return write(collection, null, sheetName);
}
/**
* 导出excel文件
*
* @param collection 数据集合
* @param clazz 数据实体类的class
* @param sheetName 工作表表名
* @param <T> 数据实体类
* @return workbook对象
*/
public static <T> XSSFWorkbook write(Collection<T> collection, Class<?> clazz, String sheetName) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(StringUtils.hasText(sheetName) ? sheetName : "Sheet0");
XSSFCellStyle titleStyle = createCellStyle(workbook);
List<FieldProperties> properties = null;
if (clazz != null) {
properties = getFieldProperties(clazz);
writeTitle(sheet, properties, titleStyle);
}
if (collection == null || collection.isEmpty()) {
return workbook;
}
if (properties == null) {
Optional<T> any = collection.stream().filter(o -> o != null).findAny();
if (!any.isPresent()) {
return workbook;
}
properties = getFieldProperties(any.get().getClass());
writeTitle(sheet, properties, titleStyle);
}
try {
writeData(sheet, properties, collection);
} catch (IllegalAccessException exception) {
log.error("throw IllegalAccessException when writeData:{}", exception.getMessage());
}
return workbook;
}
/**
* 返回表头的单元格格式
*
* @param workbook workbook对象
* @return 绿色背景的单元格格式
*/
private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return titleStyle;
}
/**
* 写入数据
*
* @param sheet 工作表
* @param properties 表头
* @param collection 数据
* @param <T> 数据实体类
* @throws IllegalAccessException 访问数据字段值时无访问权限
*/
private static <T> void writeData(XSSFSheet sheet, List<FieldProperties> properties, Collection<T> collection) throws IllegalAccessException {
int rowIndex = 1;
XSSFRow row;
for (T element : collection) {
if (element == null) {
continue;
}
row = sheet.createRow(rowIndex++);
for (int index = 0; index < properties.size(); index++) {
FieldProperties property = properties.get(index);
Object value = property.getField().get(element);
if (value == null) {
continue;
}
String type = property.getField().getType().getSimpleName();
switch (type) {
case "Date":
SimpleDateFormat format = new SimpleDateFormat(property.getExcelField().format());
row.createCell(index).setCellValue(format.format(value));
break;
case "LocalDate":
case "LocalTime":
case "LocalDateTime":
DateTimeFormatter formatter = DateTimeFormatter.ofPattern(property.getExcelField().format());
row.createCell(index).setCellValue(formatter.format((TemporalAccessor) value));
break;
default:
row.createCell(index).setCellValue(value.toString());
}
}
}
}
/**
* 写入表头
*
* @param sheet 工作表
* @param properties 表头
* @param titleStyle 表头格式
*/
private static void writeTitle(XSSFSheet sheet, List<FieldProperties> properties, XSSFCellStyle titleStyle) {
XSSFRow row = sheet.createRow(0);
for (int index = 0; index < properties.size(); index++) {
ExcelField excelField = properties.get(index).getExcelField();
String columnName = excelField.columnName();
XSSFCell cell = row.createCell(index);
cell.setCellValue(columnName);
cell.setCellStyle(titleStyle);
// 设置列宽
int columnWidth = excelField.columnWidth();
columnWidth = columnWidth < 0 || columnWidth > 255 ? 16 : columnWidth;
sheet.setColumnWidth(index, columnWidth * 256);
}
}
/**
* 获取表头字段
*
* @param clazz 数据实体类的class
* @return 排好序的表头字段
*/
private static List<FieldProperties> getFieldProperties(Class<?> clazz) {
Field[] declaredFields = clazz.getDeclaredFields();
List<FieldProperties> properties = new ArrayList<>();
for (Field declaredField : declaredFields) {
ExcelField excelField = declaredField.getAnnotation(ExcelField.class);
if (excelField == null) {
continue;
}
declaredField.setAccessible(true);
FieldProperties property = FieldProperties.builder().excelField(excelField).field(declaredField).build();
properties.add(property);
}
properties.sort((o1, o2) -> o1.getExcelField().ordinal() - o2.getExcelField().ordinal());
return properties;
}
@Getter
@Setter
@Builder
private static class FieldProperties {
private ExcelField excelField;
private Field field;
}
}