EasyExcel导出list<T>到excel表格
实体类
@Data public class Student { @ExcelProperty(value = "学生姓名") private String name; @ExcelProperty(value = "年龄") private Integer age; @ExcelProperty(value = "出生日期") private String bornDate; }
controller层
@PostMapping("/getListExcel") @ApiOperation("导出excel") public String getCameraListExcel(@RequestBody List<Student> student) { String fileName = "测试excel2"+".xlsx";
//指定path路径 String excelFileName = CommonConstant.PATH +fileName; EasyExcel.write(excelFileName,Student.class).head(Student.class).sheet("学生表")
//自适应列宽 .registerWriteHandler(new CustomCellWriteHandler())
//时间转换器 .registerConverter(new LocalDateConverter()).doWrite(student); return excelFileName; }
自适应列宽处理类
import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.util.CollectionUtils; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.Cell; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 自适应列宽 */ @Slf4j public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy { public final static Integer COLUMN_WIDTH = 255; private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList); if (needSetWidth) { Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap<>(16); cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } Integer columnWidth = this.dataLength(cellDataList, cell, isHead); if (columnWidth >= 0) { if (columnWidth > COLUMN_WIDTH) { columnWidth = 255; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: return cellData.getStringValue().getBytes().length; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } }
时间转换器
import com.alibaba.excel.converters.Converter; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.property.ExcelContentProperty; import org.springframework.stereotype.Component; import java.time.LocalDate; import java.time.format.DateTimeFormatter; /** * 导出excel时,时间格式为LocalDate的转换器工具 */ @Component 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 excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception { return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd")); } @Override public CellData<String> convertToExcelData(LocalDate localDate, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception { return new CellData<>(localDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))); } }