List of usage examples for org.apache.poi.ss.usermodel Workbook getActiveSheetIndex
int getActiveSheetIndex();
From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java
License:Open Source License
/** * Returns the increment between all consecutive cells in row with rIndex * from column c1 to column c2//from w w w . ja va2 s . c om * * @param rIndex * Row index for the sequence recognition, 1-based * @param c1 * First column of the row to be considered, 1-based * @param c2 * Last column of the row to be considered, 1-based * @return common difference or null */ private Double getRowSequenceIncrement(int rIndex, int c1, int c2) { Double result = null; Workbook workbook = spreadsheet.getWorkbook(); final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); final Row row = activeSheet.getRow(rIndex - 1); if (row != null) { Cell firstCell = row.getCell(c1 - 1); if (firstCell != null) { if (firstCell.getCellType() == CELL_TYPE_STRING) { return getSequenceIncrement(getRowStringValues(row, c1, c2)); } else if (firstCell.getCellType() == CELL_TYPE_NUMERIC) { return getSequenceIncrement(getRowNumericValues(row, c1, c2)); } } } return result; }
From source file:com.vaadin.addon.spreadsheet.CellSelectionShifter.java
License:Open Source License
/** * Returns the increment between all consecutive cells in column with cIndex * from row r1 to row r2//ww w . j a v a2 s .c om * * @param cIndex * Column index for the sequence recognition, 1-based * * @param r1 * First row of the column to be considered, 1-based * @param r2 * Last row of the column to be considered, 1-based * @return common difference or null */ private Double getColumnSequenceIncrement(int cIndex, int r1, int r2) { Double result = null; Workbook workbook = spreadsheet.getWorkbook(); final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); final Row row = activeSheet.getRow(r1 - 1); if (row != null) { Cell firstCell = row.getCell(cIndex - 1); if (firstCell != null) { if (firstCell.getCellType() == CELL_TYPE_STRING) { return getSequenceIncrement(getColumnStringValues(activeSheet, cIndex, r1, r2)); } else if (firstCell.getCellType() == CELL_TYPE_NUMERIC) { return getSequenceIncrement(getColumnNumericValues(activeSheet, cIndex, r1, r2)); } } } return result; }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Updates the cell value and type, causes a recalculation of all the values * in the cell./* ww w. j a va 2s . com*/ * * If there is a {@link CellValueHandler} defined, then it is used. * * Cells starting with "=" or "+" will be created/changed into FORMULA type. * * Cells that are existing and are NUMERIC type will be parsed according to * their existing format, or if that fails, as Double. * * Cells not containing any letters and containing at least one number will * be created/changed into NUMERIC type (formatting is not changed). * * Existing Boolean cells will be parsed as Boolean. * * For everything else and if any of the above fail, the cell will get the * STRING type and the value will just be a string, except empty values will * cause the cell type to be BLANK. * * @param col * Column index of target cell, 1-based * @param row * Row index of target cell, 1-based * @param value * The new value to set to the target cell, formulas will start * with an extra "=" or "+" */ public void onCellValueChange(int col, int row, String value) { Workbook workbook = spreadsheet.getWorkbook(); // update cell value final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Row r = activeSheet.getRow(row - 1); if (r == null) { r = activeSheet.createRow(row - 1); } Cell cell = r.getCell(col - 1); String formattedCellValue = null; int oldCellType = -1; // capture cell value to history CellValueCommand command = new CellValueCommand(spreadsheet); command.captureCellValues(new CellReference(row - 1, col - 1)); spreadsheet.getSpreadsheetHistoryManager().addCommand(command); boolean updateHyperlinks = false; if (getCustomCellValueHandler() == null || getCustomCellValueHandler().cellValueUpdated(cell, activeSheet, col - 1, row - 1, value, getFormulaEvaluator(), formatter)) { Exception exception = null; try { // handle new cell creation SpreadsheetStyleFactory styler = spreadsheet.getSpreadsheetStyleFactory(); final Locale spreadsheetLocale = spreadsheet.getLocale(); if (cell == null) { cell = r.createCell(col - 1); } else { // modify existing cell, possibly switch type formattedCellValue = getFormattedCellValue(cell); final String key = SpreadsheetUtil.toKey(col, row); oldCellType = cell.getCellType(); if (!sentCells.remove(key)) { sentFormulaCells.remove(key); } // Old value was hyperlink => needs refresh if (cell.getCellType() == Cell.CELL_TYPE_FORMULA && cell.getCellFormula().startsWith("HYPERLINK")) { updateHyperlinks = true; } } if (formulaFormatter.isFormulaFormat(value)) { if (formulaFormatter.isValidFormulaFormat(value, spreadsheetLocale)) { spreadsheet.removeInvalidFormulaMark(col, row); getFormulaEvaluator().notifyUpdateCell(cell); cell.setCellType(Cell.CELL_TYPE_FORMULA); cell.setCellFormula( formulaFormatter.unFormatFormulaValue(value.substring(1), spreadsheetLocale)); getFormulaEvaluator().notifySetFormula(cell); if (value.startsWith("=HYPERLINK(") && cell.getCellStyle().getIndex() != hyperlinkStyleIndex) { // set the cell style to link cell CellStyle hyperlinkCellStyle; if (hyperlinkStyleIndex == -1) { hyperlinkCellStyle = styler.createHyperlinkCellStyle(); hyperlinkStyleIndex = -1; } else { hyperlinkCellStyle = workbook.getCellStyleAt(hyperlinkStyleIndex); } cell.setCellStyle(hyperlinkCellStyle); styler.cellStyleUpdated(cell, true); updateHyperlinks = true; } } else { // it's formula but invalid cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); spreadsheet.markInvalidFormula(col, row); } } else { spreadsheet.removeInvalidFormulaMark(col, row); Double percentage = SpreadsheetUtil.parsePercentage(value, spreadsheetLocale); Double numVal = SpreadsheetUtil.parseNumber(cell, value, spreadsheetLocale); if (value.isEmpty()) { cell.setCellType(Cell.CELL_TYPE_BLANK); } else if (percentage != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); CellStyle cs = cell.getCellStyle(); if (cs == null) { cs = workbook.createCellStyle(); cell.setCellStyle(cs); } if (cs.getDataFormatString() != null && !cs.getDataFormatString().contains("%")) { cs.setDataFormat(workbook.createDataFormat() .getFormat(spreadsheet.getDefaultPercentageFormat())); styler.cellStyleUpdated(cell, true); } cell.setCellValue(percentage); } else if (numVal != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numVal); } else if (oldCellType == Cell.CELL_TYPE_BOOLEAN) { cell.setCellValue(Boolean.parseBoolean(value)); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); } getFormulaEvaluator().notifyUpdateCell(cell); } } catch (FormulaParseException fpe) { try { exception = fpe; // parses formula cell.setCellFormula(value.substring(1).replace(" ", "")); } catch (FormulaParseException fpe2) { exception = fpe2; /* * We could force storing the formula even if it is invalid. * Instead, just store it as the value. Clearing the formula * makes sure the value is displayed as-is. */ cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); spreadsheet.markInvalidFormula(col, row); } } catch (NumberFormatException nfe) { exception = nfe; cell.setCellValue(value); } catch (Exception e) { exception = e; cell.setCellValue(value); } if (cell != null) { markCellForUpdate(cell); if (formattedCellValue == null || !formattedCellValue.equals(getFormattedCellValue(cell)) || oldCellType != cell.getCellType()) { fireCellValueChangeEvent(cell); } } if (exception != null) { LOGGER.log(Level.FINE, "Failed to parse cell value for cell at col " + col + " row " + row + " (" + exception.getMessage() + ")", exception); } } spreadsheet.updateMarkedCells(); if (updateHyperlinks) { spreadsheet.loadHyperLinks(); } }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Checks whether the default deletion handling should be performed for the * selected cell or whether a custom deletion handler takes care of * everything.// w ww. ja v a 2 s. com * * @param selectedCellReference * @return {@code true} if the default handling should be performed, * {@code false} otherwise */ private boolean passesDeletionCheck(CellReference selectedCellReference) { if (selectedCellReference == null || customCellDeletionHandler == null) { return true; } final Workbook workbook = spreadsheet.getWorkbook(); final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); int rowIndex = selectedCellReference.getRow(); final Row row = activeSheet.getRow(rowIndex); if (row != null) { short colIndex = selectedCellReference.getCol(); final Cell cell = row.getCell(colIndex); if (cell != null) { return customCellDeletionHandler.cellDeleted(cell, activeSheet, colIndex, rowIndex, getFormulaEvaluator(), formatter); } } return true; }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Checks whether the default deletion handling should be performed for the * individually selected cells or whether a custom deletion handler takes * care of everything./*from ww w.j a va2 s. c om*/ * * @param individualSelectedCells * @return {@code true} if the default handling should be performed, * {@code false} otherwise */ private boolean passesDeletionCheck(List<CellReference> individualSelectedCells) { if (individualSelectedCells.isEmpty() || customCellDeletionHandler == null) { return true; } final Workbook workbook = spreadsheet.getWorkbook(); final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); return customCellDeletionHandler.individualSelectedCellsDeleted(individualSelectedCells, activeSheet, getFormulaEvaluator(), formatter); }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Checks whether the default deletion handling should be performed for the * cell range or whether a custom deletion handler takes care of everything. * /*from ww w .j a v a 2s . co m*/ * @param cellRangeAddresses * @return {@code true} if the default handling should be performed, * {@code false} otherwise */ private boolean passesRangeDeletionCheck(List<CellRangeAddress> cellRangeAddresses) { if (cellRangeAddresses.isEmpty() || customCellDeletionHandler == null) { return true; } final Workbook workbook = spreadsheet.getWorkbook(); final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); return customCellDeletionHandler.cellRangeDeleted(cellRangeAddresses, activeSheet, getFormulaEvaluator(), formatter); }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Gets cell data for cells within the given bounds. * * @param firstRow/*from w w w . j a va2 s . c om*/ * Starting row index, 1-based * @param firstColumn * Starting column index, 1-based * @param lastRow * Ending row index, 1-based * @param lastColumn * Ending column index, 1-based * @return A list of CellData for the cells in the given area. */ protected ArrayList<CellData> loadCellDataForRowAndColumnRange(int firstRow, int firstColumn, int lastRow, int lastColumn) { ArrayList<CellData> cellData = new ArrayList<CellData>(); Workbook workbook = spreadsheet.getWorkbook(); final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Map<String, String> componentIDtoCellKeysMap = spreadsheet.getState(false).componentIDtoCellKeysMap; @SuppressWarnings("unchecked") final Collection<String> customComponentCells = (Collection<String>) (componentIDtoCellKeysMap == null ? Collections.emptyList() : componentIDtoCellKeysMap.values()); for (int r = firstRow - 1; r < lastRow; r++) { Row row = activeSheet.getRow(r); if (row != null && row.getLastCellNum() != -1 && row.getLastCellNum() >= firstColumn) { for (int c = firstColumn - 1; c < lastColumn; c++) { final String key = SpreadsheetUtil.toKey(c + 1, r + 1); if (!customComponentCells.contains(key) && !sentCells.contains(key) && !sentFormulaCells.contains(key)) { Cell cell = row.getCell(c); if (cell != null) { final CellData cd = createCellDataForCell(cell); if (cd != null) { int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) { sentFormulaCells.add(key); } else { sentCells.add(key); } cellData.add(cd); } } } } } } return cellData; }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Removes all the cells within the given bounds from the Spreadsheet and * the underlying POI model./*from ww w . j a v a 2s . c o m*/ * * @param firstRow * Starting row index, 1-based * @param firstColumn * Starting column index, 1-based * @param lastRow * Ending row index, 1-based * @param lastColumn * Ending column index, 1-based * @param clearRemovedCellStyle * true to also clear styles from the removed cells */ protected void removeCells(int firstRow, int firstColumn, int lastRow, int lastColumn, boolean clearRemovedCellStyle) { final Workbook workbook = spreadsheet.getWorkbook(); final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); for (int i = firstRow - 1; i < lastRow; i++) { Row row = activeSheet.getRow(i); if (row != null) { for (int j = firstColumn - 1; j < lastColumn; j++) { Cell cell = row.getCell(j); if (cell != null) { final String key = SpreadsheetUtil.toKey(j + 1, i + 1); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { sentFormulaCells.remove(key); } else { sentCells.remove(key); } if (cell.getHyperlink() != null) { removeHyperlink(cell, activeSheet); } if (clearRemovedCellStyle) { // update style to 0 cell.setCellStyle(null); spreadsheet.getSpreadsheetStyleFactory().cellStyleUpdated(cell, true); } // need to make protection etc. settings for the cell // won't get effected. deleting the cell would make it // locked if (clearRemovedCellStyle || cell.getCellStyle().getIndex() == 0) { CellData cd = new CellData(); cd.col = j + 1; cd.row = i + 1; removedCells.add(cd); } else { markedCells.add(key); } cell.setCellValue((String) null); getFormulaEvaluator().notifyUpdateCell(cell); } } } } }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Removes an individual cell from the Spreadsheet and the underlying POI * model.//ww w .j ava2 s . c om * * @param rowIndex * Row index of target cell, 1-based * @param colIndex * Column index of target cell, 1-based * @param clearRemovedCellStyle * true to also clear styles from the removed cell */ protected void removeCell(int rowIndex, int colIndex, boolean clearRemovedCellStyle) { final Workbook workbook = spreadsheet.getWorkbook(); final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); final Row row = activeSheet.getRow(rowIndex - 1); if (row != null) { final Cell cell = row.getCell(colIndex - 1); if (cell != null) { CellData cd = new CellData(); cd.col = colIndex; cd.row = rowIndex; final String key = SpreadsheetUtil.toKey(colIndex, rowIndex); if (clearRemovedCellStyle || cell.getCellStyle().getIndex() == 0) { removedCells.add(cd); } else { markedCells.add(key); } if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { sentFormulaCells.remove(key); } else { sentCells.remove(key); } // POI (3.9) doesn't have a method for removing a hyperlink !!! if (cell.getHyperlink() != null) { removeHyperlink(cell, activeSheet); } if (clearRemovedCellStyle) { // update style to 0 cell.setCellStyle(null); spreadsheet.getSpreadsheetStyleFactory().cellStyleUpdated(cell, true); } cell.setCellValue((String) null); getFormulaEvaluator().notifyUpdateCell(cell); } } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java
License:Open Source License
/** * Clears the given Spreadsheet and loads the given Workbook into it. * * @param spreadsheet/*from www . j a va2 s . c om*/ * Target Spreadsheet * @param workbook * Workbook to load or null to generate a new workbook with one * sheet. * @param rowCount * Number of rows to generate in the first sheet. Only applies * when the workbook parameter is null. * @param columnCount * Number of columns to generate in the first sheet. Only applies * when the workbook parameter is null. */ static void loadSpreadsheetWith(Spreadsheet spreadsheet, Workbook workbook, int rowCount, int columnCount) { spreadsheet.clearSheetServerSide(); final Sheet sheet; if (workbook == null) { workbook = new XSSFWorkbook(); sheet = createNewSheet(workbook); spreadsheet.setInternalWorkbook(workbook); generateNewSpreadsheet(spreadsheet, sheet, rowCount, columnCount); } else { int activeSheetIndex = workbook.getActiveSheetIndex(); if (workbook.isSheetHidden(activeSheetIndex) || workbook.isSheetVeryHidden(activeSheetIndex)) { workbook.setActiveSheet(SpreadsheetUtil.getFirstVisibleSheetPOIIndex(workbook)); } sheet = workbook.getSheetAt(activeSheetIndex); spreadsheet.setInternalWorkbook(workbook); reloadSpreadsheetData(spreadsheet, sheet); } loadWorkbookStyles(spreadsheet); }