Examples with ExcelReader com.alibaba.excel.ExcelReader used on opensource projects

Example 6 with ExcelReader

use of com.alibaba.excel.ExcelReader in project Qualitis by WeBankFinTech.

the class ProjectBatchServiceImpl method readExcel.

private ExcelProjectListener readExcel(InputStream inputStream) {
    LOGGER.info("Start to read project excel");
    ExcelProjectListener listener = new ExcelProjectListener();
    ExcelReader excelReader = new ExcelReader(inputStream, null, listener);
    List<Sheet> sheets = excelReader.getSheets();
    for (Sheet sheet : sheets) {
        if (sheet.getSheetName().equals(ExcelSheetName.TEMPLATE_RULE_NAME)) {
            sheet.setClazz(ExcelTemplateRuleByProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.PROJECT_NAME)) {
            sheet.setClazz(ExcelProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.CUSTOM_RULE_NAME)) {
            sheet.setClazz(ExcelCustomRuleByProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME)) {
            sheet.setClazz(ExcelMultiTemplateRuleByProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.TEMPLATE_FILE_RULE_NAME)) {
            sheet.setClazz(ExcelTemplateFileRuleByProject.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.RULE_METRIC_NAME)) {
            sheet.setClazz(ExcelRuleMetric.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        }
    }
    LOGGER.info("Finish to read project excel. excel content: rule sheet {}, project sheet {}", listener.getExcelRuleContent(), listener.getExcelProjectContent());
    return listener;
}

Example 7 with ExcelReader

use of com.alibaba.excel.ExcelReader in project Qualitis by WeBankFinTech.

the class RuleMetricServiceImpl method upload.

@Override
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = { RuntimeException.class, UnExpectedRequestException.class })
public GeneralResponse<?> upload(InputStream fileInputStream, FormDataContentDisposition fileDisposition) throws UnExpectedRequestException, IOException {
    // Check Arguments
    if (fileInputStream == null || fileDisposition == null) {
        throw new UnExpectedRequestException("{&FILE_CAN_NOT_BE_NULL_OR_EMPTY}");
    }
    // Check suffix name of file
    String fileName = fileDisposition.getFileName();
    String suffixName = fileName.substring(fileName.lastIndexOf('.'));
    if (!suffixName.equals(SUPPORT_EXCEL_SUFFIX_NAME)) {
        throw new UnExpectedRequestException("{&DO_NOT_SUPPORT_SUFFIX_NAME}: [" + suffixName + "]. {&ONLY_SUPPORT} [" + SUPPORT_EXCEL_SUFFIX_NAME + "]");
    }
    String userName = HttpUtils.getUserName(httpServletRequest);
    LOGGER.info(userName + " start to upload rule metrics.");
    ExcelRuleMetricListener listener = new ExcelRuleMetricListener();
    List<ExcelRuleMetric> excelRuleMetrics = listener.getRuleMetricContent();
    ExcelReader excelReader = new ExcelReader(fileInputStream, null, listener);
    List<Sheet> sheets = excelReader.getSheets();
    for (Sheet sheet : sheets) {
        if (sheet.getSheetName().equals(ExcelSheetName.RULE_METRIC_NAME)) {
            sheet.setClazz(ExcelRuleMetric.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        }
    }
    if (CollectionUtils.isEmpty(excelRuleMetrics)) {
        throw new UnExpectedRequestException("{&FILE_CAN_NOT_BE_EMPTY_OR_FILE_CAN_NOT_BE_RECOGNIZED}");
    }
    try {
        for (ExcelRuleMetric excelRuleMetric : excelRuleMetrics) {
            AddRuleMetricRequest addRuleMetricRequest = new AddRuleMetricRequest();
            addRuleMetric(addRuleMetricRequest);
        }
        LOGGER.info("Succeed to add all rule metrics");
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        fileInputStream.close();
    }
    return new GeneralResponse<>("200", "{&FAILED_TO_UPLOAD_RULE_METRIC}", null);
}

Example 8 with ExcelReader

use of com.alibaba.excel.ExcelReader in project SpringBoot-Hello by ruiyeclub.

the class ExcelUtil method readExcel.

/**
 * @param inputStream   Excel的输入流
 * @param clazz         模型的类
 * @param excelTypeEnum Excel的格式(XLS或XLSX)
 * @Description: 使用模型来读取Excel
 * @Date: 2020/1/16 21:41
 * @Return: java.util.List<E>
 * @Throws: Exception
 */
public static <E> List<E> readExcel(InputStream inputStream, Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
    // 解析每行结果在listener中处理
    ModelExcelListener<E> listener = new ModelExcelListener<E>();
    ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener);
    // 默认只有一列表头
    excelReader.read(new Sheet(1, 1, clazz));
    return listener.getDataList();
}

Example 9 with ExcelReader

use of com.alibaba.excel.ExcelReader 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();
        }
    }
}

Example 10 with ExcelReader

use of com.alibaba.excel.ExcelReader in project springboot-demo by small-rose.

the class SQLColumns method excelHandler.

public static void excelHandler(String file) {
    SQLColumnsListener columnsListener = new SQLColumnsListener();
    // 表名易首页为准
    columnsListener.setTableMap(tableInfoMap);
    columnsListener.setT_sql(t_sql);
    columnsListener.setC_sql(c_sql);
    columnsListener.setCheck(true);
    columnsListener.setCheckFile(checkFile);
    columnsListener.setSqlPath(sqlPath);
    ExcelReader excelReader = EasyExcel.read(file, columnsListener).build();
    int MAX_INDEX = excelReader.excelExecutor().sheetList().size();
    System.out.println(" MAX_INDEX is " + MAX_INDEX);
    ReadSheet readSheet = null;
    for (int index = 1; index < 306; index++) {
        // 获取第 index  个 sheet 对象
        readSheet = EasyExcel.readSheet(index).build();
        // 读取数据
        excelReader.read(readSheet);
    }
    excelReader.finish();
}