Auto Fill Lists

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

1. Using Drag Fill for Built-in Lists

2. 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 in abbreviated day names, day names, abbreviated month names, and month names. 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" which defines the list in the English language.

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 every time you want to analyze 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 sequence defined in the custom lists, as shown in the image below.

The following code sample shows how to automatically fill cells with built-in and custom lists while working with SpreadJS.

$(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);
    });
  }
});