Example usage for org.apache.poi.ss.usermodel Cell getCellFormula

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getCellFormula.

Prototype

String getCellFormula();

Source Link

Document

Return a formula for the cell, for example, SUM(C4:E4)

Usage

From source file:com.github.jferard.spreadsheetwrapper.xls.poi.XlsPoiWriter.java

License:Open Source License

/** {@inheritDoc} */
@Override/*from   w  ww  .  j a  v  a  2  s.c  om*/
public/*@Nullable*/Object getCellContent(final int rowIndex, final int colIndex) {
    final Cell cell = this.getPOICell(rowIndex, colIndex);
    if (cell == null)
        return null;

    Object result;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        result = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = Boolean.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        result = "#Err";
        break;
    case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (this.isDate(cell)) {
            result = cell.getDateCellValue();
        } else {
            final double value = cell.getNumericCellValue();
            if (value == Math.rint(value))
                result = Integer.valueOf((int) value);
            else
                result = value;
        }
        break;
    case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
    default:
        throw new IllegalArgumentException(String.format("Unknown type of cell %d", cell.getCellType()));
    }
    return result;
}

---

From source file:com.github.jferard.spreadsheetwrapper.xls.poi.XlsPoiWriter.java

License:Open Source License

/** {@inheritDoc} */
@Override/*  w ww.j a v  a2 s  . c o m*/
public/*@Nullable*/String getFormula(final int r, final int c) {
    final Cell cell = this.getPOICell(r, c);
    if (XlsPoiWriter.nullCellOrInvalidType(cell, Cell.CELL_TYPE_FORMULA))
        return null;

    return cell.getCellFormula();
}

---

From source file:com.helger.poi.excel.ExcelReadUtils.java

License:Apache License

@Nullable
public static String getCellFormula(@Nullable final Cell aCell) {
    if (aCell != null)
        try {/*from  ww  w  .  j  ava 2s  .  c  om*/
            return aCell.getCellFormula();
        } catch (final RuntimeException ex) {
            // fall through
            s_aLogger.warn("Failed to get cell formula: " + ex.getMessage());
        }
    return null;
}

---

From source file:com.helger.poi.excel.ExcelReadUtilsTest.java

License:Apache License

/**
 * Validate reference sheets//  w w w .j  a v  a  2 s  . co  m
 * 
 * @param aWB
 *        Workbook to use
 */
private void _validateWorkbook(@Nonnull final Workbook aWB) {
    final Sheet aSheet1 = aWB.getSheet("Sheet1");
    assertNotNull(aSheet1);
    assertNotNull(aWB.getSheet("Sheet2"));
    final Sheet aSheet3 = aWB.getSheet("Sheet3");
    assertNotNull(aSheet3);
    assertNull(aWB.getSheet("Sheet4"));

    Cell aCell = aSheet1.getRow(0).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("A1", aCell.getStringCellValue());

    aCell = aSheet1.getRow(1).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("B2", aCell.getStringCellValue());

    aCell = aSheet1.getRow(2).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("C\n3", aCell.getStringCellValue());

    aCell = aSheet1.getRow(3).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
    assertEquals(0.00001, 4.4, aCell.getNumericCellValue());

    for (int i = 0; i < 6; ++i) {
        aCell = aSheet3.getRow(i).getCell(i);
        assertNotNull(aCell);
        assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
        assertEquals(0.00001, i + 1, aCell.getNumericCellValue());
    }

    // ="abc"
    aCell = aSheet1.getRow(4).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("\"abc\"", aCell.getCellFormula());
    assertEquals("abc", aCell.getStringCellValue());
    CellValue aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE)
            .evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aEvaluated.getCellType());
    assertEquals("abc", aEvaluated.getStringValue());

    // =4711
    aCell = aSheet1.getRow(5).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("4711", aCell.getCellFormula());
    assertEquals(0.00001, 4711, aCell.getNumericCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aEvaluated.getCellType());
    assertEquals(0.00001, 4711, aEvaluated.getNumberValue());

    // =TRUE
    aCell = aSheet1.getRow(6).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("TRUE", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());

    // Refers to cell at 6/2
    aCell = aSheet1.getRow(7).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("C7", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());
}

---

From source file:com.hurence.logisland.processor.excel.ExcelExtract.java

License:Apache License

/**
 * Handle row content and transform it into a {@link Record}
 *
 * @param row the {@link Row}/*from   ww w .  j av  a 2  s.  c  o m*/
 * @return the transformed {@link Record}
 */
private Record handleRow(Row row, List<String> header) {
    Record ret = new StandardRecord().setTime(new Date());
    int index = 0;
    for (Cell cell : row) {
        if (configuration.getFieldNames() != null && index >= configuration.getFieldNames().size()) {
            //we've reached the end of mapping. Go to next row.
            break;
        }
        if (configuration.getColumnsToSkip().contains(cell.getColumnIndex())) {
            //skip this cell.
            continue;
        }
        String fieldName = header != null ? header.get(cell.getColumnIndex())
                : configuration.getFieldNames().get(index++);
        Field field;
        // Alternatively, get the value and format it yourself
        switch (cell.getCellTypeEnum()) {
        case STRING:
            field = new Field(fieldName, FieldType.STRING, cell.getStringCellValue());
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                field = new Field(fieldName, FieldType.LONG, cell.getDateCellValue().getTime());
            } else {
                field = new Field(fieldName, FieldType.DOUBLE, cell.getNumericCellValue());
            }
            break;
        case BOOLEAN:
            field = new Field(fieldName, FieldType.BOOLEAN, cell.getBooleanCellValue());
            break;
        case FORMULA:
            field = new Field(fieldName, FieldType.STRING, cell.getCellFormula());
            break;
        default:
            //blank or unknown
            field = new Field(fieldName, FieldType.NULL, null);
            break;
        }
        ret.setField(field);
    }
    return ret;
}

---

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

/**
 * Insert a row at a given index./*w w  w .  j a  va  2 s. c om*/
 *
 * @param createAtIndex row-number of the cell at which to create a new row
 * @param sourceRow     the row to insert
 */
public void insertRowAt(int createAtIndex, Row sourceRow) {
    Row newRow = getRow(createAtIndex);
    if (newRow != null) {
        // shift all rows >= createAtIndex up by one
        getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1);
    } else {
        newRow = getSheet().createRow(createAtIndex);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }

    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < getSheet().getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            getSheet().addMergedRegion(newCellRangeAddress);
        }
    }
}

---

From source file:com.mimp.controllers.reporte.java

private static void copyRow(Sheet worksheet, int sourceRowNum, int destinationRowNum) {
        // Coge la fila antigua y nueva
        Row newRow = worksheet.getRow(destinationRowNum);
        Row sourceRow = worksheet.getRow(sourceRowNum);

        //Si existe una fila en el detino, pasa todas las filas 1 ms abajo antes de crear la nueva columna
        if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
        } else {/*from www  .ja  v  a2 s. co  m*/
            newRow = worksheet.createRow(destinationRowNum);
        }

        // Hace un loop entre las celdas de cada columna para aadir una por una a la nueva
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Copia la antigua y nueva celda
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // Si la anterior celda es null, evalua la siguiente celda defrente
            if (oldCell == null) {
                newCell = null;
                continue;
            }

            // Usa el estilo de la celda antigua
            newCell.setCellStyle(oldCell.getCellStyle());

            // Establece el tipo de valor de la celda
            newCell.setCellType(oldCell.getCellType());

            // Establece el valor de la celda
            switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;
            }
        }
    }

---

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public String getCellFormula(Cell c) {
    return c.getCellFormula();
}

---

From source file:com.ncc.excel.test.ExcelUtil.java

License:Apache License

/*** 
 * ?? //from  w  ww .j a v  a  2  s.  c  o m
 *  
 * @Title: getCellValue 
 * @Date : 2014-9-11 ?10:52:07 
 * @param cell 
 * @return 
 */
private String getCellValue(Cell cell) {
    Object result = "";
    if (cell != null) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            result = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            result = cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            result = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            result = cell.getCellFormula();
            break;
        case Cell.CELL_TYPE_ERROR:
            result = cell.getErrorCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        default:
            break;
        }
    }
    return result.toString();
}

---

From source file:com.opendoorlogistics.core.tables.io.PoiIO.java

License:Open Source License

private static String getTextValue(Cell cell, int treatAsCellType) {
    if (cell == null) {
        return null;
    }//from  ww  w.  j av a2  s .com
    switch (treatAsCellType) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            if (date != null) {
                Calendar cal = Calendar.getInstance();
                cal.setTime(date);
                @SuppressWarnings("deprecation")
                int year = date.getYear();
                if (year == -1) {
                    // equivalent to 1899 which is the first data .. assume its a time
                    String s = ODL_TIME_FORMATTER.format(date);
                    return s;
                }
                //   System.out.println(year);
            }
            return cell.getDateCellValue().toString();
        } else {
            String ret = Double.toString(cell.getNumericCellValue());
            if (ret.endsWith(".0")) {
                ret = ret.substring(0, ret.length() - 2);
            }

            return ret;
        }

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? "T" : "F";

    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();

    case Cell.CELL_TYPE_BLANK:
        return null;
    }
    return "";
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• »