Using Double-click for Auto Fill

SpreadJS allows users to automatically fill data (formulas and values) in the columns of the worksheet. Users can simply double-click the fill handle of a selected cell to fill the contents in the cell down an entire column. The fill handle allows users to fill data using four different type of options - Copy Cells, Fill Series, Fill Formatting Only and Fill Without Formatting.

While working with business applications, users often need to deal with worksheets having hundreds or thousands of rows of data that contain formulas. Using the double-click for auto fill allows the users to quickly and conveniently autofill the column formulas without having to use the mouse to manually drag and fill, saving the end user time and reducing possible errors.

Using Double-Click for Auto Fill Operations

Upon double-clicking the fill handle, the empty cells down the column (called fillRange) will be filled depending upon the contents of the startRange (the cell range that already contains data).

Shared below are the various auto fill operations that can be executed using double-click.

Auto Fill Numbers - If the startRange contains a numeric value, then the fill handle will automatically complete a list of numbers down the entire column as depicted below. The numeric series will be filled based on the corresponding pattern and the selected option of the fill handle.
Auto Fill Dates - If the startRange contains a date value, then the fill handle will autocomplete a list of dates down the entire column as depicted below. By default, when you double-click to automatically fill dates, the fill handle will execute the "Copy Cells" action. To fill consecutive dates, you can open the drag fill smart tag and choose the "Fill Series" option.
Auto Fill Formulas - If the startRange contains a formula, then the fill handle will copy the formula down the entire column as depicted below.

Note: The following points should be kept in mind while working with double-click for auto fill operations.

1) This type of auto fill works only when there are non-empty cells in the column adjacent to the fill handle.

2) When you double-click the fill handle in order to fill data in the columns, it will only extend down to the last non-empty cell in the current region.

3) Also, in case your adjacent column contains some empty cells, double-clicking the fill handle will fill the column upto the last non-empty cell in the adjacent column.

Using Code

Refer to the following code snippet in order to auto fill values and formulas using double-click in the spreadsheet.

JavaScript	Copy Code
// Auto fill Numeric Values and Formulas var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 4 }); var sheet = spread.getActiveSheet(); // Set values in the default worksheet sheet.setValue(0, 0, 'Name'); sheet.setValue(1, 0, 'Jack'); sheet.setValue(2, 0, 'Lily'); sheet.setValue(3, 0, 'Linda'); sheet.setValue(4, 0, 'John'); sheet.setValue(5, 0, 'Bob'); sheet.setValue(6, 0, 'Ming'); sheet.setValue(7, 0, 'Gorge'); sheet.setValue(8, 0, 'Kobe'); sheet.setValue(9, 0, 'James'); sheet.setValue(10, 0, 'Faker'); sheet.setValue(11, 0, 'Tai'); sheet.setValue(12, 0, 'Peter'); sheet.setValue(0, 1, 'Index'); sheet.setValue(1, 1, 1); sheet.setValue(2, 1, 2); var sheet1 = spread.getSheet(1); // Set values in the "sheet1" worksheet sheet1.setValue(0, 0, 'Product'); sheet1.setValue(1, 0, 'P1'); sheet1.setValue(2, 0, 'P2'); sheet1.setValue(3, 0, 'P3'); sheet1.setValue(4, 0, 'P4'); sheet1.setValue(5, 0, 'P5'); sheet1.setValue(6, 0, 'P6'); sheet1.setValue(7, 0, 'P7'); sheet1.setValue(8, 0, 'P8'); sheet1.setValue(9, 0, 'P9'); sheet1.setValue(10, 0, 'P10'); sheet1.setValue(11, 0, 'P11'); sheet1.setValue(12, 0, 'P12'); sheet1.setValue(0, 1, 'Price'); sheet1.setValue(1, 1, 1.1); sheet1.setValue(2, 1, 1.5); sheet1.setValue(3, 1, 1.6); sheet1.setValue(4, 1, 1.2); sheet1.setValue(5, 1, 1.5); sheet1.setValue(6, 1, 0.8); sheet1.setValue(7, 1, 0.8); sheet1.setValue(8, 1, 1.0); sheet1.setValue(9, 1, 2.2); sheet1.setValue(10, 1, 2.0); sheet1.setValue(11, 1, 0.7); sheet1.setValue(12, 1, 1.5); sheet1.setValue(0, 2, 'Units'); sheet1.setValue(1, 2, 11); sheet1.setValue(2, 2, 13); sheet1.setValue(3, 2, 10); sheet1.setValue(4, 2, 9); sheet1.setValue(5, 2, 8); sheet1.setValue(6, 2, 5); sheet1.setValue(7, 2, 10); sheet1.setValue(8, 2, 16); sheet1.setValue(9, 2, 9); sheet1.setValue(10, 2, 7); sheet1.setValue(11, 2, 10); sheet1.setValue(12, 2, 14); sheet1.setValue(0, 3, 'Comment'); sheet1.setValue(0, 4, 'Revenue'); sheet1.setFormula(1, 4, '=B2C2'); var sheet2 = spread.getSheet(2); // Set values in the "sheet2" worksheet sheet2.setValue(0, 0, 'Product'); sheet2.setValue(1, 0, 'P1'); sheet2.setValue(2, 0, 'P2'); sheet2.setValue(3, 0, 'P3'); sheet2.setValue(4, 0, 'P4'); sheet2.setValue(5, 0, 'P5'); sheet2.setValue(6, 0, 'P6'); sheet2.setValue(7, 0, 'P7'); sheet2.setValue(8, 0, 'P8'); sheet2.setValue(9, 0, 'P9'); sheet2.setValue(10, 0, 'P10'); sheet2.setValue(11, 0, 'P11'); sheet2.setValue(12, 0, 'P12'); sheet2.setValue(0, 1, 'Price'); sheet2.setValue(1, 1, 1.1); sheet2.setValue(2, 1, 1.5); sheet2.setValue(3, 1, 1.6); sheet2.setValue(4, 1, 1.2); sheet2.setValue(5, 1, 1.5); sheet2.setValue(6, 1, 0.8); sheet2.setValue(7, 1, 0.8); sheet2.setValue(8, 1, 1.0); sheet2.setValue(9, 1, 2.2); sheet2.setValue(10, 1, 2.0); sheet2.setValue(11, 1, 0.7); sheet2.setValue(12, 1, 1.5); sheet2.setValue(0, 2, 'Units'); sheet2.setValue(1, 2, 11); sheet2.setValue(2, 2, 13); sheet2.setValue(3, 2, 10); sheet2.setValue(4, 2, 9); sheet2.setValue(5, 2, 8); sheet2.setValue(6, 2, 5); sheet2.setValue(7, 2, 10); sheet2.setValue(8, 2, 16); sheet2.setValue(9, 2, 9); sheet2.setValue(10, 2, 7); sheet2.setValue(11, 2, 10); sheet2.setValue(12, 2, 14); sheet2.setValue(0, 3, 'Comment'); sheet2.setValue(0, 4, 'Revenue'); sheet2.setValue(7, 4, 'STOP'); sheet2.setFormula(1, 4, '=B2C2'); var sheet3 = spread.getSheet(3); // Set values in the "sheet3" worksheet sheet3.setValue(0, 4, 'Product'); sheet3.setValue(1, 4, 'P1'); sheet3.setValue(2, 4, 'P2'); sheet3.setValue(3, 4, 'P3'); sheet3.setValue(4, 4, 'P4'); sheet3.setValue(5, 4, 'P5'); sheet3.setValue(6, 4, 'P6'); sheet3.setValue(7, 4, 'P7'); sheet3.setValue(8, 4, 'P8'); sheet3.setValue(9, 4, 'P9'); sheet3.setValue(10, 4, 'P10'); sheet3.setValue(11, 4, 'P11'); sheet3.setValue(12, 4, 'P12'); sheet3.setValue(0, 3, 'Price'); sheet3.setValue(1, 3, 1.1); sheet3.setValue(2, 3, 1.5); sheet3.setValue(3, 3, 1.6); sheet3.setValue(4, 3, 1.2); sheet3.setValue(5, 3, 1.5); sheet3.setValue(6, 3, 0.8); sheet3.setValue(7, 3, 0.8); sheet3.setValue(8, 3, 1.0); sheet3.setValue(9, 3, 2.2); sheet3.setValue(10, 3, 2.0); sheet3.setValue(11, 3, 0.7); sheet3.setValue(12, 3, 1.5); sheet3.setValue(0, 2, 'Units'); sheet3.setValue(1, 2, 11); sheet3.setValue(2, 2, 13); sheet3.setValue(3, 2, 10); sheet3.setValue(4, 2, 9); sheet3.setValue(5, 2, 8); sheet3.setValue(6, 2, 5); sheet3.setValue(7, 2, 10); sheet3.setValue(8, 2, 16); sheet3.setValue(9, 2, 9); sheet3.setValue(10, 2, 7); sheet3.setValue(11, 2, 10); sheet3.setValue(12, 2, 14); sheet3.setValue(0, 1, 'Comment'); sheet3.setValue(0, 0, 'Revenue'); sheet3.setFormula(1, 0, '=C2*D2');

JavaScript

Copy Code

// Auto fill Numeric Values and Formulas

var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 4 });
var sheet = spread.getActiveSheet();

// Set values in the default worksheet
sheet.setValue(0, 0, 'Name'); sheet.setValue(1, 0, 'Jack');
sheet.setValue(2, 0, 'Lily'); sheet.setValue(3, 0, 'Linda');
sheet.setValue(4, 0, 'John'); sheet.setValue(5, 0, 'Bob');
sheet.setValue(6, 0, 'Ming'); sheet.setValue(7, 0, 'Gorge');
sheet.setValue(8, 0, 'Kobe'); sheet.setValue(9, 0, 'James');
sheet.setValue(10, 0, 'Faker'); sheet.setValue(11, 0, 'Tai');
sheet.setValue(12, 0, 'Peter'); sheet.setValue(0, 1, 'Index');
sheet.setValue(1, 1, 1); sheet.setValue(2, 1, 2);

var sheet1 = spread.getSheet(1);

// Set values in the "sheet1" worksheet
sheet1.setValue(0, 0, 'Product'); sheet1.setValue(1, 0, 'P1');
sheet1.setValue(2, 0, 'P2'); sheet1.setValue(3, 0, 'P3');
sheet1.setValue(4, 0, 'P4'); sheet1.setValue(5, 0, 'P5');
sheet1.setValue(6, 0, 'P6'); sheet1.setValue(7, 0, 'P7');
sheet1.setValue(8, 0, 'P8'); sheet1.setValue(9, 0, 'P9');
sheet1.setValue(10, 0, 'P10'); sheet1.setValue(11, 0, 'P11');
sheet1.setValue(12, 0, 'P12');

sheet1.setValue(0, 1, 'Price');
sheet1.setValue(1, 1, 1.1); sheet1.setValue(2, 1, 1.5);
sheet1.setValue(3, 1, 1.6); sheet1.setValue(4, 1, 1.2);
sheet1.setValue(5, 1, 1.5); sheet1.setValue(6, 1, 0.8);
sheet1.setValue(7, 1, 0.8); sheet1.setValue(8, 1, 1.0);
sheet1.setValue(9, 1, 2.2); sheet1.setValue(10, 1, 2.0);
sheet1.setValue(11, 1, 0.7); sheet1.setValue(12, 1, 1.5);
sheet1.setValue(0, 2, 'Units'); sheet1.setValue(1, 2, 11);
sheet1.setValue(2, 2, 13); sheet1.setValue(3, 2, 10);
sheet1.setValue(4, 2, 9); sheet1.setValue(5, 2, 8);
sheet1.setValue(6, 2, 5); sheet1.setValue(7, 2, 10);
sheet1.setValue(8, 2, 16); sheet1.setValue(9, 2, 9);
sheet1.setValue(10, 2, 7); sheet1.setValue(11, 2, 10);
sheet1.setValue(12, 2, 14); sheet1.setValue(0, 3, 'Comment');
sheet1.setValue(0, 4, 'Revenue');
sheet1.setFormula(1, 4, '=B2*C2');

var sheet2 = spread.getSheet(2);

// Set values in the "sheet2" worksheet
sheet2.setValue(0, 0, 'Product'); sheet2.setValue(1, 0, 'P1');
sheet2.setValue(2, 0, 'P2'); sheet2.setValue(3, 0, 'P3');
sheet2.setValue(4, 0, 'P4'); sheet2.setValue(5, 0, 'P5');
sheet2.setValue(6, 0, 'P6'); sheet2.setValue(7, 0, 'P7');
sheet2.setValue(8, 0, 'P8'); sheet2.setValue(9, 0, 'P9');
sheet2.setValue(10, 0, 'P10'); sheet2.setValue(11, 0, 'P11');
sheet2.setValue(12, 0, 'P12'); sheet2.setValue(0, 1, 'Price');
sheet2.setValue(1, 1, 1.1); sheet2.setValue(2, 1, 1.5);
sheet2.setValue(3, 1, 1.6); sheet2.setValue(4, 1, 1.2);
sheet2.setValue(5, 1, 1.5); sheet2.setValue(6, 1, 0.8);
sheet2.setValue(7, 1, 0.8); sheet2.setValue(8, 1, 1.0);
sheet2.setValue(9, 1, 2.2); sheet2.setValue(10, 1, 2.0);
sheet2.setValue(11, 1, 0.7); sheet2.setValue(12, 1, 1.5);
sheet2.setValue(0, 2, 'Units'); sheet2.setValue(1, 2, 11);
sheet2.setValue(2, 2, 13); sheet2.setValue(3, 2, 10);
sheet2.setValue(4, 2, 9); sheet2.setValue(5, 2, 8);
sheet2.setValue(6, 2, 5); sheet2.setValue(7, 2, 10);
sheet2.setValue(8, 2, 16); sheet2.setValue(9, 2, 9);
sheet2.setValue(10, 2, 7); sheet2.setValue(11, 2, 10);
sheet2.setValue(12, 2, 14); sheet2.setValue(0, 3, 'Comment');
sheet2.setValue(0, 4, 'Revenue'); sheet2.setValue(7, 4, 'STOP');

sheet2.setFormula(1, 4, '=B2*C2');

var sheet3 = spread.getSheet(3);

// Set values in the "sheet3" worksheet
sheet3.setValue(0, 4, 'Product'); sheet3.setValue(1, 4, 'P1');
sheet3.setValue(2, 4, 'P2'); sheet3.setValue(3, 4, 'P3');
sheet3.setValue(4, 4, 'P4'); sheet3.setValue(5, 4, 'P5');
sheet3.setValue(6, 4, 'P6'); sheet3.setValue(7, 4, 'P7');
sheet3.setValue(8, 4, 'P8'); sheet3.setValue(9, 4, 'P9');
sheet3.setValue(10, 4, 'P10'); sheet3.setValue(11, 4, 'P11');
sheet3.setValue(12, 4, 'P12'); sheet3.setValue(0, 3, 'Price');
sheet3.setValue(1, 3, 1.1); sheet3.setValue(2, 3, 1.5);
sheet3.setValue(3, 3, 1.6); sheet3.setValue(4, 3, 1.2);
sheet3.setValue(5, 3, 1.5); sheet3.setValue(6, 3, 0.8);
sheet3.setValue(7, 3, 0.8); sheet3.setValue(8, 3, 1.0);
sheet3.setValue(9, 3, 2.2); sheet3.setValue(10, 3, 2.0);
sheet3.setValue(11, 3, 0.7); sheet3.setValue(12, 3, 1.5);
sheet3.setValue(0, 2, 'Units'); sheet3.setValue(1, 2, 11);
sheet3.setValue(2, 2, 13); sheet3.setValue(3, 2, 10);
sheet3.setValue(4, 2, 9); sheet3.setValue(5, 2, 8);
sheet3.setValue(6, 2, 5); sheet3.setValue(7, 2, 10);
sheet3.setValue(8, 2, 16); sheet3.setValue(9, 2, 9);
sheet3.setValue(10, 2, 7); sheet3.setValue(11, 2, 10);
sheet3.setValue(12, 2, 14); sheet3.setValue(0, 1, 'Comment');
sheet3.setValue(0, 0, 'Revenue');

sheet3.setFormula(1, 0, '=C2*D2');