ExcelUtil.java
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import edu.abc.ssm.bean.ExcelBean;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
/*easyexcel 导入导出工具类*/
public class ExcelUtil {
/*按List<String> 读取*/
public List<Object> readExcel(String filePath){
InputStream inputStream = FileUtil.getResourcesFileInputStream(filePath);
if (null == inputStream) {
throw new NullPointerException("the inputStream is null!");
}
final List<Object> rows = new ArrayList<Object>();
ExcelReader reader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, new AnalysisEventListener() {
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
rows.add(o);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}, false);
reader.read(new Sheet(1, 1));
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
return rows;
}
/*按model 读取*/
public <T extends BaseRowModel> List<T> readModelExcel(String filePath, Class<? extends BaseRowModel> clazz){
InputStream inputStream = FileUtil.getResourcesFileInputStream(filePath);
if (null == inputStream) {
throw new NullPointerException("the inputStream is null!");
}
ExcelListener<T> excelListener = new ExcelListener();
ExcelReader reader = new ExcelReader(inputStream, ExcelTypeEnum.XLSX, excelListener, false);
reader.read(new Sheet(1, 1, clazz));
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
return excelListener.getRows();
}
/*按model 写出*/
public void writeExcel(String filePath, List<? extends BaseRowModel> datas){
OutputStream outputStream = null;
try {
outputStream = new FileOutputStream(new File(filePath));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
Class<? extends BaseRowModel> t = datas.get(0).getClass();
System.out.println("ModelType:" + t);
Sheet sheet1 = new Sheet(1, 0, t);
ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX,true);
writer.write(datas, sheet1);
writer.finish();
}
public static void main(String[] args) {
ExcelUtil excelUtil = new ExcelUtil();
// List<Object> data = excelUtil.readExcel("test.xlsx");
// for (Object ob:data) {
// System.out.println(ob);
// }
//测试数据
// List<ExcelBean> excelModelList = new ArrayList<ExcelBean>();
// for (int i = 0; i < 100; i++) {
// ExcelBean excelModel = new ExcelBean();
// excelModel.setUserId(i);
// excelModel.setUserName("name" + i);
// excelModelList.add(excelModel);
// }
// excelUtil.writeExcel("outtest.xlsx", excelModelList);
List<ExcelBean> data = excelUtil.readModelExcel("outtest.xlsx", ExcelBean.class);
print(data);
}
public static void print(List<? extends BaseRowModel> datas){
for (Object ob:datas) {
System.out.println(ob);
}
}
}
ExcelListener.java
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;
public class ExcelListener<T extends BaseRowModel> extends AnalysisEventListener<T> {
/**
* 暂时存储data
*/
private List<T> rows = new ArrayList<T>();
/**
* 解析每行都回调invoke()方法
* @param object
* @param context
*/
public void invoke(T object, AnalysisContext context) {
rows.add(object);
}
public void doAfterAllAnalysed(AnalysisContext context) {
//解析结束销毁不用的资源
}
public List<T> getRows(){
return rows;
}
}
ExcelBean.java
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
/*easyexcel 数据导出model*/
public class ExcelBean extends BaseRowModel{
@ExcelProperty(value = "ID", index = 0)
private int userId;
@ExcelProperty(value = "姓名", index = 1)
private String userName;
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
@Override
public String toString() {
return "ExcelBean{" +
"userId=" + userId +
", userName='" + userName + '\'' +
'}';
}
}
pom.xml
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta5</version>
</dependency>