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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getSheet

Introduction

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

Prototype

Sheet getSheet();

Source Link

Document

Returns the sheet this cell belongs to

Usage

From source file:com.github.svrtm.xlreport.Cells.java

License:Apache License

/**
 * Finalization of the implementation <code>Cells</code>.
 *
 * @return an instance of the implementation <code>Row</code>
 * @see com.github.svrtm.xlreport.Row#addCells(int)
 * @see com.github.svrtm.xlreport.Row#addCells(int...)
 *///from w  w w .j  av  a  2s  .c o  m
@SuppressWarnings("unchecked")
public TR configureCells() {
    if (columnWidth == -1 && incrementValue == -1 && cellStyle == null)
        return (TR) row;

    int increment = incrementValue;
    for (final int i : indexesCells) {
        if (row.cells.get(i) == null)
            row.prepareNewCell(i).createCell();

        final org.apache.poi.ss.usermodel.Row poiRow = row.poiRow;
        final Cell poiCell = poiRow.getCell(i);
        if (poiCell == null)
            throw new ReportBuilderException(
                    format("A cell of number %d [row:%d] can't be found. Please, create a cell before using it",
                            i, poiRow.getRowNum()));

        if (columnWidth != -1)
            poiCell.getSheet().setColumnWidth(i, columnWidth);
        if (incrementValue != -1)
            poiCell.setCellValue(increment++);
        if (enableAutoSize)
            setAutoSizeColumn(poiCell);

        if (cellStyle != null)
            if (row.cells.get(i).cellStyle == null) {
                // Apply a style to the cell
                final CellStyle poiStyle = cellStyle.getStyle();
                poiCell.setCellStyle(poiStyle);

                final List<Cell> mergedCells = findMergedCells(poiCell);
                if (mergedCells != null)
                    for (final Cell mergedCell : mergedCells)
                        mergedCell.setCellStyle(poiStyle);
            }
    }

    return (TR) row;
}

From source file:com.jkoolcloud.tnt4j.streams.parsers.AbstractExcelParser.java

License:Apache License

/**
 * Evaluates and returns cell contained value.
 *
 * @param cell//from ww  w. ja v  a  2 s .  c om
 *            cell instance to evaluate value
 * @return evaluated cell value
 */
protected Object getCellValue(Cell cell) {
    CellValue cellValue;
    synchronized (EVALUATOR_LOCK) {
        if (evaluator == null) {
            Workbook workbook = cell.getSheet().getWorkbook();
            evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        }

        cellValue = evaluator.evaluate(cell);
    }

    if (cellValue == null) {
        return cell.toString();
    }

    switch (cellValue.getCellTypeEnum()) {
    case BOOLEAN:
        return cellValue.getBooleanValue();
    case NUMERIC:
        return cellValue.getNumberValue();
    case STRING:
        return cellValue.getStringValue();
    default:
        return cellValue.formatAsString();
    }
}

From source file:com.jmc.jfxxlsdiff.util.POIXlsUtil.java

private static Object getFormulaValue(Cell cell) {
    Object cv = null;//from   www .j a  va  2s  .  com

    FormulaEvaluator fe = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
    CellValue v = fe.evaluate(cell);

    switch (v.getCellType()) {
    case Cell.CELL_TYPE_BLANK: {
        break;
    }
    case Cell.CELL_TYPE_BOOLEAN: {
        cv = v.getBooleanValue();
        break;
    }
    case Cell.CELL_TYPE_ERROR: {
        cv = v.getErrorValue();
        break;
    }
    //case Cell.CELL_TYPE_FORMULA: {
    //   cv = cell.getCellFormula();
    //   break;
    //}
    case Cell.CELL_TYPE_NUMERIC: {
        double d = v.getNumberValue();

        if (DateUtil.isCellDateFormatted(cell)) {
            Calendar cal = Calendar.getInstance();
            cal.setTime(DateUtil.getJavaDate(d));
            cv = cal.getTime();
        } else {
            cv = d;
        }
        break;
    }
    case Cell.CELL_TYPE_STRING: {
        cv = v.getStringValue();
        break;
    }
    default: {
        logger.log(Level.WARNING, "Unexpected formula cell type = {0}", v.getCellType());
        break;
    }
    }

    return cv;
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

/**
 * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat}
 * /*from w w w . j a  va 2 s. co m*/
 * @param cell
 * @param excell
 * @param style
 * @param font
 */
private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle,
        Workbook workbook, Font font, Font invisibleFont) {
    if (excell != null) {

        // [1] format cell
        formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont);

        // [2] set enum
        if (!excell.isConsiderEnum()) {
            if (StringUtils.isNotBlank(excell.getDisplayText())) {
                cell.setCellValue(excell.getDisplayText());
            }
            if (!excell.isMultiSelect() && excell.isNumberValidation()) {
                addNumberValidation(cell);
            }
        } else {
            String[] list = (String[]) excell.getRestriction().getEnumValues()
                    .toArray(new String[excell.getRestriction().getEnumValues().size()]);

            SXSSFSheet sheet = (SXSSFSheet) cell.getSheet();

            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(list);
            CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getColumnIndex());
            DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                    regions);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("Not Applicable", "Can't change the value");
            dataValidation.setShowErrorBox(true);

            try {
                if (isValidEnumList(list)) {
                    sheet.addValidationData(dataValidation);
                } else {
                    Sheet hidden = null;
                    String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText());
                    Workbook wBook = cell.getSheet().getWorkbook();
                    if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) {
                        hidden = wBook.getSheet(hiddenName);
                    } else {
                        hidden = wBook.createSheet(hiddenName);

                        for (int i = 0, length = list.length; i < length; i++) {
                            String name = list[i];
                            Row row = hidden.createRow(i);
                            Cell cell1 = row.createCell(0);
                            cell1.setCellValue(name);
                        }
                        Name namedCell = hidden.getWorkbook().getName(hiddenName);
                        namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName();
                        namedCell.setNameName(hiddenName);
                        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length);
                    }

                    dvConstraint = (XSSFDataValidationConstraint) dvHelper
                            .createFormulaListConstraint(hiddenName);
                    dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
                    dataValidation.setShowErrorBox(true);

                    cell.getSheet().addValidationData(dataValidation);
                    wBook.setSheetHidden(wBook.getSheetIndex(hidden), true);

                }

            } catch (Exception e) {
                String msg = "Excel creation failed while building cell: " + excell.getDisplayText();
                throw new IllegalStateException(msg, e);
            }

            // cell.setCellValue(excelConfig.getDropDownMsg());
        }

    }

}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void formatCell(Workbook workbook, Cell cell, ExcelCell excell,
        Map<IndexedColors, CellStyle> s_cellStyle, Font font, Font invisibleFont) {

    if (excell.getFormat() != null) {

        ExcelFormat format = excell.getFormat();

        CellStyle style = s_cellStyle.get(format.getBackgroundColor());

        if (format.isDate()) {
            // for date create a new style
            style = getDateStyle("date", cell.getSheet(), font);
            XSSFCreationHelper createHelper = (XSSFCreationHelper) cell.getSheet().getWorkbook()
                    .getCreationHelper();
            style.setDataFormat(createHelper.createDataFormat().getFormat("MMMM dd, yyyy"));
            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            font.setBold(false);//from   w  ww  .  j a v a2 s. c o  m
            font.setFontHeightInPoints((short) 12);
            style.setFont(font);
            cell.setCellValue(new Date());
        }

        if (style == null) {
            style = workbook.createCellStyle();
            s_cellStyle.put(format.getBackgroundColor(), style);
        }

        if (format.getAlignment() > 0) {
            style.setAlignment(format.getAlignment());
        }
        if (format.getBackgroundColor() != null && !IndexedColors.WHITE.equals(format.getBackgroundColor())) {
            style.setFillForegroundColor(format.getBackgroundColor().getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        if (format.getTextColor() != null) {
            font.setColor(format.getTextColor().getIndex());
            style.setFont(font);
        }
        if (format.isBold()) {
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        if (format.getFontHeight() > 0) {
            font.setFontHeightInPoints(format.getFontHeight());
        }
        if (format.isWrapText()) {
            style.setWrapText(true);
        }
        style.setFont(font);
        if (format.isHideText()) {
            invisibleFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(invisibleFont);
        }
        cell.setCellStyle(style);

    } else {
        // Let's set default formatting for free text cell
        IndexedColors defaultStyle = IndexedColors.AUTOMATIC; // we are using this index
        CellStyle style = s_cellStyle.get(defaultStyle);
        if (style == null) {
            style = workbook.createCellStyle();
            s_cellStyle.put(defaultStyle, style);
        }
        style.setWrapText(true);
        cell.setCellStyle(style);

    }

}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void addNumberValidation(Cell cell) {

    if (cell != null) {

        Sheet sheet = cell.getSheet();
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createNumericConstraint(ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, "1.00",
                        "1000000000000.00");
        CellRangeAddressList addressList = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                cell.getColumnIndex(), cell.getColumnIndex());
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                addressList);/*  w  w  w . ja v a 2s . co  m*/
        validation.setErrorStyle(ErrorStyle.STOP);
        validation.createErrorBox("Error", "Only numeric values are allowed");
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

}

From source file:com.plugin.excel.util.ExcelUtil.java

License:Apache License

/**
 * @param oldCell//  w  w  w  .  ja v a 2 s  .c o  m
 * @param newCell
 * @param styleMap
 */
public static void copyCell(Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap) {
    if (styleMap != null) {
        if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
            newCell.setCellStyle(oldCell.getCellStyle());
        } else {
            int stHashCode = oldCell.getCellStyle().hashCode();
            CellStyle newCellStyle = styleMap.get(stHashCode);
            if (newCellStyle == null) {
                newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
                newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                styleMap.put(stHashCode, newCellStyle);
            }
            newCell.setCellStyle(newCellStyle);
        }
    }
    switch (oldCell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        newCell.setCellType(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;
    default:
        break;
    }

}

From source file:com.runwaysdk.dataaccess.io.excel.AttributeColumn.java

License:Open Source License

@Override
public void setValue(Cell cell, String value) {
    if (value != null && value.length() > 0) {
        String type = this.javaType();

        if (type.equals(Long.class.getName())) {
            cell.setCellValue(Long.parseLong(value));
        } else if (type.equals(Float.class.getName())) {
            cell.setCellValue(Float.parseFloat(value));
        } else if (type.equals(Double.class.getName()) || type.equals(BigDecimal.class.getName())) {
            cell.setCellValue(Double.parseDouble(value));
        } else if (type.equals(Integer.class.getName())) {
            cell.setCellValue(Integer.parseInt(value));
        } else if (type.equals(Boolean.class.getName())) {
            cell.setCellValue(Boolean.parseBoolean(value));
        } else if (type.equals(java.util.Date.class.getName())) {
            try {
                Date date = DateUtilities.parseDate(value);
                cell.setCellValue(date);
            } catch (Exception e) {
                Date date = MdAttributeDateUtil.getTypeSafeValue(value);
                cell.setCellValue(date);
            }//w w w .ja va  2s  . c  o  m
        } else {
            CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();
            cell.setCellValue(helper.createRichTextString(value));
        }
    }
}

From source file:com.runwaysdk.dataaccess.io.excel.ExcelColumn.java

License:Open Source License

public void setValue(Cell cell, String value) {
    CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();

    cell.setCellValue(helper.createRichTextString(value));
}

From source file:com.runwaysdk.dataaccess.io.excel.StringFieldColumn.java

License:Open Source License

@Override
public void setCellValue(Cell cell, String value) {
    CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();

    cell.setCellValue(helper.createRichTextString(value));
}