通过easyexcel解析上传excel并保存到数据库

 直接上测试代码，自己跟着一步一步走

@Test
    public void testParseBarnettProductionDfitData() throws Exception {
        File file = new File("C:\\Users\\USER\\Desktop\\data11.xlsx");
        FileInputStream input = new FileInputStream(file);
        MultipartFile mfile = new MockMultipartFile("file", file.getName(), "text/plain", IOUtils.toByteArray(input));
        excelBarnettParser.parseBarnettDfitDataExcel(mfile);
    }

parseBarnettDfitDataExcel方法

public void parseBarnettDfitDataExcel(MultipartFile file) throws Exception {
        log.info("parseBarnettDfitData 开始");
        ExcelBarnettProductionDfitDataListener listener = new ExcelBarnettProductionDfitDataListener(dfitDataService);
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
            EasyExcelFactory.readBySax(inputStream, new Sheet(1, 1, ExcelBarnettProductionDfitData.class), listener);
            log.info("parseBarnettDfitData 处理完成");
        } catch (IOException e) {
            log.error("parseBarnettDfitData error", e);
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }

需要创建一个Listener,ExcelBarnettProductionDfitDataListener

 

package net.tartan.dfit.excel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import net.tartan.dfit.model.DfitData;
import net.tartan.dfit.pojo.excel.barnett.ExcelBarnettProductionDfitData;
import net.tartan.dfit.service.DfitDataService;
import org.apache.commons.lang.StringUtils;

@Slf4j
public class ExcelBarnettProductionDfitDataListener extends AnalysisEventListener {

    private DfitDataService dfitDataService;

    public ExcelBarnettProductionDfitDataListener(DfitDataService dfitDataService) {
        this.dfitDataService = dfitDataService;
    }

    @Override
    public void invoke(Object object, AnalysisContext context) {
        log.info(object.toString());
        DfitData dfitData = parse((ExcelBarnettProductionDfitData) object);
        if (dfitData != null) {
            dfitDataService.addExcelDfitData(dfitData);
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    public DfitData parse(ExcelBarnettProductionDfitData data) {
        DfitData dfitData = new DfitData();
        String uwi = data.getUwi();
        if (StringUtils.isEmpty(uwi)) {
            log.error("UWI cannot be null:{}", data);
            return null;
        }
        dfitData.setUwi(uwi);
        dfitData.setPressure(data.getPressure());
        dfitData.setFlow(data.getFlow());
        dfitData.setDfitTime(data.getDfitTime());
        return dfitData;
    }

}

其中最主要的就是invoke方法，里边需要的一些方法需要自己单写

 

  @Override
    public void invoke(Object object, AnalysisContext context) {
        log.info(object.toString());
        DfitData dfitData = parse((ExcelBarnettProductionDfitData) object);
        if (dfitData != null) {
            dfitDataService.addExcelDfitData(dfitData);
        }
    }





 public boolean addExcelDfitData(DfitData dfitData) {

        try {
            dfitDataMapper.insert(dfitData);
        } catch (DuplicateKeyException e) {
            log.error("data exists UWI:{}, date:{}", dfitData.getUwi());
            return false;
        } catch (Exception e) {
            log.error("save data error", e);
            return false;
        }
        log.info("save new testInfo! UWI:{}, date:{}", dfitData.getUwi());
        return true;
    }

 如果还有什么不懂的留言