easyExcel使用实例
程序员文章站
2024-03-21 13:02:10
...
@Resource
private CorporateActionExcelListener excelListener;
public void excel() {
InputStream inputStream;
try {
File source = new File("/Users/yaw/Documents/test.xlsx");
ByteSource byteSource = Files.asByteSource(source);
inputStream = byteSource.openStream();//用guava的io流操作,不需要使用者关闭流
} catch (FileNotFoundException e) {
return;
}
Map<String, String> customContent = Maps.newHashMap();
customContent.put("market", "HK");//传自定义参数到listener
ExcelReader excelReader = new ExcelReader(inputStream, customContent, excelListener, true);
List<Sheet> sheets = excelReader.getSheets();
for (Sheet sheet : sheets) {
sheet.setHeadLineMun(1);
if (sheet.getSheetNo() == 1) {
sheet.setClazz(CapitalEventsExcelModel.class);
} else if (sheet.getSheetNo() == 2) {
sheet.setClazz(DividendsExcelModel.class);
}
excelReader.read(sheet);
}
}
每一行的解析工作在listener中进行:
CorporateActionExcelListener:
public class CorporateActionExcelListener extends AnalysisEventListener {
private List<CapitalEvents> capitalEventsList = new ArrayList<>();
private List<Dividends> dividendsLists = new ArrayList<>();
public void invoke(Object object, AnalysisContext context) {//每一行的数据解析自动调用这里,这里的解析逻辑开发者自己填充
Integer currentRowNum = context.getCurrentRowNum();
if (currentRowNum == 0) {
return;
}
try {
handleObject(object, context);
} catch (Exception e) {
}
}
private void handleObject(Object object, AnalysisContext context) {
Map custom = (Map) context.getCustom();
Object market = custom.get(StockScheduleConstants.marketKeyName);
int sheetNo = context.getCurrentSheet().getSheetNo();
switch (sheetNo) {
case 1:
CapitalEventsExcelModel capitalEventsExcelModel = (CapitalEventsExcelModel) object;
CapitalEvents capitalEvents = new CapitalEvents();
BeanUtils.copyProperties(capitalEventsExcelModel, capitalEvents);
if (StringUtils.isNotBlank(capitalEvents.getTicker())) {//过滤非法数据
capitalEventsList.add(capitalEvents);
}
break;
case 2:
DividendsExcelModel dividendsExcelModel = (DividendsExcelModel) object;
Dividends dividends = new Dividends();
BeanUtils.copyProperties(dividendsExcelModel, dividends);
if (StringUtils.isNotBlank(dividends.getTicker())) {//过滤非法数据
dividendsLists.add(dividends);
}
break;
default:
break;
}
}
public void doAfterAllAnalysed(AnalysisContext context) {//每个sheet解析完自动调用这里
Map custom = (Map) context.getCustom();
Object market = custom.get(StockScheduleConstants.marketKeyName);
int sheetNo = context.getCurrentSheet().getSheetNo();
StopWatch stopWatch = new StopWatch();
stopWatch.start();
switch (sheetNo) {
case 1:
if (CollectionUtils.isEmpty(capitalEventsList)) {
return;
}
//处理逻辑
...
break;
case 2:
if (CollectionUtils.isEmpty(dividendsLists)) {
return;
}
//处理逻辑
...
break;
default:
break;
}
}
}