Jspreadsheet quick reference


1

Methods

MethodExample
getData: Get the full or partial table data
@Param boolan onlyHighlighedCells - Get only highlighted cells
myTable.getData([bool]);
setData: Set the table data
@Param mixed newData - New array or json data, null will reload what is in memory.
myTable.getJson([json]);
refresh: Refresh the data. myTable.refresh();
getJson: Get the full or partial table data in JSON format
@Param boolan onlyHighlighedCells - Get only highlighted cells
myTable.getData([bool]);
getRowData: Get the data from one row by number
@Param integer rowNumber - Row number
myTable.getRowData([int]);
setRowData: Set the data from one row by number
@Param integer rowNumber - Row number
@param array rowData - Row data
myTable.setRowData([int], [array]);
getColumnData: Get the data from one column by number
@Param integer columnNumber - Column number
myTable.getColumnData([int]);
setColumnData: Set the data from one column by number
@Param integer columnNumber - Column number
@param array colData - Column data
myTable.setColumnData([int], [array]);
setMerge: Merge cells
@Param string columnName - Column name, such as A1.
@Param integer colspan - Number of columns
@Param integer rowspan - Number of rows
myTable.setMerge([string], [int], [int]);
getMerge: Get merged cells properties
@Param string columnName - Column name, such as A1.
myTable.getMerge([string]);
removeMerge: Destroy merged by column name
@Param string columnName - Column name, such as A1.
myTable.removeMerge([string]);
destroyMerged: Destroy all merged cells myTable.destroyMerge();
getCell: get current cell DOM
@Param string columnName - str compatible with excel, or as object.
myTable.getCell([string]);
getLabel: get current cell DOM innerHTML
@Param string columnName - str compatible with excel, or as object.
myTable.getLabel([string]);
getValue: get current cell value
@Param mixed cellIdent - str compatible with excel, or as object.
myTable.getValue([string]);
getValueFromCoords: get value from coords
@Param integer x
@Param integer y
myTable.getValueFromCoords([integer], [integer]);
setValue: change the cell value
@Param mixed cellIdent - str compatible with excel, or as object.
@Param string Value - new value for the cell
@Param bool force - update readonly columns
myTable.setValue([string], [string], [bool]);
setValueFromCoords: get value from coords
@Param integer x
@Param integer y
@Param string Value - new value for the cell
@Param bool force - update readonly columns
myTable.getValueFromCoords([integer], [integer], [string], [bool]);
resetSelection: Reset the table selection
@Param boolean executeBlur - execute the blur from the table
myTable.resetSelection([bool]);
updateSelection: select cells
@Param object startCell - cell object
@Param object endCell - cell object
@Param boolean ignoreEvents - ignore onselection event
myTable.updateSelection([cell], [cell], true);
updateSelectionFromCoords: select cells
@Param integer x1
@Param integer y1
@Param integer x2
@Param integer y2
myTable.updateSelectionFromCoords([integer], [integer], [integer], [integer]);
getWidth: get the current column width
@Param integer columnNumber - column number starting on zero
myTable.getWidth([integer]);
setWidth: change column width
@Param integer columnNumber - column number starting on zero
@Param string newColumnWidth - New column width
myTable.setWidth([integer], [integer]);
getHeight: get the current row height
@Param integer rowNumber - row number starting on zero
myTable.getHeight([integer]);
setHeight: change row height
@Param integer rowNumber - row number starting on zero
@Param string newRowHeight- New row height
myTable.setHeight([integer], [integer]);
getHeader: get the current header by column number
@Param integer columnNumber - Column number starting on zero
myTable.getHeader([integer]);
getHeaders: get all header titles myTable.getHeaders();
setHeader: change header by column
@Param integer columnNumber - column number starting on zero
@Param string columnTitle - New header title
myTable.setHeader([integer], [string]);
getStyle: get table or cell style
@Param mixed - cell identification or null for the whole table.
myTable.getStyle([string]));
setStyle: set cell(s) CSS style
@Param mixed - json with whole table style information or just one cell identification. Ex. A1.
@param k [optional]- CSS key
@param v [optional]- CSS value
myTable.setSyle([object], [string], [string]);
resetStyle: remove all style from a cell
@Param string columnName - Column name, example: A1, B3, etc
myTable.resetStyle([string]);
getComments: get cell comments
@Param mixed - cell identification or null for the whole table.
myTable.getComments([string]);
setComments: set cell comments
@Param cell - cell identification
@Param text - comments
myTable.setComments([string], [string]);
orderBy: reorder a column asc or desc
@Param integer columnNumber - column number starting on zero
@Param smallint sortType - One will order DESC, zero will order ASC, anything else will toggle the current order
myTable.orderBy([integer], [boolean]);
getConfig: Get the table configuration variables myTable.getConfig();
setConfig: Set any new table configuration variables myTable.setConfig([object]);
insertColumn: add a new column
@param mixed - num of columns to be added or data to be added in one single column
@param int columnNumber - number of columns to be created
@param boolean insertBefore
@param object properties - column properties
myTable.insertColumn([mixed], [integer], [boolean], [object]);
deleteColumn: remove column by number
@Param integer columnNumber - Which column should be excluded starting on zero
@param integer numOfColumns - number of columns to be excluded from the reference column
myTable.deleteColumn([integer], [integer]);
moveColumn: change the column position
@Param integer columnPosition
@Param integer newColumnPosition
myTable.moveColumn([integer], [integer]);
insertRow: add a new row
@Param mixed - number of blank lines to be insert or a single array with the data of the new row
@Param integer rowNumber - reference row number
@param boolean insertBefore
myTable.insertRow([mixed], [integer], [boolean]);
deleteRow: remove row by number
@Param integer rowNumber - Which row should be excluded starting on zero
@Param integer numOfRows - number of lines to be excluded
myTable.deleteRow([integer], [integer]);
moveRow: change the row position
@Param integer rowPosition
@Param integer newRowPosition
>myTable.moveRow([integer], [integer]);
download: get the current data as a CSV file
@Param bool - true to download parsed formulas.
myTable.download([bool]);
getMeta: get the table or cell meta information
@Param mixed - cell identification or null for the whole table.
myTable.getMeta([string]);
setMeta: set the table or cell meta information
@Param mixed - json with whole table meta information.
myTable.setMeta[mixed]);
fullscreen: Toogle table fullscreen mode
@Param boolan fullscreen - define fullscreen status as true or false
myTable.fullscreen([bool]);
getSelectedRows: Get the selected rows
@Param boolan asIds - Get the rowNumbers or row DOM elements
myTable.getSelectedRows([bool]);
getSelectedColumns: Get the selected columns
@Param boolan asIds - Get the colNumbers or row DOM elements
myTable.getSelectedColumns([bool]);
showColumn: show column by number myTable.showIndex([int]);
hideColumn: hide column by number myTable.hideColumn([int]);
showIndex: show column of index numbers myTable.showIndex();
hideIndex: hide column of index numbers myTable.hideIndex();
search: search in the table, only if directive is enabled during inialization.
@Param string - Search for word
myTable.search([string]);
resetSearch: reset search table myTable.resetSearch();
whichPage: Which page showing on Jspreadsheet - Valid only when pagination is true. myTable.whichPage();
page: Go to page number- Valid only when pagination is true.
@Param integer - Go to page number
myTable.page([integer]);
undo: Undo last changes myTable.undo();
redo: Redo changes myTable.redo();
getColumnOptions: Get all options from a column or custom cell.
@Param integer - columnNumber
@Param integer - rowNumber.
myTable.getColumnOptions([integer], [integer]);
getType: Get a column type and all column attributes.
@Param integer - columnNumber
myTable.getType([integer]);
setType: Set the column and all column attributes.
@Param integer - columnNumber
@Param mixed - string for type, or object with type and all attributes.
myTable.setType([integer], [mixed]);
updateNestedHeader: Update a nested header attributes.
@Param integer x
@Param integer y
@Param object properties - Properties are title, colspan, rowspan.
myTable.updateNestedHeader([integer], [integer], [object]);
getNestedColumns: Get all columns below a nested header.
@Param integer x
@Param integer y
myTable.getNestedColumns([integer], [integer]);
setBorder: Create or update a border.
@Param integer x1
@Param integer y1
@Param integer x2
@Param integer y2
@Param string borderName
myTable.setBorder([integer], [integer],[integer], [integer], [string]);
setFooter: Create a fix footer row.
@Param array data
myTable.setFooter([array]);
getHighlighted: Get the highlighted coordinates myTable.getHighlighted();
selectAll: Select all cells myTable.selectAll();
copy: Copy data from the spreadsheet myTable.copy()

Working example



2

Events

Event Description
onevent This method is called when any of the following elements in this tables happens.
onbeforesave Before any data is sent to the backend
onsave After any data is sent to the backend
onload This method is called when the method setData
onbeforechange Before a column value is changed. NOTE: It is possible to overwrite the original value, by return a new value on this method. v3.4.0+
onchange After a column value is changed.
onafterchanges After all changes are applied in the table.
onpaste After a paste action is performed in the javascript table.
onbeforepaste Before the paste action is performed. Used to parse any input data, should return the data.
oninsertrow After a new row is inserted.
onbeforeinsertrow Before a new row is inserted. You can cancel the insert event by returning false.
ondeleterow After a row is excluded.
onbeforedeleterow Before a row is deleted. You can cancel the delete event by returning false.
oninsertcolumn After a new column is inserted.
onbeforeinsertcolumn Before a new column is inserted. You can cancel the insert event by returning false.
ondeletecolumn After a column is excluded.
onbeforedeletecolumn Before a column is excluded. You can cancel the insert event by returning false.
onmoverow After a row is moved to a new position.
onmovecolumn After a column is moved to a new position.
onresizerow After a change in row height.
onresizecolumn After a change in column width.
onselection On the selection is changed.
onsort After a colum is sorted.
onfocus On table focus
onblur On table blur
onmerge On column merge
onchangeheader On header change
onundo On undo is applied
onredo On redo is applied
oneditionstart When a openEditor is called.
oneditionend When a closeEditor is called.
onchangestyle When a setStyle is called.
onchangemeta When a setMeta is called.
onchangepage Call when pagination is enabled and the page is changed.
onbeforecreateworksheet Before a creating of a new worksheet tab.
onbeforecreateworksheet Before a creating of a new worksheet tab.

Example on handling events on Jspreadsheet


3

Initialization

ParameterDescription
cloud Hash identification for the Jspreadsheet Cloud Engine for realtime spreadsheet sharing: guid
url Load a external json file from this URL: string
data Load this data into the javascript table: array
json Load this json into the javascript table: json
persistance Remote data persistance: mixed. Bool when the URL to load is the same for the updates. String for a custom backend persistance server.
copyCompatibility When is true copy and export will bring formula results, if false will bring formulas: boolean
rows Row properties: height.: object
columns Column type, title, width, align, dropdown options, text wrapping, mask, etc.: object
cells Cell type, title, width, align, dropdown options, text wrapping, mask, etc.: object
nestedHeaders Define the nested headers, including title, colspan, etc: object
defaultColWidth Default width for a new column: integer
defaultColAlign Default align for a new column: [center, left, right]
minSpareRows Minimum number of spare rows: [integer]
minSpareCols Minimum number of spare cols: [integer]
minDimensions Minimum table dimensions: [cols,rows]
allowExport Allow table export: bool
includeHeadersOnDownload Include header titles on download: bool
columnSorting Allow column sorting: bool
columnDrag Allow column dragging: bool
columnResize Allow column resizing: bool
rowResize Allow row resizing: bool
rowDrag Allow row dragging: bool
editable Allow table edition: bool
allowInsertRow Allow insert a new row: bool
allowManualInsertRow Allow user to insert a new row: bool
allowInsertColumn Allow insert a new column: bool
allowManualInsertColumn Allow user to create a new column: bool
allowDeleteRow Allow delete a row: bool
allowDeleteColumn Allow delete a column: bool
allowRenameColumn Allow rename a column: bool
allowComments Allow comments over the cells: bool
wordWrap Global text wrapping: bool
csv Load a external CSV file from this URL: string
csvFileName Default filename for a download method: string
csvHeaders Load header titles from the CSV file: bool
csvDelimiter Default delimiter for the CSV file: string
selectionCopy Allow selection copy: bool
mergeCells Cells to be merged in the table innitialization: object
toolbar Add custom toolbars: object
search: bool Allow search in the table
pagination: integer Break the table by pages
paginationOptions: [array of numbers] Number of records per page: 25,50,75,100 for example
paginationWheel: bool Use the mouse wheel to change pages in pagination is available
fullscreen: bool Fullscreen mode
lazyLoading: bool Activate the table lazyloading
loadingSpin: bool Activate the loading spin
tableOverflow Allow table overflow: bool
tableOverflowResizable Allow table resize: bool
tableHeight Force the max height of the table: CSS String
tableWidth Force the max width of the table: CSS String
comments Initial table comments: object
meta Meta information: object
style Cells style in the table innitialization: object
parseFormulas Enable execution of formulas inside the table
autoIncrement Auto increment actions when using the dragging corner
autoCastings Convert strings into numbers when is possible
freezeColumns Number of freezed columns
orderBy Initial table order
tabs Allow tabs
allowCreateTabs Allow create new tabs
parseTableFirstRowAsHeader When creating a spreadsheet based on a HTML table, parse the first row as headers.
parseTableAutoCellType Change the column type to based on frequency of value repetions in a column or in the format of the data.
filters Allow column filters
footers Fixed footer data
plugins Plugin extensions
updateTable Method to config custom script execution. NOTE: This does not work with lazyLoading, Pagination or Search options.
contextMenu Context menu content: function() { return customMenu }
text: object All messages to be customized

4

Translations

KeyDefault value
noRecordsFound No records found
showingPage Showing page {0} of {1} entries
show Show
entries entries
insertANewColumnBefore Insert a new column before
insertANewColumnAfter Insert a new column after
deleteSelectedColumns Delete selected columns
renameThisColumn Rename this column
orderAscending Order ascending
orderDescending Order descending
insertANewRowBefore Insert a new row before
insertANewRowAfter Insert a new row after
deleteSelectedRows Delete selected rows
editComments Edit comments
addComments Add comments
comments Comments
clearComments Clear comments
copy Copy...
paste Paste...
saveAs Save as...
about About
areYouSureToDeleteTheSelectedRows Are you sure to delete the selected rows?
areYouSureToDeleteTheSelectedColumns Are you sure to delete the selected columns?
thisActionWillDestroyAnyExistingMergedCellsAreYouSure This action will destroy any existing merged cells. Are you sure?
thisActionWillClearYourSearchResultsAreYouSure This action will clear your search results. Are you sure?
thereIsAConflictWithAnotherMergedCell There is a conflict with another merged cell
invalidMergeProperties Invalid merged properties
cellAlreadyMerged Cell already merged
noCellsSelected No cells selected

Working example