EasyExcel简单使用记录

EasyExcel简单使用记录

读excel

1. 接口实现

   @PostMapping("/importExcel")
    public Result importExcel(@RequestPart("file") MultipartFile file) {
    return null;
    }
   //说明：@RequestPart 可以接收 form-data/multipartFile，可以同时接收文件和表单数据
   // @RequestParam 可以接收基本数据类型、String、multipartFile 类型
   // @RequestBody 只能接收对象 application/json
   

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

}