EasyExcel之导入导出

JAVA解析Excel工具easyexcel

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。

github地址：https://github.com/alibaba/easyexcel

依赖jar包

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.0.2</version>
</dependency>

读取Excel

从2.0.x开始类似如下方法已是过时方法，可以使用推荐的方法去实现。

 

1.无模板

@Controller
public class EasyExcelController {
	
	@RequestMapping("/readNoEntity")
	@ResponseBody
	public Map<String,Object> noEntity(MultipartFile excelFile) throws IOException {
		Map<String,Object> result = new HashMap<>();
		List<Object> list = EasyExcel.read(excelFile.getInputStream()).sheet(0).doReadSync();
		result.put("list", list);
		return result;	
	}
}

2.有模板

@Controller
public class EasyExcelController {

	@RequestMapping("/readToEntity")
	@ResponseBody
	public Map<String,Object> toEntity(MultipartFile excelFile) throws IOException {
		Map<String,Object> result = new HashMap<>();
		List<Object> list = EasyExcel.read(excelFile.getInputStream(),User.class,null).sheet(0).doReadSync();
		result.put("list", list);
		return result;	
	}
}

 模板Model同样从2.0.x开始就不需要再继承BaseRowModel了

@Data
public class User {
	
	private String name;
	
	private String account;
	
	private String address;
}

 3.也可以根据需要自定义监听，只需要继承AnalysisEventListener

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ruihao.zzx.client.demo.entity.User;

public class ExcelListener extends AnalysisEventListener<User> {
	
	private final Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);
	
	private List<User> datas = new ArrayList<>();
	//会读取每一行的数据
	@Override
	public void invoke(User object, AnalysisContext context) {
		System.out.println(object);
		datas.add(object);
	}

	@Override
	public void doAfterAllAnalysed(AnalysisContext context) {
		LOGGER.info("{}条数据，开始存储数据库！", datas.size());
		LOGGER.info("所有数据解析完成！");
	}

	public List<User> getDatas() {
		return datas;
	}

	public void setDatas(List<User> datas) {
		this.datas = datas;
	}

}

 invoke方法会一行一行的读取excel表格中的数据，使用自定义监听如下

@Controller
public class EasyExcelController {
	
	@RequestMapping("/readToEntity")
	@ResponseBody
	public Map<String,Object> toEntity(MultipartFile excelFile) throws IOException {
		Map<String,Object> result = new HashMap<>();
		ExcelListener listener = new ExcelListener();
		EasyExcel.read(excelFile.getInputStream(),User.class,listener).sheet(0).doRead();
		result.put("list", listener.getDatas());
		return result;	
	}
	
}

这里使用的是ExcelReaderSheetBuilder类的doRead()方法。doRead()和doReadSync()区别在于doReadSync()里面配了一个自定义的监听，并且返回读取到excel数据的List集合

    /**
     * Sax read
     */
    public void doRead() {
        if (excelReader == null) {
            throw new ExcelGenerateException("Must use 'EasyExcelFactory.read().sheet()' to call this method");
        }
        excelReader.read(build());
        excelReader.finish();
    }

    /**
     * Synchronous reads return results
     *
     * @return
     */
    public List<Object> doReadSync() {
        if (excelReader == null) {
            throw new ExcelAnalysisException("Must use 'EasyExcelFactory.read().sheet()' to call this method");
        }
        SyncReadListener syncReadListener = new SyncReadListener();
        registerReadListener(syncReadListener);
        excelReader.read(build());
        excelReader.finish();
        return syncReadListener.getList();
    }

 4.多个sheet的读取方法

@Controller
public class EasyExcelController {
	
	private final Logger LOGGER = LoggerFactory.getLogger(EasyExcelController.class);
	
	@RequestMapping("/readToEntity")
	@ResponseBody
	public Map<String,Object> toEntity(MultipartFile excelFile) throws IOException {
		Map<String,Object> result = new HashMap<>();
		ExcelListener listener = new ExcelListener();
		ExcelReaderBuilder builder = EasyExcel.read(excelFile.getInputStream(),User.class,listener);
		ExcelReader reader = builder.build();
		//sheet集合
		List<ReadSheet> sheets = reader.excelExecutor().sheetList();
		for(ReadSheet sheet:sheets) {
			listener.getDatas().clear();
			LOGGER.info("sheet name:{}",sheet.getSheetName());
			//读取每一个sheet的内容
			reader.read(sheet);
			List<User> current = listener.getDatas();
			LOGGER.info("content:{}",JSONObject.toJSONString(current));
		}
		reader.finish();
		result.put("list", listener.getDatas());
		return result;	
	}
	
}

 

导出Excel待续...