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

List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. 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:misuExcel.excelAdd.java

License:Open Source License

private String getCellString(Cell cell) {
    try {//from   w  ww .ja  v  a2  s.c  o m
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString().trim();
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue().toString().trim();
            } else {
                return String.valueOf(cell.getNumericCellValue()).trim();
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue()).trim();
        case Cell.CELL_TYPE_FORMULA:
            return String.valueOf(cell.getCellFormula()).trim();
        default:
            return cell.getRichStringCellValue().getString().trim();
        }
    } catch (NullPointerException e) {
        JOptionPane.showMessageDialog(null, e.getMessage(), "", JOptionPane.ERROR_MESSAGE);
    }
    return null;
}

From source file:misuExcel.excelSplit.java

License:Open Source License

private String getCellString(Cell cell) {
    try {/*from w  ww . j  ava2 s.  c om*/
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue().getString().trim();
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue().toString().trim();
            } else {
                return String.valueOf(cell.getNumericCellValue()).trim();
            }
        case Cell.CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue()).trim();
        case Cell.CELL_TYPE_FORMULA:
            return String.valueOf(cell.getCellFormula()).trim();
        default:
            return cell.getStringCellValue().trim();
        }
    } catch (NullPointerException e) {
        JOptionPane.showMessageDialog(null, e.getMessage(), "", JOptionPane.ERROR_MESSAGE);
    }
    return null;
}

From source file:misuExcel.excelWrite.java

License:Open Source License

private void outType01() {
    if (wbSheet != null && names != null && list != null) {
        Log.info("list size:" + list.size());
        String strinfo = "";
        for (int i = 0; i < list.size(); i++) {
            ArrayList<Integer> integers = list.get(i);
            Workbook splitWb = null;// ww  w .  j ava 2  s .  c  o  m
            if (indexType == 1)
                splitWb = new XSSFWorkbook();
            else if (indexType == 2)
                splitWb = new HSSFWorkbook();
            Sheet sheet = splitWb.createSheet("split");
            for (int j = 0; j < integers.size() + splitJpanel.ignore_Row; j++) {
                Row row = null;
                Row copy = null;
                if (j >= splitJpanel.ignore_Row) {
                    row = sheet.createRow(j);
                    copy = wbSheet.getRow(integers.get(j - splitJpanel.ignore_Row));
                } else {
                    row = sheet.createRow(j);
                    copy = wbSheet.getRow(j);
                }
                for (int k = 0; k < copy.getLastCellNum(); k++) {
                    Cell cell = row.createCell(k);
                    Cell copyCell = copy.getCell(k);
                    if (copyCell != null) {
                        switch (copyCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(copyCell.getRichStringCellValue().getString().trim());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(copyCell)) {
                                cell.setCellValue(copyCell.getDateCellValue());
                            } else {
                                cell.setCellValue(copyCell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(copyCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(copyCell.getCellFormula());
                            break;
                        default:
                            cell.setCellValue(copyCell.getStringCellValue().trim());
                        }
                    }
                }
            }
            createWB(splitWb, names.get(i));
            Log.info(names.get(i) + ".xlsx?");
            strinfo += names.get(i) + "." + _index + "?;";
            if (i != 0 && i % 3 == 0) {
                strinfo += "\n";
            }
        } //end for
        JOptionPane.showMessageDialog(null, strinfo);
    }
}

From source file:misuExcel.excelWrite.java

License:Open Source License

private void outType02() {
    if (wbSheet != null && names != null && list != null) {
        Log.info("list size:" + list.size());
        Workbook splitWb = null;/*  w  w w.  j av  a  2s. co m*/
        if (indexType == 1)
            splitWb = new XSSFWorkbook();
        else if (indexType == 2)
            splitWb = new HSSFWorkbook();
        for (int i = 0; i < list.size(); i++) {
            ArrayList<Integer> integers = list.get(i);
            Sheet sheet = splitWb.createSheet(names.get(i));
            for (int j = 0; j < integers.size() + splitJpanel.ignore_Row; j++) {
                Row row = null;
                Row copy = null;
                if (j >= splitJpanel.ignore_Row) {
                    row = sheet.createRow(j);
                    copy = wbSheet.getRow(integers.get(j - splitJpanel.ignore_Row));
                } else {
                    row = sheet.createRow(j);
                    copy = wbSheet.getRow(j);
                }
                for (int k = 0; k < copy.getLastCellNum(); k++) {
                    Cell cell = row.createCell(k);
                    Cell copyCell = copy.getCell(k);
                    if (copyCell != null) {
                        switch (copyCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(copyCell.getRichStringCellValue().getString().trim());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(copyCell)) {
                                cell.setCellValue(copyCell.getDateCellValue());
                            } else {
                                cell.setCellValue(copyCell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(copyCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(copyCell.getCellFormula());
                            break;
                        default:
                            cell.setCellValue(copyCell.getStringCellValue().trim());
                        }
                    }
                }
            }
        } //end for
        createWB(splitWb, fileReal + "(cut)");
        JOptionPane.showMessageDialog(null, fileReal + "(cut)." + _index + "?");
    }
}

From source file:misuExcel.excelWrite.java

License:Open Source License

private void outType03() {
    if (wbSheet != null && addWb != null && names != null && list != null) {
        Sheet sheet = addWb.getSheetAt(sheetNum_target);
        for (int i = 0; i < list.size(); i++) {
            ArrayList<Integer> integers = list.get(i);
            Row copy = wbSheet.getRow(i + addJpanel.ignore_Rowtar);
            for (int j = 0; j < integers.size(); j++) {
                Row row = sheet.getRow(integers.get(j));
                int numRow = row.getLastCellNum();
                for (int k = addJpanel.ignore_Celltar; k < copy.getLastCellNum(); k++) {
                    Cell cell = null;//  ww  w  .j  av a 2s  . c  om
                    Cell copyCell = null;
                    if (k != cellNum_target) {
                        copyCell = copy.getCell(k);
                        if (addJpanel.ignore_Celltar > cellNum_target) {
                            cell = row.createCell(k + numRow - addJpanel.ignore_Celltar);
                        } else {
                            cell = row.createCell(k < cellNum_target ? (k + numRow - addJpanel.ignore_Celltar)
                                    : (k - 1 + numRow - addJpanel.ignore_Celltar));
                        }
                    }
                    if (copyCell != null) {
                        switch (copyCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(copyCell.getRichStringCellValue().getString().trim());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(copyCell)) {
                                cell.setCellValue(copyCell.getDateCellValue());
                            } else {
                                cell.setCellValue(copyCell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(copyCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(copyCell.getCellFormula());
                            break;
                        default:
                            cell.setCellValue(copyCell.getStringCellValue().trim());
                        }
                    }
                }
            }
        } //end for
        createWB(addWb, fileReal + "(add)");
        JOptionPane.showMessageDialog(null, fileReal + "(add)." + _index + "?");
    }
}

From source file:misuExcel.excelWrite.java

License:Open Source License

private void outType04() {
    if (wbSheet != null && addWb != null && names != null && list != null) {
        Sheet sheet = addWb.getSheetAt(sheetNum_target);
        int numRow = sheet.getLastRowNum() + 1;
        ArrayList<Integer> integers = list.get(0);
        for (int j = addJpanel.ignore_Rowtar; j <= wbSheet.getLastRowNum(); j++) {
            Row row = null;// w  w  w .j av  a2 s.  c  o  m
            Row copy = null;
            if (j != cellNum_target) {
                if ((cellNum_target + 1) > addJpanel.ignore_Rowtar)
                    row = sheet.createRow(j < cellNum_target ? (j + numRow - addJpanel.ignore_Rowtar)
                            : (j + numRow - 1 - addJpanel.ignore_Rowtar));
                else
                    row = sheet.createRow(j + numRow - addJpanel.ignore_Rowtar);
                copy = wbSheet.getRow(j);
            }
            if (copy != null) {
                for (int k = 0; k < copy.getLastCellNum(); k++) {
                    Cell cell = null;
                    if (k >= addJpanel.ignore_Celltar)
                        cell = row.createCell(integers.get((k - addJpanel.ignore_Celltar)));
                    else
                        cell = row.createCell(k);
                    Cell copyCell = copy.getCell(k);
                    if (copyCell != null) {
                        switch (copyCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(copyCell.getRichStringCellValue().getString().trim());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(copyCell)) {
                                cell.setCellValue(copyCell.getDateCellValue());
                            } else {
                                cell.setCellValue(copyCell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(copyCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(copyCell.getCellFormula());
                            break;
                        default:
                            cell.setCellValue(copyCell.getStringCellValue().trim());
                        }
                    }
                }
            }
        } //end for
        createWB(addWb, fileReal + "(add)");
        JOptionPane.showMessageDialog(null, fileReal + "(add)." + _index + "?");
    }
}

From source file:mongodbutils.Filehandler.java

private Object getCellValue(Cell cell) {

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            Date dt = cell.getDateCellValue();

            JSONObject obj = new JSONObject();
            obj.put("$date", dt.getTime());
            return obj.toString();

            //return "" + cell.getDateCellValue();
        } else {/*from  www .  j a  v a2  s  .  com*/
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_BOOLEAN:
        return "" + cell.getBooleanCellValue();
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    }
    return "";
}

From source file:net.illustrato.ctrl.CtrlCore.java

private Row copyRow(Workbook workbook, Sheet worksheet, int sourceRowNum, int destinationRowNum) {
    // Get the source / new row
    Row newRow = worksheet.getRow(destinationRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {//from w w w .  j a va2s .c om
        newRow = worksheet.createRow(destinationRowNum);
    }

    // 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) {
            newCell = null;
            continue;
        }

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

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

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case HSSFCell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            //Si tenemos que modificar la formulario lo podemos hacer como string
            //oldCell.getCellFormula().replace("A"+sourceRowNum, "A"+destinationRowNum)
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
    return newRow;
}

From source file:net.mcnewfamily.rmcnew.model.Util.java

License:Open Source License

public static String getCellValueAsStringOrEmptyString(Cell cell) {
    if (cell == null) {
        return "";
    }/*w  ww .  j av a  2 s .  c  o m*/
    String value;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        value = cell.getRichStringCellValue().getString();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            value = cell.getDateCellValue().toString();
        } else {
            value = Integer.toString((int) cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        value = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
    default:
        value = "";
    }
    return value;
}

From source file:net.sf.excelutils.WorkbookUtils.java

License:Apache License

/**
 * copy row/*w w  w .  j  ava  2s .c  om*/
 *
 * @param sheet
 * @param from begin of the row
 * @param to destination fo the row
 * @param count count of copy
 */
public static void copyRow(Sheet sheet, int from, int to, int count) {

    for (int rownum = from; rownum < from + count; rownum++) {
        Row fromRow = sheet.getRow(rownum);
        Row toRow = getRow(to + rownum - from, sheet);
        if (null == fromRow)
            return;
        toRow.setHeight(fromRow.getHeight());
        toRow.setHeightInPoints(fromRow.getHeightInPoints());
        int lastCellNum = fromRow.getLastCellNum();
        lastCellNum = lastCellNum > 255 ? 255 : lastCellNum;
        for (int i = fromRow.getFirstCellNum(); i <= lastCellNum && i >= 0; i++) {
            Cell fromCell = getCell(fromRow, i);
            Cell toCell = getCell(toRow, i);
            // toCell.setEncoding(fromCell.getEncoding());
            toCell.setCellStyle(fromCell.getCellStyle());
            toCell.setCellType(fromCell.getCellType());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            default:
            }
        }
    }

    // copy merged region
    List shiftedRegions = new ArrayList();
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() >= from && r.getLastRow() < from + count) {
            CellRangeAddress n_r = new CellRangeAddress(r.getFirstRow() + to - from, r.getLastRow() + to - from,
                    r.getFirstColumn(), r.getLastColumn());
            shiftedRegions.add(n_r);
        }
    }

    // readd so it doesn't get shifted again
    Iterator iterator = shiftedRegions.iterator();
    while (iterator.hasNext()) {
        CellRangeAddress region = (CellRangeAddress) iterator.next();
        sheet.addMergedRegion(region);
    }
}