SpreadJS 13
SpreadJS Documentation / Developer's Guide / Features / Manage User Interface / Auto Fill Data / Double-click for Auto Fill
In This Topic
    Double-click for Auto Fill
    In This Topic

    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.

    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, '=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');