如题所示
项目中需要做表格导入功能,决定采用EasyExcel来进行操作,文件解析相对简单,不像导出那么复杂
demo使用到的依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.0.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.assertj</groupId>
<artifactId>assertj-core</artifactId>
<version>RELEASE</version>
<scope>compile</scope>
</dependency>
具体代码展示如下:
package com.excel.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.biz.primus.common.exception.BizSilentException;
import lombok.extern.slf4j.Slf4j;
import java.util.*;
@Slf4j
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
List<Map<String, Object>> addList = new ArrayList<>();
Map<Integer, String> key = new HashMap<>();
List<String> keyList = new ArrayList<>();
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
log.info("解析到一条头数据:{}, currentRowHolder: {}", headMap.toString(), context.readRowHolder().getRowIndex());
Set<Integer> integerSet = headMap.keySet();
for (Integer integer : integerSet) {
keyList.add(headMap.get(integer));
}
key.putAll(headMap);
}
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
HashMap<String, Object> objectObjectHashMap = new HashMap<>();
Set<Integer> integerSet = data.keySet();
String name = "";
for (Integer integer : integerSet) {
String s = data.get(integer);
if (integer == 0) {
name = data.get(integer);
} else {
boolean number = this.isNumber(data.get(integer));
if (!number) {
throw new BizSilentException(name + "数据格式错误,请调整后重试!");
}
}
objectObjectHashMap.put(key.get(integer), s);
}
addList.add(objectObjectHashMap);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
private boolean isNumber(String str) {
String reg = "\\d+(\\.\\d+)?";
if (str.matches(reg)) {
String ss[];
if (str.contains(".")) {
ss = str.split("\\.");
String s = ss[1];
return s.length() <= 2;
}
}
return str.matches(reg);
}
}
public List<Map<Integer, String>> importData(String url){
NoModelDataListener noModelDataListener = new NoModelDataListener();
InputStream inputStream;
try {
URL url = new URL(url1);
URLConnection connection = url.openConnection();
inputStream = connection.getInputStream();
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("读取文件失败!");
}
return EasyExcelFactory.read(inputStream, noModelDataListener).excelType(ExcelTypeEnum.XLSX).sheet(0).headRowNumber(3).doReadSync();
}