com.alibaba.excel.write.metadata.WriteSheet Java Examples

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

/**
 * 根据参数和版本枚举导出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);
    }

}
 

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

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

private void compositeFill(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(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();

    List<Object> list = EasyExcel.read(file).ignoreEmptyRow(false).sheet().headRowNumber(0).doReadSync();
    Map<String, String> map0 = (Map<String, String>) list.get(0);
    Assert.assertEquals("张三", map0.get(21));
    Map<String, String> map27 = (Map<String, String>) list.get(27);
    Assert.assertEquals("张三", map27.get(0));
    Map<String, String> map29 = (Map<String, String>) list.get(29);
    Assert.assertEquals("张三", map29.get(3));
}
 

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

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

/**
 * 横向的填充
 *
 * @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 complexFill() {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    // {} 代表普通变量 {.} 代表是list的变量
    String templateFileName =
        TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "complex.xlsx";

    String fileName = TestFileUtil.getPath() + "complexFill" + System.currentTimeMillis() + ".xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
    // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
    // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
    // 如果数据量大 list不是最后一行 参照下一个
    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();
}
 

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

/**
 * Compatible with old code
 */
@Deprecated
private void compatibleOldCode(WriteBasicParameter writeBasicParameter) {
    switch (holderType()) {
        case SHEET:
            compatibleOldCodeCreateRowCellStyleStrategy(writeBasicParameter,
                ((WriteSheet) writeBasicParameter).getTableStyle());
            compatibleOldCodeCreateHeadColumnWidthStyleStrategy(writeBasicParameter,
                ((WriteSheet) writeBasicParameter).getColumnWidthMap());
            return;
        case TABLE:
            compatibleOldCodeCreateRowCellStyleStrategy(writeBasicParameter,
                ((WriteTable) writeBasicParameter).getTableStyle());
            return;
        default:
    }
}
 

public WriteSheetHolder(WriteSheet writeSheet, WriteWorkbookHolder writeWorkbookHolder) {
    super(writeSheet, writeWorkbookHolder, writeWorkbookHolder.getWriteWorkbook().getConvertAllFiled());
    this.writeSheet = writeSheet;
    if (writeSheet.getSheetNo() == null && StringUtils.isEmpty(writeSheet.getSheetName())) {
        this.sheetNo = 0;
    } else {
        this.sheetNo = writeSheet.getSheetNo();
    }
    this.sheetName = writeSheet.getSheetName();
    this.parentWriteWorkbookHolder = writeWorkbookHolder;
    this.hasBeenInitializedTable = new HashMap<Integer, WriteTableHolder>();
    if (writeWorkbookHolder.getTempTemplateInputStream() != null) {
        writeLastRowTypeEnum = WriteLastRowTypeEnum.TEMPLATE_EMPTY;
    } else {
        writeLastRowTypeEnum = WriteLastRowTypeEnum.COMMON_EMPTY;
    }
}
 

@PostMapping("/export")
@QueryAction
@SneakyThrows
public Mono<Void> export(ServerHttpResponse response, QueryParamEntity parameter) {
    response.getHeaders().set(HttpHeaders.CONTENT_DISPOSITION,
        "attachment; filename=".concat(URLEncoder.encode("设备实例.xlsx", StandardCharsets.UTF_8.displayName())));
    parameter.setPaging(false);

    return StreamUtils.buffer(
        512 * 1024,
        output -> {
            ExcelWriter excelWriter = EasyExcel.write(output, DeviceInstanceImportExportEntity.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            return service.query(parameter)
                .map(entity -> FastBeanCopier.copy(entity, new DeviceInstanceImportExportEntity()))
                .buffer(100)
                .doOnNext(list -> excelWriter.write(list, writeSheet))
                .doOnComplete(excelWriter::finish)
                .then();
        })
        .map(bufferFactory::wrap)
        .as(response::writeWith);
}
 

/**
 * 写入到不同的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();
}
 

/**
 * @param writeSheet
 */
@Override
public void currentSheet(WriteSheet writeSheet, WriteTypeEnum writeType) {
    if (writeSheet == null) {
        throw new IllegalArgumentException("Sheet argument cannot be null");
    }
    if (selectSheetFromCache(writeSheet)) {
        return;
    }

    initCurrentSheetHolder(writeSheet);

    // Workbook handler need to supplementary execution
    WriteHandlerUtils.beforeWorkbookCreate(this, true);
    WriteHandlerUtils.afterWorkbookCreate(this, true);

    // Initialization current sheet
    initSheet(writeType);
}
 

private boolean selectSheetFromCache(WriteSheet writeSheet) {
    writeSheetHolder = null;
    Integer sheetNo = writeSheet.getSheetNo();
    if (sheetNo == null && StringUtils.isEmpty(writeSheet.getSheetName())) {
        sheetNo = 0;
    }
    if (sheetNo != null) {
        writeSheetHolder = writeWorkbookHolder.getHasBeenInitializedSheetIndexMap().get(sheetNo);
    }
    if (writeSheetHolder == null && !StringUtils.isEmpty(writeSheet.getSheetName())) {
        writeSheetHolder = writeWorkbookHolder.getHasBeenInitializedSheetNameMap().get(writeSheet.getSheetName());
    }
    if (writeSheetHolder == null) {
        return false;
    }
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Sheet:{} is already existed", writeSheet.getSheetNo());
    }
    writeSheetHolder.setNewInitialization(Boolean.FALSE);
    writeTableHolder = null;
    currentWriteHolder = writeSheetHolder;
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("CurrentConfiguration is writeSheetHolder");
    }
    return true;
}
 

@Test
public void test() throws Exception {
    // 方法2 如果写到不同的sheet 同一个对象
    String fileName = TestFileUtil.getPath() + "large" + System.currentTimeMillis() + ".xlsx";
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // 背景设置为红色
    headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)20);
    headWriteCellStyle.setWriteFont(headWriteFont);
    // 内容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    // 背景绿色
    contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short)20);
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
        new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);


    ExcelWriter excelWriter = EasyExcel.write(fileName,LargeData.class).registerWriteHandler(horizontalCellStyleStrategy).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    for (int j = 0; j < 100; j++) {
        excelWriter.write(data(), writeSheet);
        LOGGER.info("{} fill success.", j);
    }
    excelWriter.finish();

}
 

@Test
public void test02() {
    // 写法2
    // 这里 需要指定写用哪个class去读
    ExcelWriter excelWriter = EasyExcel.write(pathName1 + "write.xlsx", Order.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
    excelWriter.write(data(1000), writeSheet);
    /// 千万别忘记finish 会帮忙关闭流
    excelWriter.finish();
}
 

/**
 * 导出Excel
 * @param request request
 * @param response response
 * @param dataList 数据list
 * @param fileName 文件名
 * @param sheetName sheet 名
 * @param clazz clazz
 */
public static <T> void writeExcel(HttpServletRequest request, HttpServletResponse response, List<T> dataList,
		String fileName, String sheetName, Class<T> clazz) {
	ExcelWriter excelWriter = null;
	try {
		excelWriter = EasyExcelFactory
				.write(getOutputStream(fileName, request, response, ExcelTypeEnum.XLSX), clazz).build();
		WriteSheet writeSheet = EasyExcelFactory.writerSheet(sheetName).build();
		excelWriter.write(dataList, writeSheet);
	} finally {
		if (excelWriter != null)
			excelWriter.finish();
	}
}
 

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

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

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

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

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