Search in sources :

Example 6 with FillConfig

use of com.alibaba.excel.write.metadata.fill.FillConfig in project easyexcel by alibaba.

the class FillTest method compositeFill.

/**
 * 多列表组合填充填充
 *
 * @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";
    // 方案1 : 使用 try-with-resources @since 3.1.0
    try (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");
        map.put("date", new Date());
        excelWriter.fill(map, writeSheet);
    }
    // 方案2 : 不使用 try-with-resources
    ExcelWriter excelWriter = null;
    try {
        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");
        map.put("date", new Date());
        excelWriter.fill(map, writeSheet);
    } finally {
        // 千万别忘记close 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.close();
        }
    }
}
Also used : HashMap(java.util.HashMap) ExcelWriter(com.alibaba.excel.ExcelWriter) FillWrapper(com.alibaba.excel.write.metadata.fill.FillWrapper) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) Date(java.util.Date) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig) Test(org.junit.Test)

Example 7 with FillConfig

use of com.alibaba.excel.write.metadata.fill.FillConfig in project easyexcel by alibaba.

the class FillTest method complexFill.

/**
 * 复杂的填充
 *
 * @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";
    // 方案1 : 使用 try-with-resources @since 3.1.0
    try (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 = MapUtils.newHashMap();
        map.put("date", "2019年10月9日13:28:28");
        map.put("total", 1000);
        excelWriter.fill(map, writeSheet);
    }
    // 方案2 : 不使用 try-with-resources
    ExcelWriter excelWriter = null;
    try {
        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 = MapUtils.newHashMap();
        map.put("date", "2019年10月9日13:28:28");
        map.put("total", 1000);
        excelWriter.fill(map, writeSheet);
    } finally {
        // 千万别忘记close 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.close();
        }
    }
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig) Test(org.junit.Test)

Example 8 with FillConfig

use of com.alibaba.excel.write.metadata.fill.FillConfig in project springboot-learning by lyb-geek.

the class ExcelWriter method writeTemplate.

public <T> void writeTemplate(String templateFileName, List<T> data) throws Exception {
    if (StringUtils.isBlank(templateFileName)) {
        throw new BizException("模板不能为空");
    }
    setRepHeader();
    com.alibaba.excel.ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().forceNewRow(forceNewRow).build();
    excelWriter.fill(data, fillConfig, writeSheet);
    if (MapUtils.isNotEmpty(templateParamsMap)) {
        excelWriter.fill(templateParamsMap, writeSheet);
    }
    excelWriter.finish();
}
Also used : BizException(com.github.lybgeek.common.exception.BizException) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig)

Example 9 with FillConfig

use of com.alibaba.excel.write.metadata.fill.FillConfig in project rebuild by getrebuild.

the class EasyExcelGenerator method generate.

/**
 * @return
 */
public File generate() {
    File tmp = RebuildConfiguration.getFileOfTemp(String.format("RBREPORT-%d.%s", System.currentTimeMillis(), template.getName().endsWith(".xlsx") ? "xlsx" : "xls"));
    List<Map<String, Object>> datas = buildData();
    // 无数据
    if (datas.isEmpty())
        return null;
    Map<String, Object> main = null;
    if (this.hasMain) {
        Iterator<Map<String, Object>> iter = datas.iterator();
        main = iter.next();
        iter.remove();
    }
    ExcelWriter excelWriter = null;
    FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
    try {
        excelWriter = EasyExcel.write(tmp).withTemplate(template).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().registerWriteHandler(new FixsMergeStrategy()).build();
        // 有明细记录
        if (!datas.isEmpty()) {
            excelWriter.fill(datas, fillConfig, writeSheet);
        }
        // 主记录
        if (main != null) {
            excelWriter.fill(main, writeSheet);
        }
    } finally {
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }
    return tmp;
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) File(java.io.File) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig)

Example 10 with FillConfig

use of com.alibaba.excel.write.metadata.fill.FillConfig in project easyexcel by alibaba.

the class FillTest method TestFillNullPoint.

@Test
public void TestFillNullPoint() {
    String templateFileName = TestFileUtil.getPath() + "temp/issue1663" + File.separator + "template.xlsx";
    String fileName = TestFileUtil.getPath() + "temp/issue1663" + File.separator + "issue1663.xlsx";
    ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).build();
    excelWriter.fill(new FillWrapper("data1", data()), fillConfig, writeSheet);
    Map<String, Object> map = new HashMap<String, Object>();
    // Variable {date} does not exist in the template.xlsx, which should be ignored instead of reporting an error.
    map.put("date", "2019年10月9日13:28:28");
    excelWriter.fill(map, writeSheet);
    excelWriter.finish();
}
Also used : HashMap(java.util.HashMap) ExcelWriter(com.alibaba.excel.ExcelWriter) FillWrapper(com.alibaba.excel.write.metadata.fill.FillWrapper) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig) Test(org.junit.Test)

Aggregations

FillConfig (com.alibaba.excel.write.metadata.fill.FillConfig)13 WriteSheet (com.alibaba.excel.write.metadata.WriteSheet)12 ExcelWriter (com.alibaba.excel.ExcelWriter)11 HashMap (java.util.HashMap)10 Test (org.junit.Test)7 FillWrapper (com.alibaba.excel.write.metadata.fill.FillWrapper)5 Map (java.util.Map)4 Date (java.util.Date)2 WriteContext (com.alibaba.excel.context.WriteContext)1 CellDataTypeEnum (com.alibaba.excel.enums.CellDataTypeEnum)1 WriteDirectionEnum (com.alibaba.excel.enums.WriteDirectionEnum)1 WriteTemplateAnalysisCellTypeEnum (com.alibaba.excel.enums.WriteTemplateAnalysisCellTypeEnum)1 ExcelGenerateException (com.alibaba.excel.exception.ExcelGenerateException)1 WriteCellData (com.alibaba.excel.metadata.data.WriteCellData)1 ExcelContentProperty (com.alibaba.excel.metadata.property.ExcelContentProperty)1 BeanMapUtils (com.alibaba.excel.util.BeanMapUtils)1 ClassUtils (com.alibaba.excel.util.ClassUtils)1 FieldUtils (com.alibaba.excel.util.FieldUtils)1 ListUtils (com.alibaba.excel.util.ListUtils)1 MapUtils (com.alibaba.excel.util.MapUtils)1