com.alibaba.excel.ExcelReader Java Examples

/**
 * 返回 ExcelReader
 *
 * @param excel         需要解析的 Excel 文件
 * @param excelListener new ExcelListener()
 */
private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) {
    String filename = excel.getOriginalFilename();
    if (filename == null) {
        throw new ExcelException("文件格式错误！");
    }
    if (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx")) {
        throw new ExcelException("文件格式错误！");
    }
    InputStream inputStream;
    try {
        inputStream = new BufferedInputStream(excel.getInputStream());
        return new ExcelReader(inputStream, null, excelListener, false);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return null;
}
 

/**
 * 最简单的读
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void simpleRead() {
    // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    // 写法1：
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

    // 写法2：
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    ExcelReader excelReader = null;
    try {
        excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
    } finally {
        if (excelReader != null) {
            // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
            excelReader.finish();
        }
    }
}
 

private void readAndWrite5() throws Exception {
    ExcelWriter excelWriter =
        EasyExcel.write(new FileOutputStream(file)).head(ParameterData.class).relativeHeadRowIndex(0).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0).relativeHeadRowIndex(0).needHead(Boolean.FALSE).build();
    WriteTable writeTable = EasyExcel.writerTable(0).relativeHeadRowIndex(0).needHead(Boolean.TRUE).build();
    excelWriter.write(data(), writeSheet, writeTable);
    excelWriter.finish();

    ExcelReader excelReader = EasyExcel.read(file.getPath(), new ParameterDataListener()).head(ParameterData.class)
        .mandatoryUseInputStream(Boolean.FALSE).autoCloseStream(Boolean.TRUE).readCache(new MapCache()).build();
    ReadSheet readSheet = EasyExcel.readSheet().head(ParameterData.class).use1904windowing(Boolean.FALSE)
        .headRowNumber(1).sheetNo(0).sheetName("0").build();
    excelReader.read(readSheet);
    excelReader.finish();

    excelReader = EasyExcel.read(file.getPath(), new ParameterDataListener()).head(ParameterData.class)
        .mandatoryUseInputStream(Boolean.FALSE).autoCloseStream(Boolean.TRUE).readCache(new MapCache()).build();
    excelReader.read();
    excelReader.finish();
}
 

private void readAndWrite6() throws Exception {
    ExcelWriter excelWriter =
        EasyExcel.write(new FileOutputStream(file)).head(ParameterData.class).relativeHeadRowIndex(0).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0).relativeHeadRowIndex(0).needHead(Boolean.FALSE).build();
    WriteTable writeTable = EasyExcel.writerTable(0).registerConverter(new StringStringConverter())
        .relativeHeadRowIndex(0).needHead(Boolean.TRUE).build();
    excelWriter.write(data(), writeSheet, writeTable);
    excelWriter.finish();

    ExcelReader excelReader = EasyExcel.read(file.getPath(), new ParameterDataListener()).head(ParameterData.class)
        .mandatoryUseInputStream(Boolean.FALSE).autoCloseStream(Boolean.TRUE).readCache(new MapCache()).build();
    ReadSheet readSheet = EasyExcel.readSheet("0").head(ParameterData.class).use1904windowing(Boolean.FALSE)
        .headRowNumber(1).sheetNo(0).build();
    excelReader.read(readSheet);
    excelReader.finish();

    excelReader = EasyExcel.read(file.getPath(), new ParameterDataListener()).head(ParameterData.class)
        .mandatoryUseInputStream(Boolean.FALSE).autoCloseStream(Boolean.TRUE).readCache(new MapCache()).build();
    excelReader.read();
    excelReader.finish();
}
 

/**
 * 读多个或者全部sheet,这里注意一个sheet不能读取多次，多次读取需要重新读取文件
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link SysUser}
 * <p>
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UserExcelListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void testReadByAllSheetMethod2(){

    String fileName = TestFileUtil.getPath() + "sysUser" + File.separator + "user.xlsx";
    ExcelReader excelReader = EasyExcel.read(fileName).build();
    // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
    ReadSheet readSheet0 =
            EasyExcel.readSheet(0).head(SysUser.class).registerReadListener(new UserExcelListener(sysUserMapper)).build();
    ReadSheet readSheet1 =
            EasyExcel.readSheet(1).head(SysUser.class).registerReadListener(new UserExcelListener(sysUserMapper)).build();
    ReadSheet readSheet2 =
            EasyExcel.readSheet(2).head(SysUser.class).registerReadListener(new UserExcelListener(sysUserMapper)).build();
    ReadSheet readSheet3 =
            EasyExcel.readSheet(3).head(SysUser.class).registerReadListener(new UserExcelListener(sysUserMapper)).build();
    // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
    excelReader.read(readSheet0,readSheet1, readSheet2,readSheet3);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();
}
 

/**
 * 导入Excel
 * @param inputStream inputStream
 * @param clazz clazz
 * @param listener listener
 * @return boolean
 */
public static <T> Boolean readExcel(InputStream inputStream, Class<T> clazz, AnalysisEventListener<T> listener) {
	Boolean success = Boolean.TRUE;
	ExcelReader excelReader = null;
	try {
		excelReader = EasyExcelFactory.read(inputStream, clazz, listener).build();
		ReadSheet readSheet = EasyExcelFactory.readSheet(0).build();
		excelReader.read(readSheet);
	} catch (Exception e) {
		log.error("Read excel error: {}", e.getMessage(), e);
		success = Boolean.FALSE;
	} finally {
		if (excelReader != null)
			excelReader.finish();
	}
	return success;
}
 

/**
 * 读取 Excel(支持单个model的多个sheet)
 *
 * @param excel    文件
 * @param rowModel 实体类映射
 * @param listener 用于读取excel的listener
 * @return 错误的行号
 */
@SuppressWarnings("unchecked")
public static List<ErrRows> readExcel(MultipartFile excel, Class rowModel, BaseExcelListener listener) {
    ExcelReader reader = getReader(excel, rowModel, listener);
    try {
        Assert.notNull(reader, "导入Excel失败!");
        Integer totalSheetCount = reader.getSheets().size();
        for (Integer i = 0; i < totalSheetCount; i++) {
            reader.read(EasyExcel.readSheet(i).build());
        }
    } finally {
        // 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
        Optional.ofNullable(reader).ifPresent(ExcelReader::finish);
    }
    return listener.getErrRowsList();
}
 

private ExcelRuleListener partitionByRuleName(InputStream inputStream) {
    ExcelRuleListener listener = new ExcelRuleListener();
    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(ExcelTemplateRule.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.CUSTOM_RULE_NAME)) {
            sheet.setClazz(ExcelCustomRule.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        } else if (sheet.getSheetName().equals(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME)) {
            sheet.setClazz(ExcelMultiTemplateRule.class);
            sheet.setHeadLineMun(1);
            excelReader.read(sheet);
        }
    }

    return listener;
}
 

private void readAndWrite(File file) {
    ExcelWriter excelWriter = EasyExcel.write(file, RepetitionData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
    excelWriter.write(data(), writeSheet).write(data(), writeSheet).finish();
    ExcelReader excelReader = EasyExcel.read(file, RepetitionData.class, new RepetitionDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet).finish();
}
 

private void readAndWrite6(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = EasyExcel.getWriterWithTempAndHandler(null, out, null, false, null);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1, null);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    ExcelReader excelReader = EasyExcel.getReader(inputStream, new CompatibilityDataListener());
    excelReader.read(new Sheet(1, 0));
    inputStream.close();
}
 

private void readAndWrite5(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = EasyExcel.getWriterWithTemp(null, out, null, false);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1, null);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    ExcelReader excelReader = EasyExcel.getReader(inputStream, new CompatibilityDataListener());
    excelReader.read(new Sheet(1, 0));
    inputStream.close();
}
 

private void readAndWriteTable(File file) {
    ExcelWriter excelWriter = EasyExcel.write(file, RepetitionData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
    WriteTable writeTable = EasyExcel.writerTable(0).relativeHeadRowIndex(0).build();
    excelWriter.write(data(), writeSheet, writeTable).write(data(), writeSheet, writeTable).finish();
    ExcelReader excelReader = EasyExcel.read(file, RepetitionData.class, new RepetitionDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(2).build();
    excelReader.read(readSheet).finish();
}
 

private void readAndWrite4(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = new ExcelWriter(null, out, null, null);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1, null);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    ExcelReader excelReader = new ExcelReader(inputStream, null, new CompatibilityDataListener());
    excelReader.read(new Sheet(1, 0));
    inputStream.close();
}
 

private void readAndWrite3(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = new ExcelWriter(out, null);
    Sheet sheet1 = new Sheet(1, 0);
    sheet1.setSheetName("第一个sheet");
    writer.write0(data(), sheet1);
    writer.finish();
    out.close();

    InputStream inputStream = new FileInputStream(file);
    ExcelReader excelReader = new ExcelReader(inputStream, null, null, new CompatibilityDataListener());
    excelReader.read(new Sheet(1, 0));
    inputStream.close();

}
 

private void readAndWrite(File file) {
    ExcelWriter excelWriter = EasyExcel.write(file, SimpleData.class).build();
    WriteSheet writeSheet0 = EasyExcel.writerSheet(0, "第一个").build();
    WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "第二个").build();
    WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "第三个").build();
    WriteSheet writeSheet3 = EasyExcel.writerSheet(3, "第四个").build();
    excelWriter.write(data("name1"), writeSheet0);
    excelWriter.write(data("name2"), writeSheet1);
    excelWriter.write(data("name3"), writeSheet2);
    excelWriter.write(data("name4"), writeSheet3);
    excelWriter.finish();

    List<SkipData> list = EasyExcel.read(file, SkipData.class, null).sheet("第二个").doReadSync();
    Assert.assertEquals(1, list.size());
    Assert.assertEquals("name2", list.get(0).getName());

    SyncReadListener syncReadListener = new SyncReadListener();
    ExcelReader excelReader = EasyExcel.read(file, SkipData.class, null).registerReadListener(syncReadListener)
        .build();
    ReadSheet readSheet1 = EasyExcel.readSheet("第二个").build();
    ReadSheet readSheet3 = EasyExcel.readSheet("第四个").build();
    excelReader.read(readSheet1, readSheet3);
    List<Object> syncList = syncReadListener.getList();
    Assert.assertEquals(2, syncList.size());
    Assert.assertEquals("name2", ((SkipData) syncList.get(0)).getName());
    Assert.assertEquals("name4", ((SkipData) syncList.get(1)).getName());
    excelReader.finish();
}
 

private void read(File file) {
    MultipleSheetsListener multipleSheetsListener = new MultipleSheetsListener();
    ExcelReader excelReader = EasyExcel.read(file, MultipleSheetsData.class, multipleSheetsListener).build();
    List<ReadSheet> sheets = excelReader.excelExecutor().sheetList();
    int count = 1;
    for (ReadSheet readSheet : sheets) {
        excelReader.read(readSheet);
        Assert.assertEquals((long)multipleSheetsListener.getList().size(), (long)count);
        count++;
    }
    excelReader.finish();
}
 

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


    LOGGER.info("Finish to read project excel. excel content: rule sheet {}, project sheet {}", listener.getExcelRuleContent(), listener.getExcelProjectContent());
    return listener;
}
 

/**
 * 读多个或者全部sheet,这里注意一个sheet不能读取多次，多次读取需要重新读取文件
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void repeatedRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 读取全部sheet
    // 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();

    // 读取部分sheet
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    ExcelReader excelReader = null;
    try {
        excelReader = EasyExcel.read(fileName).build();

        // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
        ReadSheet readSheet1 =
            EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
        ReadSheet readSheet2 =
            EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
        // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
        excelReader.read(readSheet1, readSheet2);
    } finally {
        if (excelReader != null) {
            // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
            excelReader.finish();
        }
    }
}
 

@Test
public void testread() throws Exception {
    FileInputStream fileInputStream = new FileInputStream("D://test/t1.xlsx");

    ExcelReader excelReader = new ExcelReader(fileInputStream, ExcelTypeEnum.XLSX, null, new TestListener());
    excelReader.read();
}
 

@Test
public void hh1() throws IOException {
    ExcelReader reader =
        EasyExcel.read(new FileInputStream("D:\\test\\hg2.xls"), LockData.class, new RepeatListener())
            .headRowNumber(0).build();
    ReadSheet r2 = EasyExcel.readSheet(0).build();
    reader.read(r2);
    reader.finish();
}
 

@Test
public void hh2() throws IOException {
    ExcelReader reader =
        EasyExcel.read(new FileInputStream("D:\\test\\sheet.xls"), LockData.class, new RepeatListener())
            .headRowNumber(0).build();
    ReadSheet r2 = EasyExcel.readSheet(1).build();
    reader.read(r2);
    reader.finish();
}
 

@Test
public void hh() throws IOException {
    ExcelReader reader =
        EasyExcel.read(new FileInputStream("D:\\test\\hg2.xls"), LockData.class, new RepeatListener())
            .headRowNumber(0).build();
    ReadSheet r1 = EasyExcel.readSheet(0).build();
    ReadSheet r2 = EasyExcel.readSheet(2).build();
    reader.read(r1);
    reader.read(r2);
    reader.finish();
}
 

/**
 * Synchronous reads return results
 *
 * @return
 */
public <T> List<T> doReadAllSync() {
    SyncReadListener syncReadListener = new SyncReadListener();
    registerReadListener(syncReadListener);
    ExcelReader excelReader = build();
    excelReader.readAll();
    excelReader.finish();
    return (List<T>)syncReadListener.getList();
}
 

/**
 * 读取 Excel 的指定 sheet 指定数据
 *
 * @param excel       文件
 * @param baseRowModel    实体类映射，继承 BaseRowModel 类
 * @param sheetNo     sheet 的序号（从第几个 sheet 开始读）
 * @param headLineNum 表头行数（从第几行开始读）
 * @return Excel 数据 list
 */
public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel,
                                     int sheetNo, int headLineNum) {
    ExcelListener excelListener = new ExcelListener();
    ExcelReader reader = getReader(excel, excelListener);
    if (reader == null) {
        return null;
    }
    reader.read(new Sheet(sheetNo, headLineNum, baseRowModel.getClass()));
    return excelListener.getDatas();
}
 

/**
 * 读取 整个Excel(多个 sheet 需要 各个 sheet 字段相同)
 *
 * @param excel    文件
 * @param baseRowModel 实体类映射，继承 BaseRowModel 类
 * @return Excel 数据 list
 */
public static List readExcel(MultipartFile excel, BaseRowModel baseRowModel) {
    ExcelListener excelListener = new ExcelListener();
    ExcelReader reader = getReader(excel, excelListener);
    if (reader == null) {
        return null;
    }
    for (Sheet sheet : reader.getSheets()) {
        if (baseRowModel != null) {
            sheet.setClazz(baseRowModel.getClass());
        }
        reader.read(sheet);
    }
    return excelListener.getDatas();
}
 

/**
 * 读取Excel 指定sheet 的数据并存入数据库
 */
@Test
public void testReadMethod2(){

    String fileName = TestFileUtil.getPath() + "sysUser" + File.separator + "user.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    ExcelReader excelReader = EasyExcel.read(fileName, SysUser.class, new UserExcelListener(sysUserMapper)).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();
}
 

/**
 * 获取 ExcelReader
 *
 * @param excel    需要解析的 Excel 文件
 * @param listener new ExcelListener()
 * @return ExcelReader 或 null
 */
private static ExcelReader getReader(MultipartFile excel, Class rowModel,
                                     BaseExcelListener listener) {
    String filename = excel.getOriginalFilename();
    if (StringUtils.isEmpty(filename) || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
        throw new IllegalArgumentException("文件格式错误！");
    }

    try {
        return EasyExcel.read(excel.getInputStream(), rowModel, listener).build();
    } catch (IOException e) {
        log.error("/*------- 读取Excel IO异常 -------*/", e);
    }
    return null;
}
 

public void doReadAll() {
    ExcelReader excelReader = build();
    excelReader.readAll();
    excelReader.finish();
}
 

public ExcelReader build() {
    return new ExcelReader(readWorkbook);
}
 

public ExcelReaderSheetBuilder(ExcelReader excelReader) {
    this.readSheet = new ReadSheet();
    this.excelReader = excelReader;
}