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:
window.onload = function () {
var customList = [
['Light', 'Sun', 'Moon', 'Star', 'Sky', 'Rain', 'Cloud'],
['Dog', 'Cat', 'Lion', 'Fish', 'Snake']
];
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 3});
spread.options.customList = customList;
spread.suspendPaint();
initSheet0(spread);
initsheet1(spread);
initsheet2(spread);
spread.resumePaint();
document.getElementById("chkShowDragFillTip").addEventListener('click',function() {
spread.options.showDragFillTip = this.checked;
});
document.getElementById("chkShowDragFillSmartTag").addEventListener('click',function () {
spread.options.showDragFillSmartTag = this.checked;
});
document.getElementById("dragFillType").addEventListener('change',function () {
var type = parseInt(this.value, 10);
if (!isNaN(type)) {
spread.options.defaultDragFillType = type;
}
});
};
function initSheet0(spread) {
var 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:");
var simpleData = [
[1.0, 1],
[1.1, 2]
];
var 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 (var i = 6; i < 15; i++) {
sheet.setValue(i, 0, "Fill Data");
}
for (var i = 4; i < 8; i++) {
sheet.setColumnWidth(i, 80);
}
var dateCell = sheet.getRange(4, 4, 2, 4).formatter('m/d/yyyy');
};
function initsheet1 (spread) {
var 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.');
var 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');
}
function initsheet2 (spread) {
var 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.');
var customList = sheet.parent.options.customList;
for (var i = 0; i < customList.length; i++) {
var itemList = customList[i];
sheet.setValue(21 + i, 0, 'List ' + i + ": ");
itemList.forEach(function(item, index) {
sheet.setValue(21 + i, index + 1, item);
});
}
}
<!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/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<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.">
<option value="5" selected="selected">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 />
<label for="chkShowDragFillTip">Show Drag Fill Tip</label>
</div>
<div class="option-row">
<input id="chkShowDragFillSmartTag" type="checkbox" checked="checked" />
<label for="chkShowDragFillSmartTag">Show Drag Fill Smart Tag</label>
</div>
</div>
</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;
}
