Search in sources :

Example 16 with ReadSheet

use of com.alibaba.excel.read.metadata.ReadSheet 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)

Example 17 with ReadSheet

use of com.alibaba.excel.read.metadata.ReadSheet in project easyexcel by alibaba.

the class ReadTest method repeatedRead.

/**
 * 读多个或者全部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";
    // 写法1: 使用 try-with-resources @since 3.1.0
    try (ExcelReader 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);
    }
    // 写法2: 不使用 try-with-resources
    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.close();
        }
    }
}
Also used : ExcelReader(com.alibaba.excel.ExcelReader) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet) Test(org.junit.Test)

Example 18 with ReadSheet

use of com.alibaba.excel.read.metadata.ReadSheet in project easyexcel by alibaba.

the class ParameterDataTest method readAndWrite5.

private void readAndWrite5(File file, ExcelTypeEnum type) throws Exception {
    ExcelWriter excelWriter = EasyExcel.write(new FileOutputStream(file)).excelType(type).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();
}
Also used : ExcelReader(com.alibaba.excel.ExcelReader) MapCache(com.alibaba.excel.cache.MapCache) ExcelWriter(com.alibaba.excel.ExcelWriter) FileOutputStream(java.io.FileOutputStream) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) WriteTable(com.alibaba.excel.write.metadata.WriteTable) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet)

Example 19 with ReadSheet

use of com.alibaba.excel.read.metadata.ReadSheet in project easyexcel by alibaba.

the class ParameterDataTest method readAndWrite6.

private void readAndWrite6(File file, ExcelTypeEnum type) throws Exception {
    ExcelWriter excelWriter = EasyExcel.write(new FileOutputStream(file)).excelType(type).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();
}
Also used : ExcelReader(com.alibaba.excel.ExcelReader) MapCache(com.alibaba.excel.cache.MapCache) ExcelWriter(com.alibaba.excel.ExcelWriter) FileOutputStream(java.io.FileOutputStream) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) WriteTable(com.alibaba.excel.write.metadata.WriteTable) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet) StringStringConverter(com.alibaba.excel.converters.string.StringStringConverter)

Example 20 with ReadSheet

use of com.alibaba.excel.read.metadata.ReadSheet in project easyexcel by alibaba.

the class SkipDataTest method readAndWrite.

private void readAndWrite(File file) {
    try (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);
    }
    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();
    try (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());
    }
}
Also used : ExcelReader(com.alibaba.excel.ExcelReader) SyncReadListener(com.alibaba.excel.event.SyncReadListener) ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) SimpleData(com.alibaba.easyexcel.test.core.simple.SimpleData) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet)

Aggregations

ReadSheet (com.alibaba.excel.read.metadata.ReadSheet)20 ExcelReader (com.alibaba.excel.ExcelReader)15 Test (org.junit.Test)6 ExcelWriter (com.alibaba.excel.ExcelWriter)5 WriteSheet (com.alibaba.excel.write.metadata.WriteSheet)5 WriteTable (com.alibaba.excel.write.metadata.WriteTable)3 FileInputStream (java.io.FileInputStream)3 ArrayList (java.util.ArrayList)3 MapCache (com.alibaba.excel.cache.MapCache)2 AnalysisContext (com.alibaba.excel.context.AnalysisContext)2 FileOutputStream (java.io.FileOutputStream)2 SimpleData (com.alibaba.easyexcel.test.core.simple.SimpleData)1 TestFileUtil (com.alibaba.easyexcel.test.util.TestFileUtil)1 EasyExcel (com.alibaba.excel.EasyExcel)1 XlsxRowHandler (com.alibaba.excel.analysis.v07.handlers.sax.XlsxRowHandler)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 StringStringConverter (com.alibaba.excel.converters.string.StringStringConverter)1