Search in sources :

Example 56 with ExcelWriter

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

the class WriteLargeTest method test.

@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();
}
Also used : WriteCellStyle(com.alibaba.excel.write.metadata.style.WriteCellStyle) WriteFont(com.alibaba.excel.write.metadata.style.WriteFont) ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) HorizontalCellStyleStrategy(com.alibaba.excel.write.style.HorizontalCellStyleStrategy) Test(org.junit.Test)

Example 57 with ExcelWriter

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

the class Wirte method tableWrite.

@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);
    // 第二次写如也会创建头,然后在第一次的后面写入数据
    // / 千万别忘记close 会帮忙关闭流
    excelWriter.finish();
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) WriteTable(com.alibaba.excel.write.metadata.WriteTable) Test(org.junit.Test)

Example 58 with ExcelWriter

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

the class FillDataTest method compositeFill.

private void compositeFill(File file, File template) {
    try (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);
    }
    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));
}
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) HashMap(java.util.HashMap) Map(java.util.Map) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig)

Example 59 with ExcelWriter

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

the class FillDataTest method complexFill.

private void complexFill(File file, File template) {
    try (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);
    }
    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));
}
Also used : LoopMergeStrategy(com.alibaba.excel.write.merge.LoopMergeStrategy) HashMap(java.util.HashMap) ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) HashMap(java.util.HashMap) Map(java.util.Map) FillConfig(com.alibaba.excel.write.metadata.fill.FillConfig)

Example 60 with ExcelWriter

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

the class FillTempTest method complexFill.

/**
 * 复杂的填充
 *
 * @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();
}
Also used : OnceAbsoluteMergeStrategy(com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy) HashMap(java.util.HashMap) ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) 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