The copyPasteHeaderOptions workbook option can be used to specify which headers are included when data is copied or pasted. For example:
GC.Spread.Sheets.CopyPasteHeaderOptions:
noHeaders: 0
rowHeaders: 1
columnHeaders: 2
allHeaders: 3
SpreadJS supports copying multiple, non-contiguous cells and pasting them to a single cell range. The following cases are supported:
The same row index and row count
The same column index and column count
SpreadJS will ignore filtered rows when copying cell ranges.
SpreadJS also supports skipping invisible cell ranges when pasting cells, just set the pasteSkipInvisibleRange workbook option to true (the default value is false). For example:
Invisible ranges are present when:
Filters are used
Grouping is present
A column has a columnWidth = 0 or a row has a rowHeight = 0
Cells are hidden
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
// import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
export function AppFunc() {
const [options, setOptions] = React.useState(3);
const [pasteSkipInvisibleRange, setPasteSkipInvisibleRange] = React.useState(false);
const spreadRef = React.useRef(null);
const initSpread = (spread) => {
spreadRef.current = spread;
spread.suspendPaint();
const sheet = spread.getActiveSheet();
setDataWithHeader(sheet);
sheet.setValue(5, 0, "Copy non-contiguous cells of all names(A8:A14) and all prices(C8:C14) then paste to somewhere(such as F5).");
loadGoodListTable(sheet, 7, 0);
const rowFilter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(8, 0, 6, 4));
sheet.rowFilter(rowFilter);
rowFilter.addFilterItem(1, new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.cellValueCondition, {
compareType: GC.Spread.Sheets.ConditionalFormatting.GeneralComparisonOperators.notEqualsTo,
expected: "Vegetable"
}));
sheet.defaults.colWidth = 100;
spread.resumePaint();
};
const onChange = (e) => {
const value = parseInt(e.target.value);
setOptions(value);
spreadRef.current.options.copyPasteHeaderOptions = value;
};
const onPasteSkipInvisibleRangeChange = (e) => {
const value = e.target.checked;
setPasteSkipInvisibleRange(value);
spreadRef.current.options.pasteSkipInvisibleRange = value;
};
const setDataWithHeader = (sheet) => {
sheet.getRange(0, 0, 1, 5, 1).backColor("#B3B3B3");
sheet.getRange(0, 0, 1, 5, 1).foreColor("white");
sheet.setValue(0, 0, 'MON', 1);
sheet.setValue(0, 1, 'TUE', 1);
sheet.setValue(0, 2, 'WED', 1);
sheet.setValue(0, 3, 'THU', 1);
sheet.setValue(0, 4, 'FRI', 1);
sheet.setValue(0, 0, '8:00', 2);
sheet.setValue(1, 0, '9:00', 2);
sheet.setValue(2, 0, '10:00', 2);
sheet.setValue(3, 0, '11:00', 2);
sheet.setRowHeight(0, 45);
sheet.setRowHeight(1, 45);
sheet.setRowHeight(2, 45);
sheet.setRowHeight(3, 45);
sheet.getRange(0, 0, 4, 1).backColor("#c0d88b");
sheet.getRange(0, 1, 4, 1).backColor("#dbe6bf");
sheet.getRange(0, 2, 4, 1).backColor("#f9f9f9");
sheet.getRange(0, 3, 4, 1).backColor("#fce0c0");
sheet.getRange(0, 4, 4, 1).backColor("#fecc8d");
sheet.setValue(0, 0, 'French');
sheet.setValue(0, 2, 'French');
sheet.setValue(0, 4, 'French');
sheet.setValue(1, 1, 'Art History');
sheet.setValue(1, 3, 'Art History');
sheet.setValue(2, 0, 'Math');
sheet.setValue(2, 2, 'Math');
sheet.setValue(2, 4, 'Math');
sheet.setValue(3, 1, 'Programming');
sheet.setValue(3, 3, 'Programming');
};
const loadGoodListTable = (sheet, startRow, startCol) => {
if (startRow === undefined) {
startRow = 0;
}
if (startCol === undefined) {
startCol = 0;
}
if (sheet.getRowCount(GC.Spread.Sheets.SheetArea.viewport) - startRow < 8 ||
sheet.getColumnCount(GC.Spread.Sheets.SheetArea.viewport) - startCol < 3) {
return;
}
sheet.setRowHeight(startRow + 0, 40);
sheet.setValue(startRow + 0, startCol + 0, "Goods List");
const title = sheet.getCell(startRow + 0, startCol + 0);
title.font("bold 20px arial");
title.vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.getRange(startRow + 0, startCol + 0, 1, 4).backColor("#B3B3B3").foreColor("white");
sheet.setColumnWidth(startCol + 0, 100);
sheet.setColumnWidth(startCol + 1, 100);
sheet.setColumnWidth(startCol + 2, 100);
sheet.setColumnWidth(startCol + 3, 120);
sheet.getRange(startRow + 0, startCol + 0, 8, 4).setBorder(new GC.Spread.Sheets.LineBorder("Black", GC.Spread.Sheets.LineStyle.thin), { all: true });
sheet.setValue(startRow + 1, startCol + 0, "Name");
sheet.setValue(startRow + 1, startCol + 1, "Category");
sheet.setValue(startRow + 1, startCol + 2, "Price");
sheet.setValue(startRow + 1, startCol + 3, "Shopping Place");
for (let i = 0; i < 4; i++) {
sheet.getCell(startRow + 1, startCol + i).font("bold 15px arial");
}
sheet.setValue(startRow + 2, startCol + 0, "Apple");
sheet.setValue(startRow + 3, startCol + 0, "Potato");
sheet.setValue(startRow + 4, startCol + 0, "Tomato");
sheet.setValue(startRow + 5, startCol + 0, "Sandwich");
sheet.setValue(startRow + 6, startCol + 0, "Hamburger");
sheet.setValue(startRow + 7, startCol + 0, "Grape");
sheet.setValue(startRow + 2, startCol + 1, "Fruit");
sheet.setValue(startRow + 3, startCol + 1, "Vegetable");
sheet.setValue(startRow + 4, startCol + 1, "Vegetable");
sheet.setValue(startRow + 5, startCol + 1, "Food");
sheet.setValue(startRow + 6, startCol + 1, "Food");
sheet.setValue(startRow + 7, startCol + 1, "Fruit");
sheet.setValue(startRow + 2, startCol + 2, 1.00);
sheet.setValue(startRow + 3, startCol + 2, 2.01);
sheet.setValue(startRow + 4, startCol + 2, 3.21);
sheet.setValue(startRow + 5, startCol + 2, 2);
sheet.setValue(startRow + 6, startCol + 2, 2);
sheet.setValue(startRow + 7, startCol + 2, 4);
const myFormatter = new GC.Spread.Formatter.GeneralFormatter("$#,##0.00;[Red] $#,##0.00");
for (let i = 2; i < 8; i++) {
sheet.getCell(startRow + i, startCol + 2).formatter(myFormatter);
}
sheet.setValue(startRow + 2, startCol + 3, "Wal-Mart");
sheet.setValue(startRow + 3, startCol + 3, "Other");
sheet.setValue(startRow + 4, startCol + 3, "Other");
sheet.setValue(startRow + 5, startCol + 3, "Wal-Mart");
sheet.setValue(startRow + 6, startCol + 3, "Wal-Mart");
sheet.setValue(startRow + 7, startCol + 3, "Other");
};
return (
<div className="sample-tutorial">
<div className="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<div className="options-container">
You can select whether headers are also copied/pasted when data is copied/pasted in the Spread component.
<div className="option-row">
<p>Select a option below then select a rows or column header in the worksheet, such as 'FRI' column or '8:00' row, and copy or paste to see the results.</p>
<label>
copyPasteHeaderOptions
<select id="copyPasteHeaderOptions" value={options} onChange={onChange}>
<option value={0}>noHeaders</option>
<option value={1}>rowHeaders</option>
<option value={2}>columnHeaders</option>
<option defaultChecked value={3}>allHeaders</option>
</select>
</label>
</div>
<div className="option-row">
<p>With the pasteSkipInvisibleRange option enabled or disabled (using the checkbox below), try to copy or cut range A1:A4 (MON columns, 8:00-11:00) or C9:C14 (WED, rows 9-14) and paste to range F8:F13 to see how it affects the copy/paste behavior.</p>
<input style={{ width: "20px", float: "left" }} type="checkbox" id="pasteSkipInvisibleRange" checked={pasteSkipInvisibleRange} onChange={onPasteSkipInvisibleRangeChange}></input>
<label htmlFor="pasteSkipInvisibleRange">Paste Skip Invisible Range</label>
</div>
</div>
</div>
);
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.state = {
options: 3,
pasteSkipInvisibleRange: false
}
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<div className="options-container">
You can select whether headers are also copied/pasted when data is copied/pasted in the Spread component.
<div className="option-row">
<p>Select a option below then select a rows or column header in the worksheet, such as 'FRI' column or '8:00' row, and copy or paste to see the results.</p>
<label>
copyPasteHeaderOptions
<select id="copyPasteHeaderOptions" value={this.state.options} onChange={(e)=>{this.onChange(e)}}>
<option value={0}>noHeaders</option>
<option value={1}>rowHeaders</option>
<option value={2}>columnHeaders</option>
<option defaultChecked value={3}>allHeaders</option>
</select>
</label>
</div>
<div class="option-row">
<p>With the pasteSkipInvisibleRange option enabled or disabled (using the checkbox below), try to copy or cut range A1:A4 (MON columns, 8:00-11:00) or C9:C14 (WED, rows 9-14) and paste to range F8:F13 to see how it affects the copy/paste behavior.</p>
<input style={{width: "20px",float: "left"}} type="checkbox" id="pasteSkipInvisibleRange" checked={this.state.pasteSkipInvisibleRange} onChange={(e)=>{this.onPasteSkipInvisibleRangeChange(e)}}></input>
<label for="pasteSkipInvisibleRange">Paste Skip Invisible Range</label>
</div>
</div>
</div>;
}
initSpread(spread) {
this.spread = spread;
spread.suspendPaint();
var sheet = spread.getActiveSheet();
this.setDataWithHeader(sheet);
sheet.setValue(5, 0, "Copy non-contiguous cells of all names(A8:A14) and all prices(C8:C14) then paste to somewhere(such as F5).");
this.loadGoodListTable(sheet, 7, 0);
var rowFilter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(8, 0, 6, 4));
sheet.rowFilter(rowFilter);
rowFilter.addFilterItem(1, new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.cellValueCondition, {
compareType: GC.Spread.Sheets.ConditionalFormatting.GeneralComparisonOperators.notEqualsTo,
expected: "Vegetable"
}));
sheet.defaults.colWidth = 100;
spread.resumePaint();
}
onChange(e) {
var value = parseInt(e.target.value);
this.setState(() => ({
options: value
}), () => {
this.spread.options.copyPasteHeaderOptions = value;
});
}
onPasteSkipInvisibleRangeChange(e) {
var value = e.target.checked;
this.setState(() => ({
pasteSkipInvisibleRange: value
}), () => {
this.spread.options.pasteSkipInvisibleRange = value;
});
}
setDataWithHeader(sheet) {
sheet.getRange(0, 0, 1, 5, 1).backColor("#B3B3B3");
sheet.getRange(0, 0, 1, 5, 1).foreColor("white");
sheet.setValue(0, 0, 'MON', 1);
sheet.setValue(0, 1, 'TUE', 1);
sheet.setValue(0, 2, 'WED', 1);
sheet.setValue(0, 3, 'THU', 1);
sheet.setValue(0, 4, 'FRI', 1);
sheet.setValue(0, 0, '8:00', 2);
sheet.setValue(1, 0, '9:00', 2);
sheet.setValue(2, 0, '10:00', 2);
sheet.setValue(3, 0, '11:00', 2);
sheet.setRowHeight(0, 45);
sheet.setRowHeight(1, 45);
sheet.setRowHeight(2, 45);
sheet.setRowHeight(3, 45);
sheet.getRange(0, 0, 4, 1).backColor("#c0d88b");
sheet.getRange(0, 1, 4, 1).backColor("#dbe6bf");
sheet.getRange(0, 2, 4, 1).backColor("#f9f9f9");
sheet.getRange(0, 3, 4, 1).backColor("#fce0c0");
sheet.getRange(0, 4, 4, 1).backColor("#fecc8d");
sheet.setValue(0, 0, 'French');
sheet.setValue(0, 2, 'French');
sheet.setValue(0, 4, 'French');
sheet.setValue(1, 1, 'Art History');
sheet.setValue(1, 3, 'Art History');
sheet.setValue(2, 0, 'Math');
sheet.setValue(2, 2, 'Math');
sheet.setValue(2, 4, 'Math');
sheet.setValue(3, 1, 'Programming');
sheet.setValue(3, 3, 'Programming');
}
loadGoodListTable(sheet, startRow, startCol) {
if (startRow === undefined) {
startRow = 0;
}
if (startCol === undefined) {
startCol = 0;
}
if (sheet.getRowCount(GC.Spread.Sheets.SheetArea.viewport) - startRow < 8 ||
sheet.getColumnCount(GC.Spread.Sheets.SheetArea.viewport) - startCol < 3) {
return;
}
// sheet.addSpan(startRow + 0, startCol + 0, 1, 4);
sheet.setRowHeight(startRow + 0, 40);
sheet.setValue(startRow + 0, startCol + 0, "Goods List");
var title = sheet.getCell(startRow + 0, startCol + 0);
title.font("bold 20px arial");
title.vAlign(GC.Spread.Sheets.VerticalAlign.center);
sheet.getRange(startRow + 0, startCol + 0, 1, 4).backColor("#B3B3B3").foreColor("white");
sheet.setColumnWidth(startCol + 0, 100);
sheet.setColumnWidth(startCol + 1, 100);
sheet.setColumnWidth(startCol + 2, 100);
sheet.setColumnWidth(startCol + 3, 120);
sheet.getRange(startRow + 0, startCol + 0, 8, 4).setBorder(new GC.Spread.Sheets.LineBorder("Black", GC.Spread.Sheets.LineStyle.thin), {all: true});
sheet.setValue(startRow + 1, startCol + 0, "Name");
sheet.setValue(startRow + 1, startCol + 1, "Category");
sheet.setValue(startRow + 1, startCol + 2, "Price");
sheet.setValue(startRow + 1, startCol + 3, "Shopping Place");
for (var i = 0; i < 4; i++) {
sheet.getCell(startRow + 1, startCol + i).font("bold 15px arial");
}
sheet.setValue(startRow + 2, startCol + 0, "Apple");
sheet.setValue(startRow + 3, startCol + 0, "Potato");
sheet.setValue(startRow + 4, startCol + 0, "Tomato");
sheet.setValue(startRow + 5, startCol + 0, "Sandwich");
sheet.setValue(startRow + 6, startCol + 0, "Hamburger");
sheet.setValue(startRow + 7, startCol + 0, "Grape");
sheet.setValue(startRow + 2, startCol + 1, "Fruit");
sheet.setValue(startRow + 3, startCol + 1, "Vegetable");
sheet.setValue(startRow + 4, startCol + 1, "Vegetable");
sheet.setValue(startRow + 5, startCol + 1, "Food");
sheet.setValue(startRow + 6, startCol + 1, "Food");
sheet.setValue(startRow + 7, startCol + 1, "Fruit");
sheet.setValue(startRow + 2, startCol + 2, 1.00);
sheet.setValue(startRow + 3, startCol + 2, 2.01);
sheet.setValue(startRow + 4, startCol + 2, 3.21);
sheet.setValue(startRow + 5, startCol + 2, 2);
sheet.setValue(startRow + 6, startCol + 2, 2);
sheet.setValue(startRow + 7, startCol + 2, 4);
var myFormatter = new GC.Spread.Formatter.GeneralFormatter("$#,##0.00;[Red] $#,##0.00");
for (var i = 2; i < 8; i++) {
sheet.getCell(startRow + i, startCol + 2).formatter(myFormatter);
}
sheet.setValue(startRow + 2, startCol + 3, "Wal-Mart");
sheet.setValue(startRow + 3, startCol + 3, "Other");
sheet.setValue(startRow + 4, startCol + 3, "Other");
sheet.setValue(startRow + 5, startCol + 3, "Wal-Mart");
sheet.setValue(startRow + 6, startCol + 3, "Wal-Mart");
sheet.setValue(startRow + 7, startCol + 3, "Other");
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
select {
padding: 4px 6px;
width: 100%;
box-sizing: border-box;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
