Spreadsheet helpers
This section provides more information about common and helpful methods when dealing with spreadsheets.
Shortcuts for worksheets
All the methods are also available through the worksheet instance.
Documentation
Methods
Method |
Description |
getColumnName(number) |
Get the column letter based on a number.
jspreadsheet.helpers.getColumnName(columnNumber: Number) => String |
getCellNameFromCoords(number, number) |
Get the spreadsheet-like cell name from the coordinates.
jspreadsheet.helpers.getColumnName(x: Number, y: Number) => String |
getCoordsFromCellName(string) |
Get the coordinates from the spreadsheet-like cell name.
jspreadsheet.helpers.getCoordsFromColumnName(cellName: String) => [Number, Number] |
shiftFormula(string, number, number) |
Update all variables from a formula based a shift of x, y positions.
jspreadsheet.helpers.shiftFormula(formula: String, x: Number, y: Number) => String |
getTokensFromRange(string) |
Extract the tokens from a range. Example: getTokensFromRange('A1:A10'); // returns [A1,A2,A3,A4...]
jspreadsheet.helpers.getTokensFromRange(range: String) => Array |
getRangeFromTokens(array) |
Get the range from an array of tokens.
jspreadsheet.helpers.getRangeFromTokens(tokens: Array) => String |
getCoordsFromRange(string) |
Get the coordinates from a range string. Example: getTokensFromRange('A1:A10'); // returns [0,0,0,9]
jspreadsheet.helpers.getCoordsFromRange(range: string) => Array |
getCoordsFromRange(string) |
Get the coordinates x1,y1,x2,y2 from a range string.
jspreadsheet.helpers.getCoordsFromRange(range: String) => number[] |
getRangeFromCoords(number[]) |
Get the range string such as A1:A9 from an array of numbers
jspreadsheet.helpers.getRangeFromCoords(range[]) => String |
createFromTable(DOMElement, options) |
Extract the configuration to create a new spreadsheet from a static HTML element.
jspreadsheet.helpers.createFromTable(element: HTMLElement, options: Object) => Object |
parseCSV(string, string) |
Transform a CSV string into an array.
jspreadsheet.helpers.parseCSV(data: String, delimiter: String) => Array |
Examples
How to use the JSS data grid helpers
JavaScript example
let worksheets = jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
minDimensions: [10,10]
}]
});
// Returns A1
jspreadsheet.helpers.getCellNameFromCoords(0,0);
// Returns (4) [1, 0, 2, 3]
jspreadsheet.helpers.getCoordsFromRange('B1:C4');
// Also works with the worksheet instance. Returns 1,1
let coords = worksheets[0].helpers.getCoordsFromCellName('B2');
React example
import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Returns A1
let cellName = jspreadsheet.helpers.getCellNameFromCoords(0,0);
// Returns (4) [1, 0, 2, 3]
let range = jspreadsheet.helpers.getCoordsFromRange('B1:C4');
// Also works with the worksheet instance. Returns 1,1
let coords = spreadsheet.current[0].helpers.getCoordsFromCellName('B2');
// Render data grid component
return (
<Spreadsheet ref={spreadsheet} license={license}>
<Worksheet />
</Spreadsheet>
);
}
Vue example
<template>
<Spreadsheet ref="spreadsheet" :license="license">
<Worksheet />
</Spreadsheet>
</template>
<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
const license = 'Y2NjMDE1NTUwOWMyY2YzM2FjNGE1MDM1NjBkZDM4MDE1NWU1ZmRhMDIzMDA3Y2I4OTg0NzkwMTI0MTZhOWE3ZDAyOTdmYzVmNzcyMTBjNGY0YjYzMzRiZmFjYjg1ODZhYjdiM2EwNWM4Yjk4MDIxNDMwZTU2YzMzODZhMzQyNDIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RJMU9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';
export default {
components: {
Spreadsheet,
Worksheet,
},
mounted() {
// Returns A1
let cellName = jspreadsheet.helpers.getCellNameFromCoords(0,0);
// Returns (4) [1, 0, 2, 3]
let range = jspreadsheet.helpers.getCoordsFromRange('B1:C4');
// Also works with the worksheet instance. Returns 1,1
let coords = this.$refs.spreadsheet.current[0].helpers.getCoordsFromCellName('B2');
},
data() {
return {
license
};
}
}
</script>
Angular example
import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
@Component({
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
// Worksheets
worksheets: jspreadsheet.worksheetInstance[];
// Create a new data grid
ngOnInit() {
// Create spreadsheet
this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
worksheets: [
{
minDimensions: [10,10]
}
]
});
// Returns A1
let cellName = jspreadsheet.helpers.getCellNameFromCoords(0,0);
// Returns (4) [1, 0, 2, 3]
let range = jspreadsheet.helpers.getCoordsFromRange('B1:C4');
// Also works with the worksheet instance. Returns 1,1
let coords = this.worksheets[0].helpers.getCoordsFromCellName('B2');
}
}