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

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

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

Introduction

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

Prototype

CellStyle getCellStyle();

Source Link

Document

Return the cell's style.

Usage

From source file:com.vaadin.addon.spreadsheet.command.CellValueCommand.java

License:Open Source License

/**
 * Sets the given value to the cell at the given coordinates.
 * /*from w w  w  .ja v  a 2s.  co m*/
 * @param row
 *            Row index, 0-based
 * @param col
 *            Column index, 0-based
 * @param value
 *            Value to set to the cell
 * @param cellsToUpdate
 *            List of cells that need updating at the end. If the cell value
 *            is modified, the cell is added to this list.
 * @return Previous value of the cell or null if not available
 */
protected Object updateCellValue(int row, int col, Object value, List<Cell> cellsToUpdate) {
    Cell cell = getCell(row, col);
    Object oldValue = getCellValue(cell);
    if (value == null && cell == null) {
        return null; // nothing to do
    }

    if (cell == null && value != null) {
        // create cell
        Row row2 = getSheet().getRow(row);
        if (row2 == null) {
            row2 = getSheet().createRow(row);
        }
        cell = row2.createCell(col);
    }

    if (value == null) { // delete
        if (cell == null || cell.getCellStyle().getIndex() == 0) {
            getSheet().getRow(row).removeCell(cell);
            if (!spreadsheet.isRerenderPending()) {
                spreadsheet.markCellAsDeleted(cell, false);
            }
        } else {
            cell.setCellValue((String) null);
            if (!spreadsheet.isRerenderPending()) {
                cellsToUpdate.add(cell);
            }
        }
    } else {
        if (value instanceof String) {
            if (((String) value).startsWith("=")) {
                try {
                    cell.setCellFormula(((String) value).substring(1));
                } catch (FormulaParseException fpe) {
                    cell.setCellValue((String) value);
                }
            } else {
                cell.setCellValue((String) value);
            }
        } else if (value instanceof Byte) {
            cell.setCellErrorValue((Byte) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        }
        if (!spreadsheet.isRerenderPending()) {
            cellsToUpdate.add(cell);
        }
    }
    return oldValue;
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Gets the visibility state of the given cell.
 * //  www.j a v  a  2 s  . co  m
 * @param cell
 *            The cell to check
 * @return true if the cell is hidden, false otherwise
 */
public boolean isCellHidden(Cell cell) {
    return isActiveSheetProtected() && cell.getCellStyle().getHidden();
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Gets the locked state of the given cell.
 * //from   www  .j a  v a2 s.  c  om
 * @param cell
 *            The cell to check
 * @return true if the cell is locked, false otherwise
 */
public boolean isCellLocked(Cell cell) {
    if (isActiveSheetProtected()) {
        if (cell != null) {
            if (cell.getCellStyle().getIndex() != 0) {
                return cell.getCellStyle().getLocked();
            } else {
                return getState(false).lockedColumnIndexes.contains(cell.getColumnIndex() + 1)
                        && getState(false).lockedRowIndexes.contains(cell.getRowIndex() + 1);
            }
        } else {
            return true;
        }
    } else {
        return false;
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java

License:Open Source License

/**
 * This should be called when a Cell's styling has been changed. This will
 * tell the Spreadsheet to send the change to the client side.
 * //w  ww .  j av  a  2  s  . c o m
 * @param cell
 *            Target cell
 * @param updateCustomBorders
 *            true to also update custom borders
 */
public void cellStyleUpdated(Cell cell, boolean updateCustomBorders) {
    final String cssSelector = ".col" + (cell.getColumnIndex() + 1) + ".row" + (cell.getRowIndex() + 1);
    final Integer key = (int) cell.getCellStyle().getIndex();
    // remove/modify all possible old custom styles that the cell had (can
    // be found from state)
    ArrayList<String> add = new ArrayList<String>();
    Iterator<String> iterator = spreadsheet.getState().shiftedCellBorderStyles.iterator();
    while (iterator.hasNext()) {
        String style = iterator.next();
        // only cell with this style -> remove
        if (style.startsWith(cssSelector + "{")) {
            iterator.remove();
        } else if (style.contains(cssSelector)) { // shifted borders
            iterator.remove();
            int index = style.indexOf(cssSelector);
            if (index > 0) { // doesn't start with the selector
                style = style.replace(cssSelector + ",", "");
                // in case it is the last
                style = style.replace("," + cssSelector + "{", "{");
            } else {
                style = style.replace(cssSelector + ",", "");
                // in case it is the only
                style = style.replace(cssSelector + "{", "{");
            }
            if (!style.startsWith(",") && !style.startsWith("{")) {
                add.add(style);
            }
        }
    }
    for (String s : add) {
        spreadsheet.getState().shiftedCellBorderStyles.add(s);
    }
    // remove the cell's new custom styles from state (will be added again
    // as this cell is styled)
    if (shiftedBorderLeftStyles.containsKey(key)) {
        final String style = shiftedBorderLeftStyles.get(key);
        spreadsheet.getState().shiftedCellBorderStyles.remove(style);
    }
    if (shiftedBorderTopStyles.containsKey(key)) {
        final String style = shiftedBorderTopStyles.get(key);
        spreadsheet.getState().shiftedCellBorderStyles.remove(style);
    }
    if (mergedCellBorders.containsKey(cssSelector)) {
        final String style = buildMergedCellBorderCSS(cssSelector, mergedCellBorders.remove(cssSelector));
        spreadsheet.getState().shiftedCellBorderStyles.remove(style);
    }

    // TODO May need optimizing since the client side might already have
    // this cell style
    CellStyle cellStyle = cell.getCellStyle();
    addCellStyleCSS(cellStyle);

    shiftedBorderTopStylesMap.clear();
    shiftedBorderLeftStylesMap.clear();
    // custom styles
    doCellCustomStyling(cell);
    updateStyleMap(shiftedBorderLeftStylesMap, shiftedBorderLeftStyles);
    updateStyleMap(shiftedBorderTopStylesMap, shiftedBorderTopStyles);
    if (updateCustomBorders) {
        if (shiftedBorderLeftStyles.containsKey(key)) {
            final String style = shiftedBorderLeftStyles.get(key);
            spreadsheet.getState().shiftedCellBorderStyles.add(style);
        }
        if (shiftedBorderTopStyles.containsKey(key)) {
            final String style = shiftedBorderTopStyles.get(key);
            spreadsheet.getState().shiftedCellBorderStyles.add(style);
        }
        if (mergedCellBorders.containsKey(cssSelector)) {
            spreadsheet.getState().shiftedCellBorderStyles
                    .add(buildMergedCellBorderCSS(cssSelector, mergedCellBorders.get(cssSelector)));
        }
    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java

License:Open Source License

private void doCellCustomStyling(final Cell cell) {
    CellStyle cellStyle = cell.getCellStyle();
    final Integer key = (int) cellStyle.getIndex();
    if (key == 0) { // default style
        return;//  w w  w.  j av a 2  s .  c o  m
    }

    // merged regions have their borders in edge cells that are "invisible"
    // inside the region -> right and bottom cells need to be transfered to
    // the actual merged cell
    final int columnIndex = cell.getColumnIndex();
    final int rowIndex = cell.getRowIndex();
    MergedRegion region = spreadsheet.mergedRegionContainer.getMergedRegion((columnIndex + 1), (rowIndex + 1));
    if (region != null) {
        final String borderRight = getBorderRightStyle(cellStyle);
        final String borderBottom = getBorderBottomStyle(cellStyle);
        if ((borderRight != null && !borderRight.isEmpty())
                || (borderBottom != null && !borderBottom.isEmpty())) {
            StringBuilder sb = new StringBuilder(".col");
            sb.append(region.col1);
            sb.append(".row");
            sb.append(region.row1);
            final String cssKey = sb.toString();
            final String currentBorders = mergedCellBorders.get(cssKey);
            StringBuilder style;
            if (currentBorders != null && !currentBorders.isEmpty()) {
                style = new StringBuilder(currentBorders);
            } else {
                style = new StringBuilder();
            }
            if (borderRight != null && !borderRight.isEmpty()
                    && (currentBorders == null || !currentBorders.contains("border-right"))) {
                style.append(borderRight);
            }
            if (borderBottom != null && !borderBottom.isEmpty()
                    && (currentBorders == null || !currentBorders.contains("border-bottom"))) {
                style.append(borderBottom);
            }
            final String newBorders = style.toString();
            if (!newBorders.isEmpty()) {
                mergedCellBorders.put(cssKey, newBorders);
            }
        }

    }

    // only take transfered borders into account on the (possible) merged
    // regions edges
    if (region == null || region.col1 == (columnIndex + 1) || region.col2 == (columnIndex + 1)
            || region.row1 == (rowIndex + 1) || region.row2 == (rowIndex + 1)) {

        if (shiftedBorderLeftStyles.containsKey(key)) {
            // need to add the border right style to previous cell on
            // left, which might be a merged cell
            if (columnIndex > 0) {
                int row, col;

                MergedRegion previousRegion = spreadsheet.mergedRegionContainer.getMergedRegion(columnIndex,
                        rowIndex + 1);
                if (previousRegion != null) {
                    col = previousRegion.col1;
                    row = previousRegion.row1;
                } else {
                    col = columnIndex;
                    row = rowIndex + 1;
                }
                insertMapEntryIfNeeded(shiftedBorderLeftStylesMap, key, row, col);
            }
        }
        if (shiftedBorderTopStyles.containsKey(key)) {
            // need to add the border bottom style to cell on previous
            // row, which might be a merged cell
            if (rowIndex > 0) {
                int row, col;
                MergedRegion previousRegion = spreadsheet.mergedRegionContainer.getMergedRegion(columnIndex + 1,
                        rowIndex);

                if (previousRegion != null) {
                    col = previousRegion.col1;
                    row = previousRegion.row1;
                } else {
                    col = columnIndex + 1;
                    row = rowIndex;
                }
                insertMapEntryIfNeeded(shiftedBorderTopStylesMap, key, row, col);

            }
        }

    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java

License:Open Source License

public static Double parseNumber(Cell cell, String value, Locale locale) {
    if (value == null || value.trim().isEmpty()) {
        return null;
    }/*from   w  w w.ja v  a 2 s .  c  o  m*/
    if (cell.getCellStyle().getDataFormatString() != null) {
        DataFormatter df = new DataFormatter(locale);
        try {
            Method formatter = df.getClass().getDeclaredMethod("getFormat", Cell.class);
            formatter.setAccessible(true);
            Format format = (Format) formatter.invoke(df, cell);
            if (format != null) {
                ParsePosition parsePosition = new ParsePosition(0);
                Object parsed = format.parseObject(value, parsePosition);
                if (parsePosition.getIndex() == value.length()) {
                    if (parsed instanceof Double) {
                        return (Double) parsed;
                    } else if (parsed instanceof Number) {
                        return ((Number) parsed).doubleValue();
                    }
                }
            }
        } catch (NoSuchMethodException e) {
        } catch (InvocationTargetException e) {
        } catch (IllegalAccessException e) {
        } catch (UnsupportedOperationException e) {
        }

    }
    return parseNumber(value, locale);
}

From source file:com.web.mavenproject6.other.XLSParser.java

private static String readCell(Row row) {
    String buf = "";
    Iterator<Cell> cells = row.iterator();
    while (cells.hasNext()) {
        Cell cell = cells.next();
        int cellType = cell.getCellType();
        if (cell.getCellStyle().getLocked() == false) {
            switch (cellType) {
            case Cell.CELL_TYPE_STRING:
                buf += cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                buf += (int) cell.getNumericCellValue();
                break;

            case Cell.CELL_TYPE_FORMULA:
                buf += cell.getNumericCellValue();
                break;
            default:
                buf += " ";
                break;
            }/*from  w w  w  .  j  a  va  2s .  c  om*/
        }
    }
    return buf;
}

From source file:com.web.mavenproject6.other.XLSParser.java

private static String readCell(Row row, int offset, int count) {
    String buf = "";
    Iterator<Cell> cells = row.iterator();
    int index = 0;
    while (cells.hasNext()) {
        Cell cell = cells.next();

        int cellType = cell.getCellType();
        if (cell.getCellStyle().getLocked() == false) {
            if (index >= offset && index < offset + count) {
                switch (cellType) {
                case Cell.CELL_TYPE_STRING:
                    buf += cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    buf += (int) cell.getNumericCellValue();
                    break;

                case Cell.CELL_TYPE_FORMULA:
                    buf += cell.getNumericCellValue();
                    break;

                }//from ww w .  j ava  2  s  .  co  m
            }
            index++;
        }
    }
    return buf;
}

From source file:cz.krasny.icalstats.data.classes.output.formats.ToHtml.java

public void printStyles() {
    //ensureOut();

    // First, copy the base css
    BufferedReader in = null;//from w ww. j av a 2  s.  c  o m
    try {
        in = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("excelStyle.css")));
        String line;
        while ((line = in.readLine()) != null) {
            out.format("%s%n", line);
        }
    } catch (IOException e) {
        throw new IllegalStateException("Reading standard css", e);
    } finally {
        if (in != null) {
            try {
                in.close();
            } catch (IOException e) {
                //noinspection ThrowFromFinallyBlock
                throw new IllegalStateException("Reading standard css", e);
            }
        }
    }

    // now add css for each used style
    Set<CellStyle> seen = new HashSet<CellStyle>();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            for (Cell cell : row) {
                CellStyle style = cell.getCellStyle();
                if (!seen.contains(style)) {
                    printStyle(style);
                    seen.add(style);
                }
            }
        }
    }
}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetriever.java

License:Open Source License

private void createSummary(Workbook wb, List<String> metricNames, String metricsDir, long startTime,
        long endTime, SUMMARY_INTERVALS summaryInterval) throws IOException, MetricsGraphException {
    // convert seconds to milliseconds
    startTime = TimeUnit.SECONDS.toMillis(startTime);
    endTime = TimeUnit.SECONDS.toMillis(endTime);
    DateTime reportStart = new DateTime(startTime, DateTimeZone.UTC);
    DateTime reportEnd = new DateTime(endTime, DateTimeZone.UTC);

    Sheet sheet = wb.createSheet();/*from   w  w w .j ava2s.c  o  m*/
    wb.setSheetName(0,
            reportStart.toString(SUMMARY_TIMESTAMP) + " to " + reportEnd.toString(SUMMARY_TIMESTAMP));
    Row headingRow = sheet.createRow(0);

    int columnMax = 1;
    for (String metricName : metricNames) {
        MutableDateTime chunkStart = new MutableDateTime(reportStart);
        MutableDateTime chunkEnd = new MutableDateTime(chunkStart);
        Row row = sheet.createRow(metricNames.indexOf(metricName) + 1);
        int columnCounter = 1;
        Boolean isSum = null;

        while (reportEnd.compareTo(chunkEnd) > 0 && columnCounter < EXCEL_MAX_COLUMNS) {
            increment(chunkEnd, summaryInterval);
            if (chunkEnd.isAfter(reportEnd)) {
                chunkEnd.setMillis(reportEnd);
            }

            // offset range by one millisecond so rrd will calculate granularity correctly
            chunkEnd.addMillis(-1);
            MetricData metricData = getMetricData(getRrdFilename(metricsDir, metricName),
                    TimeUnit.MILLISECONDS.toSeconds(chunkStart.getMillis()),
                    TimeUnit.MILLISECONDS.toSeconds(chunkEnd.getMillis()));
            isSum = metricData.hasTotalCount();
            chunkEnd.addMillis(1);

            if (headingRow.getCell(columnCounter) == null) {
                Cell headingRowCell = headingRow.createCell(columnCounter);
                headingRowCell.getCellStyle().setWrapText(true);
                headingRowCell.setCellValue(getTimestamp(chunkStart, chunkEnd, columnCounter, summaryInterval));
            }

            Cell sumOrAvg = row.createCell(columnCounter);
            if (isSum) {
                sumOrAvg.setCellValue((double) metricData.getTotalCount());
            } else {
                sumOrAvg.setCellValue(cumulativeRunningAverage(metricData.getValues()));
            }

            chunkStart.setMillis(chunkEnd);
            columnCounter++;
        }
        columnMax = columnCounter;

        if (isSum != null) {
            row.createCell(0).setCellValue(convertCamelCase(metricName) + " (" + (isSum ? "sum" : "avg") + ")");
        }
    }
    for (int i = 0; i < columnMax; i++) {
        sheet.autoSizeColumn(i);
    }
}