EasyExcel简单使用记录

最新推荐文章于 2025-09-09 00:12:45 发布

原创最新推荐文章于 2025-09-09 00:12:45 发布 · 749 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #excel #开发语言

本文介绍了如何使用EasyExcel框架进行Excel的读取和写入操作。在读取方面，展示了如何接收文件，通过监听器处理数据，以及进行数据验证和字典转换。在写入时，提到了设置单元格样式，创建工作表和冻结行列的方法。

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;
                   
     // ....

}