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');
    }
}