Java Code Examples for com.alibaba.excel.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;
}
 

/**
 * 读多个或者全部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();
}
 

/**
 * 最简单的读
 * <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();
}
 

/**
 * 读取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();
}
 

/**
 * 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();
}
 

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

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

/**
 * 读多个或者全部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();
        }
    }
}
 

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

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