使用阿里的easyexcel 来实现excel文件的读取和下载

使用阿里的easyexcel 来实现excel文件的读取和下载

1.首先下载maven依赖

	<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.1</version>
    </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>

2.导出excel 文件
2.1 创建实体类对象

@Data
	public class TestImprotVo {
	    @ExcelProperty(value = "姓名",index = 0)
	    private  String name;
	    @ExcelProperty(value = "年龄",index = 1)
	    private  String age;
	}

2.2 导出代码

private static List<TestImprotVo> getStuList(){
        List<TestImprotVo> list = new ArrayList<>();
        for(int i=1; i<10;i++){
            TestImprotVo vo = new TestImprotVo();
            vo.setName("rise"+i);
            vo.setAge(String.valueOf(i));
            list.add(vo);
        }
        return list;
    }

    public static void main(String[] args) {
        String file = "D:\\dowm.xlsx";
        EasyExcel.write(file,TestImprotVo.class).sheet("学生列表").doWrite(getStuList());
    }

3.读取excel 文件
3.1 创建一个监听器类

public class ReadListen  extends AnalysisEventListener<TestImprotVo> {

    @Override
    public void invoke(TestImprotVo testImprotVo, AnalysisContext analysisContext) {
        System.out.println("每行数据"+testImprotVo);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("执行完成");
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头信息"+headMap);
    }
}

3.2 读取excel

private static final String FUJIANFILENAME[] = {"xlsx","xls"};

    @RequestMapping("/upload")
    public ResultBody test(@RequestParam("file") MultipartFile file){
        Set<String> fujianSuffix = new HashSet<>(Arrays.asList(FUJIANFILENAME));
        String originalFilename = file.getOriginalFilename();
        int length = originalFilename.length();
        String suffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, length);
        if(!fujianSuffix.contains(suffix)){
            return ResultBody.failure("请用正确模板导入数据");
        }
        try {
            EasyExcel.read(file.getInputStream(), TestImprotVo.class, new ReadListen()).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return ResultBody.success();
    }

3.3
监听器会读取到excel 中的每行数据,然后执行我们具体的业务逻辑.
4. 优点
1.使用阿里的easyexcel 在操作excel 时候在读取大文件时不会造成内存的溢出,因为它是一行一行的去读取文件,而不是把文件整个加载到内存中.
2. 使用简单,方便.