The Spreadsheet Fill Handle
The Jspreadsheet 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 a basic and fill handle advance mode.
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 from a fill handle when the third argument origin: "fill-hanlde"
available on the event onafterchanges
Event |
---|
onbeforechanges?: (worksheet: worksheetInstance, records: Array<any>, origin: string) => void | boolean | Array<any>; |
onafterchanges?: (worksheet: worksheetInstance, records: Array<any>, origin: string) => void; |
Examples
Disable the fill handle
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/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} from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
const license = 'YTc4OGU1NDk2NzY4MDE2YzVlZjVjMTAyMDRkNDhkMzdiOTlmNjJmZjhlMjQ4YjBjYzU5Nzg4OTE1NzM3MTE1OWY2ZTllNjI2ZGU0ZDczNGZjOWU1ZTczYzE0MjdmNGJkNTUzNTU0NDU5MDE1YzFiOGI5MTdmOWY1NzI1ZjI1YjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5';
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} license={license} onafterchanges={onAfterChanges}>
<Worksheet minDimensions={[6, 6]} fillHandle={false}/>
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet" :license="license" :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";
const license = 'YTc4OGU1NDk2NzY4MDE2YzVlZjVjMTAyMDRkNDhkMzdiOTlmNjJmZjhlMjQ4YjBjYzU5Nzg4OTE1NzM3MTE1OWY2ZTllNjI2ZGU0ZDczNGZjOWU1ZTczYzE0MjdmNGJkNTUzNTU0NDU5MDE1YzFiOGI5MTdmOWY1NzI1ZjI1YjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5';
export default {
components: {
Spreadsheet,
Worksheet,
},
methods: {
onAfterChanges(worksheet, records, origin) {
console.log(records, origin)
}
},
data() {
return { license }
}
}
</script>
import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTc4OGU1NDk2NzY4MDE2YzVlZjVjMTAyMDRkNDhkMzdiOTlmNjJmZjhlMjQ4YjBjYzU5Nzg4OTE1NzM3MTE1OWY2ZTllNjI2ZGU0ZDczNGZjOWU1ZTczYzE0MjdmNGJkNTUzNTU0NDU5MDE1YzFiOGI5MTdmOWY1NzI1ZjI1YjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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);
}
});
}
}
Clone Data Only
The example below removes styles and editor configurations from the content.
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/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 "content/docs/react";
import {Spreadsheet, Worksheet, jspreadsheet} from "@jspreadsheet/react";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
jspreadsheet.setLicense('YTc4OGU1NDk2NzY4MDE2YzVlZjVjMTAyMDRkNDhkMzdiOTlmNjJmZjhlMjQ4YjBjYzU5Nzg4OTE1NzM3MTE1OWY2ZTllNjI2ZGU0ZDczNGZjOWU1ZTczYzE0MjdmNGJkNTUzNTU0NDU5MDE1YzFiOGI5MTdmOWY1NzI1ZjI1YjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
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" :onbeforepaste="onbeforepaste">
<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";
jspreadsheet.setLicense('YTc4OGU1NDk2NzY4MDE2YzVlZjVjMTAyMDRkNDhkMzdiOTlmNjJmZjhlMjQ4YjBjYzU5Nzg4OTE1NzM3MTE1OWY2ZTllNjI2ZGU0ZDczNGZjOWU1ZTczYzE0MjdmNGJkNTUzNTU0NDU5MDE1YzFiOGI5MTdmOWY1NzI1ZjI1YjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
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";
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YTc4OGU1NDk2NzY4MDE2YzVlZjVjMTAyMDRkNDhkMzdiOTlmNjJmZjhlMjQ4YjBjYzU5Nzg4OTE1NzM3MTE1OWY2ZTllNjI2ZGU0ZDczNGZjOWU1ZTczYzE0MjdmNGJkNTUzNTU0NDU5MDE1YzFiOGI5MTdmOWY1NzI1ZjI1YjUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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',
},
},
],
onbeforepaste: 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;
}
});
}
},
});
}
}