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 = 'OTk1ZTA3NzRlZDQ2ZmEzMjJkOGUzZmY5MzkxNjFkMGM4ZWRmZWQ1NzYxZTk2NWRkNmRhNmIzM2Y2ZDM4MmViNjgxOTEyYmU1OGFlMzNjN2NlN2UzOGYwZjc2ODM1ZWI2NDVkYzVmMjlkNzVjZjhiM2NmMzU2Y2YxN2JiOTU2M2QsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNeU1qWTFOVEU0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';

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