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:guru.qas.martini.report.DefaultState.java

License:Apache License

protected void colorRow(short color, Row row) {
    short firstCellNum = row.getFirstCellNum();
    short lastCellNum = row.getLastCellNum();
    for (int i = firstCellNum; i <= lastCellNum; i++) {
        Cell cell = row.getCell(i);
        if (null != cell) {
            CellStyle cellStyle = cell.getCellStyle();
            Workbook workbook = cell.getSheet().getWorkbook();
            CellStyle clone = workbook.createCellStyle();

            clone.cloneStyleFrom(cellStyle);
            clone.setFillForegroundColor(color);
            clone.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            BorderStyle borderStyle = cellStyle.getBorderLeftEnum();
            clone.setBorderLeft(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle);
            short borderColor = cellStyle.getLeftBorderColor();
            clone.setLeftBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor);

            borderStyle = cellStyle.getBorderRightEnum();
            clone.setBorderRight(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle);
            borderColor = cellStyle.getRightBorderColor();
            clone.setRightBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor);

            borderStyle = cellStyle.getBorderTopEnum();
            clone.setBorderTop(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle);
            borderColor = cellStyle.getTopBorderColor();
            clone.setTopBorderColor(0 == borderColor ? IndexedColors.BLACK.getIndex() : borderColor);

            borderStyle = cellStyle.getBorderBottomEnum();
            clone.setBorderBottom(BorderStyle.NONE == borderStyle ? BorderStyle.THIN : borderStyle);
            borderColor = cellStyle.getBottomBorderColor();
            clone.setBottomBorderColor(borderColor);
            cell.setCellStyle(clone);//w  ww .  jav  a 2s. c  o m
        }
    }
}

From source file:guru.qas.martini.report.DefaultState.java

License:Apache License

protected void colorCompromisedThemes() {
    Collection<Cell> failed = statii.get("FAILED");

    if (!failed.isEmpty()) {
        List<Row> rows = Lists.newArrayListWithExpectedSize(failed.size());
        for (Cell cell : failed) {
            Row row = cell.getRow();/*from ww  w  .  j  a  v a  2  s  .c  o m*/
            rows.add(row);
        }

        Set<Cell> compromisedThemeCells = Sets.newHashSet();

        Map<String, Collection<Cell>> themeMap = themes.asMap();
        for (Map.Entry<String, Collection<Cell>> mapEntry : themeMap.entrySet()) {
            Collection<Cell> themeCells = mapEntry.getValue();

            boolean compromised = false;
            for (Iterator<Cell> iterator = themeCells.iterator(); !compromised && iterator.hasNext();) {
                Cell themeCell = iterator.next();
                Row row = themeCell.getRow();
                compromised = rows.contains(row);
            }

            if (compromised) {
                compromisedThemeCells.addAll(themeCells);
            }
        }

        Set<String> compromisedThemes = Sets.newHashSet();
        for (Cell themeCell : compromisedThemeCells) {
            String contents = themeCell.getStringCellValue();
            if (null != contents) {
                Iterable<String> themes = Splitter.onPattern("\\s+").omitEmptyStrings().split(contents);
                Iterables.addAll(compromisedThemes, themes);
            }
        }

        for (String theme : compromisedThemes) {
            Collection<Cell> cells = themes.get(theme);
            for (Cell cell : cells) {
                CellStyle cellStyle = cell.getCellStyle();
                Sheet sheet = cell.getSheet();
                Workbook workbook = sheet.getWorkbook();

                int originalFontIndex = cellStyle.getFontIndexAsInt();
                Font originalFont = workbook.getFontAt(originalFontIndex);

                CellStyle clone = workbook.createCellStyle();
                clone.cloneStyleFrom(cellStyle);

                Font font = workbook.findFont(true, IndexedColors.DARK_RED.getIndex(),
                        originalFont.getFontHeight(), originalFont.getFontName(), originalFont.getItalic(),
                        originalFont.getStrikeout(), originalFont.getTypeOffset(), originalFont.getUnderline());

                if (null == font) {
                    font = workbook.createFont();
                    font.setBold(true);
                    font.setColor(IndexedColors.DARK_RED.getIndex());
                    font.setFontHeight(originalFont.getFontHeight());
                    font.setFontName(originalFont.getFontName());
                    font.setItalic(originalFont.getItalic());
                    font.setStrikeout(originalFont.getStrikeout());
                    font.setTypeOffset(originalFont.getTypeOffset());
                    font.setUnderline(originalFont.getUnderline());
                }
                clone.setFont(font);
                cell.setCellStyle(clone);
            }
        }
    }
}

From source file:joinery.impl.Serialization.java

License:Open Source License

private static final void writeCell(final Cell cell, final Object value) {
    if (value instanceof Number) {
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        cell.setCellValue(Number.class.cast(value).doubleValue());
    } else if (value instanceof Date) {
        final CellStyle style = cell.getSheet().getWorkbook().createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        cell.setCellStyle(style);//from  w  w  w  . jav  a2  s  .com
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        cell.setCellValue(Date.class.cast(value));
    } else if (value instanceof Boolean) {
        cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
    } else {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(value != null ? String.valueOf(value) : "");
    }
}

From source file:net.ceos.project.poi.annotated.core.CellStyleHandler.java

License:Apache License

/**
 * Apply the cell comment to a cell./*from  ww w .  j a  v a 2s  .c o m*/
 * 
 * @param configCriteria
 *            the {@link XConfigCriteria} object
 * @param isAuthorizedComment
 *            the extension file
 * @param cell
 *            the {@link Cell}
 */
protected static void applyComment(final XConfigCriteria configCriteria, final Boolean isAuthorizedComment,
        final Cell cell) {
    if (StringUtils.isBlank(configCriteria.getElement().commentRules())
            || StringUtils.isNotBlank(configCriteria.getElement().commentRules()) && isAuthorizedComment) {
        if (ExtensionFileType.XLS.equals(configCriteria.getExtension())) {
            final Map<Sheet, HSSFPatriarch> drawingPatriarches = new HashMap<>();

            CreationHelper createHelper = cell.getSheet().getWorkbook().getCreationHelper();
            HSSFSheet sheet = (HSSFSheet) cell.getSheet();
            HSSFPatriarch drawingPatriarch = drawingPatriarches.get(sheet);
            if (drawingPatriarch == null) {
                drawingPatriarch = sheet.createDrawingPatriarch();
                drawingPatriarches.put(sheet, drawingPatriarch);
            }

            Comment comment = drawingPatriarch
                    .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
            comment.setString(createHelper.createRichTextString(configCriteria.getElement().comment()));

            cell.setCellComment(comment);

        } else if (ExtensionFileType.XLSX.equals(configCriteria.getExtension())) {
            CreationHelper factory = configCriteria.getWorkbook().getCreationHelper();

            Drawing drawing = cell.getSheet().createDrawingPatriarch();

            ClientAnchor anchor = factory.createClientAnchor();

            Comment comment = drawing.createCellComment(anchor);
            RichTextString str = factory.createRichTextString(configCriteria.getElement().comment());
            comment.setString(str);

            cell.setCellComment(comment);
        }
    }
}

From source file:org.bbreak.excella.core.handler.DebugErrorHandler.java

License:Open Source License

/**
 * ?/*from  w w w .  j a va  2  s  . c o m*/
 * 
 * @param workbook 
 * @param errorCell
 * @param exception
 */
protected void markupErrorCell(Workbook workbook, ParseException exception) {
    Cell errorCell = exception.getCell();
    if (errorCell == null) {
        return;
    }
    // ????
    workbook.setActiveSheet(workbook.getSheetIndex(errorCell.getSheet()));
    errorCell.setAsActiveCell();

    if (workbook instanceof XSSFWorkbook) {
        XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;

        CellStyle errorCellStyle = xssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // TODO:???????????????
        // XSSFComment xssfComment = ((XSSFSheet)sheet).createComment();
        // xssfComment.setRow( errorCell.getRowIndex());
        // xssfComment.setColumn( (short)errorCell.getColumnIndex());
        // XSSFRichTextString string = new XSSFRichTextString( ex.getMessage());
        // xssfComment.setString( ex.getMessage());
    } else {
        HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
        int sheetNum = hssfWorkbook.getNumberOfSheets();
        for (int cnt = 0; cnt < sheetNum; cnt++) {
            hssfWorkbook.getSheetAt(cnt).setSelected(false);
        }

        // ?
        CellStyle errorCellStyle = hssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // ?
        short commentColFrom = (short) (errorCell.getColumnIndex() + 1);
        short commentColTo = (short) (errorCell.getColumnIndex() + ERROR_COMENT_COL_SIZE);
        int commentRowFrom = errorCell.getRowIndex();
        int commentRowTo = errorCell.getRowIndex() + ERROR_COMENT_ROW_SIZE;

        HSSFSheet hssfSheet = (HSSFSheet) errorCell.getSheet();
        HSSFPatriarch patr = hssfSheet.createDrawingPatriarch();
        hssfSheet.setSelected(true);
        HSSFComment comment = patr.createComment(
                new HSSFClientAnchor(0, 0, 0, 0, commentColFrom, commentRowFrom, commentColTo, commentRowTo));
        comment.setVisible(true);
        comment.setString(new HSSFRichTextString(createCommentMessage(exception)));
        errorCell.setCellComment(comment);
    }
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ??? ??????<br>//from w ww. ja v a  2 s  . c om
 * <br>
 * ?[CELL_TYPE_ERROR]??<br>
 * xls? ?HSSFErrorConstants?<br>
 * xlsx? Excel??ex.#DIV/0!?#N/A?#REF!
 * 
 * @param cell 
 * @return 
 */
public static Object getCellValue(Cell cell) {
    Object value = null;

    if (cell != null) {
        switch (cell.getCellTypeEnum()) {
        case BLANK:
            break;
        case BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case ERROR:
            value = cell.getErrorCellValue();
            break;
        case NUMERIC:
            // ??
            if (isCellDateFormatted(cell)) {
                value = cell.getDateCellValue();
            } else {
                value = cell.getNumericCellValue();
            }
            break;
        case STRING:
            value = cell.getStringCellValue();
            break;
        case FORMULA:
            FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper()
                    .createFormulaEvaluator();
            // ?
            CellValue cellValue = evaluator.evaluate(cell);
            CellType cellType = cellValue.getCellTypeEnum();
            // ????
            switch (cellType) {
            case BLANK:
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case ERROR:
                if (cell instanceof XSSFCell) {
                    // XSSF??????
                    XSSFCell xssfCell = (XSSFCell) cell;
                    CTCell ctCell = xssfCell.getCTCell();
                    value = ctCell.getV();
                } else if (cell instanceof HSSFCell) {
                    // HSSF??????
                    value = cell.getErrorCellValue();
                }
                break;
            case NUMERIC:
                // ??
                if (isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            default:
                break;
            }
        default:
            break;
        }
    }
    return value;
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?????/*from   w w  w  . j av a 2  s  .  c  o m*/
 * 
 * @param cell 
 * @return ??
 */
public static String getSheetName(Cell cell) {
    Sheet sheet = cell.getSheet();
    return getSheetName(sheet);
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?/*from   w w w  .j  a v a 2  s .c  o m*/
 * 
 * @param fromCell 
 * @param toCell 
 */
public static void copyCell(Cell fromCell, Cell toCell) {

    if (fromCell != null) {

        // 
        CellType cellType = fromCell.getCellTypeEnum();
        switch (cellType) {
        case BLANK:
            break;
        case FORMULA:
            String cellFormula = fromCell.getCellFormula();
            toCell.setCellFormula(cellFormula);
            break;
        case BOOLEAN:
            toCell.setCellValue(fromCell.getBooleanCellValue());
            break;
        case ERROR:
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
            break;
        case NUMERIC:
            toCell.setCellValue(fromCell.getNumericCellValue());
            break;
        case STRING:
            toCell.setCellValue(fromCell.getRichStringCellValue());
            break;
        default:
        }

        // 
        if (fromCell.getCellStyle() != null
                && fromCell.getSheet().getWorkbook().equals(toCell.getSheet().getWorkbook())) {
            toCell.setCellStyle(fromCell.getCellStyle());
        }

        // 
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
    }
}

From source file:org.databene.formats.xls.XLSUtil.java

License:Open Source License

private static FormulaEvaluator createFormulaEvaluator(Cell cell) {
    return cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetMultiAttributeConverter.java

License:Open Source License

/**
 * {@inheritDoc}/*  w  ww.j av a2s.  c o  m*/
 *
 * @see org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#getCellValue(org.apache.poi.ss.usermodel.Cell,
 *      org.eclipse.emf.ecore.EStructuralFeature)
 */
@Override
public Object getCellValue(Cell cell, EStructuralFeature eStructuralFeature) throws EMFFormsConverterException {
    String string;
    try {
        string = cell.getStringCellValue();
    } catch (final IllegalStateException e) {
        throw new EMFFormsConverterException(
                String.format("Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$
                        cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
                e);
    }

    if (string == null || string.length() == 0) {
        return Collections.emptyList();
    }
    final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature);
    final EDataType eDataType = eAttribute.getEAttributeType();
    if (isDecimalNumber(eDataType.getInstanceClass())) {
        string = string.replace(
                DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator(), '.');
    }

    final List<Object> result = new ArrayList<Object>();
    final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance();
    for (final String element : string.split(SEPARATOR)) {
        try {
            result.add(eFactory.createFromString(eDataType, element));
        } // BEGIN SUPRESS CATCH EXCEPTION
        catch (final RuntimeException ex) {// END SUPRESS CATCH EXCEPTION
            throw new EMFFormsConverterException(
                    MessageFormat.format("The cell value {0} could not converted to a model value.", string)); //$NON-NLS-1$
        }
    }

    return result;
}