Auto Fill Lists

SpreadJS allows users to automatically fill lists by using the drag fill option in the following ways.

Using Drag Fill for Built-in Lists
Using Drag Fill for Custom Lists

Using Drag Fill for Built-in Lists

Built-in lists are provided by default when you work with SpreadJS. These lists are used especially when users want to fill "abbreviatedDayNames", "dayNames", "abbreviatedMonthNames" and "monthNames". While executing the drag fill operation for built-in lists, you can ignore the case but the filled value should exactly match the entry in the list.

For various built-in cultures, a built-in list displays a different language, as shown in the table shared below. The default culture is "en-US" that defines the list in the English language.

Culture	List
"en-US"	[ ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'], ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'], ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'], ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'] ]
"ja-JP"	[ ['日', '月', '火', '水', '木', '金', '土'], ['日曜日', '月曜日', '火曜日', '水曜日', '木曜日', '金曜日', '土曜日'], ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12'], ['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月'] ]
"zh-cn"	[ ['周日', '周一', '周二', '周三', '周四', '周五', '周六'], ['星期日', '星期一', '星期二', '星期三', '星期四', '星期五', '星期六'], ['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月'], ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'] ]
"ko-kr"	[ ["일", "월", "화", "수", "목", "금", "토"], ["일요일", "월요일", "화요일", "수요일", "목요일", "금요일", "토요일"], ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12"], ["1월", "2월", "3월", "4월", "5월", "6월", "7월", "8월", "9월", "10월", "11월", "12월"] ]

Using Drag Fill for Custom Lists

Creating and using a custom list is helpful especially when you're working with large business applications containing data that is meant to be entered repeatedly. For example - While estimating sales figures, interpreting specific regions, evaluating product categories and visualizing information monthly, quarterly or annually; it becomes cumbersome to manually enter data everytime you want to analyse the worksheet. Also, using the default Auto fill options may not help since you might have a custom series, sequence, style or order that you want to replicate while populating the worksheet. In such a scenario, custom lists enable users to create their own series of data that always repeats itself in the defined order when the drag fill operation is executed.

In order to create a custom list, you can simply navigate to the cell where you want the data to be filled, write two or more entries in the sequence and then drag the fill handle in an upward or downward direction. The data will be filled according to the content and its sequence defined in the custom lists, as shown in the image shared below.

Using Code

Refer to the following example code in order to automatically fill cells with built-in and custom lists while working with SpreadJS.

JavaScript	Copy Code
$(document).ready(function () { // Defining Custom Lists var customList = [ ['Light', 'Sun', 'Moon', 'Star', 'Sky', 'Rain'], ['Dog', 'Cat', 'Lion', 'Fish', 'Snake'] ]; // Initializing Spread var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2, customList: customList }); var sheet = spread.getSheet(0); sheet.name("Built-in DragFill"); // Fill built-in list sheet.setValue(0, 0, 'Sunday'); sheet.setValue(1, 0, 'Monday'); var startRange = new GC.Spread.Sheets.Range(0, 0, 2, 1); var fillRange = new GC.Spread.Sheets.Range(0, 0, 10, 1); sheet.fillAuto(startRange, fillRange, { fillType: GC.Spread.Sheets.Fill.FillType.auto, series: GC.Spread.Sheets.Fill.FillSeries.column }); // Custom DragFill var sheet1 = spread.getSheet(1); sheet1.name("Custom DragFill"); sheet1.setValue(0, 0, 'Please drag up or down'); sheet1.setValue(2, 0, 'The custom list customized two array currently'); sheet1.setValue(3, 0, 'Enter one or more consecutive string from the list in the cell to dragfill'); var customList = sheet1.parent.options.customList; for (var i = 0; i < customList.length; i++) { var itemList = customList[i]; sheet1.setValue(5 + i, 0, 'List ' + i + ": "); itemList.forEach(function (item, index) { sheet1.setValue(5 + i, index + 1, item); }); } });

JavaScript

Copy Code

$(document).ready(function ()

{
// Defining Custom Lists
var customList =

[

['Light', 'Sun', 'Moon', 'Star', 'Sky', 'Rain'], ['Dog', 'Cat', 'Lion', 'Fish', 'Snake']
];

// Initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2, customList: customList });
var sheet = spread.getSheet(0);
sheet.name("Built-in DragFill");

// Fill built-in list
sheet.setValue(0, 0, 'Sunday');
sheet.setValue(1, 0, 'Monday');
var startRange = new GC.Spread.Sheets.Range(0, 0, 2, 1);
var fillRange = new GC.Spread.Sheets.Range(0, 0, 10, 1);
sheet.fillAuto(startRange, fillRange, {
fillType: GC.Spread.Sheets.Fill.FillType.auto,
series: GC.Spread.Sheets.Fill.FillSeries.column
});

// Custom DragFill
var sheet1 = spread.getSheet(1);
sheet1.name("Custom DragFill");
sheet1.setValue(0, 0, 'Please drag up or down');
sheet1.setValue(2, 0, 'The custom list customized two array currently');
sheet1.setValue(3, 0, 'Enter one or more consecutive string from the list in the cell to dragfill');
var customList = sheet1.parent.options.customList;
for (var i = 0; i < customList.length; i++) {
var itemList = customList[i];
sheet1.setValue(5 + i, 0, 'List ' + i + ": ");
itemList.forEach(function (item, index) {
sheet1.setValue(5 + i, index + 1, item);
});
}
});