public class ExcelUtil extends EasyExcel {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
private ExcelUtil() {}
public static <T> ExcelReaderBuilder read(String pathName, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
return read(pathName, head, new EasyExcelConsumerListener<>(pageSize, consumer));
}
public static <T> ExcelReaderBuilder read(File file, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
return read(file, head, new EasyExcelConsumerListener<>(pageSize, consumer));
}
public static <T> ExcelReaderBuilder read(InputStream inputStream, Class<T> head, Integer pageSize, Consumer<List<T>> consumer) {
return read(inputStream, head, new EasyExcelConsumerListener<>(pageSize, consumer));
}
public static void write(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class clazz) throws Exception {
EasyExcel.write(getOutputStream(fileName, response), clazz)
.excelType(ExcelTypeEnum.XLSX)
.sheet(sheetName)
.registerWriteHandler(buildCellStyle())
.registerWriteHandler(new CustomColumnWidthHandler())
.doWrite(data);
}
public static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
return response.getOutputStream();
}
/**
* 获取表头
*/
public static Map<String, String> getHeadMap(Class<?> aClass) {
Map<String, String> HeadMap = new LinkedHashMap<>();
Field[] declaredFields = aClass.getDeclaredFields();
ExcelProperty excelProperty;
for (Field field : declaredFields) {
if (field != null) {
field.setAccessible(true);
if (field.isAnnotationPresent(ExcelProperty.class)) {
excelProperty = field.getAnnotation(ExcelProperty.class);
HeadMap.put(field.getName(), StringUtils.join(Arrays.asList(excelProperty.value()), ","));
}
}
}
return HeadMap;
}
/**
* 生成通用表格样式
*/
public static HorizontalCellStyleStrategy buildCellStyle(){
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex());
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
WriteFont font = new WriteFont();
font.setFontName("Microsoft YaHei Light");
font.setColor(IndexedColors.WHITE.getIndex());
font.setFontHeightInPoints((short) 11);
headWriteCellStyle.setWriteFont(font);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
/**
* 创建一个Excel文件多个Sheet
* @param sheetList
*/
public static void createExcel(List<ExcelSheet> sheetList, OutputStream os){
ExcelWriter excelWriter = null;
WriteSheet writeSheet = null;
int count = 0;
try {
excelWriter = EasyExcel.write(os)
.registerWriteHandler(buildCellStyle())
.registerWriteHandler(new CustomColumnWidthHandler())
.build();
for (ExcelSheet sheet : sheetList) {
writeSheet = EasyExcel.writerSheet(count++, sheet.getSheetName()).head((Class) sheet.getClazz()).build();
excelWriter.write(sheet.getData(),writeSheet);
}
} catch (Exception e) {
LOGGER.error("导出异常", e);
throw new DangerException("导出失败,请联系管理员");
}finally {
if (null != excelWriter){
excelWriter.finish();
}
}
}
/**
* 消费监听器
*/
public class EasyExcelConsumerListener<T> extends AnalysisEventListener<T> {
private int pageSize;
private List<T> list;
private Consumer<List<T>> consumer;
public EasyExcelConsumerListener(int pageSize, Consumer<List<T>> consumer) {
this.pageSize = pageSize;
this.consumer = consumer;
list = new ArrayList<>(pageSize);
}
@Override
public void invoke(T data, AnalysisContext context) {
list.add(data);
if (list.size() >= pageSize) {
consumer.accept(list);
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
consumer.accept(list);
}
/**
* Excel文档的自动列宽设置
*/
public class CustomColumnWidthHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private static final int PADDING_WIDTH = 6;
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
if (isHead) {
int columnWidth = cell.getStringCellValue().length() * 2 + PADDING_WIDTH;
columnWidth = Math.min(columnWidth, MAX_COLUMN_WIDTH);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}
public class LocalDateConverter implements Converter<LocalDate> {
@Override
public Class<LocalDate> supportJavaTypeKey() {
return LocalDate.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
Calendar calendar = new GregorianCalendar(1900, 0, -1);
Date gregorianDate = calendar.getTime();
LocalDate parse = LocalDate.parse((new SimpleDateFormat("yyyy-MM-dd")).format(addDay(gregorianDate, cellData.getNumberValue().intValue())), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
return parse;
}
@Override
public CellData<String> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
}
public static Date addDay(Date date, int day) {
Calendar calendar = new GregorianCalendar();
calendar.setTime(date);
calendar.add(5, day);
date = calendar.getTime();
return date;
}
}
案例
// 读取excel用户信息,每次读取1000条
ExcelUtil.read(filePath, User.class, 1000, pageList -> {
// 保存业务逻辑
pageList.forEach(user -> save(user));
}).sheet().doRead();