EasyExcel简单使用记录
读excel
-
接口实现
@PostMapping("/importExcel") public Result importExcel(@RequestPart("file") MultipartFile file) { return null; } //说明:@RequestPart 可以接收 form-data/multipartFile,可以同时接收文件和表单数据 // @RequestParam 可以接收基本数据类型、String、multipartFile 类型 // @RequestBody 只能接收对象 application/json
-
easyExcel读取文件
ExcelReaderBuilder read = EasyExcel .read(file.getInputStream(), TestForm.class, new TestExcelListener(testVo, this, TestForm.class, null)); read.sheet().doReadSync(); // 默认从第一行读取数据,第0行为表头 // read.sheet().headRowNumber(2).doReadSync(); 如果存在合并表头,可以设置为从第二行读取数据 /** public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener) 说明: inputStream: file.getInputStream() head: excel 对应的类 readListener:自定义listtener, extends AnalysisEventListener<T> */
model示例
@Data public class TestForm { @ExcelProperty(value = "*名称", order = 1) @NotNull(groups = {ValidatorType.ImportGroup.class}, message = "名称" + ErrorMsg.NULL_ERR) @NotBlank(groups = {ValidatorType.ImportGroup.class}, message = "名称" + ErrorMsg.NULL_ERR) private String name; @ExcelProperty(value = "*网络", order = 2) @Dictionary(table = "code_dictionary", type = "net_type", message = "网络" + ErrorMsg.DICT_ERR) @NotNull(groups = {ValidatorType.ImportGroup.class}, message = "网络" + ErrorMsg.NULL_ERR) @NotBlank(groups = {ValidatorType.ImportGroup.class}, message = "网络" + ErrorMsg.NULL_ERR) private String dealerNet; @ExcelProperty(value = "*开始时间", converter = LocalDateConverter.class, order = 5) @NotNull(groups = {ValidatorType.ImportGroup.class}, message = "开始时间" + ErrorMsg.NULL_ERR) @NotBlank(groups = {ValidatorType.ImportGroup.class}, message = "开始时间" + ErrorMsg.NULL_ERR) private LocalDate startTime; @ExcelIgnore private String errMsg; } /* 说明:@ExcelProperty: value,对应excel表头;order:列的顺序,从1开始;index,列的顺序,从0开始; converter = LocalDateConverter.class,转换类(自定义时间转换) @NotNull 在excel中不能为null @NotBlank 在excel中不能为空 @Dictionary 字典,用于将excel中的某些字段转换为数据库中的字段,例如将 "失败"转换为"fail",table,对应数据库中的表名,type是库里的dic_type dict_type dict_key dict_value net_type A 网络A net_type B 网络B */
readListener详细说明
public class TestEasyExcelListener<T> extends AnalysisEventListener<T> { private List<T> handlerDataList = Lists.newArrayList(); public CommonEasyExcelListener(Class<T> clazz, IService service, ExcelConsumer excelConsumer) { // .... } @Override public void invoke(T entity, AnalysisContext analysisContext) { //读取到每一条数据 //entity 对应 model类,例如 Test.class //analysisContext 可以获取rowIndex、readSheet、Cell相关信息 entity = handlerEntity(entity); handlerDataList.add(entity); } @Override public void invoke(LinkedHashMap<Integer, Object> data, AnalysisContext analysisContext) { //data 为读取的每条数据,没有对应实体类,可以灵活处理 } @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { super.invokeHeadMap(headMap, context); //读取表头信息 //headMap:key,rowIndex; value,表头name } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { //数据读取完成后,要做的操作,一般是保存数据 // service.save(handlerDataList); } //对excel对应类上注解进行校验 private T handlerEntity(T entity) { Field[] fields = entity.getClass().getDeclaredFields(); List<String> dictionaryType = Lists.newArrayList(); List<Field> dictionaryField = Lists.newArrayList(); List<Field> notNullField = Lists.newArrayList(); List<Field> notBlankField = Lists.newArrayList(); for (Field field : fields) { ReflectionUtils.makeAccessible(field); if (ERR_MESSAGE.equalsIgnoreCase(field.getName())) { this.errMsgField = field; } Dictionary dictionary = field.getAnnotation(Dictionary.class); if (dictionary != null) { dictionaryType.add(dictionary.type()); dictionaryField.add(field); } NotNull notNull = field.getAnnotation(NotNull.class); if (notNull != null && ArrayUtils.contains(notNull.groups(), ValidatorType.ImportGroup.class)) { notNullField.add(field); } for (Field dictionary : this.fieldMap.get(DICTIONARY)) { //entity = ... 具体校验逻辑 } for (Field notNull : this.fieldMap.get(NOT_NULL)) { //... } for (Field notBlank : this.fieldMap.get(NOT_BLANK)) { //... } } return entity; } }
写Excel
public Path generateExcel(TestParam testParam) {
// 文件输出位置
StringBuilder sb = new StringBuilder();
sb.append(ExportFileEnum.SUPERMARKET_AMOUNT_RESULT.getName()).append(Constants.UNDERLINE);
sb.append(DateTimeUtil.getFormatNow(DateTimeUtil.FILE_SUFFIX));
sb.append(Constants.EXCEL);
String filePath = filePathProperties.getExportPath() + Constants.SLASH + ExportFileEnum.SUPERMARKET_AMOUNT_RESULT.getPath() + sb;
if (!new File(filePath).exists()) {
new File(filePath).mkdirs();
}
Path returnFilePath = Paths.get(filePath, sb.toString());
ExcelWriterBuilder builder = EasyExcel.write(returnFilePath.toString());
builder.autoCloseStream(true);
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为浅蓝色
headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteFont.setBold(Boolean.TRUE);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//设置单元格边框
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
//-------设置背景色结束--------------------------
builder.registerWriteHandler(horizontalCellStyleStrategy); //表头颜色
ExcelWriter excelWriter = builder.build();
// 增加sheet页
this.excelSheet(excelWriter, testParam);
excelWriter.finish();
return returnFilePath;
}
private void excelSheet(ExcelWriter excelWriter, TestParam testParam) {
FreezeAndFilter freezeAndFilter = new FreezeAndFilter(0, 1, 0, 1);
WriteSheet writeSheet = EasyExcel.writerSheet("XXX").head(TestResult.class).build();
writeSheet.setCustomWriteHandlerList(Collections.singletonList(freezeAndFilter));
this.writeDataToExcel(excelWriter, writeSheet, testParam);
}
private void writeDataToExcel(ExcelWriter excelWriter, WriteSheet writeSheet, TestParam testParam) {
long beginTime = System.currentTimeMillis();
List<TestResult> testResultList = testMapper.exportInfo(testParam);
excelWriter.write(testResultList, writeSheet);
}
//导出数据实体类
public class TestResult {
@ExcelProperty(value = "名称", index = 0
@ColumnWidth(value = 30)
private String nme;
@ExcelProperty(value = "状态", index = 1
@ColumnWidth(value = 20)
private String status;
@ExcelProperty(value = "时间", index = 2
@ColumnWidth(value = 20)
private String completionTime;
// ....
}