Search in sources :

Example 1 with AnalysisContext

use of com.alibaba.excel.context.AnalysisContext in project diboot by dibo-software.

the class ReadExcelListener method onException.

/**
 * <h3>异常处理</h3>
 * 修补数据,回写错误
 */
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
    // 数据类型转化异常
    if (exception instanceof ExcelDataConvertException) {
        ExcelDataConvertException dataConvertException = (ExcelDataConvertException) exception;
        Map<Integer, ReadCellData<?>> cellMap = new HashMap<>((Map) context.readRowHolder().getCellMap());
        Map<String, String> errorDataMap = new HashMap<>();
        Map<String, String> errorMsgMap = new HashMap<>();
        Consumer<ExcelDataConvertException> addErrorData = e -> {
            Integer columnIndex = e.getColumnIndex();
            String key = fieldNameMap.get(columnIndex);
            errorDataMap.put(key, cellMap.remove(columnIndex).getStringValue());
            errorMsgMap.put(key, "数据格式转换异常,非期望的数据类型[" + e.getExcelContentProperty().getField().getType().getSimpleName() + "]");
        };
        addErrorData.accept(dataConvertException);
        ReadListener<?> readListener = context.readWorkbookHolder().getReadListenerList().get(0);
        if (readListener instanceof ModelBuildEventListener) {
            while (true) {
                try {
                    ((ModelBuildEventListener) readListener).invoke(cellMap, context);
                    break;
                } catch (ExcelDataConvertException convertException) {
                    addErrorData.accept(convertException);
                }
            }
        } else {
            log.error("数据转换异常", exception);
            StringBuilder errorMsg = new StringBuilder().append("第 ").append(context.readRowHolder().getRowIndex() + 1).append(" 行,");
            errorMsgMap.forEach((fieldName, msg) -> errorMsg.append(fieldHeadMap.get(fieldName)).append(":").append(msg));
            addExceptionMsg(errorMsg.toString());
            return;
        }
        T currentRowAnalysisResult = (T) context.readRowHolder().getCurrentRowAnalysisResult();
        currentRowAnalysisResult.setRowIndex(context.readRowHolder().getRowIndex());
        errorDataMap.forEach(currentRowAnalysisResult::addInvalidValue);
        errorMsgMap.forEach(currentRowAnalysisResult::addComment);
        // 校验异常
        Set<ConstraintViolation<T>> constraintViolations = V.validateBean(currentRowAnalysisResult);
        if (V.notEmpty(constraintViolations)) {
            for (ConstraintViolation<T> violation : constraintViolations) {
                String propertyName = violation.getPropertyPath().toString();
                // 剔除解析识别的数据校验
                if (!errorDataMap.containsKey(propertyName)) {
                    currentRowAnalysisResult.addComment(propertyName, violation.getMessage());
                }
            }
        }
        this.cachedData(currentRowAnalysisResult);
    } else {
        log.error("出现未预知的异常:", exception);
        addExceptionMsg("第 " + (context.readRowHolder().getRowIndex() + 1) + " 行,解析异常: " + exception.getMessage());
    }
}
Also used : ExcelBindAnnoHandler(com.diboot.file.excel.cache.ExcelBindAnnoHandler) Setter(lombok.Setter) java.util(java.util) Getter(lombok.Getter) ExcelBindDict(com.diboot.file.excel.annotation.ExcelBindDict) EasyExcel(com.alibaba.excel.EasyExcel) ExcelDataConvertException(com.alibaba.excel.exception.ExcelDataConvertException) DuplicateStrategy(com.diboot.file.excel.annotation.DuplicateStrategy) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) BusinessException(com.diboot.core.exception.BusinessException) ExcelWriter(com.alibaba.excel.ExcelWriter) AnalysisContext(com.alibaba.excel.context.AnalysisContext) ReadListener(com.alibaba.excel.read.listener.ReadListener) ExcelHelper(com.diboot.file.util.ExcelHelper) Cons(com.diboot.file.config.Cons) Head(com.alibaba.excel.metadata.Head) BaseExcelModel(com.diboot.file.excel.BaseExcelModel) EmptyStrategy(com.diboot.file.excel.annotation.EmptyStrategy) ConstraintViolation(javax.validation.ConstraintViolation) BeanUtils(com.diboot.core.util.BeanUtils) BindDict(com.diboot.core.binding.annotation.BindDict) FileHelper(com.diboot.file.util.FileHelper) ExcelReadHeadProperty(com.alibaba.excel.read.metadata.property.ExcelReadHeadProperty) ModelBuildEventListener(com.alibaba.excel.read.listener.ModelBuildEventListener) Status(com.diboot.core.vo.Status) NotNull(javax.validation.constraints.NotNull) Field(java.lang.reflect.Field) ReadCellData(com.alibaba.excel.metadata.data.ReadCellData) Collectors(java.util.stream.Collectors) Consumer(java.util.function.Consumer) ExcelBindField(com.diboot.file.excel.annotation.ExcelBindField) Slf4j(lombok.extern.slf4j.Slf4j) BaseConfig(com.diboot.core.config.BaseConfig) S(com.diboot.core.util.S) V(com.diboot.core.util.V) Annotation(java.lang.annotation.Annotation) CommentWriteHandler(com.diboot.file.excel.write.CommentWriteHandler) ExcelDataConvertException(com.alibaba.excel.exception.ExcelDataConvertException) ReadCellData(com.alibaba.excel.metadata.data.ReadCellData) ModelBuildEventListener(com.alibaba.excel.read.listener.ModelBuildEventListener) ConstraintViolation(javax.validation.ConstraintViolation)

Example 2 with AnalysisContext

use of com.alibaba.excel.context.AnalysisContext in project rebuild by getrebuild.

the class ExcelUtils method readExcel.

/**
 * @param excel
 * @param maxRows
 * @param hasHead
 * @return
 */
public static List<Cell[]> readExcel(File excel, int maxRows, boolean hasHead) {
    final List<Cell[]> rows = new ArrayList<>();
    final AtomicInteger rowNo = new AtomicInteger(0);
    try (InputStream is = new FileInputStream(excel)) {
        try (BufferedInputStream bis = new BufferedInputStream(is)) {
            // noinspection rawtypes
            EasyExcel.read(bis, null, new AnalysisEventListener() {

                @Override
                public void invokeHeadMap(Map headMap, AnalysisContext context) {
                    if (hasHead) {
                        this.invoke(headMap, context);
                    } else {
                        rowNo.incrementAndGet();
                    }
                }

                @Override
                public void invoke(Object data, AnalysisContext analysisContext) {
                    if (maxRows > 0 && rows.size() >= maxRows) {
                        return;
                    }
                    @SuppressWarnings("unchecked") Map<Integer, String> dataMap = (Map<Integer, String>) data;
                    List<Cell> row = new ArrayList<>();
                    for (int i = 0; i < dataMap.size(); i++) {
                        row.add(new Cell(dataMap.get(i), rowNo.get(), i));
                    }
                    rows.add(row.toArray(new Cell[0]));
                    rowNo.incrementAndGet();
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                }
            }).sheet().doRead();
        }
    } catch (IOException e) {
        throw new RebuildException(e);
    }
    return rows;
}
Also used : ArrayList(java.util.ArrayList) RebuildException(com.rebuild.core.RebuildException) AnalysisContext(com.alibaba.excel.context.AnalysisContext) AtomicInteger(java.util.concurrent.atomic.AtomicInteger) AnalysisEventListener(com.alibaba.excel.event.AnalysisEventListener) AtomicInteger(java.util.concurrent.atomic.AtomicInteger) List(java.util.List) ArrayList(java.util.ArrayList) Map(java.util.Map) Cell(cn.devezhao.commons.excel.Cell)

Example 3 with AnalysisContext

use of com.alibaba.excel.context.AnalysisContext in project pancm_project by xuwujing.

the class EasyExcelTest method simpleRead.

/**
 * 最简单的读
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void simpleRead() {
    // 写法1:JDK8+ ,不用额外写一个DemoDataListener
    // since: 3.0.0-beta1
    String fileName = "/home" + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
    // 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行
    // EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
    // for (DemoData demoData : dataList) {
    // log.info("读取到一条数据{}", JSON.toJSONString(demoData));
    // }
    // })).sheet().doRead();
    // 写法2:
    // 匿名内部类 不用额外写一个DemoDataListener
    fileName = "/home" + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {

        /**
         * 单次缓存的数据量
         */
        public static final int BATCH_COUNT = 100;

        /**
         *临时存储
         */
        private List<DemoData> cachedDataList = new ArrayList<>();

        /**
         * All listeners receive this method when any one Listener does an error report. If an exception is thrown here, the
         * entire read will terminate.
         *
         * @param exception
         * @param context
         * @throws Exception
         */
        @Override
        public void onException(Exception exception, AnalysisContext context) throws Exception {
        }

        /**
         * When analysis one head row trigger invoke function.
         *
         * @param headMap
         * @param context
         */
        @Override
        public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
        }

        @Override
        public void invoke(DemoData data, AnalysisContext context) {
            cachedDataList.add(data);
            if (cachedDataList.size() >= BATCH_COUNT) {
                saveData();
                // 存储完成清理 list
                cachedDataList = new ArrayList<>();
            }
        }

        /**
         * The current method is called when extra information is returned
         *
         * @param extra   extra information
         * @param context
         */
        @Override
        public void extra(CellExtra extra, AnalysisContext context) {
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            saveData();
        }

        /**
         * Verify that there is another piece of data.You can stop the read by returning false
         *
         * @param context
         * @return
         */
        @Override
        public boolean hasNext(AnalysisContext context) {
            return false;
        }

        /**
         * 加上存储数据库
         */
        private void saveData() {
            log.info("{}条数据,开始存储数据库!", cachedDataList.size());
            log.info("存储数据库成功!");
        }
    }).sheet().doRead();
    // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    // 写法3:
    fileName = "/home" + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    // 写法4:
    fileName = "/home" + "demo" + File.separator + "demo.xlsx";
    // 一个文件一个reader
    ExcelReader excelReader = null;
    try {
        excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
        // 构建一个sheet 这里可以指定名字或者no
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        // 读取一个sheet
        excelReader.read(readSheet);
    } finally {
        if (excelReader != null) {
            // 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
            excelReader.finish();
        }
    }
}
Also used : ArrayList(java.util.ArrayList) AnalysisContext(com.alibaba.excel.context.AnalysisContext) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet) CellData(com.alibaba.excel.metadata.CellData) ExcelReader(com.alibaba.excel.ExcelReader) CellExtra(com.alibaba.excel.metadata.CellExtra) Test(org.junit.Test)

Example 4 with AnalysisContext

use of com.alibaba.excel.context.AnalysisContext in project easyexcel by alibaba.

the class ReadTest method simpleRead.

/**
 * 最简单的读
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void simpleRead() {
    // 写法1:JDK8+ ,不用额外写一个DemoDataListener
    // since: 3.0.0-beta1
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
    // 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行
    EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
        for (DemoData demoData : dataList) {
            log.info("读取到一条数据{}", JSON.toJSONString(demoData));
        }
    })).sheet().doRead();
    // 写法2:
    // 匿名内部类 不用额外写一个DemoDataListener
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {

        /**
         * 单次缓存的数据量
         */
        public static final int BATCH_COUNT = 100;

        /**
         *临时存储
         */
        private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

        @Override
        public void invoke(DemoData data, AnalysisContext context) {
            cachedDataList.add(data);
            if (cachedDataList.size() >= BATCH_COUNT) {
                saveData();
                // 存储完成清理 list
                cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            saveData();
        }

        /**
         * 加上存储数据库
         */
        private void saveData() {
            log.info("{}条数据,开始存储数据库!", cachedDataList.size());
            log.info("存储数据库成功!");
        }
    }).sheet().doRead();
    // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    // 写法3:
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    // 写法4: 使用 try-with-resources @since 3.1.0
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 一个文件一个reader
    try (ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build()) {
        // 构建一个sheet 这里可以指定名字或者no
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        // 读取一个sheet
        excelReader.read(readSheet);
    }
    // 写法5: 不使用 try-with-resources
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 一个文件一个reader
    ExcelReader excelReader = null;
    try {
        excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
        // 构建一个sheet 这里可以指定名字或者no
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        // 读取一个sheet
        excelReader.read(readSheet);
    } finally {
        if (excelReader != null) {
            // 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
            excelReader.close();
        }
    }
}
Also used : ExcelReader(com.alibaba.excel.ExcelReader) ExcelProperty(com.alibaba.excel.annotation.ExcelProperty) Test(org.junit.Test) EasyExcel(com.alibaba.excel.EasyExcel) File(java.io.File) DefaultConverterLoader(com.alibaba.excel.converters.DefaultConverterLoader) ListUtils(com.alibaba.excel.util.ListUtils) List(java.util.List) TestFileUtil(com.alibaba.easyexcel.test.util.TestFileUtil) AnalysisContext(com.alibaba.excel.context.AnalysisContext) Slf4j(lombok.extern.slf4j.Slf4j) JSON(com.alibaba.fastjson.JSON) Ignore(org.junit.Ignore) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet) ReadListener(com.alibaba.excel.read.listener.ReadListener) Map(java.util.Map) PageReadListener(com.alibaba.excel.read.listener.PageReadListener) CellExtraTypeEnum(com.alibaba.excel.enums.CellExtraTypeEnum) NumberFormat(com.alibaba.excel.annotation.format.NumberFormat) DateTimeFormat(com.alibaba.excel.annotation.format.DateTimeFormat) ExcelReader(com.alibaba.excel.ExcelReader) AnalysisContext(com.alibaba.excel.context.AnalysisContext) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet) Test(org.junit.Test)

Aggregations

AnalysisContext (com.alibaba.excel.context.AnalysisContext)4 EasyExcel (com.alibaba.excel.EasyExcel)2 ExcelReader (com.alibaba.excel.ExcelReader)2 ReadListener (com.alibaba.excel.read.listener.ReadListener)2 ReadSheet (com.alibaba.excel.read.metadata.ReadSheet)2 ArrayList (java.util.ArrayList)2 List (java.util.List)2 Map (java.util.Map)2 Test (org.junit.Test)2 Cell (cn.devezhao.commons.excel.Cell)1 TestFileUtil (com.alibaba.easyexcel.test.util.TestFileUtil)1 ExcelWriter (com.alibaba.excel.ExcelWriter)1 ExcelProperty (com.alibaba.excel.annotation.ExcelProperty)1 DateTimeFormat (com.alibaba.excel.annotation.format.DateTimeFormat)1 NumberFormat (com.alibaba.excel.annotation.format.NumberFormat)1 DefaultConverterLoader (com.alibaba.excel.converters.DefaultConverterLoader)1 CellExtraTypeEnum (com.alibaba.excel.enums.CellExtraTypeEnum)1 AnalysisEventListener (com.alibaba.excel.event.AnalysisEventListener)1 ExcelDataConvertException (com.alibaba.excel.exception.ExcelDataConvertException)1 CellData (com.alibaba.excel.metadata.CellData)1