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

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

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

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

<template>
    <Spreadsheet ref="spreadsheet" :license="license">
        <Worksheet />
    </Spreadsheet>
</template>

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

const license = 'N2JhZTYxY2FhNjYwYzFjZTM1YjM0YThhMmRkMGQ2ZDVjYmRkMzA0MTY2ZWUzMWJjNTI1ZDFhN2JmNDkwODg2Mjg2YmEzMjVhNTQzZDY3NzdmZDhiZmI1MDI3MzAyZTFmZTA3ODZhZjU4NjE0Y2ZjOTNhMjJjODFmNDRiYTA5YTMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNME9EY3dNRE0zTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

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>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";

import "jspreadsheet/dist/jspreadsheet.css"
import "jsuites/dist/jsuites.css"

@Component({
    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: [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');
    }
}

Edit this page on GitHub