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:org.tiefaces.components.websheet.configuration.FormCommand.java

License:MIT License

/**
 * Builds the watch list for cell.//from   w w  w .ja  v  a2s .  c om
 *
 * @param wbWrapper
 *            the wb wrapper
 * @param sheetIndex
 *            the sheet index
 * @param cell
 *            the cell
 * @param watchList
 *            the watch list
 * @param lastStaticRow
 *            the last static row
 */
private void buildWatchListForCell(final XSSFEvaluationWorkbook wbWrapper, final int sheetIndex,
        final Cell cell, final List<Integer> watchList, final int lastStaticRow) {
    String formula = cell.getCellFormula();

    Ptg[] ptgs = FormulaParser.parse(formula, wbWrapper, FormulaType.CELL, sheetIndex);

    for (int k = 0; k < ptgs.length; k++) {
        Object ptg = ptgs[k];
        // For area formula, only first row is watched.
        // Reason is the lastRow must shift same rows with
        // firstRow.
        // Otherwise it's difficult to calculate.
        // In case some situation cannot fit, then should make
        // change to the formula.
        int areaInt = ShiftFormulaUtility.getFirstSupportedRowNumFromPtg(ptg);
        if (areaInt >= 0) {
            addToWatchList(areaInt, lastStaticRow, watchList);
        }
    }

    // when insert row, the formula may changed. so here is the
    // workaround.
    // change formula to user formula to preserve the row
    // changes.
    cell.setCellType(CellType.STRING);
    cell.setCellValue(TieConstants.USER_FORMULA_PREFIX + formula + TieConstants.USER_FORMULA_SUFFIX);
}

---

From source file:org.ujmp.poi.AbstractMatrixExcelImporter.java

License:Open Source License

public DenseObjectMatrix2D importFromSheet(final Sheet sheet) throws InvalidFormatException, IOException {
    final int rowCount = sheet.getLastRowNum();
    int columnCount = 0;

    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();/* w  ww . j  ava2 s  .c om*/
        if (row.getLastCellNum() > columnCount) {
            columnCount = row.getLastCellNum();
        }
    }

    final DefaultDenseObjectMatrix2D matrix = new DefaultDenseObjectMatrix2D(rowCount, columnCount);
    matrix.setLabel(sheet.getSheetName());

    for (int r = 0; r < rowCount; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (int c = 0; c < columnCount; c++) {
                Cell cell = row.getCell(c);
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        matrix.setAsBoolean(cell.getBooleanCellValue(), r, c);
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        matrix.setAsString(cell.getCellFormula(), r, c);
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        matrix.setAsDouble(cell.getNumericCellValue(), r, c);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        matrix.setAsString(cell.getStringCellValue(), r, c);
                        break;
                    default:
                        break;
                    }

                }
            }
        }
    }

    return matrix;
}

---

From source file:org.unhcr.eg.odk.utilities.xlsform.controller.SheetProcessor.java

public static Object getCelValue(Cell cell) {
    Object cellValue = null;//from  w w  w  .  j  av  a2 s . c o m
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        cellValue = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            cellValue = cell.getDateCellValue();
        } else {
            cellValue = cell.getNumericCellValue();
        }
        break;
    case Cell.CELL_TYPE_STRING:
        cellValue = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_BLANK:
        cellValue = new String("");
        break;
    case Cell.CELL_TYPE_ERROR:
        cellValue = cell.getErrorCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        cellValue = cell.getCellFormula();
        break;
    }
    return cellValue;
}

---

From source file:org.unhcr.eg.odk.utilities.xlsform.controller.SheetProcessor.java

public static Object getWithIntCelValue(Cell cell) {
    Object cellValue = null;//  w w  w. j  a v  a2  s .  com
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        cellValue = cell.getBooleanCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            cellValue = cell.getDateCellValue();
        } else {
            cellValue = cell.getNumericCellValue();
            Double i = (Double) cellValue;
            cellValue = i.intValue();
        }
        break;
    case Cell.CELL_TYPE_STRING:
        cellValue = cell.getStringCellValue();
        break;
    case Cell.CELL_TYPE_BLANK:
        cellValue = new String("");
        break;
    case Cell.CELL_TYPE_ERROR:
        cellValue = cell.getErrorCellValue();
        break;
    case Cell.CELL_TYPE_FORMULA:
        cellValue = cell.getCellFormula();
        break;
    }
    return cellValue;
}

---

From source file:org.unhcr.eg.odk.utilities.xlsform.excel.ExcelFileUtility.java

protected static void copyContent(Sheet sheetSource, Sheet sheetDestination) {
    //Iterate through each rows from first sheet
    Iterator<Row> rowIterator = sheetSource.iterator();
    int i = 0;/*from   ww  w.  j a v a 2 s .  c  om*/
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Row rowDestination = sheetDestination.createRow(i);
        i++;
        //For each row, iterate through each columns
        Iterator<Cell> cellIterator = row.cellIterator();
        int j = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            Cell cellDestination = rowDestination.createCell(j);
            j++;
            cellDestination.setCellComment(cell.getCellComment());
            //                cellDestination.setCellStyle(cell.getCellStyle());
            cellDestination.setCellType(cell.getCellType());
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                cellDestination.setCellValue(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellDestination.setCellValue(cell.getDateCellValue());
                } else {
                    cellDestination.setCellValue(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellDestination.setCellValue(cell.getRichStringCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                cellDestination.setCellValue(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                cellDestination.setCellValue(cell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                cellDestination.setCellFormula(cell.getCellFormula());
                break;
            }

        }

    }

}

---

From source file:org.wandora.application.tools.extractors.excel.AbstractExcelExtractor.java

License:Open Source License

public void associateToFormula(Cell cell, TopicMap tm) throws TopicMapException {
    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        if (cell.getCellFormula() != null) {
            Topic formulaTypeTopic = getFormulaTypeTopic(tm);
            Topic formulaTopic = getFormulaTopic(cell, tm);
            Topic cellTypeTopic = getCellTypeTopic(tm);
            Topic cellTopic = getCellTopic(cell, tm);

            if (formulaTypeTopic != null && formulaTopic != null && cellTypeTopic != null
                    && cellTopic != null) {
                Association a = tm.createAssociation(formulaTypeTopic);
                a.addPlayer(cellTopic, cellTypeTopic);
                a.addPlayer(formulaTopic, formulaTypeTopic);
            }/*from   w  ww.jav  a  2  s .  co m*/
        }
    }
}

---

From source file:org.wandora.application.tools.extractors.excel.AbstractExcelExtractor.java

License:Open Source License

public Topic getFormulaTopic(Cell cell, TopicMap tm) throws TopicMapException {
    String formula = cell.getCellFormula();
    if (formula != null) {
        Topic topic = getOrCreateTopic(tm, EXCEL_FORMULA_SI_PREFIX + "/" + urlEncode(formula), formula);
        topic.setData(getFormulaTypeTopic(tm), tm.getTopic(XTMPSI.getLang(DEFAULT_LANG)), formula);
        topic.addType(getFormulaTypeTopic(tm));
        return topic;
    }/*  www.  j  a va2 s .  c  om*/
    return null;
}

---

From source file:ro.dabuno.office.integration.Xlsx2Word.java

public static void main(String[] args) throws Exception {
    log.info("starting app");
    //        Workbook wb = new XSSFWorkbook(new FileInputStream(args[0]));
    Workbook wb = new XSSFWorkbook(new FileInputStream("office-files/Input.xlsx"));

    DataFormatter formatter = new DataFormatter();

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);//  www.  j  av a 2s . c o m
        System.out.println(wb.getSheetName(i));
        int j = 4;
        for (Row row : sheet) {
            System.out.println("rownum: " + row.getRowNum());
            for (Cell cell : row) {
                CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                System.out.print(cellRef.formatAsString());
                System.out.print(" - ");
                // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
                String text = formatter.formatCellValue(cell);
                System.out.println(text);

                System.out.println("------------");
                // Alternatively, get the value and format it yourself
                switch (cell.getCellTypeEnum()) {
                case STRING:
                    System.out.println(cell.getRichStringCellValue().getString());
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.println(cell.getDateCellValue());
                    } else {
                        System.out.print(cellRef.formatAsString());
                        System.out.print(" - ");
                        System.out.println((long) cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    System.out.println(cell.getCellFormula());
                    break;
                case BLANK:
                    System.out.println();
                    break;
                default:
                    System.out.println();
                }

            }
            j--;
            if (j == 0) {
                break;
            }
        }
    }

    XWPFDocument doc = new XWPFDocument();

    XWPFParagraph p0 = doc.createParagraph();
    XWPFRun r0 = p0.createRun();
    r0.setBold(false);
    r0.setText("Domnule");
    XWPFRun r00 = p0.createRun();
    r00.setBold(true);
    r00.setText(" Ionescu Ion");

    FileOutputStream out = new FileOutputStream("out/xlsx2word.docx");
    doc.write(out);
    out.close();
}

---

From source file:Servelt.ExcelReader.java

private String cellToString(Cell cell) throws Exception {
    String data = null;/*from  ww  w  . j  av a 2  s  . c om*/
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        data = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            data = cell.getDateCellValue().toString();
        } else {
            data = String.valueOf(cell.getNumericCellValue());
            while (data.endsWith("0") && data.contains(".")) {
                data = data.substring(0, data.length() - 1);
            }
            if (data.endsWith("."))
                data = data.substring(0, data.length() - 1);
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        data = String.valueOf(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        data = cell.getCellFormula();
        break;
    case Cell.CELL_TYPE_BLANK:
        data = "";
        break;
    case Cell.CELL_TYPE_ERROR:
        throw new Exception("CELL_TYPE_ERROR");
    }
    return data;
}

---

From source file:step.datapool.excel.ExcelFunctions.java

License:Open Source License

/**
* Konvertiert unterschiedliche Formate in Strings.
* 
* @param cell Excel Zelle//from w  w  w  . j  a  va2  s  . c  o m
* @param evaluator FormulaEvaluator
* @return Wert der Zelle als String
*/
public static String getCellValueAsString(Cell cell, FormulaEvaluator evaluator) {

    boolean isFormulaPatched = false;
    String initialFormula = null;

    int chkTyp = cell.getCellType();
    if (chkTyp == Cell.CELL_TYPE_FORMULA) {

        initialFormula = cell.getCellFormula();
        // Some formula have to be changed before they can be evaluated in POI
        String formula = FormulaPatch.patch(initialFormula);
        if (!formula.equals(initialFormula)) {
            isFormulaPatched = true;
            cell.setCellFormula(formula);
            evaluator.notifySetFormula(cell);
        }
    }

    try {
        int typ = evaluateFormulaCell(cell, evaluator);
        if (typ == -1)
            typ = cell.getCellType();
        switch (typ) {
        case Cell.CELL_TYPE_NUMERIC:
            /* Datum und Zeit (sind auch Zahlen) */
            if (DateUtil.isCellDateFormatted(cell)) {
                Date dat = cell.getDateCellValue();
                GregorianCalendar cal = new GregorianCalendar();
                cal.setTime(dat);
                /*
                 * In Excel beginnt die Zeitrechnung am 01.01.1900. Ein Datum ist immer als
                 * double gespeichert. Dabei ist der Teil vor dem Dezimalpunkt das Datum
                 * und der Teil nach dem Dezimalpunkt die Zeit (z.B. 1.5 entspricht 01.01.1900 12:00:00).
                 * Falls der Tag 0 angegeben ist wird der Datumsanteil mit 31.12.1899 zurueck-
                 * gegeben. Erhalten wir also ein Jahr kleiner als 1900, dann haben wir eine
                 * Zeit.
                 */
                if (cal.get(Calendar.YEAR) < 1900) { // Zeitformat
                    SimpleDateFormat STD_TIM = new SimpleDateFormat("kk:mm:ss");
                    return STD_TIM.format(dat);
                }

                SimpleDateFormat STD_DAT = new SimpleDateFormat("dd.MM.yyyy");
                return STD_DAT.format(dat); // Datumsformat
            } else {
                /* int, long, double Formate */
                double dbl = cell.getNumericCellValue();
                int tryInt = (int) dbl;
                long tryLong = (long) dbl;
                if (tryInt == dbl) {
                    return new Integer(tryInt).toString(); // int-Format
                } else if (tryLong == dbl) {
                    return new Long(tryLong).toString(); // long-Format
                }

                // return new Double(dbl).toString(); // double-Format
                String numberValueString = new Double(dbl).toString(); // double-Format

                // always use decimal format
                try {
                    // scale 14 to solve problem like value 0.22 --> 0.219999999999997
                    BigDecimal roundedBigDecimal = new BigDecimal(numberValueString).setScale(14,
                            RoundingMode.HALF_UP); // use constructor BigDecimal(String)!

                    String customValueString = getCustomDecimalFormat().format(roundedBigDecimal);
                    if (!customValueString.equals(numberValueString)) {
                        logger.debug("getCellValusAsString: Changing string value of double '{}' to '{}'",
                                numberValueString, customValueString);
                        numberValueString = customValueString; // bigdecimal-format

                    }
                } catch (Exception e) {
                    logger.error("An error occurred trying to convert the cell value number to decimal format "
                            + numberValueString, e);
                }

                return numberValueString;
            }

        case Cell.CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());

        case Cell.CELL_TYPE_FORMULA:
            /* Dieser Fall wird jetzt nie eintreffen, da im Falle einer Formel neu die
             * Berechnung zurueckgegeben wurde, die dann einen eigenen Typ hat.
             */
            return cell.getCellFormula();

        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString();

        case Cell.CELL_TYPE_BLANK:
            return "";

        case Cell.CELL_TYPE_ERROR:
            switch (cell.getErrorCellValue()) {
            case 1:
                return "#NULL!";
            case 2:
                return "#DIV/0!";
            case 3:
                return "#VALUE!";
            case 4:
                return "#REF!";
            case 5:
                return "#NAME?";
            case 6:
                return "#NUM!";
            case 7:
                return "#N/A";
            default:
                return "#ERR!";
            }

        default:
            return "ERROR: unknown Format";
        }
    } finally {
        if (isFormulaPatched) {
            cell.setCellFormula(initialFormula);
            evaluator.notifySetFormula(cell);
        }
    }

}

---

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