Search in sources :

Example 21 with ExcelWriter

use of com.alibaba.excel.ExcelWriter 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 22 with ExcelWriter

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

the class RepetitionDataTest method readAndWrite.

private void readAndWrite(File file) {
    try (ExcelWriter excelWriter = EasyExcel.write(file, RepetitionData.class).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
        excelWriter.write(data(), writeSheet).write(data(), writeSheet);
    }
    try (ExcelReader excelReader = EasyExcel.read(file, RepetitionData.class, new RepetitionDataListener()).build()) {
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
    }
}
Also used : ExcelReader(com.alibaba.excel.ExcelReader) ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet)

Example 23 with ExcelWriter

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

the class RepetitionDataTest method readAndWriteTable.

private void readAndWriteTable(File file) {
    try (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);
    }
    try (ExcelReader excelReader = EasyExcel.read(file, RepetitionData.class, new RepetitionDataListener()).build()) {
        ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(2).build();
        excelReader.read(readSheet);
    }
}
Also used : ExcelReader(com.alibaba.excel.ExcelReader) ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) WriteTable(com.alibaba.excel.write.metadata.WriteTable) ReadSheet(com.alibaba.excel.read.metadata.ReadSheet)

Example 24 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project excel-spring-boot-starter by pig-mesh.

the class ManySheetWriteHandler method write.

@Override
public void write(Object obj, HttpServletResponse response, ResponseExcel responseExcel) {
    List<?> objList = (List<?>) obj;
    ExcelWriter excelWriter = getExcelWriter(response, responseExcel);
    Sheet[] sheets = responseExcel.sheets();
    WriteSheet sheet;
    for (int i = 0; i < sheets.length; i++) {
        List<?> eleList = (List<?>) objList.get(i);
        Class<?> dataClass = eleList.get(0).getClass();
        // 创建sheet
        sheet = this.sheet(sheets[i], dataClass, responseExcel.template(), responseExcel.headGenerator());
        // 写入sheet
        excelWriter.write(eleList, sheet);
    }
    excelWriter.finish();
}
Also used : ExcelWriter(com.alibaba.excel.ExcelWriter) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) List(java.util.List) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) Sheet(com.pig4cloud.plugin.excel.annotation.Sheet)

Example 25 with ExcelWriter

use of com.alibaba.excel.ExcelWriter in project charon by harvies.

the class ConverterTest method main.

public static void main(String[] args) {
    List<Head> list = new ArrayList<>();
    Head head = new Head();
    // head.setA("aaa");
    head.setB(Arrays.asList("ccccccccc", "dddddddd", "ccccccccc", "dddddddd", "ccccccccc", "dddddddd", "ccccccccc", "dddddddd"));
    head.setC(Arrays.asList(111111111, 1222222222, 1222222222, 1222222222, 1222222222, 1222222222, 1222222222));
    head.setD(true);
    list.add(head);
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // 背景设置为红色
    // headWriteCellStyle.setFillForegroundColor(IndexedColors.ORANGE.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);
    // 单元格不溢出
    // contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.FILL);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    ExcelWriter excelWriter = EasyExcelFactory.write(FileUtils.getCurrentUserHomePath() + "/Downloads/test_write.xlsx").head(Head.class).registerWriteHandler(horizontalCellStyleStrategy).build();
    WriteSheet writeSheet = new WriteSheet();
    writeSheet.setSheetNo(0);
    writeSheet.setSheetName("工作表1");
    excelWriter.write(list, writeSheet);
    excelWriter.finish();
}
Also used : WriteCellStyle(com.alibaba.excel.write.metadata.style.WriteCellStyle) ExcelWriter(com.alibaba.excel.ExcelWriter) ArrayList(java.util.ArrayList) WriteSheet(com.alibaba.excel.write.metadata.WriteSheet) HorizontalCellStyleStrategy(com.alibaba.excel.write.style.HorizontalCellStyleStrategy)

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