欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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;
        }

    }
}

相关标签: easyExcel