- SpreadJS Overview
- Getting Started
- JavaScript Frameworks
- Best Practices
-
Features
- Workbook
- Worksheet
- Rows and Columns
- Headers
- Cells
- Data Binding
- TableSheet
- GanttSheet
- JSON Schema with SpreadJS
- SpreadJS File Format
- Data Validation
- Conditional Formatting
- Sort
- Group
- Formulas
- Serialization
- Keyboard Actions
- Shapes
- Form Controls
- Floating Objects
- Barcodes
- Charts
- Sparklines
- Tables
- Pivot Table
- Slicer
- Theme
- Culture
- SpreadJS Designer
- SpreadJS Designer Component
- Touch Support
- Formula Reference
- Import and Export Reference
- Frequently Used Events
- API Documentation
- Release Notes
Paste or Skip Data in Invisible Range
In a spreadsheet, you can choose whether to paste the data in an invisible range or not by using the pasteSkipInvisibleRange property. By default, the data gets pasted into the invisible cells, rows, and columns, as well.
However, you can skip pasting in an invisible range by setting the pasteSkipInvisibleRange property to True. This will ensure that the data is pasted to the next visible cell in the range instead of invisible cells, rows, or columns.
When | When |
|---|---|
|
|
In SpreadJS, the following conditions are considered invisible cells, rows, or columns:
Filtered Rows or Columns
Grouped Rows or Columns
Rows or Columns with ColumnWidth or RowHeight set to 0
Hidden Rows or Columns
The following table lists the features where pasteSkipInvisibleRange property is supported along with their corresponding behaviors.
Copied Object | Before Copy | pasteSkipInvisibleRange = True | pasteSkipInvisibleRange = False |
|---|---|---|---|
Value | Belongs to the cell | If the target cell is invisible, will paste to the next legal visible row or column. If the range remains invisible, the effect is the same as the copied range.If the copied range is illegal, it will not paste. If the pasted range's next visible row/column count is less than the copied range's row/column count, it will not paste the whole range. | It will be invisible when the cell remains invisible and will show the value once the cell gets visible. If the range changes to visible, the effect is the same as the copied range. |
Formula && SparklineEx | Connects with the cell | If the target cell is invisible, it will paste to the next legally visible row or column. Since the formula reference will consider the invisible range, so the result of the formula can differ from the previous result. This property does not support the pasting of DynamicArray and ArrayFormula in the sheet. For example, if you try to copy a DynamicArray, it will paste nothing into the sheet. | Same as the pasted value. |
Style (CellStyle, CellButton, formatter, and CellType ) | Belongs to the cell | If the target cell is invisible, will paste to the next legal visible row or column. If the range remains invisible, the effect will be the same as that of the copied range. | Same as the pasted value. |
DragMove | Selects a range | The move rectangle will consider the invisible range and the range rectangle will expand. | It will consider the invisible range. |
Shift Copied Cells | Copies or cuts a range | When shifting the range down/right, the moved range will consider the invisible range and the move to range will skip the invisible range. | It will consider the invisible range. |
CustomName | Cell or range reference | If the reference type is cell, new customName reference will change to a new cell and skip the invisible range. If the reference type is a range, the new customName reference will consider the invisible range. | After moving, the customName reference will change to a new range. |
Comment | Belongs to the cell | Same as the pasted value. | Same as the pasted value. |
Tag | Belongs to the cell | Same as the pasted value. | Same as the pasted value. |
Hyperlink | Belongs to the cell | Same as the pasted value. | Same as the pasted value. |
BindingPath | Belongs to the cell | Same as the pasted value. | Same as the pasted value. |
Header | Belongs to the cell | If the target row/column is invisible, it will paste to the next legal visible row or column in the header area. If the copied row/column count is more than the visible pasted row/column count, it will then paste only a part of the value. | It will only copy/paste the changed value. |
CellStates | Belongs to the cell | It will only copy the cell value and not the cell states. | It will only copy the cell value and not the cell states. |
Sparkline | Belongs to the cell | The reference of the data range is the same as that of the formula. The sparkline area is the same as the pasting value. | Same as the pasted value.If the sparkline data range is invisible, it will ignore the invisible range while calculating. |
DataValidation | While creating a DataValidation for a range, it will be saved as a range. | If the target cell is invisible, will paste to the next legally visible row or column. | When pasting a copied dataValidation range, it will be saved as multiple cells. It will be invisible when the cell is invisible and will display the value once the cell gets visible. |
ConditionalFormat | While creating a ConditionalFormat for a range, it will be saved as a range. | If the target cell is invisible, it will paste to the next legal visible row or column. a.) Copy and paste a range in the sheet, it will split into two or more ranges because it skipped the invisible range. b.) Copy multiple ranges with the same row or same column, it will split into more little ranges just like [a]. c.) copy a range and paste it to multiple ranges, the range will split into two or more ranges just like [a]. It does not support copying and pasting multiple ranges in the sheet. | It will be invisible while the cell is invisible and will show the value when the cell gets visible. a.) Copy and paste a range in the sheet, it will be the same as the copied range when it gets visible. b.) Copy multiple ranges with the same row or same column, it will be pasted as one range but will be saved as two ranges, each range is calculated independently. c.) Copy a range and paste it to multiple ranges, each range will calculate independently and will show the same result. It does not support copying and pasting multiple ranges in the sheet. |
Span | A whole range | It does not support pasting of span with skipping invisible range. The span result is the same as that of the pasting to the invisible range. But the cells in the span will paste by skipping invisible range. If the range changes to visible, the span is the same as that of the copied span. | It is still a whole range.If the range changes to visible, the span is the same as the copied span. |
Table | A whole table | If you copy only a part of the table, it will only paste the values and styles to the next legally visible row or column. If you copy a range that contains the whole table and pastes it to the invisible range, the table will not expand but the value of the table will paste with skipping the invisible range. When you paste to right or below of the table, and the table supports auto-expand, the expanded range will include the invisible range and the invisible range contains no value. | If you copy only a part of the table, it will only paste the values and styles. But, if you copy a range that contains the whole table, it will then paste the whole table. If the range changes to visible, the effect remains the same as that of the copied range. |
Using Code
This example allows you to skip pasting the data in an invisible range.
workbook.options.pasteSkipInvisibleRange = true;Using SpreadJS Designer
In SpreadJS Designer, click 'Settings' tab and select 'General' option from 'Spread Settings' group to display the 'Spread Settings' dialog as shown below. Select the Clipboard tab to check or uncheck the Allow Paste Skip Invisible Range option to enable or disable the pasting of data into the invisible range in the spreadsheet.
