com.alibaba.excel.EasyExcel Java Examples

/**
 * 填充列表
 *
 * @since 2.1.1
 */
@Test
public void listFill() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
    String templateFileName =
        TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "list.xlsx";

    // 方案1 一下子全部放到内存里面 并填充
    String fileName = TestFileUtil.getPath() + "listFill" + System.currentTimeMillis() + ".xlsx";
    // 这里 会填充到第一个sheet， 然后文件流会自动关闭
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(data());

    // 方案2 分多次 填充 会使用文件缓存（省内存）
    fileName = TestFileUtil.getPath() + "listFill" + System.currentTimeMillis() + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    excelWriter.fill(data(), writeSheet);
    excelWriter.fill(data(), writeSheet);
    // 千万别忘记关闭流
    excelWriter.finish();
}
 

private void readAndWrite(File file) {
    SimpleColumnWidthStyleStrategy simpleColumnWidthStyleStrategy = new SimpleColumnWidthStyleStrategy(50);
    SimpleRowHeightStyleStrategy simpleRowHeightStyleStrategy =
        new SimpleRowHeightStyleStrategy((short)40, (short)50);

    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)20);
    headWriteCellStyle.setWriteFont(headWriteFont);
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    WriteFont contentWriteFont = new WriteFont();
    contentWriteFont.setFontHeightInPoints((short)20);
    headWriteCellStyle.setWriteFont(contentWriteFont);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
        new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

    OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(2, 2, 0, 1);
    EasyExcel.write(file, StyleData.class).registerWriteHandler(simpleColumnWidthStyleStrategy)
        .registerWriteHandler(simpleRowHeightStyleStrategy).registerWriteHandler(horizontalCellStyleStrategy)
        .registerWriteHandler(onceAbsoluteMergeStrategy).sheet().doWrite(data());
    EasyExcel.read(file, StyleData.class, new StyleDataListener()).sheet().doRead();
}
 

/**
 * 根据参数只导出指定列
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 根据自己或者排除自己需要的列
 * <p>
 * 3. 直接写即可
 *
 * @since 2.1.1
 */
@Test
public void excludeOrIncludeWrite() {
    String fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";

    // 根据用户传入字段 假设我们要忽略 date
    Set<String> excludeColumnFiledNames = new HashSet<String>();
    excludeColumnFiledNames.add("date");
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, DemoData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
        .doWrite(data());

    fileName = TestFileUtil.getPath() + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
    // 根据用户传入字段 假设我们只要导出 date
    Set<String> includeColumnFiledNames = new HashSet<String>();
    includeColumnFiledNames.add("date");
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, DemoData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
        .doWrite(data());
}
 

/**
 * 根据参数和版本枚举导出excel文件
 *
 * @param excelParams 参数实体
 * @param excelType   excel类型枚举 03 or 07
 * @throws IOException IOException
 */
private static void exportExcel(EasyExcelParams excelParams, ExcelTypeEnum excelType) throws IOException {
    HttpServletResponse response = excelParams.getResponse();

    ServletOutputStream out = response.getOutputStream();
    prepareResponds(response, excelParams.getExcelNameWithoutExt(), excelType);
    ExcelWriter writer = null;
    try {
        writer = EasyExcel.write(out, excelParams.getDataModelClazz()).excelType(excelType).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(excelParams.getSheetName()).build();
        writer.write(excelParams.getData(), writeSheet);
    } finally {
        //必须保证写出结束后关闭IO
        Optional.ofNullable(writer).ifPresent(ExcelWriter::finish);
    }

}
 

/**
 * 图片导出
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link ImageData}
 * <p>
 * 2. 直接写即可
 */
@Test
public void imageWrite() throws Exception {
    String fileName = TestFileUtil.getPath() + "imageWrite" + System.currentTimeMillis() + ".xlsx";
    // 如果使用流 记得关闭
    InputStream inputStream = null;
    try {
        List<ImageData> list = new ArrayList<ImageData>();
        ImageData imageData = new ImageData();
        list.add(imageData);
        String imagePath = TestFileUtil.getPath() + "converter" + File.separator + "img.jpg";
        // 放入五种类型的图片 实际使用只要选一种即可
        imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
        imageData.setFile(new File(imagePath));
        imageData.setString(imagePath);
        inputStream = FileUtils.openInputStream(new File(imagePath));
        imageData.setInputStream(inputStream);
        imageData.setUrl(new URL(
            "https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg"));
        EasyExcel.write(fileName, ImageData.class).sheet().doWrite(list);
    } finally {
        if (inputStream != null) {
            inputStream.close();
        }
    }
}
 

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 horizontalFill(File file, File template) {
    ExcelWriter excelWriter = EasyExcel.write(file).withTemplate(template).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
    excelWriter.fill(data(), fillConfig, writeSheet);
    excelWriter.fill(data(), fillConfig, writeSheet);
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("date", "2019年10月9日13:28:28");
    excelWriter.fill(map, writeSheet);
    excelWriter.finish();

    List<Object> list = EasyExcel.read(file).sheet().headRowNumber(0).doReadSync();
    Assert.assertEquals(list.size(), 5L);
    Map<String, String> map0 = (Map<String, String>) list.get(0);
    Assert.assertEquals("张三", map0.get(2));
}
 

/**
 * 横向的填充
 *
 * @since 2.1.1
 */
@Test
public void horizontalFill() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // {} 代表普通变量 {.} 代表是list的变量
    String templateFileName =
        TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "horizontal.xlsx";

    String fileName = TestFileUtil.getPath() + "horizontalFill" + System.currentTimeMillis() + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
    excelWriter.fill(data(), fillConfig, writeSheet);
    excelWriter.fill(data(), fillConfig, writeSheet);

    Map<String, Object> map = new HashMap<String, Object>();
    map.put("date", "2019年10月9日13:28:28");
    excelWriter.fill(map, writeSheet);

    // 别忘记关闭流
    excelWriter.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();
}
 

/**
 * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
 *
 * @since 2.1.1
 */
@GetMapping("downloadFailedUsingJson")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    try {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // 这里需要设置不关闭流
        EasyExcel.write(response.getOutputStream(), SysUser.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                .doWrite(data());
    } catch (Exception e) {
        // 重置response
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        Map<String, String> map = new HashMap<String, String>();
        map.put("status", "failure");
        map.put("message", "下载文件失败" + e.getMessage());
        response.getWriter().println(JSONUtil.toJsonStr(map));
    }
}
 

@Test
public void test() throws Exception {
    File file = new File("D:\\test\\dataformat.xlsx");

    List<DataFormatData> list =
        EasyExcel.read(file, DataFormatData.class, null).sheet().headRowNumber(0).doReadSync();
    LOGGER.info("数据：{}", list.size());
    for (DataFormatData data : list) {
        Integer dataFormat = data.getDate().getDataFormat();

        String dataFormatString = data.getDate().getDataFormatString();

        if (dataFormat == null || dataFormatString == null) {

        } else {
            LOGGER.info("格式化：{};{}：{}", dataFormat, dataFormatString,
                DateUtil.isADateFormat(dataFormat, dataFormatString));
        }

        LOGGER.info("返回数据：{}", JSON.toJSONString(data));
    }
}
 

private void complexFill(File file, File template) {
    ExcelWriter excelWriter = EasyExcel.write(file).withTemplate(template).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new LoopMergeStrategy(2, 0)).build();
    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    excelWriter.fill(data(), fillConfig, writeSheet);
    excelWriter.fill(data(), fillConfig, writeSheet);
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("date", "2019年10月9日13:28:28");
    map.put("total", 1000);
    excelWriter.fill(map, writeSheet);
    excelWriter.finish();
    List<Object> list = EasyExcel.read(file).sheet().headRowNumber(3).doReadSync();
    Assert.assertEquals(list.size(), 21L);
    Map<String, String> map19 = (Map<String, String>) list.get(19);
    Assert.assertEquals("张三", map19.get(0));
}
 

@Test
public void testxls() throws Exception {
    File file = new File("D:\\test\\dataformat.xls");

    List<DataFormatData> list =
        EasyExcel.read(file, DataFormatData.class, null).sheet().headRowNumber(0).doReadSync();
    LOGGER.info("数据：{}", list.size());
    for (DataFormatData data : list) {
        Integer dataFormat = data.getDate().getDataFormat();

        String dataFormatString = data.getDate().getDataFormatString();

        if (dataFormat == null || dataFormatString == null) {

        } else {
            LOGGER.info("格式化：{};{}：{}", dataFormat, dataFormatString,
                DateUtil.isADateFormat(dataFormat, dataFormatString));
        }

        LOGGER.info("返回数据：{}", JSON.toJSONString(data));
    }
}
 

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

    InputStream inputStream = new FileInputStream(file);
    EasyExcel.readBySax(inputStream, new Sheet(1, 0), new CompatibilityDataListener());
    inputStream.close();
}
 

@Test
public void t02Fill() {
    ExcelWriter excelWriter = EasyExcel.write(fileFill07).withTemplate(template07).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    for (int j = 0; j < 100; j++) {
        excelWriter.fill(data(), writeSheet);
        LOGGER.info("{} fill success.", j);
    }
    excelWriter.finish();
}
 

/**
 * 数据量大的复杂填充
 * <p>
 * 这里的解决方案是 确保模板list为最后一行，然后再拼接table.还有03版没救，只能刚正面加内存。
 *
 * @since 2.1.1
 */
@Test
public void complexFillWithTable() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // {} 代表普通变量 {.} 代表是list的变量
    // 这里模板 删除了list以后的数据，也就是统计的这一行
    String templateFileName =
        TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "complexFillWithTable.xlsx";

    String fileName = TestFileUtil.getPath() + "complexFillWithTable" + System.currentTimeMillis() + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // 直接写入数据
    excelWriter.fill(data(), writeSheet);
    excelWriter.fill(data(), writeSheet);

    // 写入list之前的数据
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("date", "2019年10月9日13:28:28");
    excelWriter.fill(map, writeSheet);

    // list 后面还有个统计 想办法手动写入
    // 这里偷懒直接用list 也可以用对象
    List<List<String>> totalListList = new ArrayList<List<String>>();
    List<String> totalList = new ArrayList<String>();
    totalListList.add(totalList);
    totalList.add(null);
    totalList.add(null);
    totalList.add(null);
    // 第四列
    totalList.add("统计:1000");
    // 这里是write 别和fill 搞错了
    excelWriter.write(totalListList, writeSheet);
    excelWriter.finish();
    // 总体上写法比较复杂 但是也没有想到好的版本 异步的去写入excel 不支持行的删除和移动，也不支持备注这种的写入，所以也排除了可以
    // 新建一个 然后一点点复制过来的方案，最后导致list需要新增行的时候，后面的列的数据没法后移，后续会继续想想解决方案
}
 

@Test
public void testCache() throws Exception {
    File file = new File("D:\\test\\headt1.xls");
    EasyExcel.read(file, HeadReadData.class, new HDListener()).readCache(new Ehcache(20)).sheet(0).doRead();

    LOGGER.info("------------------");
    EasyExcel.read(file, HeadReadData.class, new HDListener()).readCache(new Ehcache(20)).sheet(0).doRead();
    LOGGER.info("------------------");
    EasyExcel.read(file, HeadReadData.class, new HDListener()).readCache(new Ehcache(20)).sheet(0).doRead();
}
 

@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 noModelWrite() {
    // 写法1
    String fileName = TestFileUtil.getPath() + "noModelWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());
}
 

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

private void readCn(File file) {
    List<DateFormatData> list =
        EasyExcel.read(file, DateFormatData.class, null).locale(Locale.CHINA).sheet().doReadSync();
    for (DateFormatData data : list) {
        Assert.assertEquals(data.getDateStringCn(), data.getDate());
        Assert.assertEquals(data.getNumberStringCn(), data.getNumber());
    }
}
 

/**
 * 日期、数字或者自定义格式转换
 * <p>
 * 默认读的转换器{@link DefaultConverterLoader#loadDefaultReadConverter()}
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link ConverterData}.里面可以使用注解{@link DateTimeFormat}、{@link NumberFormat}或者自定义注解
 * <p>
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link ConverterDataListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void converterRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet
    EasyExcel.read(fileName, ConverterData.class, new ConverterDataListener())
        // 这里注意 我们也可以registerConverter来指定自定义转换器， 但是这个转换变成全局了， 所有java为string,excel为string的都会用这个转换器。
        // 如果就想单个字段使用请使用@ExcelProperty 指定converter
        // .registerConverter(new CustomStringStringConverter())
        // 读取sheet
        .sheet().doRead();
}
 

@Test
public void hh() throws IOException {
    List<Object> list =
        EasyExcel.read(new FileInputStream("D:\\test\\201909301017rule.xlsx")).headRowNumber(2).sheet().doReadSync();
    for (Object data : list) {
        LOGGER.info("返回数据：{}", JSON.toJSONString(data));
    }
}
 

@Test
public void simpleWrite2() {
    // 写法1
    String fileName = TestFileUtil.getPath() + "t22" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, WriteData.class).sheet("模板").registerWriteHandler(new WriteHandler()).doWrite(data1());
}
 

@Test
public void simpleWrite() {
    // 写法1
    String fileName = TestFileUtil.getPath() + "t22" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).relativeHeadRowIndex(10).sheet("模板").doWrite(data());
}
 

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

@Test
public void read() throws Exception {
    long start = System.currentTimeMillis();
    EasyExcel.read(new FileInputStream("D:\\test\\MRP生产视图(1).xlsx"), LargeData.class, new LargeDataListener())
        .headRowNumber(2).sheet().doRead();
    LOGGER.info("Large data total time spent:{}", System.currentTimeMillis() - start);
}
 

/**
 * 读多个或者全部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 excludeIndex(File file) {
    Set<Integer> excludeColumnIndexes = new HashSet<Integer>();
    excludeColumnIndexes.add(0);
    excludeColumnIndexes.add(3);
    EasyExcel.write(file, ExcludeOrIncludeData.class).excludeColumnIndexes(excludeColumnIndexes).sheet()
        .doWrite(data());
    List<Map<Integer, String>> dataMap = EasyExcel.read(file).sheet().doReadSync();
    Assert.assertEquals(1, dataMap.size());
    Map<Integer, String> record = dataMap.get(0);
    Assert.assertEquals(2, record.size());
    Assert.assertEquals("column2", record.get(0));
    Assert.assertEquals("column3", record.get(1));

}