Documentationkeyboard_arrow_rightFill 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.

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('MDg1YTdmNjgyZTBkZDllZDI3NWFhZjUyOWI5YzZlNGZkNWIwMDQxNGY1NGIwNmZkOGRkYmNiZDIwZTlkOTY2YmFhNDlhMzcxNjlmMWZmZTMyMDhmMzQ2YmFhMjU3Njc3YTE1YzA4ZWY0OTc2ZjA3ODBmNDNmMmJlMDIxOGU2NjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9UZzVNVE00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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('MDg1YTdmNjgyZTBkZDllZDI3NWFhZjUyOWI5YzZlNGZkNWIwMDQxNGY1NGIwNmZkOGRkYmNiZDIwZTlkOTY2YmFhNDlhMzcxNjlmMWZmZTMyMDhmMzQ2YmFhMjU3Njc3YTE1YzA4ZWY0OTc2ZjA3ODBmNDNmMmJlMDIxOGU2NjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9UZzVNVE00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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('MDg1YTdmNjgyZTBkZDllZDI3NWFhZjUyOWI5YzZlNGZkNWIwMDQxNGY1NGIwNmZkOGRkYmNiZDIwZTlkOTY2YmFhNDlhMzcxNjlmMWZmZTMyMDhmMzQ2YmFhMjU3Njc3YTE1YzA4ZWY0OTc2ZjA3ODBmNDNmMmJlMDIxOGU2NjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9UZzVNVE00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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('MDg1YTdmNjgyZTBkZDllZDI3NWFhZjUyOWI5YzZlNGZkNWIwMDQxNGY1NGIwNmZkOGRkYmNiZDIwZTlkOTY2YmFhNDlhMzcxNjlmMWZmZTMyMDhmMzQ2YmFhMjU3Njc3YTE1YzA4ZWY0OTc2ZjA3ODBmNDNmMmJlMDIxOGU2NjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9UZzVNVE00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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('MDg1YTdmNjgyZTBkZDllZDI3NWFhZjUyOWI5YzZlNGZkNWIwMDQxNGY1NGIwNmZkOGRkYmNiZDIwZTlkOTY2YmFhNDlhMzcxNjlmMWZmZTMyMDhmMzQ2YmFhMjU3Njc3YTE1YzA4ZWY0OTc2ZjA3ODBmNDNmMmJlMDIxOGU2NjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9UZzVNVE00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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('MDg1YTdmNjgyZTBkZDllZDI3NWFhZjUyOWI5YzZlNGZkNWIwMDQxNGY1NGIwNmZkOGRkYmNiZDIwZTlkOTY2YmFhNDlhMzcxNjlmMWZmZTMyMDhmMzQ2YmFhMjU3Njc3YTE1YzA4ZWY0OTc2ZjA3ODBmNDNmMmJlMDIxOGU2NjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpZek9UZzVNVE00TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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