Java Code Examples for com.alibaba.excel.ExcelWriter#finish()

/**
 * 使用table去写入
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 然后写入table即可
 */
@Test
public void tableWrite() {
    String fileName = TestFileUtil.getPath() + "tableWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里直接写多个table的案例了，如果只有一个 也可以直一行代码搞定，参照其他案例
    // 这里 需要指定写用哪个class去写
    ExcelWriter excelWriter = null;
    try {
        excelWriter = EasyExcel.write(fileName, DemoData.class).build();
        // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build();
        // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
        WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
        WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
        // 第一次写入会创建头
        excelWriter.write(data(), writeSheet, writeTable0);
        // 第二次写如也会创建头，然后在第一次的后面写入数据
        excelWriter.write(data(), writeSheet, writeTable1);
    } finally {
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}
 

private void writeExcelToOutput(List<ExcelTemplateRule> templateRules, List<ExcelCustomRule> customRules,
                                List<ExcelMultiTemplateRule> multiTemplateRules, OutputStream outputStream) throws WriteExcelException, IOException {
    try {
        LOGGER.info("Start to write excel");
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        Sheet templateSheet = new Sheet(1, 0, ExcelTemplateRule.class);
        templateSheet.setSheetName(ExcelSheetName.TEMPLATE_RULE_NAME);
        writer.write(templateRules, templateSheet);

        Sheet customSheet = new Sheet(2, 0, ExcelCustomRule.class);
        customSheet.setSheetName(ExcelSheetName.CUSTOM_RULE_NAME);
        writer.write(customRules, customSheet);

        Sheet multiTemplateSheet = new Sheet(3, 0, ExcelMultiTemplateRule.class);
        multiTemplateSheet.setSheetName(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME);
        writer.write(multiTemplateRules, multiTemplateSheet);

        writer.finish();
        LOGGER.info("Finish to write excel");
    } catch (Exception e) {
        throw new WriteExcelException(e.getMessage());
    } finally {
        outputStream.close();
    }
}
 

/**
 * 横向的填充
 *
 * @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();
}
 

/**
 * 填充列表
 *
 * @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();
}
 

/**
 * 最简单的写
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 直接写即可
 */
@Test
public void simpleWrite() {
    // 写法1
    String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

    // 写法2
    fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写
    ExcelWriter excelWriter = null;
    try {
        excelWriter = EasyExcel.write(fileName, DemoData.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        excelWriter.write(data(), writeSheet);
    } finally {
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
}
 

@Test
public void test() throws Exception {
    // 方法2 如果写到不同的sheet 同一个对象
    String fileName = TestFileUtil.getPath() + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
    OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(2, 2, 0, 1);

    // 这里 指定文件
    ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).registerWriteHandler(onceAbsoluteMergeStrategy).build();
    WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "模板1").build();
    WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "模板2").build();
    WriteSheet writeSheet3 = EasyExcel.writerSheet(3, "模板3").build();
    excelWriter.write(data(2), writeSheet2);
    excelWriter.write(data(3), writeSheet3);
    excelWriter.write(data(1), writeSheet1);
    excelWriter.write(data(3), writeSheet3);
    excelWriter.write(data(1), writeSheet1);
    /// 千万别忘记finish 会帮忙关闭流
    excelWriter.finish();
}
 

@Test
public void tableWrite() {
    String fileName = TestFileUtil.getPath() + "tableWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里直接写多个table的案例了，如果只有一个 也可以直一行代码搞定，参照其他案例
    // 这里 需要指定写用哪个class去写
    ExcelWriter excelWriter = EasyExcel.write(fileName).build();
    // 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
    WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
    // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
    WriteTable writeTable0 = EasyExcel.writerTable(0).head(DemoData1.class).build();
    // 第一次写入会创建头
    excelWriter.write(data(), writeSheet, writeTable0);
    // 第二次写如也会创建头，然后在第一次的后面写入数据
    /// 千万别忘记finish 会帮忙关闭流
    excelWriter.finish();
}
 

/**
 * 复杂的填充
 *
 * @since 2.1.1
 */
@Test
public void complexFill() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // {} 代表普通变量 {.} 代表是list的变量
    OnceAbsoluteMergeStrategy onceAbsoluteMergeStrategy = new OnceAbsoluteMergeStrategy(2, 2, 0, 1);

    String fileName = TestFileUtil.getPath() + "complexFill" + System.currentTimeMillis() + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).registerWriteHandler(onceAbsoluteMergeStrategy).withTemplate(TestFileUtil.readUserHomeFile("test/simple.xlsx")).build();
    WriteSheet writeSheet0 = EasyExcel.writerSheet(0).build();
    WriteSheet writeSheet1 = EasyExcel.writerSheet(1).build();

    excelWriter.fill(teamp(), writeSheet0);
    excelWriter.fill(teamp(), writeSheet1);

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

    excelWriter.finish();
}
 

/**
 * 异步导出 Excel ：一个 sheet，带表头
 *
 * @param
 * @param list      数据 list，每个元素为一个 BaseRowModel
 * @param sheetName 导入文件的 sheet 名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static String asyWriteExcel(List<? extends BaseRowModel> list,
                                String sheetName, BaseRowModel object) {
    // 现将数据导出excel到本地
    try {
        String fileName = URLEncoder.encode(createFileName(), "UTF-8");
        ExcelWriter writer = new ExcelWriter(getFileOutputStream(fileName), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
        // 读取该excel,并上传到oss，返回下载链接
        // File file = readFileByLines(fileName + ".xlsx");
        // return FileUploadUtil.upload(file, fileName + ".xlsx");
    } catch (UnsupportedEncodingException e) {
        throw new RuntimeException("创建excel失败!");
    }
    return null;
}
 

/**
 * 写入到不同的sheet
 */
@Test
public void testWriteMethodMoreSheet() {
    // 方法3 如果写到不同的sheet 不同的对象
    String fileName = TestFileUtil.getPath() + System.currentTimeMillis() + "user.xlsx";
    // 这里 指定文件
    ExcelWriter excelWriter = EasyExcel.write(fileName).build();
    List<SysUser> sysUsers = initDate();
    List<List<SysUser>> partition = ListUtils.partition(sysUsers, 5);//分成多份，没份5条数据
    // 去调用写入,这里我调用了多次，这里最终会写到多个sheet里面
    for (int i = 0; i < partition.size(); i++) {
        // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class 实际上可以一直变
        WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(SysUser.class).build();
        // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
        List<SysUser> data = partition.get(i);
        excelWriter.write(data, writeSheet);
    }
    /// 千万别忘记finish 会帮忙关闭流
    excelWriter.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();
}
 

/**
 * @return
 */
public File generate() {
    String suffix = this.template.getName().endsWith(".xlsx") ? ".xlsx" : ".xls";
    File dest = SysConfiguration.getFileOfTemp("REPORT-" + System.currentTimeMillis() + suffix);

    List<Map<String, Object>> datas = buildData();
    if (datas.isEmpty()) {
        return  null;
    }

    Map<String, Object> master = null;
    if (this.hasMaster) {
        Iterator<Map<String, Object>> iter = datas.iterator();
        master = iter.next();
        iter.remove();
    }

    ExcelWriter excelWriter = null;
    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    try {
        excelWriter = EasyExcel.write(dest).withTemplate(template).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();

        // 明细记录
        if (!datas.isEmpty()) {
            excelWriter.fill(datas, fillConfig, writeSheet);
        }

        // 主记录
        if (master != null) {
            excelWriter.fill(master, writeSheet);
        }

    } finally {
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
    return dest;
}
 

/**
 * 数据量大的复杂填充
 * <p>
 * 这里的解决方案是 确保模板list为最后一行，然后再拼接table.还有03版没救，只能刚正面加内存。
 *
 * @since 2.1.1
 */
@Test
public void complexFillWithTable() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // {} 代表普通变量 {.} 代表是list的变量
    // 这里模板 删除了list以后的数据，也就是统计的这一行
    String templateFileName = "D:\\test\\complex.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(data2(), 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需要新增行的时候，后面的列的数据没法后移，后续会继续想想解决方案
}
 

/**
 * 多列表组合填充填充
 *
 * @since 2.2.0-beta1
 */
@Test
public void compositeFill() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // {} 代表普通变量 {.} 代表是list的变量 {前缀.} 前缀可以区分不同的list
    String templateFileName =
        TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "composite.xlsx";

    String fileName = TestFileUtil.getPath() + "compositeFill" + System.currentTimeMillis() + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
    // 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 data1，然后多个list必须用 FillWrapper包裹
    excelWriter.fill(new FillWrapper("data1", data()), fillConfig, writeSheet);
    excelWriter.fill(new FillWrapper("data1", data()), fillConfig, writeSheet);
    excelWriter.fill(new FillWrapper("data2", data()), writeSheet);
    excelWriter.fill(new FillWrapper("data2", data()), writeSheet);
    excelWriter.fill(new FillWrapper("data3", data()), writeSheet);
    excelWriter.fill(new FillWrapper("data3", data()), 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 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();

}
 

/**
 * 最简单的写
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link SysUser}
 * <p>
 * 2. 直接写即可
 */
@Test
public void testWriteMethod2(){

    String fileName = TestFileUtil.getPath() + System.currentTimeMillis() + "user.xlsx";
    // 这里 需要指定写用哪个class去写
    ExcelWriter excelWriter = EasyExcel.write(fileName, SysUser.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
    excelWriter.write(initDate(), writeSheet);
    /// 千万别忘记finish 会帮忙关闭流
    excelWriter.finish();
}
 

private void readAndWrite2(File file) throws Exception {
    OutputStream out = new FileOutputStream(file);
    ExcelWriter writer = EasyExcel.getWriter(out, null, false);
    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();
}
 

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

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

/**
 * 数据量大的复杂填充
 * <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需要新增行的时候，后面的列的数据没法后移，后续会继续想想解决方案
}