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:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetSingleAttributeConverter.java

License:Open Source License

/**
 * {@inheritDoc}/*ww w  .  j  a va  2s.  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 {
    final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature);
    if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return null;
    }
    if (eAttribute == null) {
        return null;
    }
    final EDataType attributeType = eAttribute.getEAttributeType();
    if (attributeType == null) {
        return null;
    }
    try {
        return readCellValue(cell, attributeType);
    } 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);
    } catch (final NumberFormatException e) {
        throw new EMFFormsConverterException(
                String.format("Cell value of column %1$s in row %2$s on sheet %3$s is not a valid number.", //$NON-NLS-1$
                        cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
                e);
    }
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.transfer.EMFFormsSpreadsheetImporterImpl.java

License:Open Source License

private String getStringCellValue(Cell cell) {
    try {//from  w ww  .  j  a  v a  2  s .co  m
        return cell.getStringCellValue();
    } catch (final IllegalArgumentException ex) {
        throw new IllegalStateException(
                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()),
                ex);
    }
}

From source file:org.eclipse.lyo.samples.excel.adapter.dao.internal.ExcelDaoImpl.java

License:Open Source License

private String getCellValue(Cell cell) {
    if (cell != null) {
        String value = null;/*from w  ww .ja  va 2s. c  o m*/
        int type = cell.getCellType();
        if (type == Cell.CELL_TYPE_STRING) {
            value = cell.getStringCellValue();
        } else if (type == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                value = FastDateFormat.getInstance(DEFAULT_OUTPUT_DATE_FORMAT).format(date);
            } else {
                double d = cell.getNumericCellValue();
                if (d == Math.floor(d)) { // need to consider when d is negative
                    value = "" + (int) d;
                } else {
                    value = "" + cell.getNumericCellValue();
                }
            }
        } else if (type == Cell.CELL_TYPE_FORMULA) {
            // get calculated value if the cell type is formula 
            Workbook wb = cell.getSheet().getWorkbook();
            CreationHelper crateHelper = wb.getCreationHelper();
            FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
            // get recursively if the value is still formula 
            value = getCellValue(evaluator.evaluateInCell(cell));
        }
        return value;
    }
    return null;
}

From source file:org.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy cell.//from  w w w  .jav a  2  s .  c o  m
 *
 * @param _oldCell the old cell
 * @param _newCell the new cell
 * @param _styleMap the style map
 */
protected void copyCell(final Cell _oldCell, final Cell _newCell, final Map<Integer, CellStyle> _styleMap) {
    if (_styleMap != null) {
        if (_oldCell.getSheet().getWorkbook() == _newCell.getSheet().getWorkbook()) {
            _newCell.setCellStyle(_oldCell.getCellStyle());
        } else {
            final 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:org.isisaddons.module.excel.dom.CellMarshaller.java

License:Apache License

private static void setCellComment(final Cell cell, final String commentText) {
    Sheet sheet = cell.getSheet();
    Row row = cell.getRow();/*  www  .j a  v  a  2  s .  c o m*/
    Workbook workbook = sheet.getWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    ClientAnchor anchor = creationHelper.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(row.getRowNum());
    anchor.setRow2(row.getRowNum() + 3);

    Drawing drawing = sheet.createDrawingPatriarch();
    Comment comment1 = drawing.createCellComment(anchor);

    RichTextString commentRtf = creationHelper.createRichTextString(commentText);

    comment1.setString(commentRtf);
    Comment comment = comment1;
    cell.setCellComment(comment);
}

From source file:org.joeffice.spreadsheet.actions.SetBordersAction.java

License:Apache License

public void setBorder(JTable currentTable, short thickness, short color) {
    SheetTableModel tableModel = (SheetTableModel) currentTable.getModel();
    List<Cell> selectedCells = CellUtils.getSelectedCells(currentTable, true);
    for (Cell cell : selectedCells) {
        Workbook workbook = cell.getSheet().getWorkbook();
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BORDER_TOP, thickness);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.TOP_BORDER_COLOR, color);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BORDER_LEFT, thickness);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.LEFT_BORDER_COLOR, color);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BORDER_BOTTOM, thickness);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BOTTOM_BORDER_COLOR, color);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.BORDER_RIGHT, thickness);
        CellUtil.setCellStyleProperty(cell, workbook, CellUtil.RIGHT_BORDER_COLOR, color);
        tableModel.fireTableCellUpdated(cell.getRowIndex(), cell.getColumnIndex());
    }/*from  w w w  . ja  va 2 s.c o  m*/
}

From source file:org.joeffice.spreadsheet.cell.CellRenderer.java

License:Apache License

public static void decorateComponent(Cell cell, JComponent renderingComponent, JComponent defaultRenderer) {
    CellStyle style = cell.getCellStyle();

    // Background neither the index or the color works for XSSF cells
    Color backgroundColor = CellUtils.poiToAwtColor(style.getFillBackgroundColorColor());
    if (backgroundColor != null) {
        renderingComponent.setBackground(backgroundColor);
    } else {//  w w w  .  j a v a  2  s  . co m
        renderingComponent.setBackground(defaultRenderer.getBackground());
    }

    // Font and forground
    short fontIndex = style.getFontIndex();
    if (fontIndex > 0) {
        Font xlsFont = cell.getSheet().getWorkbook().getFontAt(fontIndex);
        java.awt.Font font = java.awt.Font.decode(xlsFont.getFontName());
        font = font.deriveFont((float) xlsFont.getFontHeightInPoints());
        font = font.deriveFont(java.awt.Font.PLAIN);
        if (xlsFont.getItalic()) {
            font = font.deriveFont(java.awt.Font.ITALIC);
        }
        if (xlsFont.getBoldweight() == Font.BOLDWEIGHT_BOLD) {
            font = font.deriveFont(java.awt.Font.BOLD);
        }
        if (xlsFont.getUnderline() > Font.U_NONE) {
            // no underline in fonts
        }
        short fontColorIndex = xlsFont.getColor();
        Color fontColor = CellUtils.shortToColor(fontColorIndex);
        if (fontColor != null) {
            renderingComponent.setForeground(fontColor);
        } else {
            renderingComponent.setForeground(defaultRenderer.getForeground());
        }
        renderingComponent.setFont(font);
    } else {
        renderingComponent.setForeground(defaultRenderer.getForeground());
        renderingComponent.setFont(defaultRenderer.getFont());
    }

    // Borders
    // At the moment done in renderer but should be done with a JLayer to paint over the grid
    renderingComponent.setBorder(new CellBorder(cell));

    if (cell.getCellComment() != null) {
        renderingComponent.setToolTipText(cell.getCellComment().getString().getString());
    }
}

From source file:org.joeffice.spreadsheet.TableStyleable.java

License:Apache License

/**
 * Add the attribute as defined in {@link AttributedString} to the {@link MutableAttributeSet} for the JTextPane.
 *
 * @see java.awt.font.TextAttribute/*from   w w w  .j a v a 2  s  .co  m*/
 */
protected void addAttribute(AttributedCharacterIterator.Attribute attribute, Object attributeValue, Cell cell) {
    CellStyle oldStyle = cell.getCellStyle();
    Workbook workbook = cell.getSheet().getWorkbook();
    CellStyle style = cell.getSheet().getWorkbook().createCellStyle();
    style.cloneStyleFrom(oldStyle);
    Font newFont = copyFont(cell);
    if (attribute == FAMILY) {
        newFont.setFontName((String) attributeValue);
        CellUtil.setFont(cell, workbook, newFont);
    } else if (attribute == FOREGROUND) {
        Color color = (Color) attributeValue;
        if (cell instanceof XSSFCell) {
            ((XSSFCellStyle) style).setFillForegroundColor(new XSSFColor(color));
        } else {
            HSSFWorkbook xlsWorkbook = (HSSFWorkbook) workbook;
            HSSFColor xlsColor = xlsWorkbook.getCustomPalette().findColor((byte) color.getRed(),
                    (byte) color.getGreen(), (byte) color.getBlue());
            if (xlsColor == null) {
                xlsColor = xlsWorkbook.getCustomPalette().addColor((byte) color.getRed(),
                        (byte) color.getGreen(), (byte) color.getBlue());
            }
            style.setFillForegroundColor(xlsColor.getIndex());
        }
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    } else if (attribute == BACKGROUND) {
        Color color = (Color) attributeValue;
        if (cell instanceof XSSFCell) {
            ((XSSFCellStyle) style).setFillBackgroundColor(new XSSFColor(color));
        } else {
            HSSFWorkbook xlsWorkbook = (HSSFWorkbook) workbook;
            HSSFColor xlsColor = xlsWorkbook.getCustomPalette().findColor((byte) color.getRed(),
                    (byte) color.getGreen(), (byte) color.getBlue());
            if (xlsColor == null) {
                xlsColor = xlsWorkbook.getCustomPalette().addColor((byte) color.getRed(),
                        (byte) color.getGreen(), (byte) color.getBlue());
            }
            style.setFillBackgroundColor(xlsColor.getIndex());
        }
    } else if (attribute == WEIGHT) {
        short boldValue = Font.BOLDWEIGHT_BOLD;
        if (newFont.getBoldweight() == Font.BOLDWEIGHT_BOLD) {
            boldValue = Font.BOLDWEIGHT_NORMAL;
        }
        newFont.setBoldweight(boldValue);
        CellUtil.setFont(cell, workbook, newFont);
    } else if (attribute == UNDERLINE) {
        byte underlineValue = Font.U_SINGLE;
        if (newFont.getUnderline() == Font.U_SINGLE) {
            underlineValue = Font.U_NONE;
        }
        newFont.setUnderline(underlineValue);
        CellUtil.setFont(cell, workbook, newFont);
    } else if (attribute == SUPERSCRIPT) {
        short superscriptValue = Font.SS_NONE;
        if (SUPERSCRIPT_SUB.equals(attributeValue)) {
            superscriptValue = Font.SS_SUB;
        } else if (SUPERSCRIPT_SUPER.equals(attributeValue)) {
            superscriptValue = Font.SS_SUPER;
        }
        newFont.setTypeOffset(superscriptValue);
        CellUtil.setFont(cell, workbook, newFont);
    } else if (attribute == STRIKETHROUGH) {
        boolean strikeThrough = true;
        if (newFont.getStrikeout()) {
            strikeThrough = false;
        }
        newFont.setStrikeout(strikeThrough);
        CellUtil.setFont(cell, workbook, newFont);
    } else if (attribute == POSTURE) {
        boolean italic = true;
        if (newFont.getItalic()) {
            italic = false;
        }
        newFont.setItalic(italic);
        CellUtil.setFont(cell, workbook, newFont);
    } else if (attribute == SIZE) {
        newFont.setFontHeightInPoints(((Number) attributeValue).shortValue());
        CellUtil.setFont(cell, workbook, newFont);
    } else if (attribute == JUSTIFICATION) {
        CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_JUSTIFY);
    } else if (attribute == ALIGNMENT) {
        if (attributeValue.equals(StyleConstants.ALIGN_LEFT)) {
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_LEFT);
        } else if (attributeValue.equals(StyleConstants.ALIGN_RIGHT)) {
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_RIGHT);
        } else if (attributeValue.equals(StyleConstants.ALIGN_CENTER)) {
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER);
        }
    } else if (attribute == INDENTATION) {
        style.setIndention(((Number) attributeValue).shortValue());
    } else if (attribute == TEXT_TRANSFORM) {
        String text = CellUtils.getFormattedText(cell);
        String transformedText = ((TextTransformer) attributeValue).transformText(text);
        cell.setCellValue(transformedText);
    }
}

From source file:org.joeffice.spreadsheet.TableStyleable.java

License:Apache License

private Font copyFont(Cell cell) {
    CellStyle style = cell.getCellStyle();
    Workbook workbook = cell.getSheet().getWorkbook();
    short fontIndex = style.getFontIndex();
    Font xlsFont = cell.getSheet().getWorkbook().getFontAt(fontIndex);
    Font newFont = workbook.createFont();
    newFont.setFontName(xlsFont.getFontName());
    newFont.setFontHeight((short) xlsFont.getFontHeight());
    newFont.setBoldweight(xlsFont.getBoldweight());
    newFont.setItalic(xlsFont.getItalic());
    newFont.setUnderline(xlsFont.getUnderline());
    newFont.setColor(xlsFont.getColor());
    return newFont;
}

From source file:org.jplus.compare.excel.service.CompareService.java

public static List<Map> compare(Map<String, Map> last, Map<String, Map> current, String[] sortedColumns,
        Map<String, OptionBean> optionMap) {
    List<Map> maps = new ArrayList<Map>();
    for (String key : last.keySet()) {
        if (current.containsKey(key)) {
            Map lastMap = last.get(key);
            Map currentMap = current.get(key);
            boolean added = false;
            float totalAbs = 0;
            for (String column : sortedColumns) {
                Float lastFloat = NumberUtil.toFloat(lastMap.get(column));
                Float currentFloat = NumberUtil.toFloat(currentMap.get(column));
                OptionBean optionBean = optionMap.get(column);
                if (optionBean != null && optionBean.getThresholdValue() != null
                        && optionBean.getThresholdValue() > 0) {
                    float abs = Math.abs(lastFloat - currentFloat);
                    totalAbs += abs;/*  w ww  .j  a  va2  s.c  o m*/
                    if (abs >= optionBean.getThresholdValue()) {
                        currentMap.put("", totalAbs);
                        BaseExcelService.setErrorStyle((Cell) currentMap.get("cell" + column));
                        if (!added) {
                            maps.add(currentMap);
                            added = true;
                        }
                    }
                }
            }
        }
    }
    if (ObjectHelper.isNotEmpty(maps)) {
        Cell cell = (Cell) (maps.get(0)).get("cell" + SqliteUtil.getProperty("cellKey"));
        Workbook workbook = cell.getSheet().getWorkbook();
        File file = new File("Excel.xls");
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            workbook.write(fileOutputStream);
            fileOutputStream.close();
        } catch (IOException ex) {
            Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return maps;
}