easyexcel 导入_easyexcel导入

1.引入maven依赖

<!-- excel工具 开始 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
            <exclusions>
                <exclusion>
                    <groupId>org.ehcache</groupId>
                    <artifactId>ehcache</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>cglib</groupId>
                    <artifactId>cglib</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>3.2.9</version>
        </dependency>
        <dependency>
            <groupId>org.ehcache</groupId>
            <artifactId>ehcache</artifactId>
            <version>3.7.1</version>
            <exclusions>
                <exclusion>
                    <groupId>org.glassfish.jaxb</groupId>
                    <artifactId>jaxb-runtime</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- excel工具 结束 -->
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

2.Controller层

@ApiOperation(value = "导入xlsx文件")
@PostMapping(value = "importFile")
 public ResultResponse<String> importFile(
            DetailInfoApiParamVO detailInfoApiParamVO,
            @RequestParam @ApiParam(value = "用户ID") String userId,
            @ApiParam(value = "格式必须是 xlsx", required = true) MultipartFile importExcel) {
        try {
            InputStream inputStream = importExcel.getInputStream();
            return handleResult(importService.importBatchTempByFile(detailInfoApiParamVO, userId, inputStream));
        } catch (Exception e) {
            return new ResultResponse(e.getLocalizedMessage(), ErrorCodeEnum.INVALID_PARAM.getCode(), ErrorCodeEnum.INVALID_PARAM.getDescription());
        }
 }
1
2
3
4
5
6
7
8
9
10
11
12
13

3.service层

String importBatchTempByFile(DetailInfoApiParamVO detailInfoApiParamVO, String userId, InputStream inputStream);
1

4.serviceImpl层

@Override
    public String importBatchTempByFile(DetailInfoApiParamVO detailInfoApiParamVO, String userId, InputStream inputStream) {
        
        List<BasicInfoBO> basicInfoBOS = ExcelUtils.read(inputStream, BasicInfoBO.class);
        List<BasicInfoVO> basicInfoVOS = basicInfoBOS.stream().map(bo -> {
            BasicInfoVO vo = new BasicInfoVO();
            BeanUtils.copyProperties(bo, vo);
            return vo;
        }).collect(Collectors.toList());
        //逻辑代码
        return "ok";
    }
1
2
3
4
5
6
7
8
9
10
11
12

5.实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import javax.validation.constraints.NotNull;
import java.util.Date;

@Data
public class BasicInfoBO extends BaseRowModel {


  @ExcelProperty(value = "指标名称")
  private String nameame;

  @ExcelProperty(value = "创建人")
  private String createUser;

  @ExcelProperty(value = "照片")
  private String image;

  @ExcelProperty(value = "详情")
  private String extend1;

  @ExcelProperty(value = "年份")
  private String extend2;

  @ExcelProperty(value = "胜者")
  private String extend3;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

6.工具类

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.shxp.project.common.entity.ExcelListener;
import org.apache.poi.poifs.filesystem.FileMagic;

import java.io.*;
import java.util.List;

/**
     * 从Excel中读取文件，读取的类必须继承BaseRowModel
     * @param inputStream 文件输入流
     * @param clazz       继承该类必须继承BaseRowModel的类
     * @return 读取完成的list
     */
public class ExcelUtils {
	public static <T extends BaseRowModel> List<T> readExcel(final InputStream inputStream, final Class<? extends BaseRowModel> clazz) {
        if (null == inputStream) {
            throw new NullPointerException("the inputStream is null!");
        }
        AnalysisEventListener listener = new ExcelListener();
        //读取xls 和 xlxs格式
        //如果POI版本为3.17，可以如下声明
        ExcelReader reader = new ExcelReader(inputStream, null, listener);
        //判断格式，针对POI版本低于3.17
        //ExcelTypeEnum excelTypeEnum = valueOf(inputStream);
        //ExcelReader reader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
        reader.read(new com.alibaba.excel.metadata.Sheet(1, 1, clazz));

        return ((ExcelListener) listener).getData();
    }
	
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

7.ExcelListener实体类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;

import java.util.ArrayList;
import java.util.List;

/**
 * @description 处理Excel，将读取到数据保存为对象并输出
 */
public class ExcelListener<T extends BaseRowModel> extends AnalysisEventListener<T> {
    /**
     * 自定义用于暂时存储data。
     * 可以通过实例获取该值
     */
    private final List<T> data = new ArrayList<>();

    @Override
    public void invoke(T object, AnalysisContext context) {
        //数据存储
        data.add(object);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }

    public List<T> getData() {
        return data;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34