Plug-ins

Spreadsheets will typically only copy a value from one cell to another, but AutoFill provides different options for how the data can be filled - presenting the options available to the end user for them to select from.

Built-in fills

AutoFill has four built-in fill types:

  • Increment: For numeric data AutoFill will give the user the option of incrementing the value as it is filled. The amount it is incremented by can be entered by the user, and also provides the option to use a negative value to perform a decrement. As an example, fill over the Age column below.
  • Fill: The data from the original cell is used for all other cells
  • Fill vertical: The data from the first row in the selected cells is copied vertically. As an example, fill over multiple rows and columns below and select the Fill cells vertically option.
  • Fill horizontal: The data from the first column in the selected cells is copied horizontally. As an example, fill over multiple rows and columns below and select the Fill cells horizontally option.

If these fill types don't suit your requirements, additional fills can be created using plug-ins as described here.

Plug-in structure

Fill options are provided by plug-ins which are attached to the $.fn.dataTable.AutoFill.actions object. Each property in this object must be an object that provides three functions:

  • available - Determine if the data that the user dragged the fill over is suitable for this fill type
  • option - Returns a question the user will be asked for if they want to use this fill type
  • execute - Modifies the data if this fill type is selected

As such, a typical plug-in will have this structure:

$.fn.dataTable.AutoFill.actions.myPlugin = {
    available: function ( dt, cells ) { },
 
    option: function ( dt, cells ) { },
 
    execute: function ( dt, cells, node ) { }
};

Availability

The first operation that a plug-in must perform is to tell AutoFill if the fill type is suited to the click and drag that the end user has performed. This function is executed for all fill types (including the built-in ones) immediately after the user releases the mouse.

It allows you to control if your fill is suitable to be applied to the dragged over cells - for example your plug-in might only work for single row horizontal drags, or based on a particular value at the starting point.

The function is passed two parameters:

  1. dt - The DataTables API instance for the table that is being filled
  2. cells - An array that describes the cells to be filled. This is a 2D array of objects - the first array for the rows and the inner objects for the cells. Each object contains the following properties:

The return value is a boolean: true if it is available - false otherwise.

Option question

Once AutoFill has run through the fill types available functions and determined which could be applied to perform the required fill, if there is more than a single option it will ask the end user what they want to do (i.e. select the fill type).

The option method how it knows how to present the option to the end user. So you could simply return the fill type name, or for more complex cases ask for user input data (such as how the built-in increment option works).

The function is passed two parameters:

  1. dt - The DataTables API instance for the table that is being filled
  2. cells - An array that describes the cells to be filled. See the description for available above.

The return value is a string that contains the option to be shown to the end user. This can include HTML.

Execution

If the user selects your fill type, or it is the only one available, the execute function is run and it must set the values for each of the cells that the fill was performed on. This is done by setting a set property on the cell object that is contained in the 2D array of cells.

The function is passed three parameters:

  1. dt - The DataTables API instance for the table that is being filled
  2. cells - An array that describes the cells to be filled. See the description for available above.
  3. node - The element that was used to display the option (from the option method above). This allows any user input values to be obtained from the DOM.

No return value is expected.

Example

Let's consider the example from the AutoFill examples. Here the plug-in is specifically designed to operate on the data that is contained in the first column in the table - a first name / last name field - it copied the surname from the first cell to all following, but retains the first name.

The code for this is as follows:

$.fn.dataTable.AutoFill.actions.names = {
    available: function ( dt, cells ) {
        // Only available if a single column is being
        // filled and it is the first column
        return cells[0].length === 1 && cells[0][0].index.column === 0;
    },
 
    option: function ( dt, cells ) {
        // Ask the user if they want to change the surname only
        return 'Fill only surname - retain first name';
    },
 
    execute: function ( dt, cells, node ) {
        // Modify the name and set the new values
        var surname = cells[0][0].data.split(' ')[1];
 
        for ( var i=0, ien=cells.length ; i<ien ; i++ ) {
            var name = cells[i][0].data.split(' ');
 
            cells[i][0].set = name[0]+' '+surname;
        }
    }
};

Looking at it line-by-line:

  • Line 1: Attach the plug-in to the AutoFill fill type object
  • Line 2: The available function
  • Line 5: Check that there is only one column (based on the number of items in the first row selected) and that the cell it contains belongs to the first column in the table (based on the index).
  • Line 8: The option function
  • Line 10: Simply the text to show for this fill type. If you click and drag over the first column in the example, you will see this text appear in the options list.
  • Line 13: The execute function
  • Line 15: Get the data from the first cell - this is the surname we want to set
  • Line 17: Loop over each of the rows so we can set the cell value
  • Line 20: Set the set property of the cell object, so AutoFill knows how to update the table.