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

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

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

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

/**
 * 异步导出 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;
}
 

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

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

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

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

private void writeExcelToOutput(List<ExcelProject> excelProjects, List<ExcelTemplateRuleByProject> excelTemplateRuleByProjects, List<ExcelCustomRuleByProject> excelCustomRuleByProjects,
                                List<ExcelMultiTemplateRuleByProject> excelMultiTemplateRuleByProjects, OutputStream outputStream) throws WriteExcelException, IOException {
    try {
        LOGGER.info("Start to write excel");
        ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
        Sheet templateRuleSheet = new Sheet(1, 0, ExcelTemplateRuleByProject.class);
        templateRuleSheet.setSheetName(ExcelSheetName.TEMPLATE_RULE_NAME);
        writer.write(excelTemplateRuleByProjects, templateRuleSheet);

        Sheet projectSheet = new Sheet(2, 0, ExcelProject.class);
        projectSheet.setSheetName(ExcelSheetName.PROJECT_NAME);
        writer.write(excelProjects, projectSheet);

        Sheet customRuleSheet = new Sheet(3, 0, ExcelCustomRuleByProject.class);
        customRuleSheet.setSheetName(ExcelSheetName.CUSTOM_RULE_NAME);
        writer.write(excelCustomRuleByProjects, customRuleSheet);

        Sheet multiRuleSheet = new Sheet(4, 0, ExcelMultiTemplateRuleByProject.class);
        multiRuleSheet.setSheetName(ExcelSheetName.MULTI_TEMPLATE_RULE_NAME);
        writer.write(excelMultiTemplateRuleByProjects, multiRuleSheet);
        writer.finish();
        LOGGER.info("Finish to write excel");
    } catch (Exception e) {
        throw new WriteExcelException(e.getMessage());
    } finally {
        outputStream.close();
    }
}
 

/**
 * 数据量大的复杂填充
 * <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 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();

}
 

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

/**
 * 导出 Excel ：一个 sheet，带表头
 *
 * @param response  HttpServletResponse
 * @param list      需要导出的数据
 * @param fileName  导出的文件名
 * @param sheetName 导入文件的 sheet 名
 * @param object    映射实体类，Excel 模型
 */
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
                              String fileName, String sheetName, BaseRowModel object) {
    // WriteModel 是 写入 Excel 的数据模型对象
    ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
    Sheet sheet = new Sheet(1, 0, object.getClass());
    sheet.setSheetName(sheetName);
    // 异常处理
    writer.write(list, sheet);
    writer.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();
}
 

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

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