这里展示两种读取方式,读取文件和网络文件
1.读取文件
public Map getLocalFileInfo(MultipartFile file,String sheetNum){
Map<String, Object> map = new HashMap<>();
InputStream inputStream = null;
List<Object> list = new ArrayList<>();
List<String> sheetsName = new ArrayList<>();
List<Sheet> sheets = new ArrayList<>();
ExcelReader reader = null;
ExcelListener excelListener = new ExcelListener();
try {
inputStream = file.getInputStream();
reader = EasyExcelFactory.getReader(inputStream, excelListener);
sheets = reader.getSheets();
list = EasyExcelFactory.read(file.getInputStream(), new Sheet(Integer.valueOf(sheetNum)));
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
for (Sheet sheet : sheets) {
String sheetName = sheet.getSheetName();
sheetsName.add(sheetName);
}
if (list.size() > 10){
list.subList(0,10);
}
map.put("sheetNum", sheetsName);
map.put("content", list);
return map;
}
2.读取网络文件
public Map getOnlineFileInfo(String url, String sheetNum){
Map<String, Object> map = new HashMap<>();
URL conn = null;
List<Object> list = new ArrayList<>();
InputStream inputStream = null;
URLConnection connection = null;
List<Sheet> sheets = new ArrayList<>();
List<String> sheetsName = new ArrayList<>();
ExcelReader reader = null;
ExcelListener excelListener = new ExcelListener();
try {
conn = new URL(url);
connection = conn.openConnection();
connection.setConnectTimeout(1000);
inputStream = connection.getInputStream();
InputStream inputStream1 = inputStream;
reader = EasyExcelFactory.getReader(inputStream, excelListener);
sheets = reader.getSheets();
conn = new URL(url);
connection = conn.openConnection();
connection.setConnectTimeout(1000);
list = EasyExcelFactory.read(connection.getInputStream(), new Sheet(Integer.valueOf(sheetNum)));
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
for (Sheet sheet : sheets) {
sheetsName.add(sheet.getSheetName());
}
if (list.size() > 10){
list.subList(0,10);
}
map.put("sheetsName", sheetsName);
map.put("content", list);
return map;
}
其中ExcelListener代码如下:
public class ExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<Object>();
public void invoke(Object object, AnalysisContext context) {
System.out.println("当前行:"+context.getCurrentRowNum());
System.out.println(object);
datas.add(object);
doSomething(object);
}
private void doSomething(Object object) {
}
public void doAfterAllAnalysed(AnalysisContext context) {
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
}
监听器可以监听excel中的每行数据,并可根据业务需求对其进行相应操作
结果如下: