Examples with WriteCellStyle com.alibaba.excel.write.metadata.style.WriteCellStyle used on opensource projects

Example 1 with WriteCellStyle

use of com.alibaba.excel.write.metadata.style.WriteCellStyle in project easyexcel by alibaba.

the class WriteTest method writeCellDataWrite.

/**
 * 超链接、备注、公式、指定单个单元格的样式、单个单元格多种样式
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link WriteCellDemoData}
 * <p>
 * 2. 直接写即可
 *
 * @since 3.0.0-beta1
 */
@Test
public void writeCellDataWrite() {
    String fileName = TestFileUtil.getPath() + "writeCellDataWrite" + System.currentTimeMillis() + ".xlsx";
    WriteCellDemoData writeCellDemoData = new WriteCellDemoData();
    // 设置超链接
    WriteCellData<String> hyperlink = new WriteCellData<>("官方网站");
    writeCellDemoData.setHyperlink(hyperlink);
    HyperlinkData hyperlinkData = new HyperlinkData();
    hyperlink.setHyperlinkData(hyperlinkData);
    hyperlinkData.setAddress("https://github.com/alibaba/easyexcel");
    hyperlinkData.setHyperlinkType(HyperlinkType.URL);
    // 设置备注
    WriteCellData<String> comment = new WriteCellData<>("备注的单元格信息");
    writeCellDemoData.setCommentData(comment);
    CommentData commentData = new CommentData();
    comment.setCommentData(commentData);
    commentData.setAuthor("Jiaju Zhuang");
    commentData.setRichTextStringData(new RichTextStringData("这是一个备注"));
    // 备注的默认大小是按照单元格的大小 这里想调整到4个单元格那么大 所以向后 向下 各额外占用了一个单元格
    commentData.setRelativeLastColumnIndex(1);
    commentData.setRelativeLastRowIndex(1);
    // 设置公式
    WriteCellData<String> formula = new WriteCellData<>();
    writeCellDemoData.setFormulaData(formula);
    FormulaData formulaData = new FormulaData();
    formula.setFormulaData(formulaData);
    // 将 123456789 中的第一个数字替换成 2
    // 这里只是例子 如果真的涉及到公式 能内存算好尽量内存算好 公式能不用尽量不用
    formulaData.setFormulaValue("REPLACE(123456789,1,1,2)");
    // 设置单个单元格的样式 当然样式 很多的话 也可以用注解等方式。
    WriteCellData<String> writeCellStyle = new WriteCellData<>("单元格样式");
    writeCellStyle.setType(CellDataTypeEnum.STRING);
    writeCellDemoData.setWriteCellStyle(writeCellStyle);
    WriteCellStyle writeCellStyleData = new WriteCellStyle();
    writeCellStyle.setWriteCellStyle(writeCellStyleData);
    // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.
    writeCellStyleData.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    // 背景绿色
    writeCellStyleData.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    // 设置单个单元格多种样式
    WriteCellData<String> richTest = new WriteCellData<>();
    richTest.setType(CellDataTypeEnum.RICH_TEXT_STRING);
    writeCellDemoData.setRichText(richTest);
    RichTextStringData richTextStringData = new RichTextStringData();
    richTest.setRichTextStringDataValue(richTextStringData);
    richTextStringData.setTextString("红色绿色默认");
    // 前2个字红色
    WriteFont writeFont = new WriteFont();
    writeFont.setColor(IndexedColors.RED.getIndex());
    richTextStringData.applyFont(0, 2, writeFont);
    // 接下来2个字绿色
    writeFont = new WriteFont();
    writeFont.setColor(IndexedColors.GREEN.getIndex());
    richTextStringData.applyFont(2, 4, writeFont);
    List<WriteCellDemoData> data = new ArrayList<>();
    data.add(writeCellDemoData);
    EasyExcel.write(fileName, WriteCellDemoData.class).inMemory(true).sheet("模板").doWrite(data);
}

Example 2 with WriteCellStyle

use of com.alibaba.excel.write.metadata.style.WriteCellStyle in project easyexcel by alibaba.

the class WriteV34Test method test.

@Test
public void test() throws Exception {
    String fileName = TestFileUtil.getPath() + "handlerStyleWrite" + 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);
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(fileName, DemoData.class).head(head()).registerWriteHandler(horizontalCellStyleStrategy).sheet("模板").doWrite(data(1));
}

Example 3 with WriteCellStyle

use of com.alibaba.excel.write.metadata.style.WriteCellStyle in project easyexcel by alibaba.

the class StyleDataTest method t03AbstractVerticalCellStyleStrategy.

@Test
public void t03AbstractVerticalCellStyleStrategy() {
    AbstractVerticalCellStyleStrategy verticalCellStyleStrategy = new AbstractVerticalCellStyleStrategy() {

        @Override
        protected WriteCellStyle headCellStyle(Head head) {
            WriteCellStyle writeCellStyle = new WriteCellStyle();
            writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
            DataFormatData dataFormatData = new DataFormatData();
            dataFormatData.setIndex((short) 0);
            writeCellStyle.setDataFormatData(dataFormatData);
            writeCellStyle.setHidden(false);
            writeCellStyle.setLocked(true);
            writeCellStyle.setQuotePrefix(true);
            writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            writeCellStyle.setWrapped(true);
            writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            writeCellStyle.setRotation((short) 0);
            writeCellStyle.setIndent((short) 10);
            writeCellStyle.setBorderLeft(BorderStyle.THIN);
            writeCellStyle.setBorderRight(BorderStyle.THIN);
            writeCellStyle.setBorderTop(BorderStyle.THIN);
            writeCellStyle.setBorderBottom(BorderStyle.THIN);
            writeCellStyle.setLeftBorderColor(IndexedColors.RED.getIndex());
            writeCellStyle.setRightBorderColor(IndexedColors.RED.getIndex());
            writeCellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
            writeCellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());
            writeCellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
            writeCellStyle.setShrinkToFit(Boolean.TRUE);
            if (head.getColumnIndex() == 0) {
                writeCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                WriteFont writeFont = new WriteFont();
                writeFont.setItalic(true);
                writeFont.setStrikeout(true);
                writeFont.setTypeOffset(Font.SS_NONE);
                writeFont.setUnderline(Font.U_DOUBLE);
                writeFont.setBold(true);
                writeFont.setCharset((int) Font.DEFAULT_CHARSET);
            } else {
                writeCellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
            }
            return writeCellStyle;
        }

        @Override
        protected WriteCellStyle contentCellStyle(Head head) {
            WriteCellStyle writeCellStyle = new WriteCellStyle();
            writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
            if (head.getColumnIndex() == 0) {
                writeCellStyle.setFillForegroundColor(IndexedColors.DARK_GREEN.getIndex());
            } else {
                writeCellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
            }
            return writeCellStyle;
        }
    };
    EasyExcel.write(fileVerticalCellStyleStrategy07, StyleData.class).registerWriteHandler(verticalCellStyleStrategy).sheet().doWrite(data());
}

Example 4 with WriteCellStyle

use of com.alibaba.excel.write.metadata.style.WriteCellStyle in project easyexcel by alibaba.

the class WriteWorkbookHolder method createCellStyle.

/**
 * create a cell style.
 *
 * @param writeCellStyle
 * @param originCellStyle
 * @return
 */
public CellStyle createCellStyle(WriteCellStyle writeCellStyle, CellStyle originCellStyle) {
    if (writeCellStyle == null) {
        return originCellStyle;
    }
    short styleIndex = -1;
    Font originFont = null;
    boolean useCache = true;
    if (originCellStyle != null) {
        styleIndex = originCellStyle.getIndex();
        if (originCellStyle instanceof XSSFCellStyle) {
            originFont = ((XSSFCellStyle) originCellStyle).getFont();
        } else if (originCellStyle instanceof HSSFCellStyle) {
            originFont = ((HSSFCellStyle) originCellStyle).getFont(workbook);
        }
        useCache = false;
    }
    Map<WriteCellStyle, CellStyle> cellStyleMap = cellStyleIndexMap.computeIfAbsent(styleIndex, key -> MapUtils.newHashMap());
    CellStyle cellStyle = cellStyleMap.get(writeCellStyle);
    if (cellStyle != null) {
        return cellStyle;
    }
    if (log.isDebugEnabled()) {
        log.info("create new style:{},{}", writeCellStyle, originCellStyle);
    }
    WriteCellStyle tempWriteCellStyle = new WriteCellStyle();
    WriteCellStyle.merge(writeCellStyle, tempWriteCellStyle);
    cellStyle = StyleUtil.buildCellStyle(workbook, originCellStyle, tempWriteCellStyle);
    Short dataFormat = createDataFormat(tempWriteCellStyle.getDataFormatData(), useCache);
    if (dataFormat != null) {
        cellStyle.setDataFormat(dataFormat);
    }
    Font font = createFont(tempWriteCellStyle.getWriteFont(), originFont, useCache);
    if (font != null) {
        cellStyle.setFont(font);
    }
    cellStyleMap.put(tempWriteCellStyle, cellStyle);
    return cellStyle;
}

Example 5 with WriteCellStyle

use of com.alibaba.excel.write.metadata.style.WriteCellStyle in project easyexcel by alibaba.

the class StyleUtil method buildCellStyle.

/**
 * Build  cell style
 *
 * @param workbook
 * @param originCellStyle
 * @param writeCellStyle
 * @return
 */
public static CellStyle buildCellStyle(Workbook workbook, CellStyle originCellStyle, WriteCellStyle writeCellStyle) {
    CellStyle cellStyle = workbook.createCellStyle();
    if (originCellStyle != null) {
        cellStyle.cloneStyleFrom(originCellStyle);
    }
    if (writeCellStyle == null) {
        return cellStyle;
    }
    buildCellStyle(cellStyle, writeCellStyle);
    return cellStyle;
}