To fill in several types of data series, you can select cells and drag the fill handle. To use the fill handle, you select the cells that you want to use as a basis for filling additional cells, and then drag the fill handle across or down the cells that you want to fill.
To quickly fill the data, you can also double clicking the fill handle, then it will fill the expected range automatically
You can suppress series auto fill by holding down the Ctrl key as you drag the fill handle of a selection of two or more cells. After you drag fill, there will be an auto fill options button. You can click the button and change how the selection is filled. For example, you can choose to fill just cell formats by clicking Fill Formatting Only. The auto fill options provided are:
CopyCells: Fills cells with all data objects, including values, formatting, and formulas.
FillSeries: Fills cells with series.
FillFormattingOnly: Fills cells only with formatting.
FillWithoutFormatting: Fills cells with values and not formatting.
You can also clear the filled values by using drag fill back to the start range.
When you are dragging the fill handle, by default SpreadJS displays a tip to show the new dragged edge of the area to be filled. You can turn the tip display off as shown in this example:
Custom List Fill
The drag fill behavior supports custom list. It will fill the matching values in the custom list.
SpreadJS provides the day-of-the-week, and month-of-the-year in built-in custom lists. And can set the custom list in this example:
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 React, { useState, useEffect } from 'react';
import * as ReactDOM from 'react-dom';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet, Column } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export function AppFunc() {
const [spread,setSpread] = useState(null);
const [showDragFillTip, setShowDragFillTip] = useState(true);
const [showDragFillSmartTag, setShowDragFillSmartTag] = useState(true);
const [defaultDragFillType, setDefaultDragFillType] = useState(5);
let initSheet0 = function (spread) {
let sheet = spread.getSheet(0);
sheet.name("Base Fill");
sheet.setValue(1, 1, "Select a cell with data below, hold your cursor over the bottom right border until you see the ‘+’,");
sheet.setValue(2, 1, "Then drag down to autofill the cell data. You can also hold the <Ctrl> key down to auto-increment the values and you can double-click to fill it automatically:");
let simpleData = [
[1.0, 1],
[1.1, 2]
];
let dateData = [
[new Date(2018, 3, 1), new Date(2017, 11, 1), new Date(2018, 2, 31), new Date(2017, 11, 31)],
[new Date(2018, 4, 1), new Date(2018, 0, 1), new Date(2018, 3, 30), new Date(2018, 0, 31)]
];
sheet.setArray(4, 1, simpleData);
sheet.setArray(4, 4, dateData);
for (let i = 6; i < 15; i++) {
sheet.setValue(i, 0, "Fill Data");
}
for (let i = 4; i < 8; i++) {
sheet.setColumnWidth(i, 80);
}
let dateCell = sheet.getRange(4, 4, 2, 4).formatter('m/d/yyyy');
};
let initsheet1 = function (spread) {
let sheet = spread.getSheet(1);
sheet.name("String Fill");
sheet.setValue(0, 0, 'N: number, S: string. DragFill for string, detecting number from end to start, SN first and NS second. Trend N if S is same.');
let title = sheet.getCell(0, 0);
title.font("15px 'Franklin Gothic Medium'");
sheet.setValue(1, 0, 'String contains numbers only. Please drag up or down.');
sheet.setValue(6, 0, '123');
sheet.setValue(7, 0, '125');
sheet.setValue(6, 2, '-3');
sheet.setValue(7, 2, '-2');
sheet.setValue(6, 4, '003');
sheet.setValue(7, 4, '007');
sheet.setValue(1, 8, 'String contains number in the end of string. Please drag up or down and choose "Fill Series" for the single one.');
sheet.setValue(6, 8, 'a2');
sheet.setValue(6, 10, 'a1');
sheet.setValue(7, 10, 'a5');
sheet.setValue(6, 12, 'a001');
sheet.setValue(7, 12, 'a002');
sheet.setValue(6, 14, '1a2a3a4a5');
sheet.setValue(7, 14, '1a2a3a4a6');
sheet.setColumnWidth(14, 100);
sheet.setValue(24, 0, 'String contains number in the first of string. Please drag up or down.');
sheet.setValue(30, 0, '5a');
sheet.setValue(31, 0, '2a');
sheet.setValue(30, 2, '003b');
sheet.setValue(31, 2, '005b');
sheet.setValue(30, 4, '1a1a1a');
sheet.setValue(31, 4, '2a1a1a');
sheet.setValue(24, 8, 'String just to copy. Please drag up or down.');
sheet.setValue(30, 8, 'a1a1');
sheet.setValue(31, 8, 'a2a2');
sheet.setValue(30, 10, '1a1');
sheet.setValue(31, 10, '2a2');
sheet.setValue(30, 12, 'a1');
sheet.setValue(31, 12, 'b2');
}
let initsheet2 = function (spread) {
let sheet = spread.getSheet(2);
sheet.name("Custom Fill");
sheet.setValue(0, 0, 'Custom list for dragfill. Please drag up or down.');
sheet.setValue(6, 0, 'Mar');
sheet.setValue(7, 0, 'Apr');
sheet.setValue(6, 2, 'June');
sheet.setValue(7, 2, 'July');
sheet.setValue(6, 4, 'Mon');
sheet.setValue(7, 4, 'Tue');
sheet.setValue(6, 6, 'Friday');
sheet.setValue(7, 6, 'Saturday');
sheet.setValue(20, 0, 'The custom list customized two array currently, and shows as following. Enter one or more consecutive ones in the list to dragfill.');
let customList = sheet.parent.options.customList;
for (let i = 0; i < customList.length; i++) {
let itemList = customList[i];
sheet.setValue(21 + i, 0, 'List ' + i + ": ");
itemList.forEach(function (item, index) {
sheet.setValue(21 + i, index + 1, item);
});
}
}
let updateShowDragFillTip = function (value) {
setShowDragFillTip(value);
spread.options.showDragFillTip = value;
}
let updateShowDragFillSmartTag = function (value) {
setShowDragFillSmartTag(value);
spread.options.showDragFillSmartTag = value;
}
let updateDefaultDragFillType = function (type) {
type = parseInt(type, 10);
setDefaultDragFillType(type);
if (!isNaN(type)) {
spread.options.defaultDragFillType = type;
}
}
let initSpread = function (value) {
setSpread(value);
let customList = [
['Light', 'Sun', 'Moon', 'Star', 'Sky', 'Rain', 'Cloud'],
['Dog', 'Cat', 'Lion', 'Fish', 'Snake']
];
value.options.customList = customList;
value.suspendPaint();
initSheet0(value);
initsheet1(value);
initsheet2(value);
value.resumePaint();
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<Panel
showDragFillSmartTag = {showDragFillSmartTag}
showDragFillTip = {showDragFillTip}
defaultDragFillType = {defaultDragFillType}
updateShowDragFillTip={(value) => { updateShowDragFillTip(value) }}
updateShowDragFillSmartTag={(value) => { updateShowDragFillSmartTag(value) }}
updateDefaultDragFillType={(type) => { updateDefaultDragFillType(type) }}
></Panel>
</div>
)
}
const Panel = React.memo((props)=>{
return (<div class="options-container">
<div class="option-row">
<p>
Try checking the options on the right side and following the instructions in the sheet to see how those options affect the fill operations.
</p>
<label for="dragFillType">Default Drag Fill Type:</label>
<select id="dragFillType" title="Select one for default drag fill type." value={props.defaultDragFillType} onChange={(event) => { props.updateDefaultDragFillType(event.target.value) }}>
<option value={5} >Auto</option>
<option value={0} >Copy Cells</option>
<option value={1} >Fill Series</option>
<option value={2} >Fill Formatting Only</option>
<option value={3} >Fill Without Formatting</option>
</select>
</div>
<div class="option-row">
<input type="checkbox" id="chkShowDragFillTip" checked={props.showDragFillTip} onChange={(event) => { props.updateShowDragFillTip(event.target.checked) }} />
<label for="chkShowDragFillTip">Show Drag Fill Tip</label>
</div>
<div class="option-row">
<input id="chkShowDragFillSmartTag" type="checkbox" checked={props.showDragFillSmartTag} onChange={(event) => { props.updateShowDragFillSmartTag(event.target.checked) }} />
<label for="chkShowDragFillSmartTag">Show Drag Fill Smart Tag</label>
</div>
</div>)
});
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet, Column } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
const initSheet0 = (spread) => {
let sheet = spread.getSheet(0);
sheet.name("Base Fill");
sheet.setValue(1, 1, "Select a cell with data below, hold your cursor over the bottom right border until you see the ‘+’,");
sheet.setValue(2, 1, "Then drag down to autofill the cell data. You can also hold the <Ctrl> key down to auto-increment the values and you can double-click to fill it automatically:");
let simpleData = [
[1.0, 1],
[1.1, 2]
];
let dateData = [
[new Date(2018, 3, 1), new Date(2017, 11, 1), new Date(2018, 2, 31), new Date(2017, 11, 31)],
[new Date(2018, 4, 1), new Date(2018, 0, 1), new Date(2018, 3, 30), new Date(2018, 0, 31)]
];
sheet.setArray(4, 1, simpleData);
sheet.setArray(4, 4, dateData);
for (let i = 6; i < 15; i++) {
sheet.setValue(i, 0, "Fill Data");
}
for (let i = 4; i < 8; i++) {
sheet.setColumnWidth(i, 80);
}
let dateCell = sheet.getRange(4, 4, 2, 4).formatter('m/d/yyyy');
};
const initsheet1 = (spread) => {
let sheet = spread.getSheet(1);
sheet.name("String Fill");
sheet.setValue(0, 0, 'N: number, S: string. DragFill for string, detecting number from end to start, SN first and NS second. Trend N if S is same.');
let title = sheet.getCell(0, 0);
title.font("15px 'Franklin Gothic Medium'");
sheet.setValue(1, 0, 'String contains numbers only. Please drag up or down.');
sheet.setValue(6, 0, '123');
sheet.setValue(7, 0, '125');
sheet.setValue(6, 2, '-3');
sheet.setValue(7, 2, '-2');
sheet.setValue(6, 4, '003');
sheet.setValue(7, 4, '007');
sheet.setValue(1, 8, 'String contains number in the end of string. Please drag up or down and choose "Fill Series" for the single one.');
sheet.setValue(6, 8, 'a2');
sheet.setValue(6, 10, 'a1');
sheet.setValue(7, 10, 'a5');
sheet.setValue(6, 12, 'a001');
sheet.setValue(7, 12, 'a002');
sheet.setValue(6, 14, '1a2a3a4a5');
sheet.setValue(7, 14, '1a2a3a4a6');
sheet.setColumnWidth(14, 100);
sheet.setValue(24, 0, 'String contains number in the first of string. Please drag up or down.');
sheet.setValue(30, 0, '5a');
sheet.setValue(31, 0, '2a');
sheet.setValue(30, 2, '003b');
sheet.setValue(31, 2, '005b');
sheet.setValue(30, 4, '1a1a1a');
sheet.setValue(31, 4, '2a1a1a');
sheet.setValue(24, 8, 'String just to copy. Please drag up or down.');
sheet.setValue(30, 8, 'a1a1');
sheet.setValue(31, 8, 'a2a2');
sheet.setValue(30, 10, '1a1');
sheet.setValue(31, 10, '2a2');
sheet.setValue(30, 12, 'a1');
sheet.setValue(31, 12, 'b2');
}
const initsheet2 = (spread) => {
let sheet = spread.getSheet(2);
sheet.name("Custom Fill");
sheet.setValue(0, 0, 'Custom list for dragfill. Please drag up or down.');
sheet.setValue(6, 0, 'Mar');
sheet.setValue(7, 0, 'Apr');
sheet.setValue(6, 2, 'June');
sheet.setValue(7, 2, 'July');
sheet.setValue(6, 4, 'Mon');
sheet.setValue(7, 4, 'Tue');
sheet.setValue(6, 6, 'Friday');
sheet.setValue(7, 6, 'Saturday');
sheet.setValue(20, 0, 'The custom list customized two array currently, and shows as following. Enter one or more consecutive ones in the list to dragfill.');
let customList = sheet.parent.options.customList;
for (let i = 0; i < customList.length; i++) {
let itemList = customList[i];
sheet.setValue(21 + i, 0, 'List ' + i + ": ");
itemList.forEach(function (item, index) {
sheet.setValue(21 + i, index + 1, item);
});
}
}
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.state = {
showDragFillTip: true,
showDragFillSmartTag: true,
defaultDragFillType: 5
};
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<Panel
showDragFillTip={this.state.showDragFillTip}
showDragFillSmartTag={this.state.showDragFillSmartTag}
defaultDragFillType={this.state.defaultDragFillType}
updateShowDragFillTip={(value) => { this.updateShowDragFillTip(value) }}
updateShowDragFillSmartTag={(value) => { this.updateShowDragFillSmartTag(value) }}
updateDefaultDragFillType={(type) => { this.updateDefaultDragFillType(type) }}
></Panel>
</div>
)
}
updateShowDragFillTip(value) {
this.setState({showDragFillTip:value});
this.spread.options.showDragFillTip = value;
}
updateShowDragFillSmartTag(value) {
this.setState({showDragFillSmartTag:value});
this.spread.options.showDragFillSmartTag = value;
}
updateDefaultDragFillType(type) {
type = parseInt(type, 10);
this.setState({defaultDragFillType:type});
if (!isNaN(type)) {
this.spread.options.defaultDragFillType = type;
}
}
initSpread(spread) {
this.spread = spread;
let customList = [
['Light', 'Sun', 'Moon', 'Star', 'Sky', 'Rain', 'Cloud'],
['Dog', 'Cat', 'Lion', 'Fish', 'Snake']
];
spread.options.customList = customList;
spread.suspendPaint();
initSheet0(spread);
initsheet1(spread);
initsheet2(spread);
spread.resumePaint();
}
}
const Panel = (props) => {
const {
showDragFillTip,
showDragFillSmartTag,
defaultDragFillType,
updateShowDragFillTip,
updateShowDragFillSmartTag,
updateDefaultDragFillType
} = props;
return (
<div class="options-container">
<div class="option-row">
<p>
Try checking the options on the right side and following the instructions in the sheet to see how those options affect the fill operations.
</p>
<label for="dragFillType">Default Drag Fill Type:</label>
<select id="dragFillType" title="Select one for default drag fill type." value = {defaultDragFillType} onChange={(event) => { updateDefaultDragFillType(event.target.value)}}>
<option value={5} >Auto</option>
<option value={0} >Copy Cells</option>
<option value={1} >Fill Series</option>
<option value={2} >Fill Formatting Only</option>
<option value={3} >Fill Without Formatting</option>
</select>
</div>
<div class="option-row">
<input type="checkbox" id="chkShowDragFillTip" checked= {showDragFillTip} onChange={(event) => { updateShowDragFillTip(event.checked)}}/>
<label for="chkShowDragFillTip">Show Drag Fill Tip</label>
</div>
<div class="option-row">
<input id="chkShowDragFillSmartTag" type="checkbox" checked={showDragFillSmartTag} onChange={(event) => { updateShowDragFillSmartTag(event.checked)}} />
<label for="chkShowDragFillSmartTag">Show Drag Fill Smart Tag</label>
</div>
</div>
)
}
<!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="$DEMOROOT$/spread/source/data/ellipsis.js" type="text/javascript"></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" style="height: 100%;"></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 8px;
width: 100%;
box-sizing: border-box;
margin-top: 4px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
(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);
