Module org.controlsfx.controls
Package org.controlsfx.control.spreadsheet

Class SpreadsheetView

  • All Implemented Interfaces:
    Styleable, EventTarget, Skinnable
    public class SpreadsheetView
extends Control
    The SpreadsheetView is a control similar to the JavaFX TableView control but with different functionalities and use cases. The aim is to have a powerful grid where data can be written and retrieved.

    Features

    • Cells can span in row and in column.
    • Rows can be frozen to the top of the SpreadsheetView so that they are always visible on screen.
    • Columns can be frozen to the left of the SpreadsheetView so that they are always visible on screen.
    • A row header can be switched on in order to display the row number.
    • Rows can be resized just like columns with click & drag.
    • Both row and column header can be visible or invisible.
    • Selection of several cells can be made with a click and drag.
    • A copy/paste context menu is accessible with a right-click. The usual shortcuts are also working.
    • Picker can be placed above column header or to the side of the row header.
    • Rows and columns can be hidden (like Excel grouping).
    • Zoom in and out in order for the SpreadsheetView to fit on a monitor.
    • Rows can be sorted using a Comparator.

    Freezing Rows and Columns


    You can freeze some rows and some columns by right-clicking on their header. A context menu will appear if it's possible to freeze them. When frozen, the label header will then be in italic and the background will turn to dark grey.
    You have also the possibility to freeze them manually by adding and removing items from getFixedRows() and getFixedColumns(). But you are strongly advised to check if it's possible to do so with SpreadsheetColumn.isColumnFixable() for the frozen columns and with isRowFixable(int) for the frozen rows.
    A set of rows cannot be frozen if any cell inside these rows has a row span superior to the number of frozen rows. Likewise, a set of columns cannot be frozen if any cell inside these columns has a column span superior to the number of frozen columns.

    If you want to freeze several rows or columns together, and they have a span inside, you can call areRowsFixable(java.util.List) or areSpreadsheetColumnsFixable(java.util.List) to verify if you can freeze them. Be sure to add them all in once otherwise the system will detect that a span is going out of bounds and will throw an exception. Calling those methods prior every move will ensure that no exception will be thrown.

    You have also the possibility to deactivate these possibilities. For example, you force some row/column to be frozen and then the user cannot change the settings.

    Headers


    You can also access and toggle header's visibility by using the methods provided like setShowRowHeader(boolean) or setShowColumnHeader(boolean).
    Users can double-click on a column header will resize the column to the best size in order to fully see each cell in it. Same rule apply for row header. Also note that double-clicking on the little space between two row or two columns (when resizable) will also work just like Excel.

    Pickers


    You can show some little images next to the headers. They will appear on the left of the VerticalHeader and on top on the HorizontalHeader. They are called "picker" because they were used originally for picking a row or a column to insert in the SpreadsheetView.
    But you can do anything you want with it. Simply put a row or a column index in getRowPickers() and getColumnPickers() along with an instance of Picker. You can override the Picker.onClick() method in order to react when the user click on the picker.
    The pickers will appear on the top of the column's header and on the left of the row's header.
    For example, here is a picker displayed for a row that allow to group rows like Excel:
    A Picker that can hide some rows.

    Once we clicked on the picker (minus sign), the rows are hidden.
    A Picker that can sho some rows.

    Here is the code related to the images : 
     Picker picker = new Picker() {
          @Override
          public void onClick() {
          //If my details are hidden
              if (getHiddenRows().get(3)) {
                  showRow(3);
                  showRow(4);
                  showRow(5);
                  showRow(6);
              } else {
                  hideRow(3);
                  hideRow(4);
                  hideRow(5);
                  hideRow(6);
              }
          }
 };
 getRowPickers().put(2, picker);

    Copy pasting

    You can copy any cell you want and paste it elsewhere. Be aware that only the value inside will be pasted, not the style nor the type. Thus the value you're trying to paste must be compatible with the SpreadsheetCellType of the receiving cell. Pasting a Double into a String will work but the reverse operation will not.
    See SpreadsheetCellType Value Verification documentation for more information.
    A unique cell or a selection can be copied and pasted.

    Hiding rows and columns

    Rows and columns can be hidden if you need to. Simply call showRow(int) or hideRow(int) in order to toggle the visibility of a row. Same for the column.
    Note that the span of the cell (in row or column) will automatically adapt based on the visible rows or columns. You have nothing to do.
    Because toggling visibility have an impact on the Grid, if you have a lot of rows/columns to show or hide, you may consider setting them all directly by using setHiddenRows(java.util.BitSet). The BitSet represent all your rows/columns and the bit associated to it represent its visibility.

    Zoom

    The SpreadsheetView offers the possibility to zoom in or out. This is useful when you have a second monitor and you want your whole grid to fit in. Or when you want to draw the attention on a particular portion of the grid.
    You can modify the zoom factor by playing with setZoomFactor(java.lang.Double). We recommend using value between 2 and 0.1.
    Also note that the SpreadsheetView is configured to react when CTRL + and CTRL - are triggered by, respectively, zooming in and zooming out by 10%. Also CTRL 0 will bring the zoom back to default (1).

    Code Samples

    Just like the TableView, you instantiate the underlying model, a Grid. You will create some rows filled with SpreadsheetCell. 

     int rowCount = 15;
     int columnCount = 10;
     GridBase grid = new GridBase(rowCount, columnCount);
     
     ObservableList<ObservableList<SpreadsheetCell>> rows = FXCollections.observableArrayList();
     for (int row = 0; row < grid.getRowCount(); ++row) {
         final ObservableList<SpreadsheetCell> list = FXCollections.observableArrayList();
         for (int column = 0; column < grid.getColumnCount(); ++column) {
             list.add(SpreadsheetCellType.STRING.createCell(row, column, 1, 1,"value"));
         }
         rows.add(list);
     }
     grid.setRows(rows);

     SpreadsheetView spv = new SpreadsheetView(grid);
    At that moment you can span some of the cells with the convenient method provided by the grid. Then you just need to instantiate the SpreadsheetView.

    Visual:

    Screenshot of SpreadsheetView
    See Also:
    SpreadsheetCell, SpreadsheetCellBase, SpreadsheetColumn, Grid, GridBase, Picker

    • Constructor Detail

      • SpreadsheetView

        public SpreadsheetView()
        This constructor will generate sample Grid with 100 rows and 15 columns. All cells are typed as String (see SpreadsheetCellType.STRING).

      • SpreadsheetView

        public SpreadsheetView​(Grid grid)
        Creates a SpreadsheetView control with the Grid specified.
        Parameters:
        grid - The Grid that contains the items to be rendered

    • Method Detail

      • setCellGraphicFactory

        public void setCellGraphicFactory​(CellGraphicFactory cellGraphicFactory)
        Sets the CellGraphicFactory that will provide an implementation for cell that have SpreadsheetCell.isCellGraphic() set to true.
        Parameters:
        cellGraphicFactory - the CellGraphicFactory

      • getCellGraphicFactory

        public CellGraphicFactory getCellGraphicFactory()
        Returns the CellGraphicFactory if set that provide implementation for browser in SpreadsheetCell.
        Returns:
        the CellGraphicFactory

      • layoutChildren

        protected void layoutChildren()
        * Public Methods * *
        Overrides:
        layoutChildren in class Control

      • isRowHidden

        public boolean isRowHidden​(int row)
        Return true is this row is hidden.
        Parameters:
        row -
        Returns:
        true is this row is hidden.

      • getHiddenRows

        public BitSet getHiddenRows()
        Return a BitSet of the Hidden rows, where true means the row is hidden.
        Returns:
        a BitSet of the Hidden rows, where true means the row is hidden.

      • setHiddenRows

        public void setHiddenRows​(BitSet hiddenRows)
        Give a complete new BitSet of the hidden rows. The BitSet MUST have the size of Grid.getRowCount().
        Parameters:
        hiddenRows -

      • setHiddenColumns

        public void setHiddenColumns​(BitSet hiddenColumns)
        Give a complete new BitSet of the hidden columns. The BitSet MUST have the size of () .
        Parameters:
        hiddenColumns -

      • isColumnHidden

        public boolean isColumnHidden​(int column)
        Return true if this column index (regarding to getColumns() is hidden.
        Parameters:
        column -
        Returns:
        true if this column index (regarding to getColumns() is hidden.

      • getHiddenColumns

        public BitSet getHiddenColumns()
        Return a BitSet of the Hidden columns, where true means the column is hidden.
        Returns:
        a BitSet of the Hidden columns, where true means the column is hidden.

      • getFilteredRow

        public int getFilteredRow()
        Return the row where the Filter will be shown. The row is based on the Grid indexes. Return -1 if no row is set for the filters.
        Returns:
        the row where the Filter will be shown.

      • setFilteredRow

        public void setFilteredRow​(Integer row)
        Set the row (based of Grid indexes) where the Filter will appear.
        Parameters:
        row -

      • hideRow

        public void hideRow​(int row)
        Hide the specified row.
        Parameters:
        row -

      • showRow

        public void showRow​(int row)
        Show the specified row.
        Parameters:
        row -

      • getFilteredRow

        public int getFilteredRow​(int modelRow)
        Given a row index base on the Grid, return the index used in the SpreadsheetView. Beware ,if the row is hidden, the returned index is not relevant because no row is assigned to it.
        Parameters:
        modelRow -
        Returns:
        the index used in the SpreadsheetView.

      • getViewColumn

        public int getViewColumn​(int modelColumn)
        Given a column index based on the getColumns() list, return an index based on the visible columns in the SpreadsheetView.
        Parameters:
        modelColumn -
        Returns:
        an index based on the visible columns in the SpreadsheetView.

      • getModelColumn

        public int getModelColumn​(int viewColumn)
        Given a column index based on the visible column list, for example when dealing with TablePosition.getColumn(). It returns an index based on the getColumns() list of the SpreadsheetView.
        Parameters:
        viewColumn -
        Returns:
        an index based on the getColumns() list of the SpreadsheetView.

      • getViewRow

        public int getViewRow​(int modelRow)
        Given the row of a SpreadsheetCell, returns the actual row as displayed in the SpreadsheetView. Beware as it can be a time-consuming operation. Also, calling this method on a row that it hidden will return incoherent information.
        Parameters:
        modelRow - the row retrieved in SpreadsheetCell.getRow()
        Returns:
        the ViewRow if possible, -1 or another row if the row is hidden.

      • getModelRow

        public int getModelRow​(int viewRow)
        Given an index on the SpreadsheetView, return a Grid index it is related to.
        Parameters:
        viewRow - a row index based on the SpreadsheetView
        Returns:
        a Grid index it is related to.

      • getFilteredSourceIndex

        public int getFilteredSourceIndex​(int viewRow)
        Given an index on the SpreadsheetView, it will return the model row by simply considering the hidden rows (and not the actual sort if any). If you hide the row 2, it means the row 2 in the SpreadsheetView will actually display the row 3 in the model Grid. Thus calling this method with the number 2 will give you the number 3.
        Parameters:
        viewRow -
        Returns:
        the model row

      • getRowSpan

        public int getRowSpan​(SpreadsheetCell cell,
                      int index)
        Return the current row span for the given cell at the given position in the Table. If a sort is applied to the SpreadsheetView, some spanned cells may be splitted thus explaining why this method can give a different value than SpreadsheetCell.getRowSpan().
        Parameters:
        cell - the considered SpreadsheetCell
        index - the current row position of this cell
        Returns:
        the current row span for the given cell

      • getRowSpanFilter

        public int getRowSpanFilter​(SpreadsheetCell cell)
        Return the row span for the given cell without considering the actual sort. Only the hidden rows are considered.
        Parameters:
        cell -
        Returns:
        the row span for the given cell.

      • getColumnSpan

        public int getColumnSpan​(SpreadsheetCell cell)
        Return the current column span of a Cell considering all hidden columns.
        Parameters:
        cell -
        Returns:
        the current column span of a Cell.

      • getZoomFactor

        public final Double getZoomFactor()
        Return the zoomFactor used for the SpreadsheetView.
        Returns:
        the zoomFactor used for the SpreadsheetView.

      • setZoomFactor

        public final void setZoomFactor​(Double zoomFactor)
        Set a new zoomFactor for the SpreadsheetView. Advice is not to go beyond 2 and below 0.1.
        Parameters:
        zoomFactor -

      • incrementZoom

        public void incrementZoom()
        Increment the level of zoom by 0.10. The base is 1 so we will try to stay of the intervals.

      • decrementZoom

        public void decrementZoom()
        Decrement the level of zoom by 0.10. It will block at 0.20. The base is 1 so we will try to stay of the intervals.

      • edit

        public void edit​(int row,
                 SpreadsheetColumn column)
        Causes the cell at the given row/column view indexes to switch into its editing state, if it is not already in it, and assuming that the SpreadsheetView and column are also editable.

        Note: This method will cancel editing if the given row value is less than zero and the given column is null.

        Parameters:
        row -
        column -

      • getComparator

        public Comparator getComparator()
        Return the comparator used in the SortedList for the SpreadsheetView.
        Returns:
        the comparator used in the SortedList for the SpreadsheetView.

      • setComparator

        public void setComparator​(Comparator<ObservableList<SpreadsheetCell>> comparator)
        Sets a new Comparator for the SpreadsheetView in order to sort the rows.
        Parameters:
        comparator - the comparator that will sort the rows.

      • setGrid

        public final void setGrid​(Grid grid)
        Set a new Grid for the SpreadsheetView. This will be called by default by SpreadsheetView(Grid). So this is useful when you want to refresh your SpreadsheetView with a new model. This will keep the state of your SpreadsheetView (position of the bar, number of frozen rows etc).
        Parameters:
        grid - the new Grid

      • getGrid

        public final Grid getGrid()
        Return the model Grid used by the SpreadsheetView
        Returns:
        the model Grid used by the SpreadsheetView

      • getFixedRows

        public ObservableList<Integer> getFixedRows()
        You can freeze or unfreeze a row by modifying this list. Call isRowFixable(int) before trying to freeze a row. See SpreadsheetView description for information.
        Returns:
        an ObservableList of integer representing the frozen rows.

      • isRowFixable

        public boolean isRowFixable​(int row)
        Indicate whether a row can be frozen or not. Call that method before adding an item with getFixedRows() . A row cannot be frozen alone if any cell inside the row has a row span superior to one.
        Parameters:
        row -
        Returns:
        true if the row can be frozen.

      • areRowsFixable

        public boolean areRowsFixable​(List<? extends Integer> list)
        Indicates whether a List of rows can be frozen or not. A set of rows cannot be frozen if any cell inside these rows has a row span superior to the number of frozen rows.
        Parameters:
        list -
        Returns:
        true if the List of row can be frozen together.

      • isFixingRowsAllowed

        public boolean isFixingRowsAllowed()
        Return whether change to frozen rows are allowed.
        Returns:
        whether change to frozen rows are allowed.

      • setFixingRowsAllowed

        public void setFixingRowsAllowed​(boolean b)
        If set to true, user will be allowed to freeze and unfreeze the rows.
        Parameters:
        b -

      • areSpreadsheetColumnsFixable

        public boolean areSpreadsheetColumnsFixable​(List<? extends SpreadsheetColumn> list)
        Indicates whether a List of SpreadsheetColumn can be fixed or not. A set of columns cannot be frozen if any cell inside these columns has a column span superior to the number of frozen columns.
        Parameters:
        list -
        Returns:
        true if the List of columns can be frozen together.

      • areColumnsFixable

        public boolean areColumnsFixable​(List<? extends Integer> list)
        This method is the same as areSpreadsheetColumnsFixable(java.util.List) but is using a List of SpreadsheetColumn indexes. A set of columns cannot be frozen if any cell inside these columns has a column span superior to the number of frozen columns.
        Parameters:
        list -
        Returns:
        true if the List of columns can be frozen together.

      • isFixingColumnsAllowed

        public boolean isFixingColumnsAllowed()
        Return whether change to frozen columns are allowed.
        Returns:
        whether change to frozen columns are allowed.

      • setFixingColumnsAllowed

        public void setFixingColumnsAllowed​(boolean b)
        If set to true, user will be allowed to freeze and unfreeze the columns.
        Parameters:
        b -

      • setShowColumnHeader

        public final void setShowColumnHeader​(boolean b)
        Activate and deactivate the Column Header
        Parameters:
        b -

      • isShowColumnHeader

        public final boolean isShowColumnHeader()
        Return if the Column Header is showing.
        Returns:
        a boolean telling whether the column Header is shown

      • setShowRowHeader

        public final void setShowRowHeader​(boolean b)
        Activate and deactivate the Row Header.
        Parameters:
        b -

      • isShowRowHeader

        public final boolean isShowRowHeader()
        Return if the row Header is showing.
        Returns:
        a boolean telling if the row Header is being shown

      • rowHeaderWidthProperty

        public final DoubleProperty rowHeaderWidthProperty()
        This DoubleProperty represents the width of the rowHeader. This is just representing the width of the Labels, not the pickers.
        Returns:
        A DoubleProperty.

      • setRowHeaderWidth

        public final void setRowHeaderWidth​(double value)
        Specify a new width for the row header.
        Parameters:
        value -

      • getRowHeaderWidth

        public final double getRowHeaderWidth()
        Returns:
        the current width of the row header.

      • getRowPickers

        public ObservableMap<Integer,​Picker> getRowPickers()
        Returns:
        An ObservableMap with the row index as key and the Picker as a value.

      • getColumnPickers

        public ObservableMap<Integer,​Picker> getColumnPickers()
        Returns:
        An ObservableMap with the column index as key and the Picker as a value.

      • resizeRowsToFitContent

        public void resizeRowsToFitContent()
        This method will compute the best height for each line. That is to say a height where each content of each cell could be fully visible.\n Use this method wisely because it can degrade performance on great grid.

      • resizeRowsToMaximum

        public void resizeRowsToMaximum()
        This method will first apply resizeRowsToFitContent() and then take the highest height and apply it to every row.\n Just as resizeRowsToFitContent(), this method can be degrading your performance on great grid.

      • resizeRowsToDefault

        public void resizeRowsToDefault()
        This method will wipe all changes made to the row's height and set all row's height back to their default height defined in the model Grid.

      • getRowHeight

        public double getRowHeight​(int row)
        Parameters:
        row -
        Returns:
        the height of a particular row of the SpreadsheetView.

      • scrollToRow

        public void scrollToRow​(int row)
        Scrolls the SpreadsheetView so that the given row is visible. Beware, you must call getViewRow(int) before if you are using SpreadsheetCell.getRow() and the grid is sorted/filtered.
        Parameters:
        row - the row to scroll to

      • setVBarValue

        public void setVBarValue​(double value)
        Same method as ScrollBar.setValue(double) on the verticalBar.
        Parameters:
        value -

      • setHBarValue

        public void setHBarValue​(double value)
        Same method as ScrollBar.setValue(double) on the verticalBar.
        Parameters:
        value -

      • getVBarValue

        public double getVBarValue()
        Return the value of the vertical scrollbar. See ScrollBar.getValue()
        Returns:
        the value of the vertical scrollbar.

      • getHBarValue

        public double getHBarValue()
        Return the value of the horizontal scrollbar. See ScrollBar.getValue()
        Returns:
        the value of the horizontal scrollbar.

      • scrollToColumn

        public void scrollToColumn​(SpreadsheetColumn column)
        Scrolls the SpreadsheetView so that the given SpreadsheetColumn is visible.
        Parameters:
        column -

      • scrollToColumnIndex

        public void scrollToColumnIndex​(int modelColumn)
        Scrolls the SpreadsheetView so that the given column index is visible.
        Parameters:
        modelColumn -

      • setEditable

        public final void setEditable​(boolean b)
        Sets the value of the property editable.
        Parameters:
        b -

      • isEditable

        public final boolean isEditable()
        Gets the value of the property editable.
        Returns:
        a boolean telling if the SpreadsheetView is editable.

      • setPlaceholder

        public final void setPlaceholder​(Node placeholder)
        Sets the value of the placeholder property
        Parameters:
        placeholder - the node to show when the SpreadsheetView has no content to show.

      • getPlaceholder

        public final Node getPlaceholder()
        Gets the value of the placeholder property.
        Returns:
        the Node used as a placeholder that is shown when the SpreadsheetView has no content to show.

      • copyClipboard

        public void copyClipboard()
        Put the current selection into the ClipBoard. This can be overridden by developers for custom behavior.

      • pasteClipboard

        public void pasteClipboard()
        Try to paste the clipBoard to the specified position. Try to paste the current selection into the Grid. If the two contents are not matchable, then it's not pasted. This can be overridden by developers for custom behavior.

      • getSpreadsheetViewContextMenu

        public ContextMenu getSpreadsheetViewContextMenu()
        Create a menu on rightClick with two options: Copy/Paste This can be overridden by developers for custom behavior.
        Returns:
        the ContextMenu to use.

      • deleteSelectedCells

        public void deleteSelectedCells()
        This method is called when pressing the "delete" key on the SpreadsheetView. This will erase the values of selected cells. This can be overridden by developers for custom behavior.