Working with GridJs Client Side
Contents
[
Hide
]
Working with GridJs Client Side
We developed GridJs client based on x-spreadsheet.
the main steps are:
- create x_spreadsheet instance
xs = x_spreadsheet(id, options)
// the parameters are:
id:the html node id ,for example :'#gridjs-demo' for the html <div id="gridjs-demo"></div>
options:the load options,
// the parameters for options:
updateMode: currently we only support 'server'
updateUrl: set the server side url for update action based on json
view: set the view size for the sheet,for example `{width: () => 1000, height: ()=> 500}`
mode: can be read or edit, read means readonly spread sheet/edit means we can edit the spread sheet
allowSelectTextInTextBoxInReadMode: whether allow select text in TextBox control when in read mode,it is optional,the default value is false
showToolbar: means whether to show toolbar
showCheckSyntaxButton: whether to show syntax checking & spell correction button in toolbar,it is optional,the default value is false
checkSyntax: whether to perform syntax checking & spell correction for user input for text content,work with setSyntaxCheckUrl,it is optional,the default value is false
showFormulaExplain: whether to show formula explanation which applied on this cell when mouse move to the cell ,work together with setFormulaExplainUrl,it is optional,the default value is false
showFormulaTip: whether to show the existed formula which applied on this cell when mouse move to the cell,it is optional,the default value is false
showNonEditableSymbolInCell: whether to show the client side none editable symbol in cell,it is optional,the default value is false,if set to true,after click right context menu "Disable editing",the selected area which disable edit will show the symbol
showFileName: whether to show the filename
local: set the localization info for menus &toolbars ,support multiple language,the value can be:
en, zh, es, pt, de, ru, nl,
for English,Chinese,Spanish,Portuguese,German,Russian,Dutch
ar, fr,id,it,ja
for Arabic,French,Indonesian,Italian,Japanese
ko,th,tr,vi,cht
for Korean,Thai,Turkey,Vietnamese,Traditional Chinese
showContextmenu: means whether to show contextmenu on right click on a cell,it is optional,the default value is true
loadingGif: the loading gif url when loading the image/shape .it is optional,the default value is:content/img/updating.gif
for example the below code init a x_spreadsheet object.
xs = x_spreadsheet('#gridjs-demo', {
updateMode:'server',
updateUrl:'/GridJs2/UpdateCell',
mode: 'edit',
showToolbar: true,
local: 'en',
showContextmenu: true
})
- load with json data
xs.loadData(data)
// the parameters is:
data: the json data which describ the data structure for the worksheets
- set active sheet by sheetname
xs.setActiveSheetByName(sheetname)
// the parameters is:
sheetname: the sheet name
- set active sheet by id
xs.setActiveSheet(id)
// the parameters is:
sheetname: the sheet id
- set active cell
xs.setActiveCell(row,col);
// the parameters are:
row: the cell row
col: the cell column
- set active for multiple instances
xs.setActiveForMultipleInstance(isacitve);
// the parameters are:
isacitve: whether need to do edit operation at this xs instanse
// when there are more than one GridJs instances in one page, we need to call this method.
// we only support do edit operation for one instances at a page.
// for example,if we have two instances: xs1 and xs2 in one html page.
// if we need to keep edit operation in xs1,
// we shall call:
xs1.setActiveForMultipleInstance(true);
xs2.setActiveForMultipleInstance(false);
// if we need not do any edit operation for both,
// we shall call:
xs1.setActiveForMultipleInstance(false);
xs2.setActiveForMultipleInstance(false);
- set info for shape/images operation for server side action
xs.setImageInfo(imageGetActionUrl, imageAddByUploadActionUrl, imageAddByUrlActionUrl, imageCopyActionUrl, zindex, loadingGif);
// the parameters are:
imageGetActionUrl: the get image action URL in the server side controller
imageAddByUploadActionUrl: the upload image action URL in the server side controller
imageAddByUrlActionUrl: the add image from URL action URL in the server side controller
imageCopyActionUrl: the copy image action URL in the server side controller
zindex: the minimum zindex of the image in the canvas
loadingGif (optional): the loading gif url when loading the image/shape .it is optional,the default value is:content/img/updating.gif
for example:
const imageurl = "/GridJs2/imageurl";
const imageuploadurl1 = "/GridJs2/AddImage";
const imageuploadurl2 = "/GridJs2/AddImageByURL";
const imagecopyurl = "/GridJs2/CopyImage";
const basiczorder = 5678;
xs.setImageInfo(imageurl, imageuploadurl1, imageuploadurl2, imagecopyurl, basiczorder);
- set info for download operation for server side action
xs.setFileDownloadInfo(downloadActionUrl);
// the parameters are:
downloadActionUrl: the get download file action URL in the server side controller
for example:
const fileDownloadUrl = "/GridJs2/Download";
xs.setFileDownloadInfo(fileDownloadUrl);
- set info for ole object operation for server side action
xs.setOleDownloadInfo(oleActionUrl);
// the parameters are:
oleActionUrl: the ole object file action URL in the server side controller
for example:
const oleDownloadUrl = "/GridJs2/Ole";
xs.setOleDownloadInfo(oleDownloadUrl);
- set info for syntax checking & spell correction operation for server side action
xs.setSyntaxCheckUrl(checkUrl);
// the parameters are:
checkUrl: the syntax checking & spell correction operation action URL in the server side controller
for example:
const checkurl = "/GridJs2/CheckSyntax";
xs.setSyntaxCheckUrl(checkurl);
- set info for formula explanation for server side action
xs.setFormulaExplainUrl(formulaExplainUrl);
// the parameters are:
formulaExplainUrl: the formula explanation action URL in the server side controller
for example:
const formulaExplainUrl = "/GridJs2/FormulaExplain";
xs.setFormulaExplainUrl(formulaExplainUrl);
other useful apis
- Render the view
xs.reRender()
- get active sheet id
xs.getActiveSheet()
- Set Zoom level
xs.setZoomLevel(zoom)
// the parameters is:
zoom:the zoom level ,can be number ,for example 0.5 for zoom out, or 2 for zoom in
- Set FileName
xs.setFileName(name)
// the parameters is:
name:the file name with extension ,for example trip.xlsx
- Callback function for email sending feature.
xs.setEmailSendCallFunction(callback)
// the parameters is:
callback: the callback function to handle email sending, receives a mailObj parameter
callback: function(mailObj) {
// mailObj properties:
// mailObj.receiver: the email address of the receiver, e.g., 'example@gmail.com'
// mailObj.type: the format of the file to be sent, can be 'html', 'xlsx', or 'pdf'
}
- whether to enable window key event for GridJs
xs.enableKeyEvent(isenable)
// the parameters is:
isenable:whether the window key event is active for GridJs
//when has other controls in the same page, you may want to ignore the key event in GridJs
- unbind all events attached to GridJs,including window key event and window resize event.
xs.destroy()
- set visible filter for image/shape
xs.setVisibleFilter((sheet,s) =>{})
// to set a function which return true(for visible) or false(for invisible) for the visible filter with the below parameters :
sheet:the sheet instance
s:the image or shape instance
for example:
//this will make visible for image/shape in sheet with name 'Sheet3' and 'Sheet1' except for the 'Rectangle' type
xs.setVisibleFilter((sheet,s) => { if (sheet.data.name==='Sheet3'||sheet.data.name==='Sheet1') return s.type!=='Rectangle'; return false; })
//this will make visible for image/shape in sheet with name 'Sheet1'
xs.setVisibleFilter((sheet,s) => { if (sheet.data.name==='Sheet1') return true; return false; })
//this will make invisible for image/shape in all sheets
xs.setVisibleFilter((sheet,s) => { return false; })
//if all the image/shape is already loaded and you want to change the visible filter at runtime,you can call the below code to trigger a reload for image/shape
xs.reRender()
- Get the selected image/shape,if nothing select will return null
xs.sheet.selector.getObj()
- Show or hide an HTML node at a specified cell position
xs.sheet.showHtmlAtCell(isShow, html, ri, ci, deltaX, deltaY)
//the parameters are:
// - isShow: Boolean value indicating whether to show or hide the HTML content.
// - html: The HTML string to be displayed.
// - ri: Row index of the target cell.
// - ci: Column index of the target cell.
// - deltaX: (Optional) Relative X-position adjustment from the top-left corner of the cell.
// - deltaY: (Optional) Relative Y-position adjustment from the top-left corner of the cell.
// Example usage:
// Show HTML at cell A1
xs.sheet.showHtmlAtCell(true, "<span>html span</span><input length='30' id='myinput'>test</input>", 0, 0);
// Hide the HTML node
xs.sheet.showHtmlAtCell(false);
// Note: When an HTML node is shown, the default GridJS event handling is disabled to allow interaction with the HTML content.
// This means you cannot select any cells or perform edit operations until the HTML node is hidden.
- Set the selectable state for image/shape
const shape=xs.sheet.selector.getObj();
shape.setControlable(isenable)
// the parameter is:
isenable: when set to true,the image or shape can be selectable and movable/resizeable
- Get the cell object
xs.sheet.data.getCell(ri,ci)
// the parameters are:
ri:row index
ci:column index
- Get the cell style
xs.sheet.data.getCellStyle(ri,ci)
// the parameters are:
ri:row index
ci:column index
- Set the cell value
xs.sheet.data.setCellText(ri,ci,value,state)
// the parameters are:
ri:row index
ci:column index
value:the cell value
state: input | finished ,if finished ,it will do update action to servside
- Get/Set the selected cell range
xs.sheet.data.selector.range
- Set the cell value for the selected cell or cell area
xs.sheet.data.setSelectedCellText(value)
// the parameters are:
value:the value for the cell
- Set the style for the selected cell or cell area
xs.sheet.data.setSelectedCellAttr(attributename,value)
// the parameters are:
attributename:font-name | font-bold | font-italic | font-size | format|border|merge|formula |strike|textwrap |underline |align |valign |color|bgcolor|pattern
value:the value for the attribute
- Merge the selected cell area
xs.sheet.data.merge()
- Unmerge the selected cell area
xs.sheet.data.unmerge()
- Delete the selected cell
xs.sheet.data.deleteCell(type)
// the parameters are:
type:all|format all: means delete the cell and clear the style ;format means delete the cell value and keep the cell style
- Set the freeze pane
xs.sheet.data.setFreeze(ri,ci)
// the parameters are:
ri:row index
ci:column index
- Insert row or columns at the selected cell
xs.sheet.data.insert(type, n)
// the parameters are:
type: row | column
n:the row or column number
- Delete row or columns at the selected cell
xs.sheet.data.delete(type)
// the parameters are:
type: row | column
- Set the width for the column
xs.sheet.data.setColWidth(ci,width)
// the parameters are:
ci:column index
width:the width for the column
- Set the width for the columns
xs.sheet.data.setColsWidth(sci,eci,width)
// the parameters are:
sci:the start column index
eci:the end column index
width:the width for the column
- Set the width for all the columns
xs.sheet.data.setAllColsWidth(width)
// the parameters are:
width:the width for the columns
- Get the width for the column
xs.sheet.data.cols.sumWidth(min,max)
// the parameters are:
min:the start column index
max:the end column index,not include
- Set the height for the row
xs.sheet.data.setRowHeight(ri,height)
// the parameters are:
ri:row index
height:the height for the row
- Set the height for the rows
xs.sheet.data.setRowsHeight(sri,eri,height)
// the parameters are:
sri:start row index
eri:end row index
height:the height for the rows
- Set the height for all the rows
xs.sheet.data.setAllRowsHeight(height)
// the parameters are:
height:the height for the rows
- Get the height for the row
xs.sheet.data.rows.sumHeight(min,max)
// the parameters are:
min:the start row index
max:the end row index,not include
- Get/Set the display direction
xs.sheet.data.displayRight2Left
Event call back
- We can track the below events
xs.on('cell-selected', (cell, ri, ci) => {
console.log('cell selected:', cell, ', ri:', ri, ', ci:', ci);
if (ci === -1) {
console.log('ci === -1 means a row selected ',ri);
}
if (ri === -1) {
console.log('ri === -1 means a column selected',ci);
}
}).on('cells-selected', (cell, range) => {
console.log('range selected:', cell, ', rang:', range);
}).on('object-selected', (shapeOrImageObj) => {
console.log('shape or image selected id:', shapeOrImageObj.id, ', type: ', shapeOrImageObj.type);
}).on('sheet-selected', (id,name) => {
console.log('sheet selected id:', id, ', name: ',name);
}).on('sheet-loaded', (id,name) => {
console.log('sheet load finished:', id, ', name: ',name);
}).on('cell-edited', (text, ri, ci) => {
//just edit the cell
console.log('text:', text, ', ri: ', ri, ', ci:', ci);
}).on('cells-updated', (name, cells) => {
//cell value got updated
console.log('cells updated for sheet name:', name);
cells.forEach((acell, index, array) => {
console.log('acell got updated:', acell);
})
}).on('cells-deleted', (range) => {
console.log('cells deleted :', range);
}).on('rows-deleted', (ri, n) => {
console.log('rows-deleted :', ri, ",size", n);
}).on('columns-deleted', (ci, n) => {
console.log('columns-deleted :', ci, ",size", n);
}).on('rows-inserted', (ri, n) => {
console.log('rows-inserted :', ri, ",size", n);
}).on('columns-inserted', (ci, n) => {
console.log('columns-inserted :', ci, ",size", n);
});
- Pre-Check event if return false,the insert/delete operation will not go on.
xs.checkRowInsert = (ri, size) => { if (ri % 2 == 1) return true; else return false; };
xs.checkColumnInsert = (ci, size) => { if (ci % 2 == 1) return true; else return false; };
xs.checkRowDelete = (ri, size) => { if (ri % 2 == 1) return true; else return false; };
xs.checkColumnDelete = (ci, size) => { if (ci % 2 == 1) return true; else return false; };
Customization
- Set home icon and link
xs.sheet.menubar.icon.setHomeIcon(iconUrl,targetUrl)
// the parameters are:
iconUrl:the home icon URL
targetUrl:the target link URL
for example ,the below code will set the new logo and with link to google.com
xs.sheet.menubar.icon.setHomeIcon('https://forum.aspose.com/letter_avatar_proxy/v4/letter/y/3e96dc/45.png','https://www.google.com')
- Show the menu bar
xs.sheet.menubar.show()
- Hide the menu bar
xs.sheet.menubar.hide()
APIs for TextBox object
TextBox is a special kind of shape which type property is :“TextBox”, for example: the below code will show which shape is textbox
for (let shape of xs.sheet.data.shapes) {
if (shape.type === 'TextBox') {
console.log(shape.id + ' is a textbox');
}
}
- Change background color for textbox object
setBackgroundColor(color)
// the parameters are:
color: the html color value in hex string value
//for example,we assume shape 0 is a textbox object,this will set the background color to Yellow
const textbox=xs.sheet.data.shapes[0];
textbox.setBackgroundColor('#FFFF00');
- Auto change the background color and text color to get a visual active effect
setActiveEffect(boolvalue)
// the parameters are:
boolvalue: if true,will change background color and the text color of the textbox object;if false,restore to original appearence
- Hide/unhide the text content in the textbox object
hideText(boolvalue)
// the parameters are:
boolvalue: if true,will not display the text in the textbox object;if false,restore to original appearence
for detail info ,you can check the example here https://github.com/aspose-cells/Aspose.Cells-for-.NET/tree/master/Examples_GridJs