import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
/**
* Excel表头注解
*/
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
/**
* 标题
* @return 标题名称
*/
String title();
/**
* 顺序
* @return 序号
*/
int order() default 9999;
/**
* 是否必填字段
* @return 是否必填字段
*/
boolean isMustInput();
/**
* 标题是否需要前缀
* @return
*/
boolean needPrefix() default false;
}
import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import org.apache.commons.lang3.time.DateFormatUtils; public class DateUtils extends org.apache.commons.lang3.time.DateUtils { public static String formatDate(Date date, Object... pattern) { String formatDate = null; if (pattern != null && pattern.length > 0) { formatDate = DateFormatUtils.format(date, pattern[0].toString()); } else { formatDate = DateFormatUtils.format(date, "yyyy-MM-dd"); } return formatDate; } }
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.MediaType;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
/**
* excel导出工具类
*/
@Slf4j
public class ExcelExporterUtil {
@Data
public static class DataStructure {
private List<String> titles;
private List<List<Object>> dataList;
private String sheetName;
}
/**
* 导出excel
*
* @param response response
* @param dataList 数据
* @param excelName excel文件名
*/
public static void writeExcel(HttpServletResponse response, List<DataStructure> dataList, String excelName) {
if (CollectionUtils.isEmpty(dataList)) {
return;
}
Workbook wb = new XSSFWorkbook();
for (DataStructure structure : dataList) {
Sheet sheet = wb.createSheet(structure.getSheetName());
AtomicInteger ai = new AtomicInteger();
{
Row row = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
//写入头部
structure.getTitles().forEach(columnName -> {
Cell cell = row.createCell(aj.getAndIncrement());
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
Font font = wb.createFont();
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
List<List<Object>> list;
if (CollectionUtils.isNotEmpty(list = structure.getDataList())) {
for (List<Object> eleList : list) {
if (CollectionUtils.isNotEmpty(eleList)) {
Row row1 = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
for (Object obj : eleList) {
Cell cell = row1.createCell(aj.getAndIncrement());
if (obj != null) {
if (obj instanceof Date) {
cell.setCellValue(DateUtils.formatDate((Date) obj, "yyyy-MM-dd HH:mm:ss"));
} else {
cell.setCellValue(obj.toString());
}
}
}
}
}
}
wb.getSheet(structure.getSheetName()).createFreezePane(0, 1, 0, 1);
}
buildExcelDocument(excelName, wb, response);
}
/**
* 导出excel
*
* @param response response
* @param dataList 数据
* @param cls 实体类
* @param excelName excel文件名
* @param <T> 数据类型
*/
public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls, String excelName) {
List<Field> fieldList = getFields(cls);
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
writeHeaderAndList(dataList, fieldList, wb, sheet);
//冻结窗格
wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);
//浏览器下载excel
buildExcelDocument(excelName, wb, response);
}
/**
* 写入excel数据
*
* @param dataList 数据list
* @param fieldList 字段list
* @param wb wb
* @param sheet sheet页
* @param <T> 数据类型
*/
private static <T> void writeHeaderAndList(List<T> dataList, List<Field> fieldList, Workbook wb, Sheet sheet) {
AtomicInteger ai = new AtomicInteger();
{
Row row = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
//写入头部
fieldList.forEach(field -> {
ExcelField annotation = field.getAnnotation(ExcelField.class);
String columnName = "";
if (annotation != null) {
columnName = annotation.title();
}
Cell cell = row.createCell(aj.getAndIncrement());
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
Font font = wb.createFont();
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
if (CollectionUtils.isNotEmpty(dataList)) {
dataList.forEach(t -> {
Row row1 = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
fieldList.forEach(field -> {
Class<?> type = field.getType();
Object value = "";
try {
value = field.get(t);
} catch (Exception e) {
log.error("error={}",e);
}
Cell cell = row1.createCell(aj.getAndIncrement());
if (value != null) {
if (type == Date.class) {
cell.setCellValue(DateUtils.formatDate((Date) value, "yyyy-MM-dd HH:mm:ss"));
} else {
cell.setCellValue(value.toString());
}
}
});
});
}
}
/**
* 获取实体字段
*
* @param cls class
* @param <T> 实体类型
* @return
*/
private static <T> List<Field> getFields(Class<T> cls) {
Field[] fields = cls.getDeclaredFields();
// 查询出cls对象中需要导出的字段并按照顺序排序
return Arrays.stream(fields)
.filter(field -> {
ExcelField annotation = field.getAnnotation(ExcelField.class);
if (annotation != null && annotation.order() > 0) {
field.setAccessible(true);
return true;
}
return false;
}).sorted(Comparator.comparing(field -> {
int order = 0;
ExcelField annotation = field.getAnnotation(ExcelField.class);
if (annotation != null) {
order = annotation.order();
}
return order;
})).collect(Collectors.toList());
}
/**
* 设置,使得浏览器可以下载
*
* @param fileName 文件名称
* @param wb excel对象
* @param response HttpServletResponse
*/
private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {
try (OutputStream out = response.getOutputStream()) {
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
wb.write(out);
out.flush();
} catch (IOException e) {
log.error("error={}",e);
}
}
}
import lombok.Data;
import java.math.BigDecimal;
@Data
public class ObjectDto {
@ExcelField(title = "编码", order = 1, isMustInput = false)
private String code;
@ExcelField(title = "名称", order = 2, isMustInput = false)
private String name;
@ExcelField(title = "数量", order = 3, isMustInput = false)
private Integer num;
@ExcelField(title = "合计", order = 4, isMustInput = false)
private BigDecimal total;
}