Products

Documentation keyboard_arrow_right Fill handle

Spreadsheet Fill Handle

The fill handle is a tool that allows users to quickly copy formulas or data vertically (up or down a column) or horizontally (across a row). Jspreadsheet offers basic and advanced fill handle modes as described in the section below.

Advanced Fill Handle

You can use the fill handle in combination with the Formula Pro feature to achieve results similar to those in Excel and Google Sheets.

Documentation

Settings

You can enable or disable the fill handle as below.

Settings Description
fillHandle?: boolean Enable or disable the fill handle. Default: true

Events

You can identify changes originating from a fill handle operation when the third argument origin: "fill-handle" is passed to the onafterchanges event. This allows you to handle fill operations differently from other data changes.

Event
onbeforechanges?: (worksheet: worksheetInstance, records: Array<any>, origin: string) => void | boolean | Array<any>;
onafterchanges?: (worksheet: worksheetInstance, records: Array<any>, origin: string) => void;

Examples

Disable Fill Handle

<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        minDimensions: [6,6],
        fillHandle: false,
    }],
    onafterchanges: function(worksheet, records, origin) {
        console.log(origin, records);    
    }
});
</script>

import React, {useRef} from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    const onAfterChanges = function (worksheet, records, origin) {
        console.log(origin, records)
    }

    // Render data grid component
    return (
        <Spreadsheet ref={spreadsheet} onafterchanges={onAfterChanges}>
            <Worksheet minDimensions={[6, 6]} fillHandle={false}/>
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :onafterchanges="onAfterChanges">
        <Worksheet :minDimensions="[6, 6]" :fillHandle="false" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    methods: {
        onAfterChanges(worksheet, records, origin) {
            console.log(records, origin)
        }
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create component
@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`,
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];
    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                minDimensions: [6,6],
                fillHandle: false,
            }],
            onafterchanges: function(worksheet: any, records: any, origin: any) {
                console.log(origin, records);    
            }
        });
    }
}

Copy Data Without Formatting

This example demonstrates how to copy only cell values during fill operations, excluding styles, editor configurations, and formatting.

<html>
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<div id="spreadsheet"></div>

<script>
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        minDimensions: [5,5],
        data: [['A']],
        cells: {
            A1: { type: 'dropdown', source: ['A','B'] }
        },
        style: {
            A1: 'background-color: red',
        }
    }],
    onbeforechanges: function(worksheet, records, origin) {
        // Remove any format or style
        if (origin === 'fill-handle' || origin === 'paste') {
            records.forEach(record => {
                if (typeof record.options !== 'undefined') {
                    // Do not paste information about the editors
                    delete record.options;
                }
                if (typeof record.style !== 'undefined') {
                    // Do not paste style
                    delete record.style;
                }
            });
        }
    }
});
</script>
</html>

import React, {useRef} from "react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Data
    const data = [['A']];
    const cells = {
        A1: { type: 'dropdown', source: ['A','B'] }
    }
    const style = {
        A1: 'background-color: red',
    }

    const onbeforechanges = (worksheet, records, origin) => {
        // Remove any format or style
        if (origin === 'fill-handle' || origin === 'paste') {
            records.forEach(record => {
                if (typeof record.options !== 'undefined') {
                    // Do not paste information about the editors
                    delete record.options;
                }
                if (typeof record.style !== 'undefined') {
                    // Do not paste style
                    delete record.style;
                }
            });
        }
    }

    // Render data grid component
    return (
        <Spreadsheet ref={spreadsheet} onbeforechanges={onbeforechanges}>
            <Worksheet data={data} cells={cells} style={style} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet" :onbeforechanges="onbeforechanges">
        <Worksheet :data="data" :cells="cells" :style="style" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        const data = [['A']];
        const cells = {
            A1: { type: 'dropdown', source: ['A','B'] }
        }
        const style = {
            A1: 'background-color: red',
        }
    
        const onbeforechanges = (worksheet, records, origin) => {
            // Remove any format or style
            if (origin === 'fill-handle' || origin === 'paste') {
                records.forEach(record => {
                    if (typeof record.options !== 'undefined') {
                        // Do not paste information about the editors
                        delete record.options;
                    }
                    if (typeof record.style !== 'undefined') {
                        // Do not paste style
                        delete record.style;
                    }
                });
            }
        }

        return {
          data,
          style,
          cells,
          onbeforechanges
        }
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NWM2NWYxMmFkYjRhY2Y3ZTMxNzM0OWNjNGRlOTNhZDc1N2IyNWU3YmFmMmMxOGYxMDRlYjg1MjI5NWZkZGYzYmVjNjRmN2Y4YTNmMjk2ZGIyZDRhOTgyYTA5ODdmNjk2MzM3MThjNjg2Nzk3YzY5ODg0MGE0MGUyOGExMmIwM2EsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGczTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Create component
@Component({
  standalone: true,
  selector: 'app-root',
  template: `<div #spreadsheet></div>`,
})
export class AppComponent {
  @ViewChild('spreadsheet') spreadsheet: ElementRef;
  // Worksheets
  worksheets: jspreadsheet.worksheetInstance[];
  // Create a new data grid
  ngAfterViewInit() {
    // Create spreadsheet
    this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
      worksheets: [
        {
          minDimensions: [5, 5],
          data: [['A']],
          cells: {
            A1: { type: 'dropdown', source: ['A', 'B'] },
          },
          style: {
            A1: 'background-color: red',
          },
        },
      ],
      onbeforechanges: function(worksheet: any, records: any, origin: string) {
        // Remove any format or style
        if (origin === 'fill-handle' || origin === 'paste') {
          records.forEach(record => {
            if (typeof record.options !== 'undefined') {
              // Do not paste information about the editors
              delete record.options;
            }
            if (typeof record.style !== 'undefined') {
              // Do not paste style
              delete record.style;
            }
          });
        }
      },
    });
  }
}

Edit this page on GitHub