Search in sources :

Example 11 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project easyexcel by alibaba.

the class FillTempTest method listFill.

/**
 * 填充列表
 *
 * @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();
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) Test(org.junit.Test)

Example 12 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project easyexcel by alibaba.

the class FillTempTest 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";
    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);
    // 别忘记关闭流
    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) Date(java.util.Date) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig) Test(org.junit.Test)

Example 13 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project easyexcel by alibaba.

the class TempLargeDataTest method t04WriteExcelNo.

@Test
public void t04WriteExcelNo() throws Exception {
    IntStream.rangeClosed(0, 10000).forEach(index -> {
        ExcelWriter excelWriter = EasyExcel.write(fileWriteTemp07, com.alibaba.easyexcel.test.core.large.LargeData.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        for (int j = 0; j < 50; j++) {
            excelWriter.write(data(), writeSheet);
        }
        excelWriter.finish();
        LOGGER.info("{} 完成", index);
    });
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) LargeDataTest(com.alibaba.easyexcel.test.core.large.LargeDataTest) Test(org.junit.Test)

Example 14 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project easyexcel by alibaba.

the class TempLargeDataTest method t04WriteExcel.

@Test
public void t04WriteExcel() throws Exception {
    IntStream.rangeClosed(0, 100).forEach(index -> {
        ExcelWriter excelWriter = EasyExcel.write(fileWriteTemp07, com.alibaba.easyexcel.test.core.large.LargeData.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        for (int j = 0; j < 5000; j++) {
            excelWriter.write(data(), writeSheet);
        }
        excelWriter.finish();
        LOGGER.info("{} 完成", index);
    });
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) LargeDataTest(com.alibaba.easyexcel.test.core.large.LargeDataTest) Test(org.junit.Test)

Example 15 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project easyexcel by alibaba.

the class TempLargeDataTest method t04Write.

@Test
public void t04Write() throws Exception {
    ExcelWriter excelWriter = EasyExcel.write(fileWriteTemp07, com.alibaba.easyexcel.test.core.large.LargeData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    for (int j = 0; j < 2; j++) {
        excelWriter.write(data(), writeSheet);
    }
    excelWriter.finish();
    long start = System.currentTimeMillis();
    excelWriter = EasyExcel.write(fileWrite07, com.alibaba.easyexcel.test.core.large.LargeData.class).build();
    writeSheet = EasyExcel.writerSheet().build();
    for (int j = 0; j < 5000; j++) {
        excelWriter.write(data(), writeSheet);
        LOGGER.info("{} write success.", j);
    }
    excelWriter.finish();
    long cost = System.currentTimeMillis() - start;
    LOGGER.info("write cost:{}", cost);
    start = System.currentTimeMillis();
    try (FileOutputStream fileOutputStream = new FileOutputStream(fileWritePoi07)) {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("sheet1");
        for (int i = 0; i < 100 * 5000; i++) {
            SXSSFRow row = sheet.createRow(i);
            for (int j = 0; j < 25; j++) {
                SXSSFCell cell = row.createCell(j);
                cell.setCellValue("str-" + j + "-" + i);
            }
            if (i % 5000 == 0) {
                LOGGER.info("{} write success.", i);
            }
        }
        workbook.write(fileOutputStream);
        workbook.dispose();
        workbook.close();
    }
    long costPoi = System.currentTimeMillis() - start;
    LOGGER.info("poi write cost:{}", System.currentTimeMillis() - start);
    LOGGER.info("{} vs {}", cost, costPoi);
    Assert.assertTrue(costPoi * 2 > cost);
}
Also used : SXSSFRow(org.apache.poi.xssf.streaming.SXSSFRow) ExcelWriter(com.alibaba.excel.ExcelWriter) SXSSFSheet(org.apache.poi.xssf.streaming.SXSSFSheet) FileOutputStream(java.io.FileOutputStream) SXSSFWorkbook(org.apache.poi.xssf.streaming.SXSSFWorkbook) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) SXSSFCell(org.apache.poi.xssf.streaming.SXSSFCell) LargeDataTest(com.alibaba.easyexcel.test.core.large.LargeDataTest) Test(org.junit.Test)

Aggregations

ExcelWriter (com.alibaba.excel.ExcelWriter)67 WriteSheet (com.alibaba.excel.write.metadata.WriteSheet)52 Test (org.junit.Test)31 Sheet (com.alibaba.excel.metadata.Sheet)13 HashMap (java.util.HashMap)13 FillConfig (com.alibaba.excel.write.metadata.fill.FillConfig)11 List (java.util.List)9 FileOutputStream (java.io.FileOutputStream)7 IOException (java.io.IOException)7 WriteTable (com.alibaba.excel.write.metadata.WriteTable)6 ArrayList (java.util.ArrayList)6 ExcelReader (com.alibaba.excel.ExcelReader)5 ReadSheet (com.alibaba.excel.read.metadata.ReadSheet)5 FillWrapper (com.alibaba.excel.write.metadata.fill.FillWrapper)4 UnExpectedRequestException (com.webank.wedatasphere.qualitis.exception.UnExpectedRequestException)4 OutputStream (java.io.OutputStream)4 Map (java.util.Map)4 LargeDataTest (com.alibaba.easyexcel.test.core.large.LargeDataTest)3 PermissionDeniedRequestException (com.webank.wedatasphere.qualitis.exception.PermissionDeniedRequestException)3 WriteExcelException (com.webank.wedatasphere.qualitis.rule.exception.WriteExcelException)3