Examples with FormulaData com.alibaba.excel.metadata.data.FormulaData used on opensource projects

Example 1 with FormulaData

use of com.alibaba.excel.metadata.data.FormulaData 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 FormulaData

use of com.alibaba.excel.metadata.data.FormulaData in project easyexcel by alibaba.

the class CellFormulaTagHandler method endElement.

@Override
public void endElement(XlsxReadContext xlsxReadContext, String name) {
    XlsxReadSheetHolder xlsxReadSheetHolder = xlsxReadContext.xlsxReadSheetHolder();
    FormulaData formulaData = new FormulaData();
    formulaData.setFormulaValue(xlsxReadSheetHolder.getTempFormula().toString());
    xlsxReadSheetHolder.getTempCellData().setFormulaData(formulaData);
}

Example 3 with FormulaData

use of com.alibaba.excel.metadata.data.FormulaData in project easyexcel by alibaba.

the class CsvCell method setCellFormulaImpl.

@Override
protected void setCellFormulaImpl(String formula) {
    FormulaData formulaData = new FormulaData();
    formulaData.setFormulaValue(formula);
    this.formulaData = formulaData;
    this.cellType = CellType.FORMULA;
}

Example 4 with FormulaData

use of com.alibaba.excel.metadata.data.FormulaData in project easyexcel by alibaba.

the class FormulaRecordHandler method processRecord.

@Override
public void processRecord(XlsReadContext xlsReadContext, Record record) {
    FormulaRecord frec = (FormulaRecord) record;
    Map<Integer, Cell> cellMap = xlsReadContext.xlsReadSheetHolder().getCellMap();
    ReadCellData<?> tempCellData = new ReadCellData<>();
    tempCellData.setRowIndex(frec.getRow());
    tempCellData.setColumnIndex((int) frec.getColumn());
    CellType cellType = CellType.forInt(frec.getCachedResultType());
    String formulaValue = null;
    try {
        formulaValue = HSSFFormulaParser.toFormulaString(xlsReadContext.xlsReadWorkbookHolder().getHssfWorkbook(), frec.getParsedExpression());
    } catch (Exception e) {
        log.debug("Get formula value error.", e);
    }
    FormulaData formulaData = new FormulaData();
    formulaData.setFormulaValue(formulaValue);
    tempCellData.setFormulaData(formulaData);
    xlsReadContext.xlsReadSheetHolder().setTempRowType(RowTypeEnum.DATA);
    switch(cellType) {
        case STRING:
            // Formula result is a string
            // This is stored in the next record
            tempCellData.setType(CellDataTypeEnum.STRING);
            xlsReadContext.xlsReadSheetHolder().setTempCellData(tempCellData);
            break;
        case NUMERIC:
            tempCellData.setType(CellDataTypeEnum.NUMBER);
            tempCellData.setNumberValue(BigDecimal.valueOf(frec.getValue()));
            int dataFormat = xlsReadContext.xlsReadWorkbookHolder().getFormatTrackingHSSFListener().getFormatIndex(frec);
            DataFormatData dataFormatData = new DataFormatData();
            dataFormatData.setIndex((short) dataFormat);
            dataFormatData.setFormat(BuiltinFormats.getBuiltinFormat(dataFormatData.getIndex(), xlsReadContext.xlsReadWorkbookHolder().getFormatTrackingHSSFListener().getFormatString(frec), xlsReadContext.readSheetHolder().getGlobalConfiguration().getLocale()));
            tempCellData.setDataFormatData(dataFormatData);
            cellMap.put((int) frec.getColumn(), tempCellData);
            break;
        case ERROR:
            tempCellData.setType(CellDataTypeEnum.ERROR);
            tempCellData.setStringValue(ERROR);
            cellMap.put((int) frec.getColumn(), tempCellData);
            break;
        case BOOLEAN:
            tempCellData.setType(CellDataTypeEnum.BOOLEAN);
            tempCellData.setBooleanValue(frec.getCachedBooleanValue());
            cellMap.put((int) frec.getColumn(), tempCellData);
            break;
        default:
            tempCellData.setType(CellDataTypeEnum.EMPTY);
            cellMap.put((int) frec.getColumn(), tempCellData);
            break;
    }
}

Example 5 with FormulaData

use of com.alibaba.excel.metadata.data.FormulaData in project easyexcel by alibaba.

the class CellDataDataTest method data.

private List<CellDataWriteData> data() throws Exception {
    List<CellDataWriteData> list = new ArrayList<>();
    CellDataWriteData cellDataData = new CellDataWriteData();
    cellDataData.setDate(new WriteCellData<>(DateUtils.parseDate("2020-01-01 01:01:01")));
    WriteCellData<Integer> integer1 = new WriteCellData<>();
    integer1.setType(CellDataTypeEnum.NUMBER);
    integer1.setNumberValue(BigDecimal.valueOf(2L));
    cellDataData.setInteger1(integer1);
    cellDataData.setInteger2(2);
    WriteCellData<?> formulaValue = new WriteCellData<>();
    FormulaData formulaData = new FormulaData();
    formulaValue.setFormulaData(formulaData);
    formulaData.setFormulaValue("B2+C2");
    cellDataData.setFormulaValue(formulaValue);
    list.add(cellDataData);
    return list;
}