Java tutorial
package com.vaadin.addon.spreadsheet; /* * #%L * Vaadin Spreadsheet * %% * Copyright (C) 2013 - 2015 Vaadin Ltd * %% * This program is available under Commercial Vaadin Add-On License 3.0 * (CVALv3). * * See the file license.html distributed with this software for more * information about licensing. * * You should have received a copy of the CVALv3 along with this program. * If not, see <http://vaadin.com/license/cval-3>. * #L% */ import java.io.File; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.Serializable; import java.lang.reflect.Method; import java.net.MalformedURLException; import java.net.URL; import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; import java.util.Collection; import java.util.Date; import java.util.EventObject; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.poi.hssf.converter.AbstractExcelUtils; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Hyperlink; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeUtil; import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.util.PaneInformation; import org.apache.poi.ss.util.WorkbookUtil; import org.apache.poi.xssf.usermodel.XSSFHyperlink; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.xmlbeans.impl.values.XmlValueDisconnectedException; import org.jsoup.nodes.Attributes; import org.jsoup.nodes.Element; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; import com.vaadin.addon.spreadsheet.SheetOverlayWrapper.OverlayChangeListener; import com.vaadin.addon.spreadsheet.action.SpreadsheetDefaultActionHandler; import com.vaadin.addon.spreadsheet.client.MergedRegion; import com.vaadin.addon.spreadsheet.client.MergedRegionUtil.MergedRegionContainer; import com.vaadin.addon.spreadsheet.client.OverlayInfo; import com.vaadin.addon.spreadsheet.client.SpreadsheetClientRpc; import com.vaadin.addon.spreadsheet.command.SizeChangeCommand; import com.vaadin.addon.spreadsheet.command.SizeChangeCommand.Type; import com.vaadin.addon.spreadsheet.shared.GroupingData; import com.vaadin.addon.spreadsheet.shared.SpreadsheetState; import com.vaadin.event.Action; import com.vaadin.event.Action.Handler; import com.vaadin.server.Resource; import com.vaadin.ui.AbstractComponent; import com.vaadin.ui.Component; import com.vaadin.ui.Component.Focusable; import com.vaadin.ui.HasComponents; import com.vaadin.ui.declarative.DesignAttributeHandler; import com.vaadin.ui.declarative.DesignContext; import com.vaadin.util.ReflectTools; /** * Vaadin Spreadsheet is a Vaadin Add-On Component which allows displaying and * interacting with the contents of an Excel file. The Spreadsheet can be used * in any Vaadin application for enabling users to view and manipulate Excel * files in their web browsers. * * @author Vaadin Ltd. */ @SuppressWarnings("serial") public class Spreadsheet extends AbstractComponent implements HasComponents, Action.Container, Focusable { /** * Minimum row height for rows containing components (in points). */ private static final int MINIMUM_ROW_HEIGHT_FOR_COMPONENTS = 30; /** * This is a style which hides the top (address and formula) bar. */ public static final String HIDE_FUNCTION_BAR_STYLE = "hidefunctionbar"; /** * This is a style which hides the bottom (sheet selection) bar. */ public static final String HIDE_TABSHEET_STYLE = "hidetabsheet"; private static final Logger LOGGER = Logger.getLogger(Spreadsheet.class.getName()); /** * A common formula evaluator for this Spreadsheet */ private FormulaEvaluator formulaEvaluator; /** * An interface for handling the edited cell value from user input. */ public interface CellValueHandler extends Serializable { /** * Called if a cell value has been edited by the user by using the * default cell editor. Use * {@link Spreadsheet#setCellValueHandler(CellValueHandler)} to enable * it for the spreadsheet. * * @param cell * The cell that has been edited, may be <code>null</code> if * the cell doesn't yet exists * @param sheet * The sheet the cell belongs to, the currently active sheet * @param colIndex * Cell column index, 0-based * @param rowIndex * Cell row index, 0-based * @param newValue * The value user has entered * @param formulaEvaluator * The {@link FormulaEvaluator} for this sheet * @param formatter * The {@link DataFormatter} for this workbook * @return <code>true</code> if component default parsing should still * be done, <code>false</code> if not */ public boolean cellValueUpdated(Cell cell, Sheet sheet, int colIndex, int rowIndex, String newValue, FormulaEvaluator formulaEvaluator, DataFormatter formatter); } /** * An interface for handling cell deletion from user input. */ public interface CellDeletionHandler extends Serializable { /** * Called if a cell value has been deleted by the user. Use * {@link Spreadsheet#setCellDeletionHandler(CellDeletionHandler)} to * enable it for the spreadsheet. * * @param cell * The cell that has been deleted * @param sheet * The sheet the cell belongs to, the currently active sheet * @param colIndex * Cell column index, 0-based * @param rowIndex * Cell row index, 0-based * @param formulaEvaluator * The {@link FormulaEvaluator} for this sheet * @param formatter * The {@link DataFormatter} for this workbook * @return <code>true</code> if component default deletion should still * be done, <code>false</code> if not */ public boolean cellDeleted(Cell cell, Sheet sheet, int colIndex, int rowIndex, FormulaEvaluator formulaEvaluator, DataFormatter formatter); /** * Called if individually selected cell values have been deleted by the * user. Use * {@link Spreadsheet#setCellDeletionHandler(CellDeletionHandler)} to * enable it for the spreadsheet. * * @param individualSelectedCells * The cells that have been deleted * @param sheet * The sheet the cells belong to, the currently active sheet * @param formulaEvaluator * The {@link FormulaEvaluator} for this sheet * @param formatter * The {@link DataFormatter} for this workbook * @return <code>true</code> if component default deletion should still * be done, <code>false</code> if not */ public boolean individualSelectedCellsDeleted(List<CellReference> individualSelectedCells, Sheet sheet, FormulaEvaluator formulaEvaluator, DataFormatter formatter); /** * Called if a cell range has been deleted by the user. Use * {@link Spreadsheet#setCellDeletionHandler(CellDeletionHandler)} to * enable it for the spreadsheet. * * @param cellRangeAddresses * The range of cells that has been deleted * @param sheet * The sheet the cells belongs to, the currently active sheet * @param formulaEvaluator * The {@link FormulaEvaluator} for this sheet * @param formatter * The {@link DataFormatter} for this workbook * @return <code>true</code> if component default deletion should still * be done, <code>false</code> if not */ public boolean cellRangeDeleted(List<CellRangeAddress> cellRangeAddresses, Sheet sheet, FormulaEvaluator formulaEvaluator, DataFormatter formatter); } /** * An interface for handling clicks on cells that contain a hyperlink. * <p> * Implement this interface and use * {@link Spreadsheet#setHyperlinkCellClickHandler(HyperlinkCellClickHandler)} * to enable it for the spreadsheet. */ public interface HyperlinkCellClickHandler extends Serializable { /** * Called when a hyperlink cell has been clicked. * * @param cell * The cell that contains the hyperlink * @param hyperlink * The actual hyperlink * @param spreadsheet * The Spreadsheet the cell is in */ public void onHyperLinkCellClick(Cell cell, Hyperlink hyperlink, Spreadsheet spreadsheet); } private SpreadsheetStyleFactory styler; private HyperlinkCellClickHandler hyperlinkCellClickHandler; private SpreadsheetComponentFactory customComponentFactory; private final CellSelectionManager selectionManager = new CellSelectionManager(this); private final CellValueManager valueManager = new CellValueManager(this); private final CellSelectionShifter cellShifter = new CellSelectionShifter(this); private final ContextMenuManager contextMenuManager = new ContextMenuManager(this); private final SpreadsheetHistoryManager historyManager = new SpreadsheetHistoryManager(this); private ConditionalFormatter conditionalFormatter; /** The first visible row in the scroll area **/ private int firstRow; /** The last visible row in the scroll area **/ private int lastRow; /** The first visible column in the scroll area **/ private int firstColumn; /** The last visible column in the scroll area **/ private int lastColumn; private boolean chartsEnabled = true; /** * This is used for making sure the cells are sent to client side in when * the next cell data request comes. This is triggered when the client side * connector init() method is run. */ private boolean reloadCellDataOnNextScroll; private int defaultNewSheetRows = SpreadsheetFactory.DEFAULT_ROWS; private int defaultNewSheetColumns = SpreadsheetFactory.DEFAULT_COLUMNS; private boolean topLeftCellCommentsLoaded; private boolean topLeftCellHyperlinksLoaded; private SpreadsheetDefaultActionHandler defaultActionHandler; protected int mergedRegionCounter; private Workbook workbook; /** true if the component sheet should be reloaded on client side. */ private boolean reload; /** are tables for currently active sheet loaded */ private boolean tablesLoaded; private SheetState sheetState = new SheetState(this); /** image sizes need to be recalculated on column/row resizing */ private boolean reloadImageSizesFromPOI; private String defaultPercentageFormat = "0.00%"; protected String initialSheetSelection = null; private Set<Component> customComponents = new HashSet<Component>(); private Map<CellReference, PopupButton> sheetPopupButtons = new HashMap<CellReference, PopupButton>(); private HashSet<PopupButton> attachedPopupButtons = new HashSet<PopupButton>(); /** * Set of images contained in the currently active sheet. */ private HashSet<SheetOverlayWrapper> sheetOverlays; private Set<Component> overlayComponents = new HashSet<Component>(); private HashSet<SpreadsheetTable> tables; private final Map<Integer, HashSet<String>> invalidFormulas = new HashMap<Integer, HashSet<String>>(); private String srcUri; private boolean defaultColWidthSet, defaultRowHeightSet; /** * Container for merged regions for the currently active sheet. */ protected final MergedRegionContainer mergedRegionContainer = new MergedRegionContainer() { /* * (non-Javadoc) * * @see com.vaadin.addon.spreadsheet.client.MergedRegionUtil. * MergedRegionContainer#getMergedRegionStartingFrom(int, int) */ @Override public MergedRegion getMergedRegionStartingFrom(int column, int row) { List<MergedRegion> mergedRegions = getState(false).mergedRegions; if (mergedRegions != null) { for (MergedRegion region : mergedRegions) { if (region.col1 == column && region.row1 == row) { return region; } } } return null; } /* * (non-Javadoc) * * @see com.vaadin.addon.spreadsheet.client.MergedRegionUtil. * MergedRegionContainer#getMergedRegion(int, int) */ @Override public MergedRegion getMergedRegion(int column, int row) { List<MergedRegion> mergedRegions = getState(false).mergedRegions; if (mergedRegions != null) { for (MergedRegion region : mergedRegions) { if (region.col1 <= column && region.row1 <= row && region.col2 >= column && region.row2 >= row) { return region; } } } return null; } }; private Set<Integer> rowsWithComponents; /** * Minimum row height for rows containing components (in points). */ private int minimumRowHeightForComponents = 30; /** * Creates a new Spreadsheet component using the newer Excel version format * {@link XSSFWorkbook}. Also creates one sheet using the default row * {@link SpreadsheetFactory#DEFAULT_ROWS} and column * {@link SpreadsheetFactory#DEFAULT_COLUMNS} counts. */ public Spreadsheet() { this(SpreadsheetFactory.DEFAULT_ROWS, SpreadsheetFactory.DEFAULT_COLUMNS); } /** * Creates a new Spreadsheet component using the newer Excel version format * {@link XSSFWorkbook}. Also creates one sheet using the given row and * column counts. These counts will also be set as default for any new * sheets created later. * * @param defaultRowCount * Default row count for new sheets * @param defaultColumnCount * Default column count for new sheets */ public Spreadsheet(int defaultRowCount, int defaultColumnCount) { init(); setDefaultRowCount(defaultRowCount); setDefaultColumnCount(defaultColumnCount); SpreadsheetFactory.loadSpreadsheetWith(this, null, getDefaultRowCount(), getDefaultColumnCount()); } /** * Creates a new Spreadsheet component and loads the given Workbook. * * @param workbook * Workbook to load */ public Spreadsheet(Workbook workbook) { init(); SpreadsheetFactory.loadSpreadsheetWith(this, workbook, getDefaultRowCount(), getDefaultColumnCount()); } /** * Creates a new Spreadsheet component and loads the given Excel file. * * @param file * Excel file * @throws IOException * If file has invalid format or there is no access to the file */ public Spreadsheet(File file) throws IOException { init(); SpreadsheetFactory.reloadSpreadsheetComponent(this, file); srcUri = file.toURI().toString(); } /** * Creates a new Spreadsheet component based on the given input stream. The * expected format is that of an Excel file. * * @param inputStream * Stream that provides Excel-formatted data. * @throws IOException * If there is an error handling the stream, or if the data is * in an invalid format. */ public Spreadsheet(InputStream inputStream) throws IOException { init(); SpreadsheetFactory.reloadSpreadsheetComponent(this, inputStream); } private void init() { sheetOverlays = new HashSet<SheetOverlayWrapper>(); tables = new HashSet<SpreadsheetTable>(); registerRpc(new SpreadsheetHandlerImpl(this)); setSizeFull(); // Default to full size defaultActionHandler = new SpreadsheetDefaultActionHandler(); addActionHandler(defaultActionHandler); } /** * Adds an action handler to the spreadsheet that handles the event produced * by the context menu (right click) on cells and row and column headers. * The action handler is component, not workbook, specific. * <p> * The parameters on the * {@link Handler#handleAction(Action, Object, Object)} and * {@link Handler#getActions(Object, Object)} depend on the actual target of * the right click. * <p> * The second parameter (sender) on * {@link Handler#getActions(Object, Object)} is always the spreadsheet * component. In case of a cell, the first parameter (target) on contains * the latest {@link SelectionChangeEvent} for the spreadsheet. In case of a * row or a column header, the first parameter (target) is a * {@link CellRangeAddress}. To distinct between column / row header, you * can use {@link CellRangeAddress#isFullColumnRange()} and * {@link CellRangeAddress#isFullRowRange()}. * <p> * Similarly for {@link Handler#handleAction(Action, Object, Object)} the * second parameter (sender) is always the spreadsheet component. The third * parameter (target) is the latest {@link SelectionChangeEvent} for the * spreadsheet, or the {@link CellRangeAddress} defining the selected row / * column header. */ @Override public void addActionHandler(Handler actionHandler) { contextMenuManager.addActionHandler(actionHandler); getState().hasActions = contextMenuManager.hasActionHandlers(); } /** * Removes the spreadsheet's {@link SpreadsheetDefaultActionHandler} added * on {@link Spreadsheet#init()} */ public void removeDefaultActionHandler() { removeActionHandler(defaultActionHandler); } /* * (non-Javadoc) * * @see * com.vaadin.event.Action.Container#removeActionHandler(com.vaadin.event * .Action.Handler) */ @Override public void removeActionHandler(Handler actionHandler) { contextMenuManager.removeActionHandler(actionHandler); getState().hasActions = contextMenuManager.hasActionHandlers(); } /** * Sets the {@link CellValueHandler} for this component (not workbook/sheet * specific). It is called when a cell's value has been updated by the user * by using the spreadsheet component's default editor (text input). * * @param customCellValueHandler * New handler or <code>null</code> if none should be used */ public void setCellValueHandler(CellValueHandler customCellValueHandler) { getCellValueManager().setCustomCellValueHandler(customCellValueHandler); } /** * See {@link CellValueHandler}. * * @return the current {@link CellValueHandler} for this component or * <code>null</code> if none has been set */ public CellValueHandler getCellValueHandler() { return getCellValueManager().getCustomCellValueHandler(); } /** * Sets the {@link CellDeletionHandler} for this component (not * workbook/sheet specific). It is called when a cell has been deleted by * the user. * * @param customCellDeletionHandler * New handler or <code>null</code> if none should be used */ public void setCellDeletionHandler(CellDeletionHandler customCellDeletionHandler) { getCellValueManager().setCustomCellDeletionHandler(customCellDeletionHandler); } /** * See {@link CellDeletionHandler}. * * @return the current {@link CellDeletionHandler} for this component or * <code>null</code> if none has been set */ public CellDeletionHandler getCellDeletionHandler() { return getCellValueManager().getCustomCellDeletionHandler(); } /** * Sets the {@link HyperlinkCellClickHandler} for this component (not * workbook/sheet specific). It's called when the user click a cell that is * a hyperlink. * * @param hyperLinkCellClickHandler * New handler or <code>null</code> if none should be used */ public void setHyperlinkCellClickHandler(HyperlinkCellClickHandler hyperLinkCellClickHandler) { hyperlinkCellClickHandler = hyperLinkCellClickHandler; } /** * See {@link HyperlinkCellClickHandler}. * * @return the current {@link HyperlinkCellClickHandler} for this component * or <code>null</code> if none has been set */ public HyperlinkCellClickHandler getHyperlinkCellClickHandler() { return hyperlinkCellClickHandler; } /** * Gets the ContextMenuManager for this Spreadsheet. This is component (not * workbook/sheet) specific. * * @return The ContextMenuManager */ public ContextMenuManager getContextMenuManager() { return contextMenuManager; } /** * Gets the CellSelectionManager for this Spreadsheet. This is component * (not workbook/sheet) specific. * * @return The CellSelectionManager */ public CellSelectionManager getCellSelectionManager() { return selectionManager; } /** * Gets the CellValueManager for this Spreadsheet. This is component (not * workbook/sheet) specific. * * @return The CellValueManager */ public CellValueManager getCellValueManager() { return valueManager; } /** * Gets the CellShifter for this Spreadsheet. This is component (not * workbook/sheet) specific. * * @return The CellShifter */ protected CellSelectionShifter getCellShifter() { return cellShifter; } /** * Gets the SpreadsheetHistoryManager for this Spreadsheet. This is * component (not workbook/sheet) specific. * * @return The SpreadsheetHistoryManager */ public SpreadsheetHistoryManager getSpreadsheetHistoryManager() { return historyManager; } /** * Gets the MergedRegionContainer for this Spreadsheet. This is component * (not workbook/sheet) specific. * * @return The MergedRegionContainer */ protected MergedRegionContainer getMergedRegionContainer() { return mergedRegionContainer; } /** * Returns the first visible column in the main scroll area (NOT freeze * pane) * * @return Index of first visible column, 1-based */ public int getFirstColumn() { return firstColumn; } /** * Returns the last visible column in the main scroll area (NOT freeze pane) * * @return Index of last visible column, 1-based */ public int getLastColumn() { return lastColumn; } /** * Returns the first visible row in the scroll area (not freeze pane) * * @return Index of first visible row, 1-based */ public int getFirstRow() { return firstRow; } /** * Returns the last visible row in the main scroll area (NOT freeze pane) * * @return Index of last visible row, 1-based */ public int getLastRow() { return lastRow; } /** * Returns the index the last frozen row (last row in top freeze pane). * * @return Last frozen row or 0 if none */ public int getLastFrozenRow() { return getState(false).verticalSplitPosition; } /** * Returns the index the last frozen column (last column in left freeze * pane). * * @return Last frozen column or 0 if none */ public int getLastFrozenColumn() { return getState(false).horizontalSplitPosition; } /** * Returns true if embedded charts are displayed * * @see #setChartsEnabled(boolean) * @return */ public boolean isChartsEnabled() { return chartsEnabled; } /** * Use this method to define whether embedded charts should be displayed in * the spreadsheet or not. * * @param chartsEnabled */ public void setChartsEnabled(boolean chartsEnabled) { this.chartsEnabled = chartsEnabled; clearSheetOverlays(); loadOrUpdateOverlays(); } /** * Returns true if the component is being fully re-rendered after this * round-trip (sheet change etc.) * * @return true if re-render will happen, false otherwise */ public boolean isRerenderPending() { return reload; } /* * (non-Javadoc) * * @see * com.vaadin.server.AbstractClientConnector#fireEvent(java.util.EventObject * ) */ @Override protected void fireEvent(EventObject event) { super.fireEvent(event); } /** * This method is called when the sheet is scrolled. It takes care of * sending newly revealed data to the client side. * * @param firstRow * Index of first visible row after the scroll, 1-based * @param firstColumn * Index of first visible column after the scroll, 1-based * @param lastRow * Index of last visible row after the scroll, 1-based * @param lastColumn * Index of first visible column after the scroll, 1-based */ protected void onSheetScroll(int firstRow, int firstColumn, int lastRow, int lastColumn) { if (reloadCellDataOnNextScroll || this.firstRow != firstRow || this.lastRow != lastRow || this.firstColumn != firstColumn || this.lastColumn != lastColumn) { this.firstRow = firstRow; this.lastRow = lastRow; this.firstColumn = firstColumn; this.lastColumn = lastColumn; loadCells(firstRow, firstColumn, lastRow, lastColumn); } if (initialSheetSelection != null) { selectionManager.onSheetAddressChanged(initialSheetSelection, true); initialSheetSelection = null; } else if (reloadCellDataOnNextScroll) { selectionManager.reloadCurrentSelection(); } reloadCellDataOnNextScroll = false; } /** * Tells whether the given cell range is editable or not. * * @param cellRangeAddress * Cell range to test * @return True if range is editable, false otherwise. */ protected boolean isRangeEditable(CellRangeAddress cellRangeAddress) { return isRangeEditable(cellRangeAddress.getFirstRow(), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastRow(), cellRangeAddress.getLastColumn()); } /** * Determines if the given cell range is editable or not. * * @param row1 * Index of starting row, 0-based * @param col1 * Index of starting column, 0-based * @param row2 * Index of ending row, 0-based * @param col2 * Index of ending column, 0-based * * @return True if the whole range is editable, false otherwise. */ protected boolean isRangeEditable(int row1, int col1, int row2, int col2) { if (isActiveSheetProtected()) { for (int r = row1; r <= row2; r++) { final Row row = getActiveSheet().getRow(r); if (row != null) { for (int c = col1; c <= col2; c++) { final Cell cell = row.getCell(c); if (isCellLocked(cell)) { return false; } } } else { return false; } } } return true; } /** * Creates a CellRangeAddress from the given cell address string. Also * checks that the range is valid within the currently active sheet. If it * is not, the resulting range will be truncated to fit the active sheet. * * @param addressString * Cell address string, e.g. "B3:C5" * @return A CellRangeAddress based on the given coordinates. */ protected CellRangeAddress createCorrectCellRangeAddress(String addressString) { final String[] split = addressString.split(":"); final CellReference cr1 = new CellReference(split[0]); final CellReference cr2 = new CellReference(split[1]); int r1 = cr1.getRow() > cr2.getRow() ? cr2.getRow() : cr1.getRow(); int r2 = cr1.getRow() > cr2.getRow() ? cr1.getRow() : cr2.getRow(); int c1 = cr1.getCol() > cr2.getCol() ? cr2.getCol() : cr1.getCol(); int c2 = cr1.getCol() > cr2.getCol() ? cr1.getCol() : cr2.getCol(); if (r1 >= getState().rows) { r1 = getState().rows - 1; } if (r2 >= getState().rows) { r2 = getState().rows - 1; } if (c1 >= getState().cols) { c1 = getState().cols - 1; } if (c2 >= getState().cols) { c2 = getState().cols - 1; } return new CellRangeAddress(r1, r2, c1, c2); } /** * Creates a CellRangeAddress from the given start and end coordinates. Also * checks that the range is valid within the currently active sheet. If it * is not, the resulting range will be truncated to fit the active sheet. * * @param row1 * Index of the starting row, 1-based * @param col1 * Index of the starting column, 1-based * @param row2 * Index of the ending row, 1-based * @param col2 * Index of the ending column, 1-based * * @return A CellRangeAddress based on the given coordinates. */ protected CellRangeAddress createCorrectCellRangeAddress(int row1, int col1, int row2, int col2) { int r1 = row1 > row2 ? row2 : row1; int r2 = row1 > row2 ? row1 : row2; int c1 = col1 > col2 ? col2 : col1; int c2 = col1 > col2 ? col1 : col2; if (r1 >= getState().rows) { r1 = getState().rows; } if (r2 >= getState().rows) { r2 = getState().rows; } if (c1 >= getState().cols) { c1 = getState().cols; } if (c2 >= getState().cols) { c2 = getState().cols; } return new CellRangeAddress(r1 - 1, r2 - 1, c1 - 1, c2 - 1); } /* * (non-Javadoc) * * @see com.vaadin.ui.AbstractComponent#getState() */ @Override protected SpreadsheetState getState() { return (SpreadsheetState) super.getState(); } /* * (non-Javadoc) * * @see com.vaadin.ui.AbstractComponent#getState(boolean) */ @Override protected SpreadsheetState getState(boolean markAsDirty) { return (SpreadsheetState) super.getState(markAsDirty); } /* * (non-Javadoc) * * @see com.vaadin.ui.AbstractComponent#setLocale(java.util.Locale) */ @Override public void setLocale(Locale locale) { super.setLocale(locale); valueManager.updateLocale(locale); refreshAllCellValues(); } @Override public void attach() { super.attach(); valueManager.updateLocale(getLocale()); } /** * See {@link Workbook#setSheetHidden(int, int)}. * <p> * Gets the Workbook with {@link #getWorkbook()} and uses its API to access * status on currently visible/hidden/very hidden sheets. * * If the currently active sheet is set hidden, another sheet is set as * active sheet automatically. At least one sheet should be always visible. * * @param hidden * Visibility state to set: 0-visible, 1-hidden, 2-very hidden. * @param sheetPOIIndex * Index of the target sheet within the POI model, 0-based * @throws IllegalArgumentException * If the index or state is invalid, or if trying to hide the * only visible sheet. */ public void setSheetHidden(int sheetPOIIndex, int hidden) throws IllegalArgumentException { // POI allows user to hide all sheets ... if (hidden != 0 && SpreadsheetUtil.getNumberOfVisibleSheets(workbook) == 1 && !workbook.isSheetHidden(sheetPOIIndex)) { throw new IllegalArgumentException("At least one sheet should be always visible."); } boolean isHidden = workbook.isSheetHidden(sheetPOIIndex); boolean isVeryHidden = workbook.isSheetVeryHidden(sheetPOIIndex); int activeSheetIndex = workbook.getActiveSheetIndex(); workbook.setSheetHidden(sheetPOIIndex, hidden); // skip component reload if "nothing changed" if (hidden == 0 && (isHidden || isVeryHidden) || hidden != 0 && !(isHidden && isVeryHidden)) { if (sheetPOIIndex != activeSheetIndex) { reloadSheetNames(); getState().sheetIndex = getSpreadsheetSheetIndex(activeSheetIndex) + 1; } else { // the active sheet can be only set as hidden int oldVisibleSheetIndex = getState().sheetIndex - 1; if (hidden != 0 && activeSheetIndex == (workbook.getNumberOfSheets() - 1)) { // hiding the active sheet, and it was the last sheet oldVisibleSheetIndex--; } int newActiveSheetIndex = getVisibleSheetPOIIndex(oldVisibleSheetIndex); workbook.setActiveSheet(newActiveSheetIndex); reloadActiveSheetData(); SpreadsheetFactory.reloadSpreadsheetData(this, getActiveSheet()); } } } /** * Returns an array containing the names of the currently visible sheets. * Does not contain the names of hidden or very hidden sheets. * <p> * To get all of the current {@link Workbook}'s sheet names, you should * access the POI API with {@link #getWorkbook()}. * * @return Names of the currently visible sheets. */ public String[] getVisibleSheetNames() { final String[] names = getState(false).sheetNames; return Arrays.copyOf(names, names.length); } /** * Sets a name for the sheet at the given visible sheet index. * * @param sheetIndex * Index of the target sheet among the visible sheets, 0-based * @param sheetName * New sheet name. Not null, empty nor longer than 31 characters. * Must be unique within the Workbook. * @throws IllegalArgumentException * If the index is invalid, or if the sheet name is invalid. See * {@link WorkbookUtil#validateSheetName(String)}. */ public void setSheetName(int sheetIndex, String sheetName) throws IllegalArgumentException { if (sheetIndex < 0 || sheetIndex >= getState().sheetNames.length) { throw new IllegalArgumentException("Invalid Sheet index given."); } int poiSheetIndex = getVisibleSheetPOIIndex(sheetIndex); setSheetNameWithPOIIndex(poiSheetIndex, sheetName); } /** * Sets a name for the sheet at the given POI model index. * * @param sheetIndex * Index of the target sheet within the POI model, 0-based * @param sheetName * New sheet name. Not null, empty nor longer than 31 characters. * Must be unique within the Workbook. * @throws IllegalArgumentException * If the index is invalid, or if the sheet name is invalid. See * {@link WorkbookUtil#validateSheetName(String)}. * */ public void setSheetNameWithPOIIndex(int sheetIndex, String sheetName) throws IllegalArgumentException { if (sheetIndex < 0 || sheetIndex >= workbook.getNumberOfSheets()) { throw new IllegalArgumentException("Invalid POI Sheet index given."); } if (sheetName == null || sheetName.isEmpty()) { throw new IllegalArgumentException( "Sheet Name cannot be null or an empty String, or contain backslash \\."); } if (isSheetNameExisting(sheetName)) { throw new IllegalArgumentException("Sheet name must be unique within the workbook."); } workbook.setSheetName(sheetIndex, sheetName); if (!workbook.isSheetVeryHidden(sheetIndex) && !workbook.isSheetHidden(sheetIndex)) { int ourIndex = getSpreadsheetSheetIndex(sheetIndex); getState().sheetNames[ourIndex] = sheetName; } } /** * Sets the protection enabled with the given password for the sheet at the * given index. <code>null</code> password removes the protection. * * @param sheetPOIIndex * Index of the target sheet within the POI model, 0-based * @param password * The password to set for the protection. Pass <code>null</code> * to remove the protection. */ public void setSheetProtected(int sheetPOIIndex, String password) { if (sheetPOIIndex < 0 || sheetPOIIndex >= workbook.getNumberOfSheets()) { throw new IllegalArgumentException("Invalid POI Sheet index given."); } workbook.getSheetAt(sheetPOIIndex).protectSheet(password); getState().sheetProtected = getActiveSheet().getProtect(); // if the currently active sheet was protected, the protection for the // currently selected cell might have changed if (sheetPOIIndex == workbook.getActiveSheetIndex()) { loadCustomComponents(); selectionManager.reSelectSelectedCell(); } } /** * Sets the protection enabled with the given password for the currently * active sheet. <code>null</code> password removes the protection. * * @param password * The password to set for the protection. Pass <code>null</code> * to remove the protection. */ public void setActiveSheetProtected(String password) { setSheetProtected(workbook.getActiveSheetIndex(), password); } /** * Creates a new sheet as the last sheet and sets it as the active sheet. * * If the sheetName given is null, then the sheet name is automatically * generated by Apache POI in {@link Workbook#createSheet()}. * * @param sheetName * Can be null, but not empty nor longer than 31 characters. Must * be unique within the Workbook. * @param rows * Number of rows the sheet should have * @param columns * Number of columns the sheet should have * @throws IllegalArgumentException * If the sheet name is empty or over 31 characters long or not * unique. */ public void createNewSheet(String sheetName, int rows, int columns) throws IllegalArgumentException { if (sheetName != null && sheetName.isEmpty()) { throw new IllegalArgumentException("Sheet Name cannot be an empty String."); } if (sheetName != null && sheetName.length() > 31) { throw new IllegalArgumentException("Sheet Name cannot be longer than 31 characters"); } if (sheetName != null && isSheetNameExisting(sheetName)) { throw new IllegalArgumentException("Sheet name must be unique within the workbook."); } final Sheet previousSheet = getActiveSheet(); SpreadsheetFactory.addNewSheet(this, workbook, sheetName, rows, columns); fireSheetChangeEvent(previousSheet, getActiveSheet()); } /** * Deletes the sheet with the given POI model index. * * Note: A workbook must contain at least one visible sheet. * * @param poiSheetIndex * POI model index of the sheet to delete, 0-based, max value * {@link Workbook#getNumberOfSheets()} -1. * @throws IllegalArgumentException * In case there is only one visible sheet, or if the index is * invalid. */ public void deleteSheetWithPOIIndex(int poiSheetIndex) throws IllegalArgumentException { if (getNumberOfVisibleSheets() < 2) { throw new IllegalArgumentException("A workbook must contain at least one visible worksheet"); } int removedVisibleIndex = getSpreadsheetSheetIndex(poiSheetIndex); workbook.removeSheetAt(poiSheetIndex); // POI doesn't seem to shift the active sheet index ... int oldIndex = getState().sheetIndex - 1; if (removedVisibleIndex <= oldIndex) { // removed before current if (oldIndex == (getNumberOfVisibleSheets())) { // need to shift index backwards if the current sheet is last workbook.setActiveSheet(getVisibleSheetPOIIndex(oldIndex - 1)); } else { workbook.setActiveSheet(getVisibleSheetPOIIndex(oldIndex)); } } // need to reload everything because there is a ALWAYS chance that the // removed sheet effects the currently visible sheet (via cell formulas // etc.) reloadActiveSheetData(); } /** * Deletes the sheet at the given index. * * Note: A workbook must contain at least one visible sheet. * * @param sheetIndex * Index of the sheet to delete among the visible sheets, * 0-based, maximum value {@link #getNumberOfVisibleSheets()} -1. * @throws IllegalArgumentException * In case there is only one visible sheet, or if the given * index is invalid. */ public void deleteSheet(int sheetIndex) throws IllegalArgumentException { if (getNumberOfVisibleSheets() < 2) { throw new IllegalArgumentException("A workbook must contain at least one visible worksheet"); } deleteSheetWithPOIIndex(getVisibleSheetPOIIndex(sheetIndex)); } /** * Returns the number of currently visible sheets in the component. Doesn't * include the hidden or very hidden sheets in the POI model. * * @return Number of visible sheets. */ public int getNumberOfVisibleSheets() { if (getState().sheetNames != null) { return getState().sheetNames.length; } else { return 0; } } /** * Returns the total number of sheets in the workbook (includes hidden and * very hidden sheets). * * @return Total number of sheets in the workbook */ public int getNumberOfSheets() { return workbook.getNumberOfSheets(); } private boolean isSheetNameExisting(String sheetName) { for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (workbook.getSheetName(i).equals(sheetName)) { return true; } } return false; } /** * Returns the index of the currently active sheet among the visible sheets * ( hidden or very hidden sheets not included). * * @return Index of the active sheet, 0-based */ public int getActiveSheetIndex() { return getState(false).sheetIndex - 1; } /** * Returns the POI model index of the currently active sheet (index among * all sheets including hidden and very hidden sheets). * * @return POI model index of the active sheet, 0-based */ public int getActiveSheetPOIIndex() { return getVisibleSheetPOIIndex(getState(false).sheetIndex - 1); } /** * Sets the currently active sheet within the sheets that are visible. * * @param sheetIndex * Index of the target sheet (among the visible sheets), 0-based * @throws IllegalArgumentException * If the index is invalid */ public void setActiveSheetIndex(int sheetIndex) throws IllegalArgumentException { if (sheetIndex < 0 || sheetIndex >= getState().sheetNames.length) { throw new IllegalArgumentException("Invalid Sheet index given."); } int POISheetIndex = getVisibleSheetPOIIndex(sheetIndex); setActiveSheetWithPOIIndex(POISheetIndex); } /** * Sets the currently active sheet. The sheet at the given index should be * visible (not hidden or very hidden). * * @param sheetIndex * Index of sheet in the POI model (contains all sheets), 0-based * @throws IllegalArgumentException * If the index is invalid, or if the sheet at the given index * is hidden or very hidden. */ public void setActiveSheetWithPOIIndex(int sheetIndex) throws IllegalArgumentException { if (sheetIndex < 0 || sheetIndex >= workbook.getNumberOfSheets()) { throw new IllegalArgumentException("Invalid POI Sheet index given."); } if (workbook.isSheetHidden(sheetIndex) || workbook.isSheetVeryHidden(sheetIndex)) { throw new IllegalArgumentException( "Cannot set a hidden or very hidden sheet as the active sheet. Given index: " + sheetIndex); } workbook.setActiveSheet(sheetIndex); reloadActiveSheetData(); SpreadsheetFactory.reloadSpreadsheetData(this, workbook.getSheetAt(sheetIndex)); reloadActiveSheetStyles(); } /** * This method will be called when a selected sheet change is requested. * * @param tabIndex * Index of the sheet to select. * @param scrollLeft * Current horizontal scroll position * @param scrollTop * Current vertical scroll position */ protected void onSheetSelected(int tabIndex, int scrollLeft, int scrollTop) { // this is for the very rare occasion when the sheet has been // selected and the selected sheet value is still negative int oldIndex = Math.abs(getState().sheetIndex) - 1; getState().verticalScrollPositions[oldIndex] = scrollTop; getState().horizontalScrollPositions[oldIndex] = scrollLeft; Sheet oldSheet = getActiveSheet(); setActiveSheetIndex(tabIndex); Sheet newSheet = getActiveSheet(); fireSheetChangeEvent(oldSheet, newSheet); } /** * This method is called when the creation of a new sheet has been * requested. * * @param scrollLeft * Current horizontal scroll position * @param scrollTop * Current vertical scroll position */ protected void onNewSheetCreated(int scrollLeft, int scrollTop) { getState().verticalScrollPositions[getState().sheetIndex - 1] = scrollTop; getState().horizontalScrollPositions[getState().sheetIndex - 1] = scrollLeft; createNewSheet(null, defaultNewSheetRows, defaultNewSheetColumns); } /** * This method is called when a request to rename a sheet has been made. * * @param sheetIndex * Index of the sheet to rename (among visible sheets). * @param sheetName * New name for the sheet. */ protected void onSheetRename(int sheetIndex, String sheetName) { // if excel doesn't keep these in history, neither will we setSheetNameWithPOIIndex(getVisibleSheetPOIIndex(sheetIndex), sheetName); } /** * Get the number of columns in the currently active sheet, or if * {@link #setMaxColumns(int)} has been used, the current number of columns * the component shows (not the amount of columns in the actual sheet in the * POI model). * * @return Number of visible columns. */ public int getColumns() { return getState().cols; } /** * Get the number of rows in the currently active sheet, or if * {@link #setMaxRows(int)} has been used, the current number of rows the * component shows (not the amount of rows in the actual sheet in the POI * model). * * @return Number of visible rows. */ public int getRows() { return getState().rows; } /** * Gets the current DataFormatter. * * @return The data formatter for this Spreadsheet. */ public DataFormatter getDataFormatter() { return valueManager.getDataFormatter(); } /** * Returns the Cell at the given address. If the cell is updated in outside * code, call {@link #refreshCells(Cell...)} AFTER ALL UPDATES (value, type, * formatting or style) to mark the cell as "dirty". * * @param cellAddress * Address of the Cell to return, e.g. "A3" * @return The cell at the given address, or null if not defined */ public Cell getCell(String cellAddress) { CellReference ref = new CellReference(cellAddress); Row r = workbook.getSheetAt(workbook.getActiveSheetIndex()).getRow(ref.getRow()); if (r != null) { return r.getCell(ref.getCol()); } else { return null; } } /** * Returns the Cell at the given coordinates. If the cell is updated in * outside code, call {@link #refreshCells(Cell...)} AFTER ALL UPDATES * (value, type, formatting or style) to mark the cell as "dirty". * * @param row * Row index of the cell to return, 0-based * @param col * Column index of the cell to return, 0-based * @return The cell at the given coordinates, or null if not defined */ public Cell getCell(int row, int col) { Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); return getCell(row, col, sheet); } /** * Returns the Cell at the given coordinates. If the cell is updated in * outside code, call {@link #refreshCells(Cell...)} AFTER ALL UPDATES * (value, type, formatting or style) to mark the cell as "dirty". * * @param row * Row index of the cell to return, 0-based * @param col * Column index of the cell to return, 0-based * @param sheet * Sheet of the cell * @return The cell at the given coordinates, or null if not defined */ public Cell getCell(int row, int col, Sheet sheet) { Row r = sheet.getRow(row); if (r != null) { return r.getCell(col); } else { return null; } } /** * Returns the Cell corresponding to the given reference. If the cell is * updated in outside code, call {@link #refreshCells(Cell...)} AFTER ALL * UPDATES (value, type, formatting or style) to mark the cell as "dirty". * * @param cellReference * Reference to the cell to return * @return The cell corresponding to the given reference, or null if not * defined */ public Cell getCell(CellReference cellReference) { return cellReference == null ? null : getCell(cellReference.getRow(), cellReference.getCol()); } /** * Returns the Cell corresponding to the given reference. If the cell is * updated in outside code, call {@link #refreshCells(Cell...)} AFTER ALL * UPDATES (value, type, formatting or style) to mark the cell as "dirty". * * @param cellReference * Reference to the cell to return * @param sheet * Sheet of the cell * @return The cell corresponding to the given reference, or null if not * defined */ public Cell getCell(CellReference cellReference, Sheet sheet) { return cellReference == null ? null : getCell(cellReference.getRow(), cellReference.getCol(), sheet); } /** * Deletes the cell from the sheet and the underlying POI model as well. * This really deletes the cell, instead of just making it's value blank. * * @param row * Row index of the cell to delete, 0-based * @param col * Column index of the cell to delete, 0-based */ public void deleteCell(int row, int col) { final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); final Cell cell = activeSheet.getRow(row).getCell(col); if (cell != null) { // cell.setCellStyle(null); // TODO NPE on HSSF styler.cellStyleUpdated(cell, true); activeSheet.getRow(row).removeCell(cell); valueManager.cellDeleted(cell); refreshCells(cell); } } /** * Refreshes the given cell(s). Should be called when the cell * value/formatting/style/etc. updating is done. * * NOTE: For optimal performance temporarily collect your updated cells and * call this method only once per update cycle. Calling this method * repeatedly for individual cells is not a good idea. * * @param cells * Cell(s) to update */ public void refreshCells(Cell... cells) { if (cells != null) { for (Cell cell : cells) { markCellAsUpdated(cell, true); } updateMarkedCells(); } } /** * Refreshes the given cell(s). Should be called when the cell * value/formatting/style/etc. updating is done. * * NOTE: For optimal performance temporarily collect your updated cells and * call this method only once per update cycle. Calling this method * repeatedly for individual cells is not a good idea. * * @param cells * A Collection of Cells to update */ public void refreshCells(Collection<Cell> cells) { if (cells != null && !cells.isEmpty()) { for (Cell cell : cells) { markCellAsUpdated(cell, true); } updateMarkedCells(); } } /** * Marks the cell as updated. Should be called when the cell * value/formatting/style/etc. updating is done. * * @param cellStyleUpdated * True if the cell style has changed * * @param cell * The updated cell */ void markCellAsUpdated(Cell cell, boolean cellStyleUpdated) { valueManager.cellUpdated(cell); if (cellStyleUpdated) { styler.cellStyleUpdated(cell, true); } } /** * Marks the cell as deleted. This method should be called after removing a * cell from the {@link Workbook} using POI API. * * @param cellStyleUpdated * True if the cell style has changed * @param cell * The cell that has been deleted. */ public void markCellAsDeleted(Cell cell, boolean cellStyleUpdated) { valueManager.cellDeleted(cell); if (cellStyleUpdated) { styler.cellStyleUpdated(cell, true); } refreshCells(cell); } /** * Updates the content of the cells that have been marked for update with * {@link #markCellAsUpdated(Cell, boolean)}. * <p> * Does NOT update custom components (editors / always visible) for the * cells. For that, use {@link #reloadVisibleCellContents()} */ void updateMarkedCells() { // update conditional formatting in case styling has changed. New values // are fetched in ValueManager (below). conditionalFormatter.createConditionalFormatterRules(); // FIXME should be optimized, should not go through all links, comments // etc. always valueManager.updateMarkedCellValues(); // if the selected cell is of type formula, there is a change that the // formula has been changed. selectionManager.reSelectSelectedCell(); // Update the cell comments as well to show them instantly after adding // them loadCellComments(); // update custom components, editors reloadVisibleCellContents(); } /** * Creates a new Formula type cell with the given formula. * * After all editing is done, call {@link #refreshCells(Cell...)()} or * {@link #refreshAllCellValues()} to make sure client side is updated. * * @param row * Row index of the new cell, 0-based * @param col * Column index of the new cell, 0-based * @param formula * The formula to set to the new cell (should NOT start with "=" * nor "+") * @return The newly created cell * @throws IllegalArgumentException * If columnIndex < 0 or greater than the maximum number of * supported columns (255 for *.xls, 1048576 for *.xlsx) */ public Cell createFormulaCell(int row, int col, String formula) throws IllegalArgumentException { final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Row r = activeSheet.getRow(row); if (r == null) { r = activeSheet.createRow(row); } Cell cell = r.getCell(col); if (cell == null) { cell = r.createCell(col, Cell.CELL_TYPE_FORMULA); } else { final String key = SpreadsheetUtil.toKey(col + 1, row + 1); valueManager.clearCellCache(key); cell.setCellType(Cell.CELL_TYPE_FORMULA); } cell.setCellFormula(formula); valueManager.cellUpdated(cell); return cell; } /** * Create a new cell (or replace existing) with the given value, the type of * the value parameter will define the type of the cell. The value may be of * the following types: Boolean, Calendar, Date, Double or String. The * default type will be String, value of ({@link #toString()} will be given * as the cell value. * * For formula cells, use {@link #createFormulaCell(int, int, String)}. * * After all editing is done, call {@link #refreshCells(Cell...)} or * {@link #refreshAllCellValues()} to make sure the client side is updated. * * @param row * Row index of the new cell, 0-based * @param col * Column index of the new cell, 0-based * @param value * Object representing the type and value of the Cell * @return The newly created cell * @throws IllegalArgumentException * If columnIndex < 0 or greater than the maximum number of * supported columns (255 for *.xls, 1048576 for *.xlsx) */ public Cell createCell(int row, int col, Object value) throws IllegalArgumentException { final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Row r = activeSheet.getRow(row); if (r == null) { r = activeSheet.createRow(row); } Cell cell = r.getCell(col); if (cell == null) { cell = r.createCell(col); } else { final String key = SpreadsheetUtil.toKey(col + 1, row + 1); valueManager.clearCellCache(key); } if (value instanceof Double) { cell.setCellValue((Double) value); } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value); } else if (value instanceof Date) { cell.setCellValue((Date) value); } else if (value instanceof Calendar) { cell.setCellValue((Calendar) value); } else { cell.setCellValue(value.toString()); } valueManager.cellUpdated(cell); return cell; } /** * Forces recalculation and update to the client side for values of all of * the sheet's cells. * * Note: DOES NOT UPDATE STYLES; use {@link #refreshCells(Cell...)} when * cell styles change. */ public void refreshAllCellValues() { getFormulaEvaluator().clearAllCachedResultValues(); valueManager.clearCachedContent(); // only reload if the cells have been loaded once previously if (firstColumn == -1) { // client will request cells soon, no need for reload now return; } updateRowAndColumnRangeCellData(1, 1, getRows(), getColumns()); // if the selected cell is of type formula, there is a change that the // formula has been changed. selectionManager.reSelectSelectedCell(); } /** * Set the number of columns shown for the current sheet. Any null cells are * left empty. Any cells outside the given columns are hidden. Does not * update the actual POI-based model! * * The default value will be the actual size of the sheet from the POI * model. * * @param cols * New maximum column count. */ public void setMaxColumns(int cols) { if (getState().cols != cols) { getState().cols = cols; } } /** * Set the number of rows shown for the current sheet. Any null cells are * left empty. Any cells outside the given rows are hidden. Does not update * the actual POI-based model! * * The default value will be the actual size of the sheet from the POI * model. * * @param rows * New maximum row count. */ public void setMaxRows(int rows) { if (getState().rows != rows) { getState().rows = rows; } } /** * Does {@link #setMaxColumns(int)} & {@link #setMaxRows(int)} in one * method. * * @param rows * Maximum row count * @param cols * Maximum column count */ public void setSheetMaxSize(int rows, int cols) { getState().cols = cols; getState().rows = rows; } /** * Gets the default column width for the currently active sheet. This is * derived from the active sheet's ({@link #getActiveSheet()}) default * column width (Sheet {@link #getDefaultColumnWidth()}). * * @return The default column width in PX */ public int getDefaultColumnWidth() { return getState().defColW; } /** * Sets the default column width in pixels that the component uses, this * doesn't change the default column width of the underlying sheet, returned * by {@link #getActiveSheet()} and {@link Sheet#getDefaultColumnWidth()}. * * @param widthPX * The default column width in pixels */ public void setDefaultColumnWidth(int widthPX) { if (widthPX <= 0) { throw new IllegalArgumentException("Default column width must be over 0, given value: " + widthPX); } getState().defColW = widthPX; defaultColWidthSet = true; } /** * Gets the default row height in points. By default it should be the same * as {@link Sheet#getDefaultRowHeightInPoints()} for the currently active * sheet {@link #getActiveSheet()}. * * @return Default row height for the currently active sheet, in points. */ public float getDefaultRowHeight() { return getState().defRowH; } /** * Sets the default row height in points for this Spreadsheet and the * currently active sheet, returned by {@link #getActiveSheet()}. * * @param heightPT * New default row height in points. */ public void setDefaultRowHeight(float heightPT) { if (heightPT <= 0.0f) { throw new IllegalArgumentException("Default row height must be over 0, given value: " + heightPT); } getActiveSheet().setDefaultRowHeightInPoints(heightPT); getState().defRowH = heightPT; defaultRowHeightSet = true; } /** * This method is called when column auto-fit has been initiated from the * browser by double-clicking the border of the target column header. * * @param columnIndex * Index of the target column, 0-based */ protected void onColumnAutofit(int columnIndex) { SizeChangeCommand command = new SizeChangeCommand(this, Type.COLUMN); command.captureValues(new Integer[] { columnIndex + 1 }); autofitColumn(columnIndex); historyManager.addCommand(command); } /** * Sets the column to automatically adjust the column width to fit the * largest cell content within the column. This is a POI feature, and is * meant to be called after all the data for the target column has been * written. See {@link Sheet#autoSizeColumn(int)}. * <p> * This does not take into account cells that have custom Vaadin components * inside them. * * @param columnIndex * Index of the target column, 0-based */ public void autofitColumn(int columnIndex) { final Sheet activeSheet = getActiveSheet(); activeSheet.autoSizeColumn(columnIndex); getState().colW[columnIndex] = AbstractExcelUtils .getColumnWidthInPx(activeSheet.getColumnWidth(columnIndex)); getCellValueManager().clearCacheForColumn(columnIndex + 1); getCellValueManager().loadCellData(firstRow, columnIndex + 1, lastRow, columnIndex + 1); if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; loadOrUpdateOverlays(); } } /** * Shifts rows between startRow and endRow n number of rows. If you use a * negative number for n, the rows will be shifted upwards. This method * ensures that rows can't wrap around. * <p> * If you are adding / deleting rows, you might want to change the number of * visible rows rendered {@link #getRows()} with {@link #setMaxRows(int)}. * <p> * See {@link Sheet#shiftRows(int, int, int)}. * * @param startRow * The first row to shift, 0-based * @param endRow * The last row to shift, 0-based * @param n * Number of rows to shift, positive numbers shift down, negative * numbers shift up. */ public void shiftRows(int startRow, int endRow, int n) { shiftRows(startRow, endRow, n, false, false); } /** * Shifts rows between startRow and endRow n number of rows. If you use a * negative number for n, the rows will be shifted upwards. This method * ensures that rows can't wrap around. * <p> * If you are adding / deleting rows, you might want to change the number of * visible rows rendered {@link #getRows()} with {@link #setMaxRows(int)}. * <p> * See {@link Sheet#shiftRows(int, int, int, boolean, boolean)}. * * @param startRow * The first row to shift, 0-based * @param endRow * The last row to shift, 0-based * @param n * Number of rows to shift, positive numbers shift down, negative * numbers shift up. * @param copyRowHeight * True to copy the row height during the shift * @param resetOriginalRowHeight * True to set the original row's height to the default */ public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { Sheet sheet = getActiveSheet(); int lastNonBlankRow = getLastNonBlankRow(sheet); sheet.shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight); // need to re-send the cell values to client // remove all cached cell data that is now empty getFormulaEvaluator().clearAllCachedResultValues(); int start = n < 0 ? Math.max(lastNonBlankRow, startRow) : startRow; int end = n < 0 ? endRow : startRow + n - 1; valueManager.updateDeletedRowsInClientCache(start + 1, end + 1); int firstAffectedRow = n < 0 ? startRow + n : startRow; int lastAffectedRow = n < 0 ? endRow : endRow + n; if (copyRowHeight || resetOriginalRowHeight) { // might need to increase the size of the row heights array int oldLength = getState(false).rowH.length; int neededLength = endRow + n + 1; if (n > 0 && oldLength < neededLength) { getState().rowH = Arrays.copyOf(getState().rowH, neededLength); } for (int i = firstAffectedRow; i <= lastAffectedRow; i++) { Row row = sheet.getRow(i); if (row != null) { if (row.getZeroHeight()) { getState().rowH[i] = 0f; } else { getState().rowH[i] = row.getHeightInPoints(); } } else { getState().rowH[i] = sheet.getDefaultRowHeightInPoints(); } } } if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; } // need to shift the cell styles, clear and update // need to go -1 and +1 because of shifted borders.. final ArrayList<Cell> cellsToUpdate = new ArrayList<Cell>(); for (int r = (firstAffectedRow - 1); r <= (lastAffectedRow + 1); r++) { if (r < 0) { r = 0; } Row row = sheet.getRow(r); final Integer rowIndex = new Integer(r + 1); if (row == null) { valueManager.updateDeletedRowsInClientCache(rowIndex, rowIndex); if (getState(false).hiddenRowIndexes.contains(rowIndex)) { getState().hiddenRowIndexes.remove(rowIndex); } for (int c = 0; c < getState().cols; c++) { styler.clearCellStyle(r, c); } } else { if (row.getZeroHeight()) { getState().hiddenRowIndexes.add(rowIndex); } else if (getState(false).hiddenRowIndexes.contains(rowIndex)) { getState().hiddenRowIndexes.remove(rowIndex); } for (int c = 0; c < getState().cols; c++) { Cell cell = row.getCell(c); if (cell == null) { styler.clearCellStyle(r, c); if (r <= lastNonBlankRow + n) { // There might be a pre-shift value for this cell in // client-side and should be overwritten cell = row.createCell(c); cellsToUpdate.add(cell); } } else { cellsToUpdate.add(cell); } } } } rowsMoved(firstAffectedRow, lastAffectedRow, n); for (Cell cell : cellsToUpdate) { styler.cellStyleUpdated(cell, false); markCellAsUpdated(cell, false); } styler.loadCustomBorderStylesToState(); updateMarkedCells(); // deleted and formula cells and style selectors updateRowAndColumnRangeCellData(firstRow, firstColumn, lastRow, lastColumn); // shifted area values updateMergedRegions(); CellReference selectedCellReference = selectionManager.getSelectedCellReference(); if (selectedCellReference != null) { if (selectedCellReference.getRow() >= firstAffectedRow && selectedCellReference.getRow() <= lastAffectedRow) { selectionManager.onSheetAddressChanged(selectedCellReference.formatAsString(), false); } } } private boolean hasSheetOverlays() { return sheetOverlays != null && sheetOverlays.size() > 0; } /** * Called when number of rows has moved. Spreadsheet needs to update its * internal state. * * Note: If n is negative it would mean the rows has moved up. Positive * value indicates that new rows are moved below. * * @param first * the first row that has changed, 0-based * @param last * the last row that has changed, 0-based * @param n * the amount of lines that rows has been moved */ private void rowsMoved(int first, int last, int n) { // Merged regions if (n < 0) { // Remove merged cells from deleted rows. POI will handle the other // updated values. for (int row = (first + n); row <= first; ++row) { Sheet sheet = getActiveSheet(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() == row) { removeMergedRegion(i); } } } } // PopupButtons if (!sheetPopupButtons.isEmpty()) { Map<CellReference, PopupButton> updated = new HashMap<CellReference, PopupButton>(); for (PopupButton pbutton : sheetPopupButtons.values()) { CellReference cell = pbutton.getCellReference(); unRegisterPopupButton(pbutton); int row = cell.getRow(); if (rowWasRemoved(row, first, n)) { // do nothing -> will be removed } else if (numberOfRowsAboveWasChanged(row, last, first)) { int newRow = cell.getRow() + n; int col = cell.getCol(); CellReference newCell = new CellReference(newRow, col); pbutton.setCellReference(newCell); updated.put(newCell, pbutton); } else { updated.put(cell, pbutton); } } sheetPopupButtons = updated; } // Invalid formula indicators int activeSheetIndex = workbook.getActiveSheetIndex(); HashSet<String> original = invalidFormulas.get(activeSheetIndex); if (original != null) { HashSet<String> updated = new HashSet<String>(); for (String key : original) { int row = SpreadsheetUtil.getRowFromKey(key) - 1; int col = SpreadsheetUtil.getColumnIndexFromKey(key) - 1; if (rowWasRemoved(row, first, n)) { // do nothing -> will be removed } else if (numberOfRowsAboveWasChanged(row, last, first)) { // the number of the rows above has changed -> update the // row index updated.add(SpreadsheetUtil.toKey(col + 1, row + n + 1)); } else { updated.add(key); } } original.clear(); invalidFormulas.put(activeSheetIndex, updated); } } private boolean numberOfRowsAboveWasChanged(int row, int last, int first) { return first <= row && row <= last; } private boolean rowWasRemoved(int row, int first, int n) { return n < 0 && first + n < row && row <= first; } /** * Get the common {@link FormulaEvaluator} instance. */ public FormulaEvaluator getFormulaEvaluator() { return formulaEvaluator; } private int getLastNonBlankRow(Sheet sheet) { for (int r = sheet.getLastRowNum(); r >= 0; r--) { Row row = sheet.getRow(r); if (row != null) { for (short c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { return r; } } } } return 0; } private void updateMergedRegions() { int regions = getActiveSheet().getNumMergedRegions(); if (regions > 0) { getState().mergedRegions = new ArrayList<MergedRegion>(); for (int i = 0; i < regions; i++) { final CellRangeAddress region = getActiveSheet().getMergedRegion(i); try { final MergedRegion mergedRegion = new MergedRegion(); mergedRegion.col1 = region.getFirstColumn() + 1; mergedRegion.col2 = region.getLastColumn() + 1; mergedRegion.row1 = region.getFirstRow() + 1; mergedRegion.row2 = region.getLastRow() + 1; mergedRegion.id = mergedRegionCounter++; getState().mergedRegions.add(i, mergedRegion); } catch (IndexOutOfBoundsException ioobe) { createMergedRegionIntoSheet(region); } } while (regions < getState(false).mergedRegions.size()) { getState().mergedRegions.remove(getState(false).mergedRegions.size() - 1); } } else { getState().mergedRegions = null; } } /** * Deletes rows. See {@link Sheet#removeRow(Row)}. Removes all row content, * deletes cells and resets the sheet size. * * Does not shift rows up (!) - use * {@link #shiftRows(int, int, int, boolean, boolean)} for that. * * @param startRow * Index of the starting row, 0-based * @param endRow * Index of the ending row, 0-based */ public void deleteRows(int startRow, int endRow) { Sheet sheet = getActiveSheet(); for (int i = startRow; i <= endRow; i++) { Row row = sheet.getRow(i); if (row != null) { getActiveSheet().removeRow(row); } } for (int i = startRow; i <= endRow; i++) { getState(false).rowH[i] = sheet.getDefaultRowHeightInPoints(); } updateMergedRegions(); valueManager.updateDeletedRowsInClientCache(startRow + 1, endRow + 1); if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; } updateMarkedCells(); CellReference selectedCellReference = getSelectedCellReference(); if (selectedCellReference.getRow() >= startRow && selectedCellReference.getRow() <= endRow) { selectionManager.reSelectSelectedCell(); } } /** * Merges cells. See {@link Sheet#addMergedRegion(CellRangeAddress)}. * * @param selectionRange * The cell range to merge, e.g. "B3:C5" */ public void addMergedRegion(String selectionRange) { addMergedRegion(CellRangeAddress.valueOf(selectionRange)); } /** * Merge cells. See {@link Sheet#addMergedRegion(CellRangeAddress)}. * * @param row1 * Index of the starting row of the merged region, 0-based * @param col1 * Index of the starting column of the merged region, 0-based * @param row2 * Index of the ending row of the merged region, 0-based * @param col2 * Index of the ending column of the merged region, 0-based */ public void addMergedRegion(int row1, int col1, int row2, int col2) { addMergedRegion(new CellRangeAddress(row1, row2, col1, col2)); } /** * Merges the given cells. See * {@link Sheet#addMergedRegion(CellRangeAddress)}. * <p> * If another existing merged region is completely inside the given range, * it is removed. If another existing region either encloses or overlaps the * given range, an error is thrown. See * {@link CellRangeUtil#intersect(CellRangeAddress, CellRangeAddress)}. * <p> * Note: POI doesn't seem to update the cells that are "removed" due to the * merge - the values for those cells still exist and continue being used in * possible formulas. If you need to make sure those values are removed, * just delete the cells before creating the merged region. * <p> * If the added region affects the currently selected cell, a new * {@link SelectionChangeEvent} is fired. * * @param region * The range of cells to merge * @throws IllegalArgumentException * If the given region overlaps with or encloses another * existing region within the sheet. */ public void addMergedRegion(CellRangeAddress region) throws IllegalArgumentException { final Sheet sheet = getActiveSheet(); // need to check if there are merged regions already inside the given // range, otherwise very bad inconsistencies appear. int index = 0; while (index < sheet.getNumMergedRegions()) { CellRangeAddress existingRegion = sheet.getMergedRegion(index); int intersect = CellRangeUtil.intersect(region, existingRegion); if (intersect == CellRangeUtil.INSIDE) { deleteMergedRegion(index); } else if (intersect == CellRangeUtil.OVERLAP || intersect == CellRangeUtil.ENCLOSES) { throw new IllegalArgumentException("An existing region " + existingRegion + " " + (intersect == CellRangeUtil.OVERLAP ? "overlaps " : "encloses ") + "the given region " + region); } else { index++; } } createMergedRegionIntoSheet(region); selectionManager.mergedRegionAdded(region); } private void createMergedRegionIntoSheet(CellRangeAddress region) { Sheet sheet = getActiveSheet(); int addMergedRegionIndex = sheet.addMergedRegion(region); MergedRegion mergedRegion = new MergedRegion(); mergedRegion.col1 = region.getFirstColumn() + 1; mergedRegion.col2 = region.getLastColumn() + 1; mergedRegion.row1 = region.getFirstRow() + 1; mergedRegion.row2 = region.getLastRow() + 1; mergedRegion.id = mergedRegionCounter++; if (getState().mergedRegions == null) { getState().mergedRegions = new ArrayList<MergedRegion>(); } getState().mergedRegions.add(addMergedRegionIndex - 1, mergedRegion); // update the style & data for the region cells, effects region + 1 // FIXME POI doesn't seem to care that the other cells inside the merged // region should be removed; the values those cells have are still used // in formulas.. for (int r = mergedRegion.row1; r <= (mergedRegion.row2 + 1); r++) { Row row = sheet.getRow(r - 1); for (int c = mergedRegion.col1; c <= (mergedRegion.col2 + 1); c++) { if (row != null) { Cell cell = row.getCell(c - 1); if (cell != null) { styler.cellStyleUpdated(cell, false); if ((c != mergedRegion.col1 || r != mergedRegion.row1) && c <= mergedRegion.col2 && r <= mergedRegion.row2) { getCellValueManager().markCellForRemove(cell); } } } } } styler.loadCustomBorderStylesToState(); updateMarkedCells(); } /** * Removes a merged region with the given index. Current merged regions can * be inspected within the currently active sheet with * {@link #getActiveSheet()} and {@link Sheet#getMergedRegion(int)} and * {@link Sheet#getNumMergedRegions()}. * <p> * Note that in POI after removing a merged region at index n, all regions * added after the removed region will get a new index (index-1). * <p> * If the removed region affects the currently selected cell, a new * {@link SelectionChangeEvent} is fired. * * @param index * Position of the target merged region in the POI merged region * array, 0-based */ public void removeMergedRegion(int index) { final CellRangeAddress removedRegion = getActiveSheet().getMergedRegion(index); deleteMergedRegion(index); updateMarkedCells(); // update selection if removed region overlaps selectionManager.mergedRegionRemoved(removedRegion); } private void deleteMergedRegion(int index) { final Sheet sheet = getActiveSheet(); sheet.removeMergedRegion(index); MergedRegion mergedRegion = getState().mergedRegions.remove(index); // update the style for the region cells, effects region + 1 row&col for (int r = mergedRegion.row1; r <= (mergedRegion.row2 + 1); r++) { Row row = sheet.getRow(r - 1); if (row != null) { for (int c = mergedRegion.col1; c <= (mergedRegion.col2 + 1); c++) { Cell cell = row.getCell(c - 1); if (cell != null) { styler.cellStyleUpdated(cell, false); valueManager.markCellForUpdate(cell); } else { styler.clearCellStyle(r, c); } } } } styler.loadCustomBorderStylesToState(); } /** * Discards all current merged regions for the sheet and reloads them from * the POI model. * <p> * This can be used if you want to add / remove multiple merged regions * directly from the POI model and need to update the component. * * Note that you must also make sure that possible styles for the merged * regions are updated, if those were modified, by calling * {@link #reloadActiveSheetStyles()}. */ public void reloadAllMergedRegions() { SpreadsheetFactory.loadMergedRegions(this); } /** * Reloads all the styles for the currently active sheet. */ public void reloadActiveSheetStyles() { styler.reloadActiveSheetCellStyles(); } /** * Hides or shows the given column, see * {@link Sheet#setColumnHidden(int, boolean)}. * * @param columnIndex * Index of the target column, 0-based * @param hidden * True to hide the target column, false to show it. */ public void setColumnHidden(int columnIndex, boolean hidden) { getActiveSheet().setColumnHidden(columnIndex, hidden); if (hidden && !getState().hiddenColumnIndexes.contains(columnIndex + 1)) { getState().hiddenColumnIndexes.add(columnIndex + 1); getState().colW[columnIndex] = 0; } else if (!hidden && getState().hiddenColumnIndexes.contains(columnIndex + 1)) { getState().hiddenColumnIndexes.remove(getState().hiddenColumnIndexes.indexOf(columnIndex + 1)); getState().colW[columnIndex] = AbstractExcelUtils .getColumnWidthInPx(getActiveSheet().getColumnWidth(columnIndex)); getCellValueManager().clearCacheForColumn(columnIndex + 1); getCellValueManager().loadCellData(firstRow, columnIndex + 1, lastRow, columnIndex + 1); } if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; loadOrUpdateOverlays(); } } /** * Gets the visibility state of the given column. See * {@link Sheet#isColumnHidden(int)}. * * @param columnIndex * Index of the target column, 0-based * @return true if the target column is hidden, false if it is visible. */ public boolean isColumnHidden(int columnIndex) { return getActiveSheet().isColumnHidden(columnIndex); } /** * Hides or shows the given row, see {@link Row#setZeroHeight(boolean)}. * * @param rowIndex * Index of the target row, 0-based * @param hidden * True to hide the target row, false to show it. */ public void setRowHidden(int rowIndex, boolean hidden) { final Sheet activeSheet = getActiveSheet(); Row row = activeSheet.getRow(rowIndex); if (row == null) { row = activeSheet.createRow(rowIndex); } row.setZeroHeight(hidden); if (hidden && !getState().hiddenRowIndexes.contains(rowIndex + 1)) { getState().hiddenRowIndexes.add(rowIndex + 1); getState().rowH[rowIndex] = 0.0F; } else if (!hidden && getState().hiddenRowIndexes.contains(rowIndex + 1)) { getState().hiddenRowIndexes.remove(getState().hiddenRowIndexes.indexOf(rowIndex + 1)); getState().rowH[rowIndex] = row.getHeightInPoints(); } if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; loadOrUpdateOverlays(); } } /** * Gets the visibility state of the given row. A row is hidden when it has * zero height, see {@link Row#getZeroHeight()}. * * @param rowIndex * Index of the target row, 0-based * @return true if the target row is hidden, false if it is visible. */ public boolean isRowHidden(int rowIndex) { Row row = getActiveSheet().getRow(rowIndex); return row == null ? false : row.getZeroHeight(); } /** * Reinitializes the component from the given Excel file. * * @param file * Data source file. Excel format is expected. * @throws IOException * If the file can't be read, or the file is of an invalid * format. */ public void read(File file) throws IOException { SpreadsheetFactory.reloadSpreadsheetComponent(this, file); srcUri = file.toURI().toString(); } /** * Reinitializes the component from the given input stream. The expected * format is that of an Excel file. * * @param inputStream * Data source input stream. Excel format is expected. * @throws IOException * If handling the stream fails, or the data is in an invalid * format. */ public void read(InputStream inputStream) throws IOException { SpreadsheetFactory.reloadSpreadsheetComponent(this, inputStream); srcUri = null; } /** * Exports current spreadsheet into a File with the given name. * * @param fileName * The full name of the file. If the name doesn't end with '.xls' * or '.xlsx', the approriate one will be appended. * @return A File with the content of the current {@link Workbook}, In the * file format of the original {@link Workbook}. * @throws FileNotFoundException * If file name was invalid * @throws IOException * If the file can't be written to for any reason */ public File write(String fileName) throws FileNotFoundException, IOException { return SpreadsheetFactory.write(this, fileName); } /** * Exports current spreadsheet as an output stream. * * @param outputStream * The target stream * @throws IOException * If writing to the stream fails */ public void write(OutputStream outputStream) throws IOException { SpreadsheetFactory.write(this, outputStream); } /** * The row buffer size determines the amount of content rendered outside the * top and bottom edges of the visible cell area, for smoother scrolling. * <p> * Size is in pixels, the default is 200. * * @return The current row buffer size */ public int getRowBufferSize() { return getState().rowBufferSize; } /** * Sets the row buffer size. Comes into effect the next time sheet is * scrolled or reloaded. * <p> * The row buffer size determines the amount of content rendered outside the * top and bottom edges of the visible cell area, for smoother scrolling. * * @param rowBufferInPixels * The amount of extra content rendered outside the top and * bottom edges of the visible area. */ public void setRowBufferSize(int rowBufferInPixels) { getState().rowBufferSize = rowBufferInPixels; } /** * The column buffer size determines the amount of content rendered outside * the left and right edges of the visible cell area, for smoother * scrolling. * <p> * Size is in pixels, the default is 200. * * @return The current column buffer size */ public int getColBufferSize() { return getState().columnBufferSize; } /** * Sets the column buffer size. Comes into effect the next time sheet is * scrolled or reloaded. * <p> * The column buffer size determines the amount of content rendered outside * the left and right edges of the visible cell area, for smoother * scrolling. * * @param colBufferInPixels * The amount of extra content rendered outside the left and * right edges of the visible area. */ public void setColBufferSize(int colBufferInPixels) { getState().columnBufferSize = colBufferInPixels; } /** * Gets the default row count for new sheets. * * @return The default row count for new sheets. */ public int getDefaultRowCount() { return defaultNewSheetRows; } /** * Sets the default row count for new sheets. * * @param defaultRowCount * The number of rows to give sheets that are created with the * '+' button on the client side. */ public void setDefaultRowCount(int defaultRowCount) { defaultNewSheetRows = defaultRowCount; } /** * Gets the default column count for new sheets. * * @return The default column count for new sheets. */ public int getDefaultColumnCount() { return defaultNewSheetColumns; } /** * Sets the default column count for new sheets. * * @param defaultColumnCount * The number of columns to give sheets that are created with the * '+' button on the client side. */ public void setDefaultColumnCount(int defaultColumnCount) { defaultNewSheetColumns = defaultColumnCount; } /** * Call this to force the spreadsheet to reload the currently viewed cell * contents. This forces reload of all: custom components (always visible & * editors) from {@link SpreadsheetComponentFactory}, hyperlinks, cells' * comments and cells' contents. Also updates styles for the visible area. */ public void reloadVisibleCellContents() { loadCustomComponents(); updateRowAndColumnRangeCellData(firstRow, firstColumn, lastRow, lastColumn); } /* * (non-Javadoc) * * @see * com.vaadin.server.AbstractClientConnector#setResource(java.lang.String, * com.vaadin.server.Resource) * * Provides package visibility. */ @Override protected void setResource(String key, Resource resource) { super.setResource(key, resource); } void clearSheetServerSide() { workbook = null; styler = null; valueManager.clearCachedContent(); selectionManager.clear(); historyManager.clear(); invalidFormulas.clear(); sheetPopupButtons.clear(); sheetState.clear(); clearSheetOverlays(); } private void clearSheetOverlays() { for (SheetOverlayWrapper image : sheetOverlays) { removeOverlayData(image); } sheetOverlays.clear(); } void setInternalWorkbook(Workbook workbook) { this.workbook = workbook; formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); styler = createSpreadsheetStyleFactory(); reloadActiveSheetData(); if (workbook instanceof HSSFWorkbook) { getState().workbookProtected = ((HSSFWorkbook) workbook).isWriteProtected(); } else if (workbook instanceof XSSFWorkbook) { getState().workbookProtected = ((XSSFWorkbook) workbook).isStructureLocked(); } // clear all tables from memory tables.clear(); getState().verticalScrollPositions = new int[getState().sheetNames.length]; getState().horizontalScrollPositions = new int[getState().sheetNames.length]; conditionalFormatter = createConditionalFormatter(); getState().workbookChangeToggle = !getState().workbookChangeToggle; } /** * Override this method to provide your own {@link ConditionalFormatter} * implementation. This method is called each time we open a workbook. * * @return A {@link ConditionalFormatter} that is tied to this spreadsheet. */ protected ConditionalFormatter createConditionalFormatter() { return new ConditionalFormatter(this); } /** * Override this method to provide your own {@link SpreadsheetStyleFactory} * implementation. This method is called each time we open a workbook. * * @return A {@link SpreadsheetStyleFactory} that is tied to this * Spreadsheet. */ protected SpreadsheetStyleFactory createSpreadsheetStyleFactory() { return new SpreadsheetStyleFactory(this); } /** * Clears and reloads all data related to the currently active sheet. */ protected void reloadActiveSheetData() { selectionManager.clear(); valueManager.clearCachedContent(); firstColumn = lastColumn = firstRow = lastRow = -1; clearSheetOverlays(); topLeftCellCommentsLoaded = false; topLeftCellHyperlinksLoaded = false; reload = true; getState().sheetIndex = getSpreadsheetSheetIndex(workbook.getActiveSheetIndex()) + 1; getState().sheetProtected = getActiveSheet().getProtect(); getState().cellKeysToEditorIdMap = null; getState().hyperlinksTooltips = null; getState().componentIDtoCellKeysMap = null; getState().overlays = null; getState().mergedRegions = null; getState().cellComments = null; getState().cellCommentAuthors = null; getState().visibleCellComments = null; getState().invalidFormulaCells = null; for (Component c : customComponents) { unRegisterCustomComponent(c); } customComponents.clear(); if (attachedPopupButtons != null && !attachedPopupButtons.isEmpty()) { for (PopupButton sf : new ArrayList<PopupButton>(attachedPopupButtons)) { unRegisterPopupButton(sf); } attachedPopupButtons.clear(); } // clear all tables, possible tables for new/changed sheet are added // after first round trip. tablesLoaded = false; reloadSheetNames(); updateMergedRegions(); styler.reloadActiveSheetColumnRowStyles(); getState().displayGridlines = getActiveSheet().isDisplayGridlines(); getState().displayRowColHeadings = getActiveSheet().isDisplayRowColHeadings(); markAsDirty(); } /** * This method should be always called when the selected cell has changed so * proper actions can be triggered for possible custom component inside the * cell. */ protected void loadCustomEditorOnSelectedCell() { CellReference selectedCellReference = selectionManager.getSelectedCellReference(); if (selectedCellReference != null && customComponentFactory != null) { final short col = selectedCellReference.getCol(); final int row = selectedCellReference.getRow(); final String key = SpreadsheetUtil.toKey(col + 1, row + 1); Map<String, String> cellKeysToEditorIdMap = getState(false).cellKeysToEditorIdMap; if (cellKeysToEditorIdMap != null && cellKeysToEditorIdMap.containsKey(key) && customComponents != null) { String componentId = getState(false).cellKeysToEditorIdMap.get(key); for (Component c : customComponents) { if (c.getConnectorId().equals(componentId)) { customComponentFactory.onCustomEditorDisplayed(getCell(row, col), row, col, this, getActiveSheet(), c); return; } } } } } private void reloadSheetNames() { final ArrayList<String> sheetNamesList = new ArrayList<String>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (!workbook.isSheetVeryHidden(i) && !workbook.isSheetHidden(i)) { sheetNamesList.add(workbook.getSheetName(i)); } } getState().sheetNames = sheetNamesList.toArray(new String[sheetNamesList.size()]); } /** * Returns POI model based index for the given Spreadsheet sheet index. * * @param visibleSheetIndex * Index of the sheet within this Spreadsheet, 0-based * @return Index of the sheet within the POI model, or -1 if something went * wrong. 0-based. */ public int getVisibleSheetPOIIndex(int visibleSheetIndex) { int realIndex = -1; int i = -1; do { realIndex++; if (!workbook.isSheetVeryHidden(realIndex) && !workbook.isSheetHidden(realIndex)) { i++; } } while (i < visibleSheetIndex && realIndex < (workbook.getNumberOfSheets() - 1)); return realIndex; } /** * Gets the Spreadsheet sheet-index for the sheet at the given POI index. * Index will be returned for a visible sheet only. * * @param poiSheetIndex * Index of the target sheet within the POI model, 0-based * @return Index of the target sheet in the Spreadsheet, 0-based */ private int getSpreadsheetSheetIndex(int poiSheetIndex) { int ourIndex = -1; for (int i = 0; i <= poiSheetIndex; i++) { if (!workbook.isSheetVeryHidden(i) && !workbook.isSheetHidden(i)) { ourIndex++; } } return ourIndex; } /** * Gets the protection state of the sheet at the given POI index. * * @param poiSheetIndex * Index of the target sheet within the POI model, 0-based * @return true if the target {@link Sheet} is protected, false otherwise. */ public boolean isSheetProtected(int poiSheetIndex) { return workbook.getSheetAt(poiSheetIndex).getProtect(); } /** * Gets the protection state of the current sheet. * * @return true if the current {@link Sheet} is protected, false otherwise. */ public boolean isActiveSheetProtected() { return getState().sheetProtected; } /** * Gets the visibility state of the given cell. * * @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(); } /** * Gets the locked state of the given cell. * * @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; } } /** * Gets the RPC proxy for communication to the client side. * * @return Client RPC proxy instance */ protected SpreadsheetClientRpc getRpcProxy() { return getRpcProxy(SpreadsheetClientRpc.class); } /* * (non-Javadoc) * * @see com.vaadin.ui.AbstractComponent#beforeClientResponse(boolean) */ @Override public void beforeClientResponse(boolean initial) { super.beforeClientResponse(initial); if (reload) { reload = false; getState().reload = true; if (initialSheetSelection == null) { if (sheetState.getSelectedCellsOnSheet(getActiveSheet()) == null) { initialSheetSelection = "A1"; } else { initialSheetSelection = sheetState.getSelectedCellsOnSheet(getActiveSheet()); } } } else { getState().reload = false; } } /** * Gets the currently used style factory for this Spreadsheet. * * @return The current style factory. */ public SpreadsheetStyleFactory getSpreadsheetStyleFactory() { return styler; } /** * Note that modifications done directly with the POI {@link Workbook} API * will not get automatically updated into the Spreadsheet component. * <p> * Use {@link #markCellAsDeleted(Cell, boolean)}, * {@link #markCellAsUpdated(Cell, boolean)}, or * {@link #reloadVisibleCellContents()} to update content. * * @return The currently presented workbook */ public Workbook getWorkbook() { return workbook; } /** * Reloads the component with the given Workbook. * * @param workbook * New workbook to load */ public void setWorkbook(Workbook workbook) { if (workbook == null) { throw new NullPointerException("Cannot open a null workbook with Spreadsheet component."); } SpreadsheetFactory.reloadSpreadsheetComponent(this, workbook); } /** * Note that modifications done directly with the POI {@link Sheet} API will * not get automatically updated into the Spreadsheet component. * <p> * Use {@link #markCellAsDeleted(Cell, boolean)}, * {@link #markCellAsUpdated(Cell, boolean)}, or * {@link #reloadVisibleCellContents()} to update content. * * @return The currently active (= visible) sheet */ public Sheet getActiveSheet() { return workbook.getSheetAt(workbook.getActiveSheetIndex()); } /** * Updates the given range of cells. Takes frozen panes in to account. * * NOTE: Does not run style updates! */ private void updateRowAndColumnRangeCellData(int r1, int c1, int r2, int c2) { // FIXME should be optimized, should not go through all links, comments // etc. always loadHyperLinks(); loadCellComments(); loadOrUpdateOverlays(); loadPopupButtons(); // custom components not updated here on purpose valueManager.loadCellData(r1, c1, r2, c2); } /** * Sends data of the given cell area to client side. Data is only sent once, * unless there are changes. Cells with custom components are skipped. * * @param firstRow * Index of the starting row, 1-based * @param firstColumn * Index of the starting column, 1-based * @param lastRow * Index of the ending row, 1-based * @param lastColumn * Index of the ending column, 1-based */ protected void loadCells(int firstRow, int firstColumn, int lastRow, int lastColumn) { loadCustomComponents(); loadHyperLinks(); loadCellComments(); loadOrUpdateOverlays(); loadTables(); loadPopupButtons(); valueManager.loadCellData(firstRow, firstColumn, lastRow, lastColumn); loadCustomEditorOnSelectedCell(); } void onLinkCellClick(int row, int column) { Cell cell = getActiveSheet().getRow(row - 1).getCell(column - 1); if (hyperlinkCellClickHandler != null) { hyperlinkCellClickHandler.onHyperLinkCellClick(cell, cell.getHyperlink(), Spreadsheet.this); } else { DefaultHyperlinkCellClickHandler.get().onHyperLinkCellClick(cell, cell.getHyperlink(), Spreadsheet.this); } } void onRowResized(Map<Integer, Float> newRowSizes, int row1, int col1, int row2, int col2) { SizeChangeCommand command = new SizeChangeCommand(this, Type.ROW); command.captureValues(newRowSizes.keySet().toArray(new Integer[newRowSizes.size()])); historyManager.addCommand(command); for (Entry<Integer, Float> entry : newRowSizes.entrySet()) { int index = entry.getKey(); float height = entry.getValue(); setRowHeight(index - 1, height); } if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; } loadCells(row1, col1, row2, col2); } /** * Sets the row height for currently active sheet. Updates both POI model * and the visible sheet. * * @param index * Index of target row, 0-based * @param height * New row height in points */ public void setRowHeight(int index, float height) { if (height == 0.0F) { setRowHidden(index, true); } else { Row row = getActiveSheet().getRow(index); if (getState().hiddenRowIndexes.contains(Integer.valueOf(index + 1))) { getState().hiddenRowIndexes.remove(Integer.valueOf(index + 1)); if (row != null && row.getZeroHeight()) { row.setZeroHeight(false); } } getState().rowH[index] = height; if (row == null) { row = getActiveSheet().createRow(index); } row.setHeightInPoints(height); } } void onColumnResized(Map<Integer, Integer> newColumnSizes, int row1, int col1, int row2, int col2) { SizeChangeCommand command = new SizeChangeCommand(this, Type.COLUMN); command.captureValues(newColumnSizes.keySet().toArray(new Integer[newColumnSizes.size()])); historyManager.addCommand(command); for (Entry<Integer, Integer> entry : newColumnSizes.entrySet()) { int index = entry.getKey(); int width = entry.getValue(); setColumnWidth(index - 1, width); } if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; } loadCells(row1, col1, row2, col2); } /** * Sets the column width in pixels (using conversion) for the currently * active sheet. Updates both POI model and the visible sheet. * * @param index * Index of target column, 0-based * @param width * New column width in pixels */ public void setColumnWidth(int index, int width) { if (width == 0) { setColumnHidden(index, true); } else { if (getState().hiddenColumnIndexes.contains(Integer.valueOf(index + 1))) { getState().hiddenColumnIndexes.remove(Integer.valueOf(index + 1)); } if (getActiveSheet().isColumnHidden(index)) { getActiveSheet().setColumnHidden(index, false); } getState().colW[index] = width; getActiveSheet().setColumnWidth(index, SpreadsheetUtil.pixel2WidthUnits(width)); if (getActiveSheet() instanceof XSSFSheet) { ((XSSFSheet) getActiveSheet()).getColumnHelper().cleanColumns(); } getCellValueManager().clearCacheForColumn(index + 1); getCellValueManager().loadCellData(firstRow, index + 1, lastRow, index + 1); } } void loadHyperLinks() { if (getState(false).hyperlinksTooltips == null) { getState(false).hyperlinksTooltips = new HashMap<String, String>(); } else { getState().hyperlinksTooltips.clear(); } if (getLastFrozenRow() > 0 && getLastFrozenColumn() > 0 && !topLeftCellHyperlinksLoaded) { loadHyperLinks(1, 1, getLastFrozenRow(), getLastFrozenColumn()); } if (getLastFrozenRow() > 0) { loadHyperLinks(1, firstColumn, getLastFrozenRow(), lastColumn); } if (getLastFrozenColumn() > 0) { loadHyperLinks(firstRow, 1, lastRow, getLastFrozenColumn()); } loadHyperLinks(firstRow, firstColumn, lastRow, lastColumn); } private void loadHyperLinks(int r1, int c1, int r2, int c2) { for (int r = r1 - 1; r < r2; r++) { final Row row = getActiveSheet().getRow(r); if (row != null) { for (int c = c1 - 1; c < c2; c++) { Cell cell = row.getCell(c); if (cell != null) { try { Hyperlink link = cell.getHyperlink(); if (link != null) { if (link instanceof XSSFHyperlink) { String tooltip = ((XSSFHyperlink) link).getTooltip(); // Show address if no defined tooltip (like // in // excel) if (tooltip == null) { tooltip = link.getAddress(); } getState().hyperlinksTooltips.put(SpreadsheetUtil.toKey(c + 1, r + 1), tooltip); } else { getState().hyperlinksTooltips.put(SpreadsheetUtil.toKey(c + 1, r + 1), link.getAddress()); } } else { // Check if the cell has HYPERLINK function if (DefaultHyperlinkCellClickHandler.isHyperlinkFormulaCell(cell)) { getState().hyperlinksTooltips.put(SpreadsheetUtil.toKey(c + 1, r + 1), DefaultHyperlinkCellClickHandler.getHyperlinkFunctionCellAddress(cell)); } } } catch (XmlValueDisconnectedException exc) { LOGGER.log(Level.FINEST, exc.getMessage(), exc); } } } } } } private void loadOrUpdateOverlays() { // Fixes the issue of overlays being lost when creating or removing // frozen rows/columns. More like a kludge, a real solution is yet to be // found. if (!hasSheetOverlays()) { SpreadsheetFactory.loadSheetOverlays(this); } if (hasSheetOverlays()) { // reload images from POI because row / column sizes have changed // currently doesn't effect anything because POI doesn't update the // image anchor data after resizing if (reloadImageSizesFromPOI) { clearSheetOverlays(); SpreadsheetFactory.loadSheetOverlays(this); reloadImageSizesFromPOI = false; } for (final SheetOverlayWrapper overlay : sheetOverlays) { if (isOverlayVisible(overlay)) { addOverlayData(overlay); overlay.setVisible(true); } else { // was visible but went out of visibility if (overlay.isVisible()) { removeOverlayData(overlay); overlay.setVisible(false); } } } } } /** * Adds necessary data to display the overlay in the current view. */ private void addOverlayData(final SheetOverlayWrapper overlay) { if (overlay.getComponent(true) != null) { registerCustomComponent(overlay.getComponent(true)); overlayComponents.add(overlay.getComponent(true)); } if (overlay.getId() != null && overlay.getResource() != null) { setResource(overlay.getId(), overlay.getResource()); } if (overlay.getId() != null) { if (getState().overlays == null) { getState().overlays = new HashMap<String, OverlayInfo>(); } getState().overlays.put(overlay.getId(), createOverlayInfo(overlay)); overlay.setOverlayChangeListener(new OverlayChangeListener() { @Override public void overlayChanged() { loadOrUpdateOverlays(); } }); } } /** * Undoes what addOverlayData did. */ private void removeOverlayData(final SheetOverlayWrapper overlay) { if (overlay.getId() != null) { if (getState().overlays != null) { getState().overlays.remove(overlay.getId()); } setResource(overlay.getId(), null); } if (overlay.getComponent(false) != null) { overlayComponents.remove(overlay.getComponent(false)); unRegisterCustomComponent(overlay.getComponent(false)); } } /** * Decides if overlay is visible in the current view. */ private boolean isOverlayVisible(SheetOverlayWrapper overlay) { int col1 = overlay.getAnchor().getCol1(); int col2 = overlay.getAnchor().getCol2(); int row1 = overlay.getAnchor().getRow1(); int row2 = overlay.getAnchor().getRow2(); // type=2, doesn't size with cells final boolean isType2 = (col2 == 0 && row2 == 0); if (!isType2) { // to ensure compatibility with grouping/hidden columns if (isColumnRangeHidden(col1, col2) || isRowRangeHidden(row1, row2)) { return false; } } int horizontalSplitPosition = getLastFrozenColumn(); int verticalSplitPosition = getLastFrozenRow(); // the sheet is divided into four areas by vertical and horizontal split boolean visibleInArea1 = horizontalSplitPosition > 0 && verticalSplitPosition > 0 && overlay.isVisible(1, 1, verticalSplitPosition, horizontalSplitPosition); boolean visibleInArea2 = horizontalSplitPosition > 0 && overlay.isVisible(firstRow, 1, lastRow, horizontalSplitPosition); boolean visibleInArea3 = verticalSplitPosition > 0 && overlay.isVisible(1, firstColumn, verticalSplitPosition, lastColumn); boolean visibleInArea4 = overlay.isVisible(firstRow, firstColumn, lastRow, lastColumn); return visibleInArea1 || visibleInArea2 || visibleInArea3 || visibleInArea4; } /** * Return true if all the rows in the range are hidden (including row2). */ private boolean isRowRangeHidden(int row1, int row2) { for (int row = row1; row <= row2; row++) { if (!isRowHidden(row)) { return false; } } return true; } /** * Return true if all the columns in the range are hidden (including col2). */ private boolean isColumnRangeHidden(int col1, int col2) { for (int col = col1; col <= col2; col++) { if (!isColumnHidden(col)) { return false; } } return true; } private OverlayInfo createOverlayInfo(SheetOverlayWrapper overlayWrapper) { OverlayInfo info = new OverlayInfo(overlayWrapper.getType()); Sheet sheet = getActiveSheet(); int col = overlayWrapper.getAnchor().getCol1(); while (isColumnHidden(col)) { col++; } int row = overlayWrapper.getAnchor().getRow1(); while (isRowHidden(row)) { row++; } info.col = col + 1; // 1-based info.row = row + 1; // 1-based info.height = overlayWrapper.getHeight(sheet, getState(false).rowH); info.width = overlayWrapper.getWidth(sheet, getState(false).colW, getState(false).defColW); // FIXME: height and width can be -1, it is never handled anywhere // if original start row/column is hidden, use 0 dy/dx if (col == overlayWrapper.getAnchor().getCol1()) { info.dx = overlayWrapper.getDx1(sheet); } if (row == overlayWrapper.getAnchor().getRow1()) { info.dy = overlayWrapper.getDy1(sheet); } return info; } private void loadCellComments() { if (firstColumn == -1) { // Spreadsheet not loaded. This method will be called again. return; } if (getState(false).cellComments == null) { getState(false).cellComments = new HashMap<String, String>(); } else { getState().cellComments.clear(); } if (getState(false).cellCommentAuthors == null) { getState(false).cellCommentAuthors = new HashMap<String, String>(); } else { getState().cellCommentAuthors.clear(); } if (getState(false).visibleCellComments == null) { getState(false).visibleCellComments = new ArrayList<String>(); } else { getState().visibleCellComments.clear(); } if (getState(false).invalidFormulaCells == null) { getState(false).invalidFormulaCells = new HashSet<String>(); } else { getState().invalidFormulaCells.clear(); } if (getLastFrozenRow() > 0 && getLastFrozenColumn() > 0 && !topLeftCellCommentsLoaded) { loadCellComments(1, 1, getLastFrozenRow(), getLastFrozenColumn()); } if (getLastFrozenRow() > 0) { loadCellComments(1, firstColumn, getLastFrozenRow(), lastColumn); } if (getLastFrozenColumn() > 0) { loadCellComments(firstRow, 1, lastRow, getLastFrozenColumn()); } loadCellComments(firstRow, firstColumn, lastRow, lastColumn); } private void loadCellComments(int r1, int c1, int r2, int c2) { Sheet sheet = getActiveSheet(); for (int r = r1 - 1; r < r2; r++) { Row row = sheet.getRow(r); if (row != null && row.getZeroHeight()) { continue; } for (int c = c1 - 1; c < c2; c++) { if (sheet.isColumnHidden(c)) { continue; } int c_one_based = c + 1; int row_one_based = r + 1; MergedRegion region = mergedRegionContainer.getMergedRegion(c_one_based, row_one_based); // do not add comments that are "below" merged regions. // client side handles cases where comment "moves" (because // shifting etc.) from merged cell into basic or vice versa. if (region == null || region.col1 == c_one_based && region.row1 == row_one_based) { Comment comment = sheet.getCellComment(r, c); String key = SpreadsheetUtil.toKey(c_one_based, row_one_based); if (comment != null) { // by default comments are shown when mouse is over the // red // triangle on the cell's top right corner. the comment // position is calculated so that it is completely // visible. getState().cellComments.put(key, comment.getString().getString()); getState().cellCommentAuthors.put(key, comment.getAuthor()); if (comment.isVisible()) { getState().visibleCellComments.add(key); } } if (isMarkedAsInvalidFormula(c_one_based, row_one_based)) { getState().invalidFormulaCells.add(key); } } else { c = region.col2 - 1; } } } } /** * Loads the custom components for the currently viewed cells and clears * previous components that are not currently visible. */ private void loadCustomComponents() { if (customComponentFactory != null) { if (getState().cellKeysToEditorIdMap == null) { getState().cellKeysToEditorIdMap = new HashMap<String, String>(); } else { getState().cellKeysToEditorIdMap.clear(); } if (getState().componentIDtoCellKeysMap == null) { getState().componentIDtoCellKeysMap = new HashMap<String, String>(); } else { getState().componentIDtoCellKeysMap.clear(); } if (customComponents == null) { customComponents = new HashSet<Component>(); } HashSet<Component> newCustomComponents = new HashSet<Component>(); Set<Integer> rowsWithComponents = new HashSet<Integer>(); // iteration indexes 0-based int verticalSplitPosition = getLastFrozenRow(); int horizontalSplitPosition = getLastFrozenColumn(); if (verticalSplitPosition > 0 && horizontalSplitPosition > 0) { // top left pane loadRangeComponents(newCustomComponents, rowsWithComponents, 1, 1, verticalSplitPosition, horizontalSplitPosition); } if (verticalSplitPosition > 0) { // top right pane loadRangeComponents(newCustomComponents, rowsWithComponents, 1, firstColumn, verticalSplitPosition, lastColumn); } if (horizontalSplitPosition > 0) { // bottom left pane loadRangeComponents(newCustomComponents, rowsWithComponents, firstRow, 1, lastRow, horizontalSplitPosition); } loadRangeComponents(newCustomComponents, rowsWithComponents, firstRow, firstColumn, lastRow, lastColumn); // unregister old for (Iterator<Component> i = customComponents.iterator(); i.hasNext();) { Component c = i.next(); if (!newCustomComponents.contains(c)) { unRegisterCustomComponent(c); i.remove(); } } customComponents = newCustomComponents; if (!rowsWithComponents.isEmpty()) { handleRowSizes(rowsWithComponents); } } else { getState().cellKeysToEditorIdMap = null; getState().componentIDtoCellKeysMap = null; if (customComponents != null && !customComponents.isEmpty()) { for (Component c : customComponents) { unRegisterCustomComponent(c); } customComponents.clear(); } handleRowSizes(new HashSet<Integer>()); } } void loadRangeComponents(HashSet<Component> newCustomComponents, Set<Integer> rowsWithComponents, int row1, int col1, int row2, int col2) { for (int r = row1 - 1; r < row2; r++) { final Row row = getActiveSheet().getRow(r); for (int c = col1 - 1; c < col2; c++) { // Cells that are inside a merged region are skipped: MergedRegion region = mergedRegionContainer.getMergedRegion(c + 1, r + 1); if (region == null || (region.col1 == (c + 1) && region.row1 == (r + 1))) { Cell cell = null; if (row != null) { cell = row.getCell(c); } // check if the cell has a custom component Component customComponent = customComponentFactory.getCustomComponentForCell(cell, r, c, this, getActiveSheet()); if (customComponent != null) { final String key = SpreadsheetUtil.toKey(c + 1, r + 1); if (!customComponents.contains(customComponent)) { registerCustomComponent(customComponent); } getState().componentIDtoCellKeysMap.put(customComponent.getConnectorId(), key); newCustomComponents.add(customComponent); rowsWithComponents.add(r); } else if (!isCellLocked(cell)) { // no custom component and not locked, check if // the cell has a custom editor Component customEditor = customComponentFactory.getCustomEditorForCell(cell, r, c, this, getActiveSheet()); if (customEditor != null) { final String key = SpreadsheetUtil.toKey(c + 1, r + 1); if (!newCustomComponents.contains(customEditor) && !customComponents.contains(customEditor)) { registerCustomComponent(customEditor); } getState().cellKeysToEditorIdMap.put(key, customEditor.getConnectorId()); newCustomComponents.add(customEditor); rowsWithComponents.add(r); } } } if (region != null) { c = region.col2 - 1; } } } } private void handleRowSizes(Set<Integer> rowsWithComponents) { // Set larger height for new rows with components for (Integer row : rowsWithComponents) { if (isRowHidden(row)) { continue; } float currentHeight = getState(false).rowH[row]; if (currentHeight < getMinimumRowHeightForComponents()) { getState().rowH[row] = getMinimumRowHeightForComponents(); } } // Reset row height for rows which no longer have components if (this.rowsWithComponents != null) { Sheet activeSheet = getActiveSheet(); for (Integer row : this.rowsWithComponents) { if (!rowsWithComponents.contains(row)) { if (isRowHidden(row)) { getState().rowH[row] = 0; } else { Row r = activeSheet.getRow(row); if (r == null) { getState().rowH[row] = activeSheet.getDefaultRowHeightInPoints(); } else { getState().rowH[row] = r.getHeightInPoints(); } } } } } this.rowsWithComponents = rowsWithComponents; } /** * Determines if the cell at the given coordinates is currently visible * (rendered) in the browser. * * @param row * Row index, 1-based * @param col * Column index, 1-based * * @return True if the cell is visible, false otherwise */ private boolean isCellVisible(int row, int col) { int verticalSplitPosition = getLastFrozenRow(); int horizontalSplitPosition = getLastFrozenColumn(); return (col >= firstColumn && col <= lastColumn && row >= firstRow && row <= lastRow) || (col >= 1 && col <= horizontalSplitPosition && row >= 1 && row <= verticalSplitPosition) || (col >= firstColumn && col <= lastColumn && row >= 1 && row <= verticalSplitPosition) || (col >= 1 && col <= horizontalSplitPosition && row >= firstRow && row <= lastRow); } private void registerPopupButton(PopupButton button) { attachedPopupButtons.add(button); registerCustomComponent(button); } private void unRegisterPopupButton(PopupButton button) { attachedPopupButtons.remove(button); unRegisterCustomComponent(button); } private void registerCustomComponent(Component component) { if (!equals(component.getParent())) { component.setParent(this); } } private void unRegisterCustomComponent(Component component) { component.setParent(null); } /** * Set a new component factory for this Spreadsheet. If a {@link Workbook} * has been set, all components will be reloaded. * * @param customComponentFactory * The new component factory to use. */ public void setSpreadsheetComponentFactory(SpreadsheetComponentFactory customComponentFactory) { this.customComponentFactory = customComponentFactory; if (firstRow != -1) { loadCustomComponents(); loadCustomEditorOnSelectedCell(); } else { getState().cellKeysToEditorIdMap = null; if (customComponents != null && !customComponents.isEmpty()) { for (Component c : customComponents) { unRegisterCustomComponent(c); } customComponents.clear(); } } } /** * Gets the current SpreadsheetComponentFactory. * * @return The currently used component factory. */ public SpreadsheetComponentFactory getSpreadsheetComponentFactory() { return customComponentFactory; } /** * Sets a pop-up button to the given cell in the currently active sheet. If * there is already a pop-up button in the given cell, it will be replaced. * <p> * Note that if the active sheet is changed, all pop-up buttons are removed * from the spreadsheet. * * @param cellAddress * address to the target cell, e.g. "C3" * @param popupButton * PopupButton to set for the target cell. Passing null here * removes the pop-up button for the target cell. */ public void setPopup(String cellAddress, PopupButton popupButton) { setPopup(new CellReference(cellAddress), popupButton); } /** * Sets a pop-up button to the given cell in the currently active sheet. If * there is already a pop-up button in the given cell, it will be replaced. * <p> * Note that if the active sheet is changed, all pop-up buttons are removed * from the spreadsheet. * * @param row * Row index of target cell, 0-based * @param col * Column index of target cell, 0-based * @param popupButton * PopupButton to set for the target cell. Passing null here * removes the pop-up button for the target cell. */ public void setPopup(int row, int col, PopupButton popupButton) { setPopup(new CellReference(row, col), popupButton); } /** * Sets a pop-up button to the given cell in the currently active sheet. If * there is already a pop-up button in the given cell, it will be replaced. * <p> * Note that if the active sheet is changed, all pop-up buttons are removed * from the spreadsheet. * * @param cellReference * Reference to the target cell * @param popupButton * PopupButton to set for the target cell. Passing null here * removes the pop-up button for the target cell. */ public void setPopup(CellReference cellReference, PopupButton popupButton) { removePopupButton(cellReference); if (popupButton != null) { CellReference absoluteCellReference = SpreadsheetUtil.relativeToAbsolute(this, cellReference); popupButton.setCellReference(absoluteCellReference); sheetPopupButtons.put(absoluteCellReference, popupButton); if (isCellVisible(absoluteCellReference.getRow() + 1, absoluteCellReference.getCol() + 1)) { registerPopupButton(popupButton); markAsDirty(); } } } private void removePopupButton(CellReference cellReference) { CellReference absoluteCellReference = SpreadsheetUtil.relativeToAbsolute(this, cellReference); PopupButton oldButton = sheetPopupButtons.get(absoluteCellReference); if (oldButton != null) { unRegisterPopupButton(oldButton); sheetPopupButtons.remove(absoluteCellReference); markAsDirty(); } } /** * Registers and unregister pop-up button components for the currently * visible cells. */ private void loadPopupButtons() { if (sheetPopupButtons != null) { for (PopupButton popupButton : sheetPopupButtons.values()) { if (getActiveSheet().getSheetName().equals(popupButton.getCellReference().getSheetName())) { int column = popupButton.getColumn() + 1; int row = popupButton.getRow() + 1; if (isCellVisible(row, column)) { registerPopupButton(popupButton); } else { unRegisterPopupButton(popupButton); } } } } } /** * Registers the given table to this Spreadsheet, meaning that this table * will be reloaded when the active sheet changes to the sheet containing * the table. * <p> * Populating the table content (pop-up button and other content) is the * responsibility of the table, with {@link SpreadsheetTable#reload()}. * <p> * When the sheet is changed to a different sheet than the one that the * table belongs to, the table contents are cleared with * {@link SpreadsheetTable#clear()}. If the table is a filtering table, the * filters are NOT cleared (can be done with * {@link SpreadsheetFilterTable#clearAllFilters()}. * <p> * The pop-up buttons are always removed by the spreadsheet when the sheet * changes. * * @param table * The table to register */ public void registerTable(SpreadsheetTable table) { tables.add(table); } /** * Unregisters the given table from this Spreadsheet - it will no longer get * reloaded when the sheet is changed back to the sheet containing the * table. This does not delete any table content, use * {@link #deleteTable(SpreadsheetTable)} to completely remove the table. * <p> * See {@link #registerTable(SpreadsheetTable)}. * * @param table * The table to unregister */ public void unregisterTable(SpreadsheetTable table) { tables.remove(table); } /** * Deletes the given table: removes it from "memory" (see * {@link #registerTable(SpreadsheetTable)}), clears and removes all * possible filters (if table is a {@link SpreadsheetFilterTable}), and * clears all table pop-up buttons and content. * * @param table * The table to delete */ public void deleteTable(SpreadsheetTable table) { unregisterTable(table); if (table.isTableSheetCurrentlyActive()) { for (PopupButton popupButton : table.getPopupButtons()) { removePopupButton(popupButton.getCellReference()); } if (table instanceof SpreadsheetFilterTable) { ((SpreadsheetFilterTable) table).clearAllFilters(); } table.clear(); } } /** * Gets all the tables that have been registered to this Spreadsheet. See * {@link #registerTable(SpreadsheetTable)}. * * @return All tables for this spreadsheet */ public HashSet<SpreadsheetTable> getTables() { return tables; } /** * Gets the tables that belong to the currently active sheet ( * {@link #getActiveSheet()}). See {@link #registerTable(SpreadsheetTable)}. * * @return All tables for the currently active sheet */ public List<SpreadsheetTable> getTablesForActiveSheet() { List<SpreadsheetTable> temp = new ArrayList<SpreadsheetTable>(); for (SpreadsheetTable table : tables) { if (table.getSheet().equals(getActiveSheet())) { temp.add(table); } } return temp; } /** * Reload tables for current sheet */ private void loadTables() { if (!tablesLoaded) { for (SpreadsheetTable table : tables) { if (table.getSheet().equals(getActiveSheet())) { table.reload(); } } tablesLoaded = true; } } /** * Returns the formatted value for the given cell, using the * {@link DataFormatter} with the current locale. * * See {@link DataFormatter#formatCellValue(Cell, FormulaEvaluator)}. * * @param cell * Cell to get the value from * @return Formatted value */ public final String getCellValue(Cell cell) { return valueManager.getDataFormatter().formatCellValue(cell, valueManager.getFormulaEvaluator()); } /** * Gets grid line visibility for the currently active sheet. * * @return True if grid lines are visible, false if they are hidden */ public boolean isGridlinesVisible() { if (getActiveSheet() != null) { return getActiveSheet().isDisplayGridlines(); } return true; } /** * Sets grid line visibility for the currently active sheet. * * @param visible * True to show grid lines, false to hide them */ public void setGridlinesVisible(boolean visible) { if (getActiveSheet() == null) { throw new NullPointerException("no active sheet"); } getActiveSheet().setDisplayGridlines(visible); getState().displayGridlines = visible; } /** * Gets row and column heading visibility for the currently active sheet. * * @return true if headings are visible, false if they are hidden */ public boolean isRowColHeadingsVisible() { if (getActiveSheet() != null) { return getActiveSheet().isDisplayRowColHeadings(); } return true; } /** * Sets row and column heading visibility for the currently active sheet. * * @param visible * true to show headings, false to hide them */ public void setRowColHeadingsVisible(boolean visible) { if (getActiveSheet() == null) { throw new NullPointerException("no active sheet"); } getActiveSheet().setDisplayRowColHeadings(visible); getState().displayRowColHeadings = visible; } /** * This is a parent class for a value change events. */ public abstract static class ValueChangeEvent extends Component.Event { private final Set<CellReference> changedCells; public ValueChangeEvent(Component source, Set<CellReference> changedCells) { super(source); this.changedCells = changedCells; } public Set<CellReference> getChangedCells() { return changedCells; } } /** * This event is fired when cell value changes. */ public static class CellValueChangeEvent extends ValueChangeEvent { public CellValueChangeEvent(Component source, Set<CellReference> changedCells) { super(source, changedCells); } } /** * This event is fired when the value of a cell referenced by a formula cell * changes making the formula value change */ public static class FormulaValueChangeEvent extends ValueChangeEvent { public FormulaValueChangeEvent(Component source, Set<CellReference> changedCells) { super(source, changedCells); } } /** * This event is fired when cell selection changes. */ public static class SelectionChangeEvent extends Component.Event { private final CellReference selectedCellReference; private final List<CellReference> individualSelectedCells; private final CellRangeAddress selectedCellMergedRegion; private final List<CellRangeAddress> cellRangeAddresses; /** * Creates a new selection change event. * * @param source * Source Spreadsheet * @param selectedCellReference * see {@link #getSelectedCellReference()} * @param individualSelectedCells * see {@link #getIndividualSelectedCells()} * @param selectedCellMergedRegion * see {@link #getSelectedCellMergedRegion()} * @param cellRangeAddresses * see {@link #getCellRangeAddresses()} */ public SelectionChangeEvent(Component source, CellReference selectedCellReference, List<CellReference> individualSelectedCells, CellRangeAddress selectedCellMergedRegion, List<CellRangeAddress> cellRangeAddresses) { super(source); this.selectedCellReference = selectedCellReference; this.individualSelectedCells = individualSelectedCells; this.selectedCellMergedRegion = selectedCellMergedRegion; this.cellRangeAddresses = cellRangeAddresses; } /** * Gets the Spreadsheet where this event happened. * * @return Source Spreadsheet */ public Spreadsheet getSpreadsheet() { return (Spreadsheet) getSource(); } /** * Returns reference to the currently selected single cell OR in case of * multiple selections the last cell clicked OR in case of area select * the cell from which the area selection was started. * * @return CellReference to the single selected cell, or the last cell * selected manually (e.g. with ctrl+mouseclick) */ public CellReference getSelectedCellReference() { return selectedCellReference; } /** * Gets all the individually selected single cells in the current * selection. * * @return All non-contiguously selected cells (e.g. with * ctrl+mouseclick) */ public List<CellReference> getIndividualSelectedCells() { return individualSelectedCells; } /** * Gets the merged region the single selected cell is a part of, if * applicable. * * @return The {@link CellRangeAddress} described the merged region the * single selected cell is part of, if any. */ public CellRangeAddress getSelectedCellMergedRegion() { return selectedCellMergedRegion; } /** * Gets all separately selected cell ranges. * * @return All separately selected cell ranges (e.g. with * ctrl+shift+mouseclick) */ public List<CellRangeAddress> getCellRangeAddresses() { return cellRangeAddresses; } /** * Gets a combination of all selected cells. * * @return A combination of all selected cells, regardless of selection * mode. Doesn't contain duplicates. */ public Set<CellReference> getAllSelectedCells() { return Spreadsheet.getAllSelectedCells(selectedCellReference, individualSelectedCells, cellRangeAddresses); } } private static Set<CellReference> getAllSelectedCells(CellReference selectedCellReference, List<CellReference> individualSelectedCells, List<CellRangeAddress> cellRangeAddresses) { Set<CellReference> cells = new HashSet<CellReference>(); for (CellReference r : individualSelectedCells) { cells.add(r); } cells.add(selectedCellReference); if (cellRangeAddresses != null) { for (CellRangeAddress a : cellRangeAddresses) { for (int x = a.getFirstColumn(); x <= a.getLastColumn(); x++) { for (int y = a.getFirstRow(); y <= a.getLastRow(); y++) { cells.add(new CellReference(y, x)); } } } } return cells; } /** * Used for knowing when a user has changed the cell selection in any way. */ public interface SelectionChangeListener extends Serializable { public static final Method SELECTION_CHANGE_METHOD = ReflectTools.findMethod(SelectionChangeListener.class, "onSelectionChange", SelectionChangeEvent.class); /** * This is called when user changes cell selection. * * @param event * SelectionChangeEvent that happened */ public void onSelectionChange(SelectionChangeEvent event); } /** * Used for knowing when a user has changed the cell value in Spreadsheet * UI. */ public interface CellValueChangeListener extends Serializable { public static final Method CELL_VALUE_CHANGE_METHOD = ReflectTools.findMethod(CellValueChangeListener.class, "onCellValueChange", CellValueChangeEvent.class); /** * This is called when user changes the cell value in Spreadsheet. * * @param event * CellValueChangeEvent that happened */ public void onCellValueChange(CellValueChangeEvent event); } /** * Used for knowing when a cell referenced by a formula cell has changed in * the Spreadsheet UI making the formula value change */ public interface FormulaValueChangeListener extends Serializable { public static final Method FORMULA_VALUE_CHANGE_METHOD = ReflectTools.findMethod( FormulaValueChangeListener.class, "onFormulaValueChange", FormulaValueChangeEvent.class); /** * This is called when user changes the cell value in Spreadsheet. * * @param event * FormulaValueChangeEvent that happened */ public void onFormulaValueChange(FormulaValueChangeEvent event); } /** * Adds the given SelectionChangeListener to this Spreadsheet. * * @param listener * Listener to add. */ public void addSelectionChangeListener(SelectionChangeListener listener) { addListener(SelectionChangeEvent.class, listener, SelectionChangeListener.SELECTION_CHANGE_METHOD); } /** * Adds the given CellValueChangeListener to this Spreadsheet. * * @param listener * Listener to add. */ public void addCellValueChangeListener(CellValueChangeListener listener) { addListener(CellValueChangeEvent.class, listener, CellValueChangeListener.CELL_VALUE_CHANGE_METHOD); } /** * Adds the given FormulaValueChangeListener to this Spreadsheet. * * @param listener * Listener to add. */ public void addFormulaValueChangeListener(FormulaValueChangeListener listener) { addListener(FormulaValueChangeEvent.class, listener, FormulaValueChangeListener.FORMULA_VALUE_CHANGE_METHOD); } /** * Removes the given SelectionChangeListener from this Spreadsheet. * * @param listener * Listener to remove. */ public void removeSelectionChangeListener(SelectionChangeListener listener) { removeListener(SelectionChangeEvent.class, listener, SelectionChangeListener.SELECTION_CHANGE_METHOD); } /** * Removes the given CellValueChangeListener from this Spreadsheet. * * @param listener * Listener to remove. */ public void removeCellValueChangeListener(CellValueChangeListener listener) { removeListener(CellValueChangeEvent.class, listener, CellValueChangeListener.CELL_VALUE_CHANGE_METHOD); } /** * An event that is fired when an attempt to modify a locked cell has been * made. */ public static class ProtectedEditEvent extends Component.Event { public ProtectedEditEvent(Component source) { super(source); } } /** * A listener for when an attempt to modify a locked cell has been made. */ public interface ProtectedEditListener extends Serializable { public static final Method SELECTION_CHANGE_METHOD = ReflectTools.findMethod(ProtectedEditListener.class, "writeAttempted", ProtectedEditEvent.class); /** * Called when the SpreadSheet detects that the client tried to edit a * locked cell (usually by pressing a key). Method is not called for * each such event; instead, the SpreadSheet waits a second before * sending a new event. This is done to give the user time to react to * the results of this call (e.g. showing a notification). * * @param event * ProtectedEditEvent that happened */ public void writeAttempted(ProtectedEditEvent event); } /** * Add listener for when an attempt to modify a locked cell has been made. * * @param listener * The listener to add. */ public void addProtectedEditListener(ProtectedEditListener listener) { addListener(ProtectedEditEvent.class, listener, ProtectedEditListener.SELECTION_CHANGE_METHOD); } /** * Removes the given ProtectedEditListener. * * @param listener * The listener to remove. */ public void removeProtectedEditListener(ProtectedEditListener listener) { removeListener(ProtectedEditEvent.class, listener, ProtectedEditListener.SELECTION_CHANGE_METHOD); } /** * Creates or removes a freeze pane from the currently active sheet. * * If both colSplit and rowSplit are zero then the existing freeze pane is * removed. * * @param rowSplit * Vertical position of the split, 1-based row index * @param colSplit * Horizontal position of the split, 1-based column index */ public void createFreezePane(int rowSplit, int colSplit) { getActiveSheet().createFreezePane(colSplit, rowSplit); SpreadsheetFactory.loadFreezePane(this); reloadActiveSheetData(); } /** * Removes the freeze pane from the currently active sheet if one is * present. */ public void removeFreezePane() { PaneInformation paneInformation = getActiveSheet().getPaneInformation(); if (paneInformation != null && paneInformation.isFreezePane()) { getActiveSheet().createFreezePane(0, 0); SpreadsheetFactory.loadFreezePane(this); reloadActiveSheetData(); } } /** * Gets a reference to the current single selected cell. * * @return Reference to the currently selected single cell. * <p> * <em>NOTE:</em> other cells might also be selected: use * {@link #addSelectionChangeListener(SelectionChangeListener)} to * get notified for all selection changes or call * {@link #getSelectedCellReferences()}. */ public CellReference getSelectedCellReference() { return selectionManager.getSelectedCellReference(); } /** * Gets all the currently selected cells. * * @return References to all currently selected cells. */ public Set<CellReference> getSelectedCellReferences() { SelectionChangeEvent event = selectionManager.getLatestSelectionEvent(); if (event == null) { return new HashSet<CellReference>(); } else { return event.getAllSelectedCells(); } } /** * An event that is fired to registered listeners when the selected sheet * has been changed. */ public static class SheetChangeEvent extends Component.Event { private final Sheet newSheet; private final Sheet previousSheet; private final int newSheetVisibleIndex; private final int newSheetPOIIndex; /** * Creates a new SheetChangeEvent. * * @param source * Spreadsheet that triggered the event * @param newSheet * New selection * @param previousSheet * Previous selection * @param newSheetVisibleIndex * New visible index of selection * @param newSheetPOIIndex * New POI index of selection */ public SheetChangeEvent(Component source, Sheet newSheet, Sheet previousSheet, int newSheetVisibleIndex, int newSheetPOIIndex) { super(source); this.newSheet = newSheet; this.previousSheet = previousSheet; this.newSheetVisibleIndex = newSheetVisibleIndex; this.newSheetPOIIndex = newSheetPOIIndex; } /** * Gets the newly selected sheet. * * @return The new selection */ public Sheet getNewSheet() { return newSheet; } /** * Gets the sheet that was previously selected. * * @return The previous selection */ public Sheet getPreviousSheet() { return previousSheet; } /** * Gets the index of the newly selected sheet among all visible sheets. * * @return Index of new selection among visible sheets */ public int getNewSheetVisibleIndex() { return newSheetVisibleIndex; } /** * Gets the POI index of the newly selected sheet. * * @return POI index of new selection */ public int getNewSheetPOIIndex() { return newSheetPOIIndex; } } /** * A listener for when a sheet is selected. */ public interface SheetChangeListener extends Serializable { public static final Method SHEET_CHANGE_METHOD = ReflectTools.findMethod(SheetChangeListener.class, "onSheetChange", SheetChangeEvent.class); /** * This method is called an all registered listeners when the selected * sheet has changed. * * @param event * Sheet selection event */ public void onSheetChange(SheetChangeEvent event); } /** * Adds the given SheetChangeListener to this Spreadsheet. * * @param listener * Listener to add */ public void addSheetChangeListener(SheetChangeListener listener) { addListener(SheetChangeEvent.class, listener, SheetChangeListener.SHEET_CHANGE_METHOD); } /** * Removes the given SheetChangeListener from this Spreadsheet. * * @param listener * Listener to remove */ public void removeSheetChangeListener(SheetChangeListener listener) { removeListener(SheetChangeEvent.class, listener, SheetChangeListener.SHEET_CHANGE_METHOD); } private void fireSheetChangeEvent(Sheet previousSheet, Sheet newSheet) { int newSheetPOIIndex = workbook.getActiveSheetIndex(); fireEvent(new SheetChangeEvent(this, newSheet, previousSheet, getSpreadsheetSheetIndex(newSheetPOIIndex), newSheetPOIIndex)); } /* * (non-Javadoc) * * @see com.vaadin.ui.HasComponents#iterator() */ @SuppressWarnings("unchecked") @Override public Iterator<Component> iterator() { return new IteratorChain<Component>(Arrays.asList(customComponents.iterator(), attachedPopupButtons.iterator(), overlayComponents.iterator())); } /** * This is called when the client-side connector has been initialized. */ protected void onConnectorInit() { reloadCellDataOnNextScroll = true; valueManager.clearCachedContent(); } /** * Reloads all data from the current spreadsheet and performs a full * re-render. <br/> * Functionally same as calling {@link #setWorkbook(Workbook)} with * {@link #getWorkbook()} parameter. */ public void reload() { setWorkbook(getWorkbook()); } /** * Sets the content of the status label. * * @param value * The new content. Can not be HTML. */ public void setStatusLabelValue(String value) { getState().infoLabelValue = value; } /** * Gets the content of the status label * * @return Current content of the status label. */ public String getStatusLabelValue() { return getState().infoLabelValue; } /** * Selects the cell at the given coordinates * * @param row * Row index, 0-based * @param col * Column index, 0-based */ public void setSelection(int row, int col) { setSelectionRange(row, col, row, col); } /** * Selects the given range, using the cell at row1 and col1 as an anchor. * * @param row1 * Index of the first row of the area, 0-based * @param col1 * Index of the first column of the area, 0-based * @param row2 * Index of the last row of the area, 0-based * @param col2 * Index of the last column of the area, 0-based */ public void setSelectionRange(int row1, int col1, int row2, int col2) { CellReference ref = new CellReference(row1, col1); CellRangeAddress cra = new CellRangeAddress(row1, row2, col1, col2); selectionManager.handleCellRangeSelection(ref, cra, true); } /** * Selects the cell(s) at the given coordinates * * @param selectionRange * The wanted range, e.g. "A3" or "B3:C5" */ public void setSelection(String selectionRange) { CellRangeAddress cra = CellRangeAddress.valueOf(selectionRange); setSelectionRange(cra.getFirstRow(), cra.getFirstColumn(), cra.getLastRow(), cra.getLastColumn()); } /** * Gets the ConditionalFormatter * * @return the {@link ConditionalFormatter} used by this {@link Spreadsheet} */ public ConditionalFormatter getConditionalFormatter() { return conditionalFormatter; } /** * Disposes the current {@link Workbook}, if any, and loads a new empty XSLX * Workbook. * * Note: Discards all data. Be sure to write out the old Workbook if needed. */ public void reset() { SpreadsheetFactory.loadNewXLSXSpreadsheet(this); srcUri = null; } /* Attribute names for declarative format support. */ private static final String ATTR_ACTIVE_SHEET = "active-sheet-index"; private static final String ATTR_DEFAULT_COL_WIDTH = "default-column-width"; private static final String ATTR_DEFAULT_COL_COUNT = "default-column-count"; private static final String ATTR_DEFAULT_ROW_COUNT = "default-row-count"; private static final String ATTR_DEFAULT_ROW_HEIGHT = "default-row-height"; private static final String ATTR_NO_GRIDLINES = "no-gridlines"; private static final String ATTR_NO_HEADINGS = "no-headings"; private static final String ATTR_NO_FUNCTION_BAR = "no-function-bar"; private static final String ATTR_NO_SHEET_SELECTION_BAR = "no-sheetselection-bar"; private static final String ATTR_SRC = "src"; private CommentAuthorProvider commentAuthorProvider; /* * (non-Javadoc) * * @see com.vaadin.ui.AbstractComponent#readDesign(org.jsoup.nodes.Element, * com.vaadin.ui.declarative.DesignContext) */ @Override public void readDesign(Element design, DesignContext designContext) { super.readDesign(design, designContext); Attributes attr = design.attributes(); if (attr.hasKey(ATTR_SRC)) { String src = DesignAttributeHandler.readAttribute(ATTR_SRC, attr, String.class); try { URL url = new URL(src); read(url.openStream()); srcUri = src; } catch (MalformedURLException e) { LOGGER.log(Level.SEVERE, "Failed to parse the provided URI.", e); } catch (IOException e) { LOGGER.log(Level.SEVERE, "Failed to read Excel file from provided URI.", e); } } if (attr.hasKey(ATTR_DEFAULT_COL_COUNT)) { Integer colCount = DesignAttributeHandler.readAttribute(ATTR_DEFAULT_COL_COUNT, attr, Integer.class); setDefaultColumnCount(colCount); } if (attr.hasKey(ATTR_DEFAULT_COL_WIDTH)) { Integer colWidth = DesignAttributeHandler.readAttribute(ATTR_DEFAULT_COL_WIDTH, attr, Integer.class); setDefaultColumnWidth(colWidth); } if (attr.hasKey(ATTR_DEFAULT_ROW_COUNT)) { Integer rowCount = DesignAttributeHandler.readAttribute(ATTR_DEFAULT_ROW_COUNT, attr, Integer.class); setDefaultRowCount(rowCount); } if (attr.hasKey(ATTR_DEFAULT_ROW_HEIGHT)) { Float rowHeight = DesignAttributeHandler.readAttribute(ATTR_DEFAULT_ROW_HEIGHT, attr, Float.class); setDefaultRowHeight(rowHeight); } if (attr.hasKey(ATTR_ACTIVE_SHEET)) { Integer activeSheet = DesignAttributeHandler.readAttribute(ATTR_ACTIVE_SHEET, attr, Integer.class); setActiveSheetIndex(activeSheet); } if (attr.hasKey(ATTR_NO_GRIDLINES)) { Boolean noGridlines = DesignAttributeHandler.readAttribute(ATTR_NO_GRIDLINES, attr, Boolean.class); setGridlinesVisible(!noGridlines); } if (attr.hasKey(ATTR_NO_HEADINGS)) { Boolean noHeadings = DesignAttributeHandler.readAttribute(ATTR_NO_HEADINGS, attr, Boolean.class); setRowColHeadingsVisible(!noHeadings); } if (attr.hasKey(ATTR_NO_FUNCTION_BAR)) { Boolean hidden = DesignAttributeHandler.readAttribute(ATTR_NO_FUNCTION_BAR, attr, Boolean.class); setFunctionBarVisible(!hidden); } if (attr.hasKey(ATTR_NO_SHEET_SELECTION_BAR)) { Boolean hidden = DesignAttributeHandler.readAttribute(ATTR_NO_SHEET_SELECTION_BAR, attr, Boolean.class); setSheetSelectionBarVisible(!hidden); } } /* * (non-Javadoc) * * @see com.vaadin.ui.AbstractComponent#getCustomAttributes() */ @Override protected Collection<String> getCustomAttributes() { Collection<String> result = super.getCustomAttributes(); result.add(ATTR_ACTIVE_SHEET); result.add(ATTR_DEFAULT_COL_COUNT); result.add(ATTR_DEFAULT_COL_WIDTH); result.add(ATTR_DEFAULT_ROW_COUNT); result.add(ATTR_DEFAULT_ROW_HEIGHT); result.add(ATTR_NO_GRIDLINES); result.add(ATTR_NO_HEADINGS); result.add(ATTR_NO_FUNCTION_BAR); result.add(ATTR_NO_SHEET_SELECTION_BAR); result.add(ATTR_SRC); return result; } /* * (non-Javadoc) * * @see com.vaadin.ui.AbstractComponent#writeDesign(org.jsoup.nodes.Element, * com.vaadin.ui.declarative.DesignContext) */ @Override public void writeDesign(Element design, DesignContext designContext) { super.writeDesign(design, designContext); Attributes attr = design.attributes(); DesignAttributeHandler.writeAttribute(ATTR_NO_GRIDLINES, attr, !isGridlinesVisible(), false, Boolean.class, designContext); DesignAttributeHandler.writeAttribute(ATTR_NO_HEADINGS, attr, !isRowColHeadingsVisible(), false, Boolean.class, designContext); DesignAttributeHandler.writeAttribute(ATTR_NO_FUNCTION_BAR, attr, !isFunctionBarVisible(), false, Boolean.class, designContext); DesignAttributeHandler.writeAttribute(ATTR_NO_SHEET_SELECTION_BAR, attr, !isSheetSelectionBarVisible(), false, Boolean.class, designContext); DesignAttributeHandler.writeAttribute(ATTR_ACTIVE_SHEET, attr, getActiveSheetIndex(), 0, Integer.class, designContext); DesignAttributeHandler.writeAttribute(ATTR_DEFAULT_COL_COUNT, attr, getDefaultColumnCount(), SpreadsheetFactory.DEFAULT_COLUMNS, Integer.class, designContext); DesignAttributeHandler.writeAttribute(ATTR_DEFAULT_ROW_COUNT, attr, getDefaultRowCount(), SpreadsheetFactory.DEFAULT_ROWS, Integer.class, designContext); if (defaultColWidthSet) { DesignAttributeHandler.writeAttribute(ATTR_DEFAULT_COL_WIDTH, attr, getDefaultColumnWidth(), SpreadsheetUtil.getDefaultColumnWidthInPx(), Integer.class, designContext); } if (defaultRowHeightSet) { DesignAttributeHandler.writeAttribute(ATTR_DEFAULT_ROW_HEIGHT, attr, getDefaultRowHeight(), SpreadsheetFactory.DEFAULT_ROW_HEIGHT_POINTS, Float.class, designContext); } if (srcUri != null) { DesignAttributeHandler.writeAttribute(ATTR_SRC, attr, srcUri, null, String.class, designContext); } } /** * Returns the formatting string that is used when a user enters percentages * into the Spreadsheet. * <p> * Default is "0.00%". * * @return The formatting applied to percentage values when entered by the * user */ public String getDefaultPercentageFormat() { return defaultPercentageFormat; } /** * Sets the formatting string that is used when a user enters percentages * into the Spreadsheet. * <p> * Default is "0.00%". */ public void setDefaultPercentageFormat(String defaultPercentageFormat) { this.defaultPercentageFormat = defaultPercentageFormat; } /** * This interface can be implemented to provide the comment author name set * to new comments in cells. */ public interface CommentAuthorProvider extends Serializable { /** * Gets the author name for a new comment about to be added to the cell * at the given cell reference. * * @param targetCell * Reference to the target cell * @return Comment author name */ public String getAuthorForComment(CellReference targetCell); } /** * Sets the given CommentAuthorProvider to this Spreadsheet. * * @param commentAuthorProvider * New provider */ public void setCommentAuthorProvider(CommentAuthorProvider commentAuthorProvider) { this.commentAuthorProvider = commentAuthorProvider; } /** * Gets the CommentAuthorProvider currently set to this Spreadsheet. * * @return Current provider or null if not set. */ public CommentAuthorProvider getCommentAuthorProvider() { return commentAuthorProvider; } /** * Triggers editing of the cell comment in the given cell reference. Note * that the cell must have a previously set cell comment in order to be able * to edit it. * * @param cr * Reference to the cell containing the comment to edit */ public void editCellComment(CellReference cr) { getRpcProxy().editCellComment(cr.getCol(), cr.getRow()); } /** * Sets the visibility of the top function bar. By default the bar is * visible. * * @param functionBarVisible * True to show the top bar, false to hide it. */ public void setFunctionBarVisible(boolean functionBarVisible) { if (functionBarVisible) { removeStyleName(HIDE_FUNCTION_BAR_STYLE); } else { addStyleName(HIDE_FUNCTION_BAR_STYLE); } } /** * Gets the visibility of the top function bar. By default the bar is * visible. * * @return True if the function bar is visible, false otherwise. */ public boolean isFunctionBarVisible() { return !getStyleName().contains(HIDE_FUNCTION_BAR_STYLE); } /** * Sets the visibility of the bottom sheet selection bar. By default the bar * is visible. * * @param sheetSelectionBarVisible * True to show the sheet selection bar, false to hide it. */ public void setSheetSelectionBarVisible(boolean sheetSelectionBarVisible) { if (sheetSelectionBarVisible) { removeStyleName(HIDE_TABSHEET_STYLE); } else { addStyleName(HIDE_TABSHEET_STYLE); } } /** * Gets the visibility of the bottom sheet selection bar. By default the bar * is visible. * * @return True if the sheet selection bar is visible, false otherwise. */ public boolean isSheetSelectionBarVisible() { return !getStyleName().contains(HIDE_TABSHEET_STYLE); } /** * Enables or disables the report style. When enabled, the top and bottom * bars of Spreadsheet will be hidden. * * @param reportStyle * True to hide both toolbars, false to show them. */ public void setReportStyle(boolean reportStyle) { setFunctionBarVisible(!reportStyle); setSheetSelectionBarVisible(!reportStyle); } /** * Gets the state of the report style. * * @return True if report style is enabled, false otherwise. */ public boolean isReportStyle() { return !isSheetSelectionBarVisible() && !isFunctionBarVisible(); } public void setInvalidFormulaErrorMessage(String invalidFormulaErrorMessage) { getState().invalidFormulaErrorMessage = invalidFormulaErrorMessage; } /* * (non-Javadoc) * * @see com.vaadin.ui.Component.Focusable#getTabIndex() */ @Override public int getTabIndex() { return getState(false).tabIndex; } /* * (non-Javadoc) * * @see com.vaadin.ui.Component.Focusable#setTabIndex(int) */ @Override public void setTabIndex(int tabIndex) { getState().tabIndex = tabIndex; } /* * (non-Javadoc) * * @see com.vaadin.ui.AbstractComponent#focus() */ @Override public void focus() { super.focus(); } /** * Controls if a column group is collapsed or not. * * @param isCols * <code>true</code> when collapsing columns, <code>false</code> * when collapsing rows * @param index * A column that is part of the group, 0-based * @param collapsed * If the group should be collapsed or not */ protected void setGroupingCollapsed(boolean isCols, int index, boolean collapsed) { XSSFSheet activeSheet = (XSSFSheet) getActiveSheet(); if (isCols) { if (collapsed) { GroupingUtil.collapseColumn(activeSheet, index); } else { short expandLevel = GroupingUtil.expandColumn(activeSheet, index); updateExpandedRegion(activeSheet, index, expandLevel); } } else { if (collapsed) { GroupingUtil.collapseRow(activeSheet, index); } else { GroupingUtil.expandRow(activeSheet, index); } } SpreadsheetFactory.calculateSheetSizes(this, activeSheet); SpreadsheetFactory.loadGrouping(this); reloadActiveSheetStyles(); if (hasSheetOverlays()) { reloadImageSizesFromPOI = true; loadOrUpdateOverlays(); } updateMarkedCells(); } private void updateExpandedRegion(XSSFSheet sheet, int columnIndex, int expandLevel) { if (expandLevel < 0) { return; } int endIndex = -1; for (GroupingData data : getState().colGroupingData) { if (data.level == expandLevel) { endIndex = data.endIndex; break; } } if (endIndex < 0) { return; } // update the style for the region cells, effects region + 1 row&col int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); for (int r = firstRowNum; r <= lastRowNum; r++) { Row row = sheet.getRow(r); if (row != null) { for (int c = columnIndex; c <= endIndex; c++) { Cell cell = row.getCell(c); if (cell != null) { valueManager.markCellForUpdate(cell); } } } } } /** * Called when a grouping level header is clicked * * @param isCols * true if the user clicked on cols, false for row level headers * @param level * which level the user clicked */ protected void levelHeaderClicked(boolean isCols, int level) { /* * A click on a header should change groupings so that all levels above * the selected are expanded, and the selected level is all collapsed * (which hides any levels underneath this). */ if (getActiveSheet() instanceof HSSFSheet) { return; } XSSFSheet xsheet = (XSSFSheet) getActiveSheet(); CTWorksheet ctWorksheet = xsheet.getCTWorksheet(); if (isCols) { CTCols ctCols = ctWorksheet.getColsList().get(0); List<CTCol> colList = ctCols.getColList(); for (CTCol col : colList) { short l = col.getOutlineLevel(); // It's a lot easier to not call expand/collapse if (l >= 0 && l < level) { // expand if (col.isSetHidden()) { col.unsetHidden(); } } else { // collapse col.setHidden(true); } } } else { /* * Groups are more complicated than cols, use existing * collapse/expand functionality. */ int lastlevel = 0; for (int i = 0; i < getRows(); i++) { XSSFRow row = xsheet.getRow(i); if (row == null) { lastlevel = 0; continue; } short l = row.getCTRow().getOutlineLevel(); if (l != lastlevel) { // group starts here int end = (int) GroupingUtil.findEndOfRowGroup(this, i, row, l); long uniqueIndex = GroupingUtil.findUniqueRowIndex(this, i, end, l); if (l > 0 && l < level) { // expand GroupingUtil.expandRow(xsheet, (int) uniqueIndex); } else if (l >= level) { // collapse GroupingUtil.collapseRow(xsheet, (int) uniqueIndex); } lastlevel = l; } } } SpreadsheetFactory.reloadSpreadsheetComponent(this, workbook); } void markInvalidFormula(int col, int row) { int activeSheetIndex = workbook.getActiveSheetIndex(); if (!invalidFormulas.containsKey(activeSheetIndex)) { invalidFormulas.put(activeSheetIndex, new HashSet<String>()); } invalidFormulas.get(activeSheetIndex).add(SpreadsheetUtil.toKey(col, row)); } boolean isMarkedAsInvalidFormula(int col, int row) { int activeSheetIndex = workbook.getActiveSheetIndex(); if (invalidFormulas.containsKey(activeSheetIndex)) { return invalidFormulas.get(activeSheetIndex).contains(SpreadsheetUtil.toKey(col, row)); } return false; } void removeInvalidFormulaMark(int col, int row) { int activeSheetIndex = workbook.getActiveSheetIndex(); if (invalidFormulas.containsKey(activeSheetIndex)) { invalidFormulas.get(activeSheetIndex).remove(SpreadsheetUtil.toKey(col, row)); } } public void addSheetOverlay(SheetOverlayWrapper image) { sheetOverlays.add(image); } /** * Get the minimum row heigth in points for the rows that contain custom * components * @return the minimum row heigths in points */ public int getMinimumRowHeightForComponents() { return minimumRowHeightForComponents; } /*** * Set the minimum row heigth in points for the rows that contain custom * components. If set to a small value, it might cause some components * like checkboxes to be cut off * @param minimumRowHeightForComponents the minimum row height in points */ public void setMinimumRowHeightForComponents(final int minimumRowHeightForComponents) { this.minimumRowHeightForComponents = minimumRowHeightForComponents; } }