Essential JS Spreadsheet
13 May 202024 minutes to read
The Spreadsheet can be easily configured to the DOM element, such as div. you can create a Spreadsheet with a highly customizable look and feel.
Syntax
$(element).ejSpreadsheet(options)
Name | Type | Description |
---|---|---|
|
|
Settings for Spreadsheet. |
Example
<div id="Spreadsheet"></div>
<script>
// Create Spreadsheet
$('#Spreadsheet').ejSpreadsheet();
</script>
Requires
- jQuery.js
- jsrender.js
- jQuery.validate.js
- ej.globalize.js
- ej.core.js
- ej.data.js
- ej.scroller.js
- ej.ribbon.js
- ej.chart.js
- ej.listbox.js
- ej.menu.js
- ej.colorpicker.js
- ej.slider.js
- ej.excelfilter.js
- ej.treeview.js
- ej.button.js
- ej.checkbox.js
- ej.draggable.js
- ej.waitingpopup.js
- ej.radiobutton.js
- ej.autocomplete.js
- ej.dropdownlist.js
- ej.datepicker.js
- ej.dialog.js
- ej.editor.js
- ej.pager.js
- ej.ribbon.js
- ej.uploadbox.js
- ej.togglebutton.js
- ej.calculate.js
- ej.tab.js
- ej.toolbar.js
Members
activeSheetIndex number
Gets or sets an active sheet index in the Spreadsheet. By defining this value, you can specify which sheet should be active in workbook.
Default Value
- 1
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheetCount: 5,
activeSheetIndex: 3
});
</script>
allowAutoCellType boolean
Gets or sets a value that indicates whether to enable or disable auto rendering of cell type in the Spreadsheet.
Default Value
- false
NOTE
allowCellType
must betrue
while using this property.
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellType: true,
allowAutoCellType:true
});
</script>
allowAutoFill boolean
Gets or sets a value that indicates whether to enable or disable auto fill feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowAutoFill: true
});
</script>
allowAutoSum boolean
Gets or sets a value that indicates whether to enable or disable auto sum feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowAutoSum: true
});
</script>
allowCellFormatting boolean
Gets or sets a value that indicates whether to enable or disable cell format feature in the Spreadsheet. By enabling this, you can customize styles and number formats.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellFormatting : true
});
</script>
allowCellType boolean
Gets or sets a value that indicates whether to enable or disable cell type feature in the Spreadsheet.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellType : true
});
</script>
allowCharts boolean
Gets or sets a value that indicates whether to enable or disable chart feature in the Spreadsheet. By enabling this feature, you can create and customize charts in Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCharts: false
});
</script>
allowClear boolean
Gets or sets a value that indicates whether to enable or disable clear feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowClear: false
});
</script>
allowClipboard boolean
Gets or sets a value that indicates whether to enable or disable clipboard feature in the Spreadsheet. By enabling this feature, you can perform cut/copy and paste operations in Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowClipboard : true
});
</script>
allowComments boolean
Gets or sets a value that indicates whether to enable or disable comment feature in the Spreadsheet. By enabling this, you can add/delete/modify comments in Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowComments: true
});
</script>
allowConditionalFormats boolean
Gets or sets a value that indicates whether to enable or disable Conditional Format feature in the Spreadsheet. By enabling this, you can apply formatting to the selected range of cells based on the provided conditions (Greater than, Less than, Equal, Between, Contains, etc.).
NOTE
allowConditionalFormats
must betrue
while using conditional formatting.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellFormatting : true,
allowConditionalFormats : true
});
</script>
allowDataValidation boolean
Gets or sets a value that indicates whether to enable or disable data validation feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowDataValidation: true
});
</script>
allowDelete boolean
Gets or sets a value that indicates whether to enable or disable the delete action in the Spreadsheet. By enabling this feature, you can delete existing rows, columns, cells and sheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowDelete: true
});
</script>
allowDragAndDrop boolean
Gets or sets a value that indicates whether to enable or disable drag and drop feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowDragAndDrop: true
});
</script>
allowEditing boolean
Gets or sets a value that indicates whether to enable or disable the edit action in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowEditing: true
});
</script>
allowFiltering boolean
Gets or sets a value that indicates whether to enable or disable filtering feature in the Spreadsheet. Filtering can be used to limit the data displayed using required criteria.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowFiltering: true
});
</script>
allowFormatAsTable boolean
Gets or sets a value that indicates whether to enable or disable table feature in the Spreadsheet. By enabling this, you can render table in selected range.
NOTE
allowCellFormatting
andallowFiltering
must betrue
while using format as table.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellFormatting :true,
allowFiltering:true,
allowFormatAsTable: true
});
</script>
allowFormatPainter boolean
Get or sets a value that indicates whether to enable or disable format painter feature in the Spreadsheet. By enabling this feature, you can copy the format from the selected range and apply it to another range.
NOTE
allowCellFormatting
must betrue
while enable this feature.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellFormatting: true,
allowFormatPainter: true
});
</script>
allowFormulaBar boolean
Gets or sets a value that indicates whether to enable or disable formula bar in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowFormulaBar: false
});
</script>
allowFreezing boolean
Gets or sets a value that indicates whether to enable or disable freeze pane support in Spreadsheet. By enabling this feature, you can use freeze top row, freeze first column and freeze panes options.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowFreezing : false
});
</script>
allowHyperlink boolean
Gets or sets a value that indicates whether to enable or disable hyperlink feature in the Spreadsheet. By enabling this feature, you can add hyperlink which is used to easily navigate to the cell reference from one sheet to another or a web page.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowHyperlink: true
});
</script>
allowImport boolean
Gets or sets a value that indicates whether to enable or disable import feature in the Spreadsheet. By enabling this feature, you can open existing Spreadsheet documents.
NOTE
Need to specify
importMapper
while enabling this feature.Import feature supports XLS, XLSX file formats.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowImport: true,
importSettings:
{
importMapper: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/Import"
}
});
</script>
allowInsert boolean
Gets or sets a value that indicates whether to enable or disable the insert action in the Spreadsheet. By enabling this feature, you can insert new rows, columns, cells and sheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowInsert: true
});
</script>
allowKeyboardNavigation boolean
Gets or sets a value that indicates whether to enable or disable keyboard navigation feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowKeyboardNavigation: true
});
</script>
allowLockCell boolean
Gets or sets a value that indicates whether to enable or disable lock cell feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowLockCell: true
});
</script>
allowMerging boolean
Gets or sets a value that indicates whether to enable or disable merge feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowMerging: true
});
</script>
allowOverflow boolean
Gets or sets a value that indicates whether to enable or disable overflow feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowOverflow: false
});
</script>
allowResizing boolean
Gets or sets a value that indicates whether to enable or disable resizing feature in the Spreadsheet. By enabling this feature, you can change the column width and row height by dragging its header boundaries.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowResizing: true
});
</script>
allowSearching boolean
Gets or sets a value that indicates whether to enable or disable find and replace feature in the Spreadsheet. By enabling this, you can easily find and replace a specific value in the sheet or workbook. By using goto behavior, you can select and highlight all cells that contains specific data or data types.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSearching : true
});
</script>
allowSelection boolean
Gets or sets a value that indicates whether to enable or disable selection in the Spreadsheet. By enabling this feature, selected items will be highlighted.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSelection: true
});
</script>
allowSorting boolean
Gets or sets a value that indicates whether to enable the sorting feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSorting: true
});
</script>
allowSparkline boolean
Gets or sets a value that indicates whether to enable the sparkline feature in the Spreadsheet.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSparkline: true
});
</script>
allowUndoRedo boolean
Gets or sets a value that indicates whether to enable or disable undo and redo feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowUndoRedo: true
});
</script>
allowWrap boolean
Gets or sets a value that indicates whether to enable or disable wrap text feature in the Spreadsheet. By enabling this, cell content can wrap to the next line, if the cell content exceeds the boundary of the cell.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowWrap: true
});
</script>
apWidth number
Gets or sets a value that indicates to define the width of the activation panel in Spreadsheet.
Default Value
- 300
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
apWidth: 100
});
</script>
autoFillSettings Object
Gets or sets an object that indicates to customize the auto fill behavior in the Spreadsheet.
autoFillSettings.fillType enum
This property is used to set fillType unit in Spreadsheet. It has five types which are CopyCells, FillSeries, FillFormattingOnly, FillWithoutFormatting and FlashFill.
NOTE
allowAutoFill
must betrue
while using this property.
Name | Description |
---|---|
CopyCells | Specifies the CopyCells property in AutoFillOptions. |
FillSeries | Specifies the FillSeries property in AutoFillOptions. |
FillFormattingOnly | Specifies the FillFormattingOnly property in AutoFillOptions. |
FillWithoutFormatting | Specifies the FillWithoutFormatting property in AutoFillOptions. |
FlashFill | Specifies the FlashFill property in AutoFillOptions. |
Default Value
- ej.Spreadsheet.AutoFillOptions.FillSeries
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowAutoFill: true,
autoFillSettings:{
fillType: ej.Spreadsheet.AutoFillOptions.CopyCells
}
});
</script>
autoFillSettings.showFillOptions boolean
Gets or sets a value that indicates to enable or disable auto fill options in the Spreadsheet.
NOTE
allowAutoFill
must betrue
while enabling this property.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowAutoFill: true,
autoFillSettings:{
showFillOptions: true
}
});
</script>
chartSettings Object
Gets or sets an object that indicates to customize the chart behavior in the Spreadsheet.
chartSettings.height number
Gets or sets a value that defines the chart height in Spreadsheet.
NOTE
allowCharts
must betrue
while using this property.
Default Value
- 220
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCharts: true,
chartSettings: {
height : 300
}
});
</script>
chartSettings.width number
Gets or sets a value that defines the chart width in the Spreadsheet.
NOTE
allowCharts
must betrue
while using this property.
Default Value
- 440
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCharts: true,
chartSettings: {
width : 500
}
});
</script>
columnCount number
Gets or sets a value that defines the number of columns displayed in the sheet.
Default Value
- 21
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
columnCount: 30
});
</script>
columnWidth number
Gets or sets a value that indicates to define the common width for each column in the Spreadsheet.
Default Value
- 64
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
columnWidth: 100
});
</script>
cssClass string
Gets or sets a value to add root CSS class for customizing Spreadsheet skins.
Default Value
- ””
Example
<div id="Spreadsheet"></div>
<script>
// Set the CSS class during initialization.
$('#Spreadsheet').ejSpreadsheet({
cssClass: "gradient-lime"
});
</script>
customFormulas Array
Gets or sets a value that indicates custom formulas in Spreadsheet.
Default Value
- []
Example
<div id="Spreadsheet"></div>
<script>
// Set the custom formula
$('#Spreadsheet').ejSpreadsheet({
customFormulas: [{
formulaName:"CUSTOMTOTAL",
functionName:"customTotal"
}]
});
function customTotal(args){}//args-It uses the value given by the user while using custom formula in Spreadsheet.
</script>
enableContextMenu boolean
Gets or sets a value that indicates whether to enable or disable context menu in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
enableContextMenu: true
});
</script>
enablePivotTable boolean
Gets or sets a value that indicates whether to enable or disable pivot table in the Spreadsheet.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
enablePivotTable: false
});
</script>
enableTouch boolean
Gets or sets a value that indicates whether to enable or disable touch support in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
enableTouch: true
});
</script>
exportSettings Object
Gets or sets an object that indicates to customize the exporting behavior in Spreadsheet.
exportSettings.allowExporting boolean
Gets or sets a value that indicates whether to enable or disable save feature in Spreadsheet. By enabling this feature, you can save existing Spreadsheet.
NOTE
User must specify
excelUrl
orpdfUrl
orcsvUrl
while enabling this feature
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
exportSettings:{
allowExporting: true,
csvUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/CsvExport", // It is used to set the url of the csv export.
excelUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/ExcelExport", //It is used to set the url of the excel export.
pdfUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/PdfExport", //It is used to set the url of the pdf export.
}
});
</script>
exportSettings.enableFormulaCalculation boolean
Gets or sets a value that indicates whether to enable or disable formula calculation in Spreadsheet. By enabling this feature, formula calculated while export the Spreadsheet in protected mode.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
exportSettings:{
enableFormulaCalculation: true,
csvUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/CsvExport", // It is used to set the url of the csv export.
excelUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/ExcelExport", //It is used to set the url of the excel export.
pdfUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/PdfExport", //It is used to set the url of the pdf export.
}
});
</script>
exportSettings.csvUrl string
Gets or sets a value that indicates to define csvUrl for export to CSV format.
NOTE
User must specify
allowExporting
true
while using this property.
Default Value
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
exportSettings:{
allowExporting: true,
csvUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/CsvExport", // It is used to set the url of the csv export.
}
});
</script>
exportSettings.excelUrl string
Gets or sets a value that indicates to define excelUrl for export to excel format.
NOTE
User must specify
allowExporting
true
while using this property.
Default Value
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
exportSettings:{
allowExporting: true,
excelUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/ExcelExport", //It is used to set the url of the excel export.
}
});
</script>
exportSettings.password string
Gets or sets a value that indicates to define password while export to excel format.
NOTE
User must specify
allowExporting
true
while using this property.
Default Value
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
exportSettings:{
allowExporting: true,
excelUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/ExcelExport", //It is used to set the url of the excel export
password :"Spreadsheet"
}
});
</script>
exportSettings.pdfUrl string
Gets or sets a value that indicates to define pdfUrl for export to PDF format.
NOTE
User must specify
allowExporting
true
while using this property.
Default Value
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
exportSettings:{
allowExporting: true,
pdfUrl: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/PdfExport", //It is used to set the url of the pdf export.
}
});
</script>
formatSettings Object
Gets or sets an object that indicates to customize the format behavior in the Spreadsheet.
formatSettings.allowCellBorder boolean
Gets or sets a value that indicates whether to enable or disable cell border feature in the Spreadsheet.
NOTE
allowCellFormatting
must betrue
while using this property.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellFormatting: true,
formatSettings:{
allowCellBorder: true
}
});
</script>
formatSettings.allowDecimalPlaces boolean
Gets or sets a value that indicates whether to enable or disable decimal places in the Spreadsheet.
NOTE
allowCellFormatting
must betrue
while using this property.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellFormatting: true,
formatSettings:{
allowDecimalPlaces: true
}
});
</script>
formatSettings.allowFontFamily boolean
Gets or sets a value that indicates whether to enable or disable font family feature in Spreadsheet.
NOTE
allowCellFormatting
must betrue
while using this property.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowCellFormatting : true,
formatSettings:{
allowFontFamily: true
}
});
</script>
importSettings Object
Gets or sets an object that indicates to customize the import behavior in the Spreadsheet.
importSettings.importMapper string
Sets import mapper to perform import feature in Spreadsheet.
NOTE
allowImport
must betrue
while using this property.
Default Value
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowImport: true,
importSettings:{
importMapper: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/Import"
}
});
</script>
importSettings.importOnLoad boolean
Gets or sets a value that indicates whether to enable or disable import while initial loading.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
importSettings: {
importOnLoad: true
}
});
</script>
importSettings.importUrl string
Sets import URL to access the online files in the Spreadsheet.
NOTE
allowImport
must betrue
while using this property.
Default Value
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowImport: true,
importSettings:{
importUrl: "http://mvc.syncfusion.com/Spreadsheet/Spreadsheet.xlsx", //It is used to access the online files in Spreadsheet.
}
});
</script>
importSettings.password string
Gets or sets a value that indicates to define password while importing in the Spreadsheet.
NOTE
allowImport
must betrue
while using this property.
Default Value
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowImport: true,
importSettings:{
importMapper: "http://js.syncfusion.com/demos/ejservices/api/Spreadsheet/Import",
password: "Spreadsheet" //It opens the excel file using this password.
}
});
</script>
isImport boolean
Gets a value that indicates whether importing or not while loading the sheets in Spreadsheet.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
loadComplete: "loadComplete"
});
function loadComplete(args) {
if (!this.model.isImport)
this.setWidthToColumns([140, 128, 105, 100, 100, 110, 120, 120, 100]);
}
</script>
isReadOnly boolean
Gets or sets a value that indicates whether to enable or disable readonly support in the Spreadsheet.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
isReadOnly: true
});
</script>
locale string
Gets or sets a value that indicates whether to customizing the user interface (UI) as locale-specific in order to display regional data (i.e.) in a language and culture specific to a particular country or region.
Default Value
- “en-US”
Example
<div id="Spreadsheet"></div>
<script>
ej.Spreadsheet.locale["es-ES"] = {
Sheet: "Hoja"
};
$('#Spreadsheet').ejSpreadsheet({
locale: "es-ES"
});
</script>
nameManager Array
Gets or sets a value that indicates name manager in Spreadsheet.
nameManager.name string
Specifies the name for the cell or a range.
Default Value
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
nameManager:[{
name: "inputRange",
refersto: "=Sheet1!$A$1:$A$2"
}]
});
</script>
nameManager.refersto string
Specifies the address for the cell or a range.
Default Value
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
nameManager:[{
name: "inputRange",
refersto: "=Sheet1!$A$1:$A$2"
}]
});
</script>
pictureSettings Object
Gets or sets an object that indicates to customize the picture behavior in the Spreadsheet.
pictureSettings.allowPictures boolean
Gets or sets a value that indicates whether to enable or disable picture feature in Spreadsheet. By enabling this, you can add pictures in Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
pictureSettings:{
allowPictures: true
}
});
</script>
pictureSettings.height number
Gets or sets a value that indicates to define height to picture in the Spreadsheet.
NOTE
allowPictures
must betrue
while using this property.
Default Value
- 220
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
pictureSettings:{
allowPictures: true,
height: 300
}
});
</script>
pictureSettings.width number
Gets or sets a value that indicates to define width to picture in the Spreadsheet.
NOTE
allowPictures
must betrue
while using this property.
Default Value
- 440
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
pictureSettings:{
allowPictures: true,
width: 500
}
});
</script>
printSettings Object
Gets or sets an object that indicates to customize the print option in Spreadsheet.
printSettings.allowPageSetup boolean
Gets or sets a value that indicates whether to enable or disable page setup support for printing in Spreadsheet.
NOTE
allowPrinting
must betrue
while enabling this property.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowPrinting: true,
printSettings:{
allowPageSetup: true
}
});
</script>
printSettings.allowPageSize boolean
Gets or sets a value that indicates whether to enable or disable page size support for printing in Spreadsheet.
NOTE
allowPrinting
must betrue
while enabling this property.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowPrinting: true,
printSettings:{
allowPageSize: true
}
});
</script>
printSettings.allowPrinting boolean
Gets or sets a value that indicates whether to enable or disable print feature in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
printSettings:{
allowPrinting: true
}
});
</script>
ribbonSettings Object
Gets or sets an object that indicates to customize the ribbon settings in Spreadsheet.
ribbonSettings.applicationTab Object
Gets or sets an object that indicates application tab settings in Spreadsheet.
NOTE
showRibbon
must betrue
while using this property.
ribbonSettings.applicationTab.type enum
Gets or sets a value that indicates to set application tab type in Spreadsheet. It has two types, Menu and Backstage.
Name | Description |
---|---|
Menu | To enable menu type in ribbon. |
Backstage | To enable back stage type in ribbon. |
Default Value
- ej.Ribbon.ApplicationTabType.Backstage
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
ribbonSettings: {
applicationTab: {
type: ej.Ribbon.ApplicationTabType.Menu
}
}
});
</script>
ribbonSettings.applicationTab.menuSettings Object
Gets or sets an object that indicates menu settings for application tab in Spreadsheet.
NOTE
ApplicationTabType
should beMenu
while using this property.
ribbonSettings.applicationTab.menuSettings.isAppend boolean
Gets or sets a value that indicates whether to enable or disable isAppend property in ribbon settings.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
ribbonSettings:{
applicationTab:{
type: ej.Ribbon.ApplicationTabType.Menu
menuSettings:{
isAppend:true
}
}
}
});
</script>
ribbonSettings.applicationTab.menuSettings.dataSource Array
Specifies the data source to append in application tab.
Default Value
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
ribbonSettings:{
applicationTab:{
type: ej.Ribbon.ApplicationTabType.Menu,
menuSettings:{
isAppend:true,
dataSource:[{ id: "File", text: "File" }]
}
}
}
});
</script>
rowCount number
Gets or sets a value that indicates whether to define the number of rows to be displayed in the sheet.
Default Value
- 20
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
rowCount: 30
});
</script>
rowHeight number
Gets or sets a value that indicates to define the common height for each row in the sheet.
Default Value
- 20
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
rowHeight: 30
});
</script>
scrollSettings Object
Gets or sets an object that indicates to customize the scroll options in the Spreadsheet.
scrollSettings.allowScrolling boolean
Gets or sets a value that indicates whether to enable or disable scrolling in Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
scrollSettings:{
allowScrolling: true,
}
});
</script>
scrollSettings.allowSheetOnDemand boolean
Gets or sets a value that indicates whether to enable or disable sheet on demand. By enabling this, it render only the active sheet element while paging remaining sheets are created one by one.
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
scrollSettings:{
allowScrolling: true,
allowSheetOnDemand: true
}
});
</script>
scrollSettings.allowVirtualScrolling boolean
Gets or sets a value that indicates whether to enable or disable virtual scrolling feature in the Spreadsheet.
NOTE
allowScrolling
must betrue
while enabling this property.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
scrollSettings:{
allowScrolling: true,
allowVirtualScrolling: true
}
});
</script>
scrollSettings.height number|string
Gets or sets the value that indicates to define the height of spreadsheet.
Default Value
- 100%
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
scrollSettings:{
allowScrolling: true,
height: 600
}
});
</script>
scrollSettings.isResponsive boolean
Gets or sets the value that indicates whether to enable or disable responsive mode in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
scrollSettings:{
allowScrolling: true,
width: "100%",
height: "100%",
isResponsive: true
}
});
</script>
scrollSettings.scrollMode enum
Gets or sets a value that indicates to set scroll mode in Spreadsheet. It has two scroll modes, Normal and Infinite.
NOTE
allowScrolling
must betrue
while enabling this property.
Name | Description |
---|---|
Infinite | To enable Infinite scroll mode for Spreadsheet. |
Normal | To enable Normal scroll mode for Spreadsheet. |
Default Value
- ej.Spreadsheet.scrollMode.Infinite
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
scrollSettings:{
allowScrolling: true,
height: "100%",
width: "100%",
scrollmode: ej.Spreadsheet.scrollMode.Infinite,
}
});
</script>
scrollSettings.width number|string
Gets or sets the value that indicates to define the height of the spreadsheet.
Default Value
- 100%
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
scrollSettings:{
allowScrolling: true,
width: 1300,
scrollmode: ej.Spreadsheet.scrollMode.Infinite
}
});
</script>
selectionSettings Object
Gets or sets an object that indicates to customize the selection options in the Spreadsheet.
selectionSettings.activeCell string
Gets or sets a value that indicates to define active cell in spreadsheet.
NOTE
allowSelection
must betrue
while using this property.
Default Value
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSelection: true,
selectionSettings:{
activeCell: "A1"
}
});
</script>
selectionSettings.animationTime number
Gets or sets a value that indicates to define animation time while selection in the Spreadsheet.
NOTE
allowSelection
must betrue
while using this property.
Default Value
- 0.001
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSelection: true,
selectionSettings:{
enableAnimation: true,
animationTime: 0.002
}
});
</script>
selectionSettings.enableAnimation boolean
Gets or sets a value that indicates to enable or disable animation while selection.
NOTE
allowSelection
must betrue
while using this property
Default Value
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSelection: true,
selectionSettings:{
enableAnimation: true
}
});
</script>
selectionSettings.selectionType enum
Gets or sets a value that indicates to set selection type in Spreadsheet. It has three types which are Column, Row and Default.
NOTE
allowSelection
must betrue
while using this property
Name | Description |
---|---|
Column | To select only Column in Spreadsheet. |
Row | To select only Row in Spreadsheet. |
Default | To select both Column/Row in Spreadsheet. |
Default Value
- ej.Spreadsheet.SelectionType.Default
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSelection: true,
selectionSettings:{
selectionType: ej.Spreadsheet.SelectionType.Row,
animationTime: 0.001,
enableAnimation: true
}
});
</script>
selectionSettings.selectionUnit enum
Gets or sets a value that indicates to set selection unit in Spreadsheet. It has three types which are Single, Range and MultiRange.
NOTE
allowSelection
must betrue
while using this property
Name | Description |
---|---|
Single | To enable Single selection in Spreadsheet |
Range | To enable Range selection in Spreadsheet |
MultiRange | To enable MultiRange selection in Spreadsheet |
Default Value
- ej.Spreadsheet.SelectionUnit.MultiRange
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
allowSelection: true,
selectionSettings:{
selectionUnit: ej.Spreadsheet.SelectionUnit.Single
}
});
</script>
sheetCount number
Gets or sets a value that indicates to define the number of sheets to be created at the initial load.
Default Value
- 1
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheetCount: 5
});
</script>
sheets Array
Gets or sets an object that indicates to customize the sheet behavior in Spreadsheet.
sheets.border Array
Specifies the border for the cell in the Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
border: [{ type: ej.Spreadsheet.BorderType.AllBorder, color: "#456534", range: "C6:D9" }]
}]
});
</script>
sheets.border.type enum
Specifies border type in the Spreadsheet.
Name | Description |
---|---|
Top | To apply top border for the given range of cell. |
Left | To apply left border for the given range of cell. |
Right | To apply right border for the given range of cell. |
Bottom | To apply bottom border for the given range of cell. |
OutSide | To apply outside border for the given range of cell. |
AllBorder | To apply all border for the given range of cell. |
ThickBox | To apply thick box border for the given range of cell. |
ThickBottom | To apply thick bottom border for the given range of cell. |
TopandBottom | To apply top and bottom border for the given range of cell. |
TopandThickBottom | To apply top and thick bottom border for the given range of cell. |
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
border: [{ type: ej.Spreadsheet.BorderType.AllBorder, color: "#456534", range: "C6:D9" }]
}]
});
</script>
sheets.border.color string
Specifies border color for range of cells in Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
border: [{ type: ej.Spreadsheet.BorderType.AllBorder, color: "#456534", range: "C6:D9" }]
}]
});
</script>
sheets.border.range string
To apply border for the specified range of cell.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
border: [{ type: ej.Spreadsheet.BorderType.AllBorder, color: "#456534", range: "C6:D9" }]
}]
});
</script>
sheets.cellTypes Array
Specifies the cell types for a cell or range in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
cellTypes: [{ range: 'F5', settings: { type: ej.Spreadsheet.CustomCellType.Button, 'background-color': 'yellow', color: 'black', text: 'BUTTON' } }]
}]
});
</script>
sheets.cFormatRule Array
Specifies the conditional formatting for the range of cell in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
cFormatRule: [{ action: ej.Spreadsheet.CFormatRule.LessThan, inputs: ["30"], color: ej.Spreadsheet.CFormatHighlightColor.RedFillwithDarkRedText, range: "A1:E1" }],
rows:[
{
cells: [
{ value: "20"},
{ value: "30"},
{ value: "15"},
{ value: "40"},
{ value: "50"}
]
}
]
}]
});
</script>
sheets.cFormatRule.action enum
Specifies the conditions to apply for the range of cells in Spreadsheet.
Name | Description |
---|---|
GreaterThan | To identify greater than values in the given range of cells. |
LessThan | To identify less than values in the given range of cells. |
Between | To identify in between values in the given range of cells. |
EqualTo | To identify the equal values in the given range of cells. |
TextContains | To identify the specified text in the range of cells. |
DateOccurs | To identify the specified date in the range of cells. |
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
cFormatRule: [{ action: ej.Spreadsheet.CFormatRule.LessThan, inputs: ["30"], color: ej.Spreadsheet.CFormatHighlightColor.RedFillwithDarkRedText, range: "A1:E1" }],
rows:[
{
cells: [
{ value: "20"},
{ value: "30"},
{ value: "15"},
{ value: "40"},
{ value: "50"}
]
}
]
}]
});
</script>
sheets.cFormatRule.color enum
Specifies the color to apply for the range of cell while conditional formatting.
Name | Description |
---|---|
RedFillwithDarkRedText | Highlights red with dark red text color. |
YellowFillwithDarkYellowText | Highlights yellow with dark yellow text color. |
GreenFillwithDarkGreenText | Highlights green with dark green text color. |
RedFill | Highlights with red fill. |
RedText | Highlights with red text. |
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
cFormatRule: [{ action: ej.Spreadsheet.CFormatRule.LessThan, inputs: ["30"], color: ej.Spreadsheet.CFormatHighlightColor.RedFillwithDarkRedText, range: "A1:E1" }],
rows:[
{
cells: [
{ value: "20"},
{ value: "30"},
{ value: "15"},
{ value: "40"},
{ value: "50"}
]
}
]
}]
});
</script>
sheets.cFormatRule.inputs Array
Specifies the inputs for conditional formatting in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
cFormatRule: [{ action: ej.Spreadsheet.CFormatRule.LessThan, inputs: ["30"], color: ej.Spreadsheet.CFormatHighlightColor.RedFillwithDarkRedText, range: "A1:E1" }],
rows:[
{
cells: [
{ value: "20"},
{ value: "30"},
{ value: "15"},
{ value: "40"},
{ value: "50"}
]
}
]
}]
});
</script>
sheets.cFormatRule.range string
Specifies the range for conditional formatting in Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
cFormatRule: [{ action: ej.Spreadsheet.CFormatRule.LessThan, inputs: ["30"], color: ej.Spreadsheet.CFormatHighlightColor.RedFillwithDarkRedText, range: "A1:E1" }],
rows:[
{
cells: [
{ value: "20"},
{ value: "30"},
{ value: "15"},
{ value: "40"},
{ value: "50"}
]
}
]
}]
});
</script>
sheets.colCount number
Gets or sets a value that indicates to define column count in the Spreadsheet.
Default Value:
- 21
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rangeSettings: [{dataSource: window.defaultData}],
colCount: 25
}]
});
</script>
sheets.columnWidth number
Gets or sets a value that indicates to define column width in the Spreadsheet.
Default Value:
- 64
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
columnWidth: 100
}]
});
</script>
sheets.dataSource Object
Gets or sets the data to render the Spreadsheet.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
// the datasource "window.defaultData" is referred from 'http://js.syncfusion.com/demos/web/scripts/xljsondata.js'
dataSource: window.defaultData
}]
});
</script>
sheets.fieldAsColumnHeader boolean
Gets or sets a value that indicates whether to enable or disable field as column header in the Spreadsheet.
Default Value:
- false
Example
<div id="Spreadsheet"></div>
<script>
var dataManager = ej.DataManager("http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/");
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
dataSource: dataManager,
query: ej.Query().take(50).select(["OrderID", "CustomerID", "EmployeeID", "ShipName", "ShipAddress", "ShipCity", "ShipCountry"]),
fieldAsColumnHeader: true,
primaryKey: "OrderID"
}]
});
</script>
sheets.frozenRows number
Gets or sets a value to freeze rows in the Spreadsheet.
Default Value:
- 0
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
frozenRows: 3
}]
});
</script>
sheets.frozenColumns number
Gets or sets a value to freeze columns in the Spreadsheet.
Default Value:
- 0
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
frozenColumns: 3
}]
});
</script>
sheets.headerStyles Object
Specifies the header styles for the headers in datasource range.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
dataSource: window.defaultData, showHeader: true, headerStyles: { "font-weight": "bold", "vertical-align": "middle", "text-align": "center", "background-color": "#559ad9", "color": "#FFFFFF" }}]
});
</script>
sheets.hideColumns Array
To hide the specified columns in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
hideColumns: [3, 4]
}]
});
</script>
sheets.hideRows Array
To hide the specified rows in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
hideRows: [2, 3]
}]
});
</script>
sheets.mergeCells Array
To merge specified ranges in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
mergeCells:["A1:A2","B2:C2"]
}]
});
</script>
sheets.primaryKey string
Specifies the primary key for the datasource in Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
var dataManager = ej.DataManager("http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/");
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
dataSource: dataManager,
query: ej.Query().take(50).select(["OrderID", "CustomerID", "EmployeeID", "ShipName", "ShipAddress", "ShipCity", "ShipCountry"]),
primaryKey: "OrderID"
}]
});
</script>
sheets.query Object
Specifies the query for the dataSource
in Spreadsheet.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
var dataManager = ej.DataManager("http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/");
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
dataSource: dataManager,
query: ej.Query().take(50).select(["OrderID", "CustomerID", "EmployeeID", "ShipName", "ShipAddress", "ShipCity", "ShipCountry"]),
primaryKey: "OrderID"
}]
});
</script>
sheets.rangeSettings Array
Specifies single range or multiple range settings for a sheet in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
{rangeSettings: [{dataSource: window.defaultData, showHeader: true, startCell: "A1"}]},
{rangeSettings: [{dataSource: window.personList, showHeader: true, startCell: "D1"}]}
}]
});
</script>
sheets.rangeSettings.dataSource Object
Gets or sets the data to render the Spreadsheet.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rangeSettings: [{dataSource: window.defaultData, showHeader: true}]}]
});
</script>
sheets.rangeSettings.headerStyles Object
Specifies the header styles for the headers in datasource range.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rangeSettings: [{dataSource: window.defaultData, showHeader: true, headerStyles: { "font-weight": "bold", "vertical-align": "middle", "text-align": "center", "background-color": "#559ad9", "color": "#FFFFFF" }}]
}]
});
</script>
sheets.rangeSettings.primaryKey string
Specifies the primary key for the datasource in Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
var dataManager = ej.DataManager("http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/");
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rangeSettings:[{
dataSource: dataManager,
query: ej.Query().take(50).select(["OrderID", "CustomerID", "EmployeeID", "ShipName", "ShipAddress", "ShipCity", "ShipCountry"]),
primaryKey: "OrderID"
}]
}]
});
</script>
sheets.rangeSettings.query Object
Specifies the query for the datasource in Spreadsheet.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
var dataManager = ej.DataManager("http://mvc.syncfusion.com/Services/Northwnd.svc/Orders/");
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rangeSettings:[{
dataSource: dataManager,
query: ej.Query().take(50).select(["OrderID", "CustomerID", "EmployeeID", "ShipName", "ShipAddress", "ShipCity", "ShipCountry"]),
primaryKey: "OrderID"
}]
}]
});
</script>
sheets.rangeSettings.showHeader boolean
Gets or sets a value that indicates whether to enable or disable the datasource header in Spreadsheet.
Default Value:
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rangeSettings: [{dataSource: window.defaultData, showHeader: true}]
}]
});
</script>
sheets.rangeSettings.startCell string
Specifies the start cell for the datasource range in Spreadsheet.
Default Value:
- “A1”
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rangeSettings: [{ dataSource: window.defaultData, startCell: "B1" }]
}]
});
</script>
sheets.rowCount number
Gets or sets a value that indicates to define row count in the Spreadsheet.
Default Value:
- 20
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rangeSettings: [{dataSource: window.defaultData}],
rowCount: 30
}]
});
</script>
sheets.rows Array
Specifies the rows for a sheet in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
height:30,
cells:[
{ value: "Item Name", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }},
{ value: "Quantity", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }},
]
}
]
}],
});
</script>
sheets.rows.height number
Gets or sets the height of a row in Spreadsheet.
Default Value:
- 20
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
height:30,
cells: [
{ value: "Item Name", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }},
{ value: "Quantity", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
]
}
]
}],
});
</script>
sheets.rows.cells Array
Specifies the cells of a row in Spreadsheet.
Default Value:
- []
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [
{
cells: [
{ value: "Item Name", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } },
{ value: "Quantity", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" } }
]
}
]
}]
});
</script>
sheets.rows.cells.comment Object
Specifies the comment for a cell in Spreadsheet.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [
{
cells: [
{ value: "Item Name", comment: { value: "Name of the item" } }
]
}
]
}]
});
</script>
sheets.rows.cells.comment.isVisible boolean
Get or sets the value that indicates whether to show or hide comments in Spreadsheet.
Default Value:
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "Item Name",comment: { value: "Name of the item" , isVisible:true}}
]
}
]
}]
});
</script>
sheets.rows.cells.comment.value string
Specifies the value for the comment in Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "Item Name",comment: { value: "Name of the item" , isVisible:true}}
]
}
]
}]
});
</script>
sheets.rows.cells.format Object
Specifies the format of a cell in Spreadsheet.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "20", format: { type: "currency" } }
]
}
]
}]
});
</script>
sheets.rows.cells.format.decimalPlaces number
Specifies the number of decimal places for the given input.
Default Value:
- 2
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [
{ cells: [{ value: "200", format: { type: "currency", decimalPlaces: 3 } }] }
]
}]
});
</script>
sheets.rows.cells.format.formatStr string
Specifies the string format for the given input.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [
{ cells: [{ value: "20000", format: { type: "percentage", formatStr: "{0:P3}" } }] }
]
}]
});
</script>
sheets.rows.cells.format.thousandSeparator boolean
Specifies the thousand separator for the given input.
Default Value:
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [
{ cells: [{ value: "200000", format: { type: "number", thousandSeparator: true } }] }
]
}]
});
</script>
sheets.rows.cells.format.type string
Specifies the type of the format in Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "20", format: { type: "currency" } }
]
}
]
}]
});
</script>
sheets.rows.cells.hyperlink Object
Specifies the hyperlink for a cell in Spreadsheet.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "AliExpress", hyperlink: { webAddr: "www.aliexpress.com" } }
]
}
]
}]
});
</script>
sheets.rows.cells.hyperlink.webAddr string
Specifies the web address for the hyperlink of a cell.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "AliExpress", hyperlink: { webAddr: "www.aliexpress.com" } }
]
}
]
}]
});
</script>
sheets.rows.cells.hyperlink.cellAddr string
Specifies the cell address for the hyperlink of a cell.
Default Value:
- ””
Example
sheets.rows.cells.hyperlink.sheetIndex number
Specifies the sheet index to which the cell is referred.
NOTE
User must give
cellAddr
to use this property.
Default Value:
- 1
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "AliExpress", hyperlink: { cellAddr: "B2" , sheetIndex: 2} }
]
}
]
}]
});
</script>
sheets.rows.cells.index number
Specifies the index of a cell in Spreadsheet.
Default Value:
- 0
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ index: 1, value: "Item Name", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }},
{ value: "Quantity", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }}
]
}
]
}]
});
</script>
sheets.rows.cells.isLocked boolean
Specifies whether to lock or unlock a particular cell.
Default Value:
- false
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [{
cells: [{ value: "Item Name", isLocked: true }]
}]
}]
});
</script>
sheets.rows.cells.style Object
Specifies the styles of a cell in Spreadsheet.
Default Value:
- null
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "Item Name",style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }},
{ value: "Quantity",style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }}
]
}
]
}]
});
</script>
sheets.rows.cells.style.backgroundColor string
Specifies the background color of a cell in the Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [
{
cells: [
{ value: "Item Name", style: { "background-color": "#428bca" } }
]
}
]
}]
});
</script>
sheets.rows.cells.style.color string
Specifies the font color of a cell in the Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [
{
cells: [
{ value: "Item Name", style: { "color": "#428bca" } }
]
}
]
}]
});
</script>
sheets.rows.cells.style.fontWeight string
Specifies the font weight of a cell in the Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets: [{
rows: [
{
cells: [
{ value: "Item Name", style: { "font-weight": "bold" } }
]
}
]
}]
});
</script>
sheets.rows.cells.value string
Specifies the value for a cell in Spreadsheet.
Default Value:
- ””
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
cells: [
{ value: "Item Name"},
{ value: "Quantity"}
]
}
]
}]
});
</script>
sheets.rows.index number
Gets or sets the index of a row in Spreadsheet.
Default Value:
- 0
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
rows:[
{
index:1,
height:30,
cells: [
{ value: "Item Name", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }},
{ value: "Quantity", style: { "font-weight": "bold", "color": "#FFFFFF", "background-color": "#428bca" }},
]
}
]
}],
});
</script>
sheets.showGridlines boolean
Gets or sets a value that indicates whether to show or hide grid lines in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
showGridlines: true
}]
});
</script>
sheets.showHeader boolean
Gets or sets a value that indicates whether to enable or disable the datasource header in Spreadsheet.
Default Value:
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
dataSource: window.defaultData, showHeader: true
}]
});
</script>
sheets.showHeadings boolean
Gets or sets a value that indicates whether to show or hide headings in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
showHeadings: true
}]
});
</script>
sheets.sheetName string
Specifies the name for sheet in the Spreadsheet.
Default Value
- string
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
sheetName: "Sheet Name"
}]
});
</script>
sheets.startCell string
Specifies the start cell for the datasource range in Spreadsheet.
Default Value:
- “A1”
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
sheets:[{
dataSource: window.defaultData, startCell: "B1"
}]
});
</script>
showPager boolean
Gets or sets a value that indicates whether to show or hide pager in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
showPager: true
});
</script>
showRibbon boolean
Gets or sets a value that indicates whether to show or hide ribbon in the Spreadsheet.
Default Value
- true
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
showRibbon: true
});
</script>
undoRedoStep number
This is used to set the number of undo-redo steps in the Spreadsheet.
Default Value
- 20
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
undoRedoStep: 15
});
</script>
userName string
Define the username for the Spreadsheet which is displayed in comment.
Default Value
- User Name
Example
<div id="Spreadsheet"></div>
<script>
$('#Spreadsheet').ejSpreadsheet({
userName: "User Name"
});
</script>
Methods
addCustomFormula(formulaName, functionName)
This method is used to add custom formulas in Spreadsheet.
Name | Type | Description |
---|---|---|
formulaName | string | Pass the name of the formula. |
functionName | string | Pass the name of the function. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.addCustomFormula("CUSTOMTOTAL","customTotal"); // Sends a add custom formula request to the Spreadsheet.
function customTotal(args){}//args-It uses the value given by the user while using custom formula in Spreadsheet.
</script>
<script>
// Sends a add custom formula request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("addCustomFormula","CUSTOMTOTAL","customTotal" );
function customTotal(args){}//args-It uses the value given by the user while using custom formula in Spreadsheet.
</script>
addNewSheet()
This method is used to add a new sheet in the last position of the sheet container.
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.addNewSheet(); // Sends a add new sheet request to the Spreadsheet.
</script>
<script>
// Sends a add new sheet request to the Spreadsheet
$("#Spreadsheet").ejSpreadsheet("addNewSheet");
</script>
blankWorkbook()
This method is used to blank the workbook in Spreadsheet.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To blank the workbook in Spreadsheet
excelObj.blankWorkbook();
</script>
clearAll([range])
It is used to clear all the data and format in the specified range of cells in Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Optional. If range is specified, then it will clear all content in the specified range else it will use the current selected range. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.clearAll("A2:A6"); // Sends a clear all request to the Spreadsheet.
</script>
<script>
// Sends a clear all request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("clearAll", "A2:A6");
</script>
clearAllFormat([range])
This property is used to clear all the formats applied in the specified range in Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Optional. If range is specified, then it will clear all format in the specified range else it will use the current selected range. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.clearAllFormat("A2:A6"); // Sends a clear all format request to the Spreadsheet.
</script>
<script>
// Sends a clear all format request to the Spreadsheet
$("#Spreadsheet").ejSpreadsheet("clearAllFormat", "A2:A6");
</script>
clearBorder([range])
Used to clear the applied border in the specified range in Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Optional. If range is specified, then it will clear border in the specified range else it will use the current selected range. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.clearBorder("A2:A6"); // Sends a clear border request to the Spreadsheet.
</script>
<script>
// Sends a clear border request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("clearBorder", "A2:A6");
</script>
clearContents([range])
This property is used to clear the contents in the specified range in Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array |
Optional. If the range is specified, then it will clear the content in the specified range else it will use the current selected range. |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.clearContents("A2:A6"); // Sends a clear content request to the Spreadsheet.
</script>
<script>
// Sends a clear content request to the Spreadsheet
$("#Spreadsheet").ejSpreadsheet("clearContents", "A2:A6");
</script>
clearRange(rangeName)
This method is used to remove only the data in the range denoted by the specified range name.
Name | Type | Description |
---|---|---|
rangeName | string | Pass the defined rangeSettings property name. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.clearRange("updateTable"); // Sends a clear range request to the Spreadsheet.
</script>
<div id="Spreadsheet"></div>
<script>
// Sends a clear range request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("clearRange","updateTable");
</script>
clearRangeData([range], [property],[cells],[skipHiddenRow],[status],[skipCell])
It is used to remove data in the specified range of cells based on the defined property.
Name | Type | Description |
---|---|---|
range | Array|string | Optional. If range is specified, it will clear data for the specified range else it will use the current selected range. |
property | string | Optional. If property is specified, it will remove the specified property in the range else it will remove default properties |
cells | element | Optional. |
skipHiddenRow | boolean |
Optional. pass
|
status | string | Optional. Pass the status to perform undo and redo operation. |
skipCell | boolean | Optional. It specifies whether to skip element processing or not. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.clearRangeData("A1:A5", ["value", "value2"], excelObj.getRange("A1:A5"), true);
</script>
<script>
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sends a clear range data request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("clearRangeData", "A1:A5", ["value", "value2"], excelObj.getRange("A1:A5"), false);
</script>
clearUndoRedo()
This method is used to clear undo and redo collections in the Spreadsheet.
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.clearUndoRedo();
</script>
<script>
// Sends a clear undo redo request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("clearUndoRedo");
</script>
copySheet(fromIdx, toIdx, isCopySheet)
This method is used to copy or move the sheets in Spreadsheet.
Name | Type | Description |
---|---|---|
fromIdx | number | Pass the sheet index that you want to copy or move. |
toIdx | number | Pass the position index where you want to copy or move. |
isCopySheet | boolean | Pass
|
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.copySheet(2, 1, true); // Sends a copy sheet request to the Spreadsheet.
//excelObj.copySheet(2, 1, false); // Sends a move sheet request to the Spreadsheet.
</script>
<script>
// Sends a copy sheet request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("copySheet", 2, 1, true);
</script>
deleteEntireColumn(startCol, endCol)
This method is used to delete the entire column which is selected.
Name | Type | Description |
---|---|---|
startCol | number | Pass the start column index. |
endCol | number | Pass the end column index. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Delete a column in the sheet.
excelObj.deleteEntireColumn(2, 3);
</script>
<script>
// Delete a column in the sheet.
$("#Spreadsheet").ejSpreadsheet("deleteEntireColumn",2,3);
</script>
deleteEntireRow(startRow, endRow)
This method is used to delete the entire row which is selected.
Name | Type | Description |
---|---|---|
startRow | number | Pass the start row index. |
endRow | number | Pass the end row index. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Delete a row in the sheet.
excelObj.deleteEntireRow(2,3);
</script>
<script>
// Delete a row in the sheet.
$("#Spreadsheet").ejSpreadsheet("deleteEntireRow", 2, 3);
</script>
deleteSheet(idx)
This method is used to delete a particular sheet in the Spreadsheet.
Name | Type | Description |
---|---|---|
idx | number | Pass the sheet index to perform delete action. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.deleteSheet(3); // Sends a sheet delete request to the Spreadsheet
</script>
<script>
// Sends a sheet delete request to the Spreadsheet
$("#Spreadsheet").ejSpreadsheet("deleteSheet", 3);
</script>
deleteShiftLeft(startCell, endCell)
This method is used to delete the selected cells and shift the remaining cells to left.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
startCell |
|
Pass the Object "CellIndex"
|
|||||||||
endCell |
|
Pass the Object "CellIndex"
|
Example
<script>
var startCell= {rowIndex: 1, colIndex: 2}, endCell= {rowIndex: 1, colIndex: 2};
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Delete a cell and shift cells left in the sheet.
excelObj.deleteShiftLeft(startCell, endCell);
</script>
<script>
var startCell= {rowIdx: 1, colIdx: 2}, endCell= {rowIdx: 1, colIdx: 2};
// Delete a cell and shift cells left in the sheet.
$("#Spreadsheet").ejSpreadsheet("deleteShiftLeft", startCell, endCell);
</script>
deleteShiftUp(startCell, endCell)
This method is used to delete the selected cells and shift the remaining cells up.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
startCell |
|
Pass the Object "CellIndex"
|
|||||||||
endCell |
|
Pass the Object "CellIndex"
|
Example
<script>
var startCell= {rowIndex: 1, colIndex: 2}, endCell= {rowIndex: 1, colIndex: 2};
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Delete a cell and shift cells up in the sheet.
excelObj.deleteShiftUp(startCell, endCell);
</script>
<script>
var startCell= {rowIdx: 1, colIdx: 2}, endCell= {rowIdx: 1, colIdx: 2};
// Delete a cell and shift cells up in the sheet.
$("#Spreadsheet").ejSpreadsheet("deleteShiftUp", startCell, endCell);
</script>
editRange(rangeName, fn)
This method is used to edit data in the specified range of cells based on its corresponding rangeSettings.
Name | Type | Description |
---|---|---|
rangeName | string | Pass the defined rangeSettings property name. |
fn | function | Pass the function that you want to perform range edit. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var updateFn = function(cell, cellIdx) {
if (cellIdx % 2 == 0)
return "SpreadSheet";
else
return "Grid";
};
var rangeSettings = {
range: "B1:C2",
name: "updateTable",
cssClass: "readOnly1",
readOnly: true,
contextTab: {},
contextMenuSettings: {
dataSource: [{
id: 1,
text: "Copy",
parentId: null,
sprite: "e-icon e-copy"
}, {
id: 2,
text: "Refresh",
parentId: null,
}]
},
showPanel: true
};
//Update new rangeSettings property before invoking edit range
excelObj.model.sheets[1].rangeSettings["updateTable"] = rangeSettings;
//Sends a edit range request to the Spreadsheet
excelObj.editRange("updateTable", updateFn);
</script>
<script>
//Sends a edit range request to the Spreadsheet after updating new rangeSettings.
$("#Spreadsheet").ejSpreadsheet("editRange", "updateTable", updateFn);
</script>
getActivationPanel()
This method is used to get the activation panel in the Spreadsheet.
Returns:
Element
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getActivationPanel(); // Gets sheet ActivationPanel element in Spreadsheet.
</script>
<script>
// Gets sheet ActivationPanel element in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("getActivationPanel");
</script>
getActiveCell([sheetIdx])
This method is used to get the active cell object in Spreadsheet. It will returns object which contains rowIndex and colIndex of the active cell.
Name | Type | Description |
---|---|---|
sheetIdx | number | Optional. If sheetIdx is specified, it will return the active cell object in specified sheet index else it will use the current sheet index |
Returns:
Object
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getActiveCell(1); // Gets the activeCell object in specified sheet index.
</script>
<script>
// Gets the activeCell element in specified sheetindex
$("#Spreadsheet").ejSpreadsheet("getActiveCell", 1);
</script>
getActiveCellElem([sheetIdx])
This method is used to get the active cell element based on the given sheet index in the Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number |
Optional. If sheetIndex is specified, it will return the active cell element in specified sheet index else it will use the current active sheet index. |
Returns:
Element
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getActiveCellElem(1); // Gets activeCell element in Spreadsheet.
</script>
<script>
// Gets activeCell element in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("getActiveCellElem", 1);
</script>
getActiveSheetIndex()
This method is used to get the current active sheet index in Spreadsheet.
Returns:
number
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getActiveSheetIndex(); // Gets activeSheet index in Spreadsheet.
</script>
<script>
// Gets activeSheet index in Spreadsheet
$("#Spreadsheet").ejSpreadsheet("getActiveSheetIndex");
</script>
getAutoFillElem()
This method is used to get the auto fill element in Spreadsheet.
Returns:
Element
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getAutoFillElem(); // Gets autofill element in Spreadsheet.
</script>
<script>
// Gets autofill element in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("getAutoFillElem");
</script>
getAlphaRange(sRIndex, sCIndex, eRIndex, eCIndex)
This method is used to get the alpha range of the given index in Spreadsheet.
Name | Type | Description |
---|---|---|
sRIndex | number | Pass the start row index. |
sCIndex | number | Pass the start column index. |
eRIndex | number | Pass the end row index. |
eCIndex | number | Pass the end column index. |
Returns:
String
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getAlphaRange(2,3,4,5); // Get the alpha range for the given index
</script>
getCell(rowIdx, colIdx, [sheetIdx])
This method is used to get the cell element based on specified row and column index in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Pass the row index. |
colIdx | number | Pass the column index. |
sheetIdx | number | Optional. Pass the sheet index that you want to get cell. |
Returns:
Element
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getCell(2, 3, 1); // Get the cell based on rowIndex and colIndex.
</script>
<script>
// Get the cell based on row and column index
$("#Spreadsheet").ejSpreadsheet("getCell", 2, 3, 1);
</script>
getDataSettings(sheetIdx)
This method is used to get the data settings in the Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the sheet index. |
Returns:
number
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getDataSettings(1); // Gets the data settings in Spreadsheet
</script>
<script>
// Gets the data settings in Spreadsheet
$("#Spreadsheet").ejSpreadsheet("getDataSettings", 1);
</script>
getFrozenColumns(sheetIdx)
This method is used to get the frozen columns index in the Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the sheet index. |
Returns:
number
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getFrozenColumns(1); // Gets the frozen column index in Spreadsheet
</script>
<script>
// Gets the frozen column index in Spreadsheet
$("#Spreadsheet").ejSpreadsheet("getFrozenColumns", 1);
</script>
getFrozenRows(sheetIdx)
This method is used to get the frozen row index in Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the sheet index. |
Returns:
number
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getFrozenRows(1); // Gets the frozen row index in Spreadsheet.
</script>
<script>
// Gets the frozen row index in Spreadsheet
$("#Spreadsheet").ejSpreadsheet("getFrozenRows", 1);
</script>
getHyperlink(cell)
This method is used to get the hyperlink data as object from the specified cell in Spreadsheet.
Name | Type | Description |
---|---|---|
cell | Element | Pass the DOM element to get hyperlink |
Returns:
Object
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getHyperlink(excelObj.getCell(1, 1)); // To get the hyperlink data of specified cell.
</script>
<script>
// To get the hyperlink data of specified cell.
$("#Spreadsheet").ejSpreadsheet("getHyperlink", excelObj.getCell(1, 1, 1));
</script>
getObjectKeys(obj)
This method is used to get the keys from the given object in Spreadsheet.
Name | Type | Description |
---|---|---|
obj | Object | Pass the Object |
Returns:
Array
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getObjectKeys({"font-family": "Arial Black", "font-weight": "normal", "font-style": "Italic", "font-size": "9pt", "text-decoration": "line-through",color:"#FFFF00"}); // To get the keys from the given object.
</script>
getRange(range, sheetIdx, [skipHiddenRow])
This method is used to get all cell elements in the specified range.
Name | Type | Description |
---|---|---|
range | string | Pass the range that you want to get the cells. |
sheetIdx | number | Pass the index of the sheet. |
skipHiddenRow | boolean |
Optional. Pass
|
Returns:
Element
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getRange("A2:A5", 1, false); // Get the cells based on the given range
</script>
<script>
// Get the cells based on the given range
$("#Spreadsheet").ejSpreadsheet("getRange", "A2:A5", 1, false);
</script>
getRangeData([options])
This method is used to get the data in specified range in Spreadsheet.
Name | Type | Description |
---|---|---|
options | Object | Optional. Pass the range, property, sheetIdx, valueOnly in options |
Returns:
Array
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getRangeData({range: [2, 6, 2, 6], property: ["value", "value2", "format"], sheetIdx: 1}); // To get the cells data of specified range
</script>
<script>
// To get the cells data of specified range
$("#Spreadsheet").ejSpreadsheet("getRangeData", {range: [2, 6, 2, 6], property: ["value", "value2", "format"], sheetIdx: 1});
</script>
getRangeDataAsObject(startcell, endcell, [skipHiddenRow])
This method is used to get the data as object in the specified range.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
startcell |
|
Pass the Object "CellIndex"
|
|||||||||
endcell |
|
Pass the Object "CellIndex"
|
|||||||||
skipHiddenRow | boolean |
Optional. Pass
|
Returns:
Object
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getRangeDataAsObject({ rowIndex:2,colIndex:2 },{ rowIndex:4,colIndex:4 }, true, 1); // To get the data as object from the given cell
</script>
getRangeIndices(range)
This method is used to get the range indices array based on the specified alpha range in Spreadsheet.
Name | Type | Description |
---|---|---|
range | string | Pass the alpha range that you want to get range indices. |
Returns:
Array
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getRangeIndices("A1:A9"); // Get range indices based on specified alpha range.
</script>
<script>
// Get cells based on specified range indices
$("#Spreadsheet").ejSpreadsheet("getRangeIndices", "A1:A9");
</script>
getSheet(sheetIdx)
This method is used to get the sheet details based on the given sheet index in Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the sheet index to get the sheet object. |
Returns:
Object
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getSheet(1); // Gets sheet details of Spreadsheet.
</script>
<script>
// Gets sheet details of Spreadsheet
$("#Spreadsheet").ejSpreadsheet("getSheet", 1);
</script>
getSheetElement(sheetIdx)
This method is used to get the sheet content div element of Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the sheet index to get the sheet content. |
Returns:
Element
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getSheetElement(1); // Gets sheet content of Spreadsheet.
</script>
<script>
// Gets sheet content of Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("getSheetElement", 1);
</script>
getSheets()
This method is used to get all the sheets in workbook.
Returns:
Array
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getSheets(); // Gets sheets details of Spreadsheet.
</script>
<script>
// Gets sheets details of Spreadsheet
$("#Spreadsheet").ejSpreadsheet("getSheets");
</script>
gotoPage(sheetIdx, newSheet)
This method is used to send a paging request to the specified sheet Index in the Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the sheet index to perform paging at specified sheet index |
newSheet | boolean | Pass
it navigate to that sheet else it create a new sheet. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.gotoPage(1, false); // Sends a paging request to the Spreadsheet with specified sheet index
</script>
<script>
// Sends a paging request to the Spreadsheet with specified sheet index.
$("#Spreadsheet").ejSpreadsheet("gotoPage", 3, true);
</script>
getVisibleCellDetails()
This method is used to get the visible cell details in Spreadsheet.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"),
cellDetails = excelObj.getVisibleCellDetails(), //To get the visible cell details in Spreadsheet.
topLeftCell = cellDetails.topLeftCell, // To get the visible topLeftCell.
rowCount = cellDetails.rowCount, // To get the visible rowcount.
colCount = cellDetails.colCount; // To get the visible colCount.
</script>
hideActivationPanel()
This method is used to hide the pivot table activationPanel in the Spreadsheet.
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.hideActivationPanel(); // To hide the pivot table activationPanel in the Spreadsheet.
</script>
<script>
// To hide the pivot table activationPanel in the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("hideActivationPanel");
</script>
hideColumn(startCol, endCol)
This method is used to hide the entire columns from the specified range (startCol, endCol) in Spreadsheet.
Name | Type | Description |
---|---|---|
startCol | number | Index of the start column. |
endCol | number | Optional. Index of the end column. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Hide the column by passing column index in the active sheet.
excelObj.hideColumn(1);
// Hide the columns from startCol to endCol in the active sheet.
excelObj.hideColumn(1, 4);
</script>
<script>
// Hide the column by passing column index in the active sheet.
$("#Spreadsheet").ejSpreadsheet("hideColumn", 1);
// Hide the columns from startCol to endCol in the active sheet.
$("#Spreadsheet").ejSpreadsheet("hideColumn", 1, 4);
</script>
hideFormulaBar()
This method is used to hide the formula bar in Spreadsheet.
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To hide formula bar in Spreadsheet.
excelObj.hideFormulaBar();
</script>
hideRow(startRow, endRow)
This method is used to hide the rows, based on the specified row index in Spreadsheet.
Name | Type | Description |
---|---|---|
startRow | number | Index of the start row. |
endRow | number | Optional. Index of the end row. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Hide the row by passing row index in the active sheet.
excelObj.hideRow(1);
// Hide a rows from startRow to endRow in the active sheet.
excelObj.hideRow(1, 4);
</script>
<script>
// Hide the row by passing row index in the active sheet.
$("#Spreadsheet").ejSpreadsheet("hideRow", 1);
// Hide a rows from startRow to endRow in the active sheet.
$("#Spreadsheet").ejSpreadsheet("hideRow", 1, 4);
</script>
hideSheet(sheetIdx)
This method is used to hide the sheet based on the specified sheetIndex or sheet name in the Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | string|number | Pass the sheet name or index that you want to hide. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Sends a hide sheet request to the Spreadsheet.
excelObj.hideSheet("Sheet2");
</script>
<script>
//Sends a hide sheet request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("hideSheet", 2);
</script>
hideWaitingPopUp()
This method is used to hide the displayed waiting pop-up in Spreadsheet.
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// hide waiting popup in the Spreadsheet.
excelObj.hideWaitingPopUp();
</script>
<script>
// hide waiting popup in the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("hideWaitingPopUp");
</script>
insertEntireColumn(startCol, endCol)
This method is used to insert a column before the active cell’s column in the Spreadsheet.
Name | Type | Description |
---|---|---|
startCol | number | Pass start column. |
endCol | number | Pass end column. |
Example
<div id="Spreadsheet"></div>
<script>
//initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Insert a column in the sheet.
excelObj.insertEntireColumn(1, 2);
</script>
<script>
// Insert a column in the sheet.
$("#Spreadsheet").ejSpreadsheet("insertEntireColumn", 1, 2);
</script>
insertEntireRow(startRow, endRow)
This method is used to insert a row before the active cell’s row in the Spreadsheet.
Name | Type | Description |
---|---|---|
startRow | number | Pass start row. |
endRow | number | Pass end row. |
Example
<div id="Spreadsheet"></div>
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Insert a row in the sheet.
excelObj.insertEntireRow(1, 2);
</script>
<script>
// Insert a row in the sheet.
$("#Spreadsheet").ejSpreadsheet("insertEntireRow", 1, 2);
</script>
insertSheet()
This method is used to insert a new sheet to the left of the current active sheet.
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.insertSheet(); // Sends a insert new sheet request to the Spreadsheet.
</script>
<script>
// Sends an insert new sheet request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("insertSheet");
</script>
insertShiftBottom(startCell, endCell)
This method is used to insert cells in the selected or specified range and shift remaining cells to bottom.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
startCell |
|
Pass the Object "CellIndex"
|
|||||||||
endCell |
|
Pass the Object "CellIndex"
|
Example
<div id="Spreadsheet"></div>
<script>
var startCell= {rowIndex: 1, colIndex: 2}, endCell= {rowIndex: 1, colIndex: 2};
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Insert a cells and shift cells bottom in the sheet.
excelObj.insertShiftBottom(startCell, endCell);
</script>
<script>
var startCell= {rowIndex: 1, colIndex: 2}, endCell= {rowIndex: 1, colIndex: 2};
// Insert a cell and shift cells bottom in the sheet.
$("#Spreadsheet").ejSpreadsheet("insertShiftBottom", startCell, endCell);
</script>
insertShiftRight(startCell, endCell)
This method is used to insert cells in the selected or specified range and shift remaining cells to right.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
startCell |
|
Pass the Object "CellIndex"
|
|||||||||
endCell |
|
Pass the Object "CellIndex"
|
Example
<div id="Spreadsheet"></div>
<script>
var startCell= {rowIndex: 1, colIndex: 2}, endCell= {rowIndex: 1, colIndex: 2};
//initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Insert a cells and shift cells right in the sheet.
excelObj.insertShiftRight(startCell, endCell);
</script>
<script>
var startCell= {rowIndex: 1, colIndex: 2}, endCell= {rowIndex: 1, colIndex: 2};
// Insert a cell and shift cells right in the sheet.
$("#Spreadsheet").ejSpreadsheet("insertShiftRight", startCell, endCell);
</script>
import(importRequest)
This method is used to import excel file manually by using form data.
Name | Type | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
importRequest |
|
Pass the form data object to import files manually.
|
The Objects are File, Password, URL, FileStream, FileType.
Example
<div id="Spreadsheet"></div>
<script>
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"); // Initialize the Spreadsheet object.
var importRequest = { Url:"http://staging.syncfusion.com:7777/ejSpreadsheet/Spreadsheet.xlsx" };
excelObj.import(importRequest);
</script>
loadFromJSON(response)
This method is used to load JSON data in Spreadsheet.
Name | Type | Description |
---|---|---|
response | Element | Pass the response that you want to load. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), response;
response = excelObj.saveAsJSON();
excelObj.loadFromJSON(response); // To load JSON data in the Spreadsheet.
</script>
lockCells(range, [isLocked])
This method is used to lock/unlock the range of cells in active sheet. Lock cells are activated only after the sheet is protected. Once the sheet is protected it is unable to lock/unlock cells.
Name | Type | Description |
---|---|---|
range | string|Array | Pass the alpha range cells or array range of cells. |
isLocked | string |
Optional. By default is
|
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.lockCells("A3:B5", true); // To lock cells in the Spreadsheet.
</script>
<script>
// To lock cells in the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("lockCells","A3:B5", true);
</script>
mergeAcrossCells([range], [alertStatus])
This method is used to merge cells by across in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string | Optional. To pass the cell range or selected cells are process. |
alertStatus | boolean |
Optional. If pass
|
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.mergeAcrossCells("A3:B5", true); // To merge cells across in the Spreadsheet.
</script>
<script>
// To merge cells across in the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("mergeAcrossCells","A3:B5");
</script>
mergeCells([range], [alertStatus])
This method is used to merge the selected cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Optional. To pass the cell range or selected cells are process. |
alertStatus | boolean |
Optional. If pass
|
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.mergeCells("A3:B5", true); // To merge the selected cell in Spreadsheet.
</script>
<script>
// To merge the selected cell in Spreadsheet
$("#Spreadsheet").ejSpreadsheet("mergeCells","A3:B5");
</script>
performSelection(startCell, endCell)
This method is used to select a cell or range in the Spreadsheet.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
startCell |
|
Pass the Object "CellIndex"
|
|||||||||
endCell |
|
Pass the Object "CellIndex"
|
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To perform selection for the specified range.
excelObj.performSelection({ rowIndex: 1, colIndex: 1 }, { rowIndex: 2, colIndex: 2 });
// Range as string
excelObj.performSelection("B1:C3");
</script>
<script>
// To perform selection for the specified range.
$("#Spreadsheet").ejSpreadsheet("performSelection", { rowIndex: 1, colIndex: 1 }, { rowIndex: 2, colIndex: 2 });
// Range as string
$("#Spreadsheet").ejSpreadsheet("performSelection", "B1:C3");
</script>
protectSheet([isProtected])
This method is used to protect or unprotect active sheet.
Name | Type | Description |
---|---|---|
isProtected | boolean |
Optional. By default is
|
Example
<script>
//initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To protect sheet in Spreadsheet.
excelObj.protectSheet(false);
</script>
<script>
// To defined refresh selection in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("protectSheet", false);
</script>
redo()
This method is used to perform the redo action in Spreadsheet.
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To perform redo action in Spreadsheet
excelObj.redo();
</script>
refreshContent(sheetIdx)
This method is used to refresh the Spreadsheet based on the sheet model values.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the index of the sheet. |
Example
<script>
//initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), sheet = excelObj.getSheet();
sheet.rowCount = 30;
sheet.colCount = 20;
excelObj.refreshContent(1); // refresh the spreadsheet based on the row count and column count in normal mode
</script>
<script>
$("#Spreadsheet").ejSpreadsheet("refreshContent",1);
</script>
refreshSpreadsheet()
This method is used to refresh the Spreadsheet element based on the page layout.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.refreshSpreadsheet();
</script>
<script>
$("#Spreadsheet").ejSpreadsheet("refreshSpreadsheet");
</script>
refresh()
This method destroys and re-creates the entire Spreadsheet control.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.refresh();
</script>
<script>
$("#Spreadsheet").ejSpreadsheet("refresh");
</script>
removeCustomFormula(formulaName, functionName)
This method is used to remove custom formulae in Spreadsheet.
Name | Type | Description |
---|---|---|
formulaName | string | Pass the name of the formula. |
functionName | string | Pass the name of the function. |
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.removeCustomFormula("CUSTOMTOTAL","customTotal"); // Sends a remove custom formula request to the Spreadsheet.
function customTotal(args){}//args-It uses the value given by the user while using custom formula in Spreadsheet.
</script>
<script>
// Sends a remove custom formula request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("removeCustomFormula","CUSTOMTOTAL","customTotal" );
function customTotal(args){}//args-It uses the value given by the user while using custom formula in Spreadsheet .
</script>
removeHyperlink(range, [isClearHLink], [status], [cells], [skipHiddenRow])
This method is used to remove the hyperlink from selected cells of current sheet.
Name | Type | Description |
---|---|---|
range | string | Hyperlink remove from the specified range. |
isClearHLink | boolean |
Optional. If it is
|
status | boolean | Optional. Pass the status to perform undo and redo operations. |
cells | String|Array | Optional. Pass the cells that you want to remove hyperlink. |
skipHiddenRow | boolean |
Optional. Pass
|
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.removeHyperlink("A2:A3", false); // To remove the hyperlink of specified range
</script>
<script>
// To remove the hyperlink of specified range
$("#Spreadsheet").ejSpreadsheet("removeHyperlink", "A2:A3", false);
</script>
removeRange(rangeName)
This method is used to remove the range data and its defined rangeSettings property based on the specified range name.
Name | Type | Description |
---|---|---|
rangeName | string | Pass the defined rangeSetting property name. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sends a remove range request to the Spreadsheet.
excelObj.removeRange("updateTable");
</script>
<script>
// Sends a remove range request to the Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("removeRange", "updateTable");
</script>
removeReadOnly([range])
This method is used to remove the readonly option for the specified range.
Name | Type | Description |
---|---|---|
range | string|Array | Pass the range. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Removes readonly option for the range.
excelObj.removeReadOnly("B3");
</script>
<script>
// Removes readonly option for the range.
$("#Spreadsheet").ejSpreadsheet("removeReadOnly", "B3");
</script>
saveAsJSON()
This method is used to save JSON data in Spreadsheet.
Returns:
Object
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.saveAsJSON();
</script>
<script>
$("#Spreadsheet").ejSpreadsheet("saveAsJSON");
</script>
saveBatchChanges(sheetIdx)
This method is used to save batch changes in Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the sheet index for Spreadsheet. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Set sheet index for Spreadsheet.
excelObj.saveBatchChanges(1);
</script>
<script>
//Set sheet index for Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("saveBatchChanges", 1);
</script>
setActiveCell(rowIdx, colIdx, sheetIdx)
This method is used to set the active cell in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Pass the row index. |
colIdx | number | Pass the column index. |
sheetIdx | number | Pass the index of the sheet. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.setActiveCell(1, 0, 1); // Sets activeCell in Spreadsheet.
</script>
<script>
// Sets activeCell in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("setActiveCell", 1, 0, 1);
</script>
setActiveSheetIndex(sheetIdx)
This method is used to set active sheet index for the Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the active sheet index for Spreadsheet. |
Example
<script>
var sheetIndex= 1;
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Set active sheet index for Spreadsheet.
excelObj.setActiveSheetIndex(sheetIndex);
</script>
<script>
//Set active sheet index for Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("setActiveSheetIndex", sheetIndex);
</script>
setBorder(property, [range])
This method is used to set border for the specified range of cells in the Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
property |
|
Pass the Object "BorderOptions".
|
||||||||||||
range | string | Optional. If range is specified, it will set border for the specified range else it will use the selected range. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.setBorder({ style: "solid", type: "outside", color: "#000000"}, "B2:B6"); // To set borders in Spreadsheet
</script>
<script>
// To set borders in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("setBorder", { style: "solid", type: "outside", color: "#000000"}, "B2:B6");
</script>
setHeightToRows(heightColl)
This method is used to set the height for the rows in the Spreadsheet.
Name | Type | Description |
---|---|---|
heightColl | Array|Object | Pass the row index and height of the rows. |
Example
<div id="Spreadsheet"></div>
<script>
var heightRowObj= [{rowIndex: 2, height: 40}, {rowIndex: 3, height: 50}], heightRowArr = [50, 40];
//Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Set height for specified rows in active sheet.
excelObj.setHeightToRows(heightRowObj);
// Set height for rows starting from the '0'th index in active sheet.
excelObj.setHeightToRows(heightRowArr);
</script>
<script>
var heightRowObj= [{rowIndex: 2, height: 40}, {rowIndex: 3, height: 50}], heightRowArr = [50, 40];
// Set height for specified rows in active sheet.
$("#Spreadsheet").ejSpreadsheet("setHeightToRows", heightRowObj);
// Set height for rows starting from the '0'th index in active sheet.
$("#Spreadsheet").ejSpreadsheet("setHeightToRows", heightRowArr);
</script>
setHyperlink(range, link, sheetIdx)
This method is used to set the hyperlink in selected cells of the current sheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
range | string|Array | If range is specified, it will set the hyperlink in range of the cells. | ||||||||||||
link |
|
Pass the Object "LinkOptions"
|
||||||||||||
sheetIdx | number | If we pass cellAddress then which sheet to be navigate in the applied link. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.setHyperlink("A2:A3",{"cellAddr":"A2:A8"}, 3); // To set the hyperlink of specified range.
</script>
<script>
// To set the hyperlink of specified range
$("#Spreadsheet").ejSpreadsheet("setHyperlink", "A2:A3",{"cellAddr":"A2:A8"},3);
</script>
setReadOnly([range])
This method is used to set the readonly option for the specified range.
Name | Type | Description |
---|---|---|
range | string|Array | Pass the range. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sets readonly option for the range.
excelObj.setReadOnly("B3");
</script>
<script>
// Sets readonly option for the range.
$("#Spreadsheet").ejSpreadsheet("setReadOnly", "B3");
</script>
setSheetFocus()
This method is used to set the focus to the Spreadsheet.
Example
<script>
//initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Set focus on Spreadsheet.
excelObj.setSheetFocus();
</script>
<script>
// Set focus on Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("setSheetFocus");
</script>
setWidthToColumns(widthColl, [sheetIndex])
This method is used to set the width for the columns in the Spreadsheet.
Name | Type | Description |
---|---|---|
widthColl | Array|Object | Pass the column index and width of the columns. |
sheetIndex | Number | Optional. If sheetIdx is specified, it will set the width for columns to the specified sheet else it will use active sheet. |
Example
<script>
var widthCollObj= [{colIndex: 2, width: 40}, {colIndex: 3, width: 50}], widthCollArr = [80, 90];
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Set width for specified columns in active sheet
excelObj.setWidthToColumns(widthCollObj);
// Set width for columns starting from the '0'th index in second sheet.
excelObj.setWidthToColumns(widthCollArr, 2);
</script>
<script>
// Set width for specified columns in active sheet.
$("#Spreadsheet").ejSpreadsheet("setWidthToColumns", widthCollObj);
// Set width for columns starting from the '0'th index in second sheet.
$("#Spreadsheet").ejSpreadsheet("setWidthToColumns", widthCollArr, 2);
</script>
sheetRename(sheetName)
This method is used to rename the active sheet.
Name | Type | Description |
---|---|---|
sheetName | string | Pass the sheet name that you want to change the current active sheet name. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.sheetRename("Sep-Billing"); // Sends a sheet rename request to the Spreadsheet
</script>
<script>
// To rename a sheet in Spreadsheet
$("#Spreadsheet").ejSpreadsheet("sheetRename", "Sep-Billing");
</script>
showActivationPanel(rangeName)
This method is used to display the activationPanel for the specified range name.
Name | Type | Description |
---|---|---|
rangeName | string | Pass the range name that you want to display the activation panel. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.showActivationPanel("upTable"); // To display the activationPanel in Spreadsheet
</script>
<script>
// To display the activationPanel in Spreadsheet
$("#Spreadsheet").ejSpreadsheet("showActivationPanel", "upTable");
</script>
showColumn(startCol, endCol)
This method is used to show the hidden columns within the specified range in the Spreadsheet.
Name | Type | Description |
---|---|---|
startColIdx | number | Index of the start column. |
endColIdx | number | Optional. Index of the end column. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// show the hidden column by passing column index in the active sheet.
excelObj.showColumn(1);
// show the hidden columns from startColIdx to startColIdx in the active sheet.
excelObj.showColumn(3, 6);
</script>
<script>
// show the hidden column by passing column index in the active sheet.
$("#Spreadsheet").ejSpreadsheet("showColumn", 1);
// show the hidden columns from startColIdx to startColIdx in the active sheet.
$("#Spreadsheet").ejSpreadsheet("showColumn", 3, 6);
</script>
showFormulaBar()
This method is used to show the formula bar in Spreadsheet.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To show the formula bar in Spreadsheet
excelObj.showFormulaBar();
</script>
<script>
// To show the formula bar in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("showFormulaBar ");
</script>
showGridlines(status)
This method is used to show/hide gridlines in active sheet in the Spreadsheet.
Name | Type | Description |
---|---|---|
status | boolean | Pass
|
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To hide the gridlines in the sheet.
excelObj.showGridlines(false);
</script>
<script>
// hide the gridlines in the sheet.
$("#Spreadsheet").ejSpreadsheet("showGridlines", false);
</script>
showHeadings(status)
This method is used to show/hide the headers in active sheet in the Spreadsheet.
Name | Type | Description |
---|---|---|
startRow | boolean | Pass
|
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To hide the headers in the sheet.
excelObj.showHeadings(false);
</script>
<script>
// To hide the headers in the sheet.
$("#Spreadsheet").ejSpreadsheet("showHeadings", false);
</script>
showPager(status)
This method is used to show/hide pager in the Spreadsheet.
Name | Type | Description |
---|---|---|
status | boolean | Pass
|
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.showPager(false); // Gets or sets the value to show/hide pager.
</script>
<script>
// Gets or sets the value to show/hide pager.
$("#Spreadsheet").ejSpreadsheet("showPager",false);
</script>
showRow(startRow, endRow)
This method is used to show the hidden rows in the specified range in the Spreadsheet.
Name | Type | Description |
---|---|---|
startRow | number | Index of the start row. |
endRow | number | Optional. Index of the end row. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// show the hidden row by passing row index in the active sheet.
excelObj.showRow(1);
// To show the hidden rows for startRow to endRow in the active sheet.
excelObj.showRow(3, 6);
</script>
<script>
// show the hidden row by passing row index in the active sheet.
$("#Spreadsheet").ejSpreadsheet("showRow", 1);
// To show the hidden rows for startRow to endRow in the active sheet.
$("#Spreadsheet").ejSpreadsheet("showRow", 3, 6);
</script>
showWaitingPopUp()
This method is used to show waiting pop-up in Spreadsheet.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To show waiting popup in Spreadsheet
excelObj.showWaitingPopUp();
</script>
<script>
// show waiting popup in the Spreadsheet
$("#Spreadsheet").ejSpreadsheet("showWaitingPopUp");
</script>
undo()
This method is used to perform the undo action in Spreadsheet.
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To perform undo action in Spreadsheet
excelObj.undo();
</script>
unhideSheet(sheetInfo)
This method is used to unhide the sheet based on specified sheet name or sheet index.
Name | Type | Description |
---|---|---|
sheetInfo | string|number | Pass the sheet name or index that you want to unhide. |
Example
<script>
//Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To unhide a sheet in Spreadsheet.
excelObj.unhideSheet("Sheet2");
</script>
<script>
//To unhide a sheet in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("unhideSheet", "Sheet2");
</script>
unmergeCells([range])
This method is used to unmerge the selected range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string | Optional. If the range is specified, then it will un merge the specified range else it will use the current selected range. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To unmerge the selected cells in Spreadsheet.
excelObj.unmergeCells("A3:B5");
</script>
<script>
// To unmerge the selected cells in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("unmergeCells","A3:B5");
</script>
unWrapText([range])
This method is used to unwrap the selected range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | Array|string |
Optional. If the range is specified, then it will update unwrap in the specified range else it will use the current selected range. |
Example
<script>
//Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To unwrap the cell text.
excelObj.unWrapText("A1:B3");
</script>
<script>
// To unwrap the cell text.
$("#Spreadsheet").ejSpreadsheet("unWrapText","A1:B3")
</script>
updateData(data, [range])
This method is used to update the data for the specified range of cells in the Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
data |
|
Pass the Object "DataOptions"
|
||||||||||||
range | Array|string |
Optional. If range is specified, it will update data for the specified range else it will use the current selected range. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To update data in the specified range of the cells in Spreadsheet.
excelObj.updateData([{ value: 10, value2: 10, type: "general"}, { value: 25000, value2: 25000, type: "general"}], "A3");
</script>
<script>
// To update a data in the specified range of cells in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("updateData", [{ value: 10, value2: 10, type: "general"}, { value: 25000, value2: 25000, type: "general"}], "A3");
</script>
updateFormulaBar()
This method is used to update the formula bar in the Spreadsheet.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To update the formula bar in Spreadsheet
excelObj.updateFormulaBar();
</script>
updateRange(sheetIdx, settings)
This method is used to update the range of cells based on the specified settings which we want to update in the Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sheetIdx | number | Pass the sheet index that you want to update. | ||||||||||||
settings |
|
Pass the Object "RangeOptions"
|
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var settings = { dataSource: [{ Product: "XYZ", Price: "2000" }], showHeader: "true", startCell: "F1" };
// To update range of cells with the specified settings
excelObj.updateRange(1, settings);
</script>
<script>
var settings = { dataSource: [{ Product: "XYZ", Price: "2000" }], showHeader: "true", startCell: "F1" };
// To update cells range of cells with the specified settings
$("#Spreadsheet").ejSpreadsheet("updateRange", 1, settings);
</script>
updateUndoRedoCollection(details)
This method is used to update the details for custom undo and redo operations.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
details |
|
Pass the Object "UndoRedoOptions"
|
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), details = { action: "custom", cell: excelObj.getActiveCell(), sheetIndex: 1 };
// To update undo and redo collections.
excelObj.updateUndoRedoCollection(details);
</script>
<script>
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), details = { action: "custom", cell: excelObj.getActiveCell() };
// To update undo and redo collections.
$("#Spreadsheet").ejSpreadsheet("updateUndoRedoCollection", details);
</script>
updateUniqueData(data, [range], [skipCell])
This method is used to update the unique data for the specified range of cells in Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
data |
|
Pass the Object "DataOptions"
|
||||||||||||
range | Array|string | Optional. If range is specified, it will update data for the specified range else it will use the current selected range. | ||||||||||||
skipCell | boolean | Optional. It specifies whether to skip element processing or not. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To update unique data in Spreadsheet.
excelObj.updateUniqueData({ value: 10, value2: 10, type: "general"}, [1, 0, 5, 0]);
</script>
<script>
// To update unique data in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("updateUniqueData", [{ value: 10, value2: 10, type: "general"}], [1, 0, 5, 0]);
</script>
wrapText([range])
This method is used to wrap the selected range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | Array|string |
Optional. If the range is specified, then it will update wrap in the specified range else it will use the current selected range. |
Example
<script>
//Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To wrap the cell Text.
excelObj.wrapText("A1:B3");
</script>
<script>
// To wrap the cell Text.
$("#Spreadsheet").ejSpreadsheet("wrapText","A1:B3")
</script>
XLCellType
XLCellType.addCellTypes(range, settings, [sheetIdx])
This method is used to set a cell type from the specified range of cells in the spreadsheet.
Name | Type | Description | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
range | string | Pass the range where you want apply cell type. | |||||||||||||||||||||||||||
settings |
|
Pass the Object "CellTypeSettings"
|
|||||||||||||||||||||||||||
sheetIdx | number | Optional. Pass sheet index. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var customList = [{ id: "1", text: "ALFKI" }, { id: "2", text: "ANATR" }, { id: "3", text: "ANTON" }, { id: "4", text: "AROUT" }, { id: "5", text: "BERGS" }, { id: "6", text: "BLAUS" }];
// To add button cell type in Spreadsheet.
excelObj.XLCellType.addCellTypes("A1:B3", {"type" : ej.Spreadsheet.CustomCellType.Button, "text" : "Button1", "background-color" : "green" }, 1);
// To add dropdownlist cell type in spreadsheet.
excelObj.XLCellType.addCellTypes("A1:B3", { 'type': ej.Spreadsheet.CustomCellType.DropDownList, 'dataSourceRange': 'A2:A23', 'dataSourceSheetIndex': 1 }, 2);
// To add the dropdownlist with custom datasource in Spreadsheet.
this.XLCellType.addCellTypes("A1:B3", { 'type': ej.Spreadsheet.CustomCellType.DropDownList, 'dataSource': customList, 'field': "text" });
//To add checkbox cell type in spreadsheet.
excelObj.XLCellType.addCellTypes("A1:B3", { 'type': ej.Spreadsheet.CustomCellType.CheckBox, isChecked:true});
//To add date picker cell type in spreadsheet.
xlObj.XLCellType.addCellTypes("B1", { 'type': ej.Spreadsheet.CustomCellType.DatePicker, 'value': '2/12/2016'});
</script>
CustomCellType
Specifies the cell type in the Spreadsheet,
Name | Description |
---|---|
Button | To insert the Button in cell. |
CheckBox | To insert the Checkbox in cell. |
DatePicker | To insert the Datepicker in cell. |
DropDownList | To insert the Dropdownlist in cell. |
XLCellType.removeCellTypes(range, [sheetIdx])
This method is used to remove cell type from the specified range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Pass the range where you want remove cell type. |
sheetIdx | number | Optional. Pass sheet index. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To remove cell types in Spreadsheet.
excelObj.XLCellType.removeCellTypes("A1:B2", 1);
</script>
XLCFormat
XLCFormat.clearCF([range])
This method is used to clear the applied conditional formatting rules in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | Array|string | Optional. If range is specified, it will clear rules for the specified range else it will use the current selected range. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To clear conditional formatting rules in Spreadsheet.
excelObj.XLCFormat.clearCF([1, 0, 7, 0]);
</script>
XLCFormat.getCFRule(rowIdx, colIdx)
This method is used to get the applied conditional formatting rules as array of objects based on the specified row Index and column Index in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Pass the row index. |
colIdx | number | Pass the column index. |
Returns:
Array
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Gets the conditional formatting rules in Spreadsheet.
excelObj.XLCFormat.getCFRule(2, 0);
</script>
XLCFormat.setCFRule(rule)
This method is used to set the conditional formatting rule in the Spreadsheet.
Name | Type | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
rule |
|
Pass the Object "CFormatOptions"
|
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sets the conditional formatting rules in Spreadsheet
excelObj.XLCFormat.setCFRule({ action: "lessthan", inputs: ["30"], color: "yellowft", range: "H3:H7" });
</script>
XLChart
XLChart.changeTheme(chartId, theme)
This method is used to change the theme of the chart in the Spreadsheet.
Name | Type | Description |
---|---|---|
chartId | string | Pass the chart id. |
theme |
|
Pass the chart theme which want to update. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), chartId = "Spreadsheet_chart1"
var theme = ej.datavisualization.Chart.Theme.Azuredark;
excelObj.XLChart.changeTheme(chartId, theme); // To update chart theme.
</script>
NOTE
You can use the supported chart themes.
XLChart.changeType(chartId, option)
This method is used to change the type of the chart in the Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
chartId | string | Pass the chart id. | ||||||||||||
option |
|
Pass Object ChartOptions.
|
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), chartId = "Spreadsheet_chart1"
excelObj.XLChart.changeType(chartId,{type: "radar", enable3D: false, marker: {visible: false}} ); // To change chart type.
</script>
XLChart.changeDataRange(chartId, xRange, yRange, lRange)
This method is used to change the data range of the chart in the Spreadsheet.
Name | Type | Description |
---|---|---|
chartId | string | Pass the chart id. |
xRange | string | X axis range of chart data. |
yRange | string | Y axis range of chart data. |
lRange | string | Legend range of chart data. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), chartId = "Spreadsheet_chart1";
excelObj.XLChart.changeDataRange(chartId, "A2:A7", "A2:B5", "A1:B1"); // To change the data range of the chart.
</script>
XLChart.createChart([range], [options])
This method is used to create a chart for specified range in Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
range | string|Array | Optional. If range is specified, it will create chart for the specified range else it will use the current selected range. | ||||||||||||
options |
|
Optional.Pass Object ChartOptions.
|
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLChart.createChart("A3:A7",{"type":"stackingcolumn100","enable3D":"true","marker":{"visible":false}}); // To create chart in Spreadsheet.
</script>
XLChart.refreshChart(id, options)
This method is used to refresh the chart in the Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
id | string | To pass the chart Id. | ||||||||||||
options |
|
|
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLChart.refreshChart(id,{"type":"stackingcolumn100","enable3D":"true","marker":{"visible":false}}); // To refresh the chart in Spreadsheet
</script>
XLChart.resizeChart(id, height, width)
This method is used to resize the chart of specified id in the Spreadsheet.
Name | Type | Description |
---|---|---|
id | string | To pass the chart id. |
height | number | To pass height value. |
width | number | To pass the width value. |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLChart.resizeChart("Spreadsheet_chart1", 200, 300); // It is used to resize the chart in Spreadsheet.
</script>
XLChart.updateChartElement(chartId, value)
This method is used to update the chart element, such as axes, titles, data labels, grid lines and legends in the Spreadsheet.
Name | Type | Description |
---|---|---|
chartId | string | Pass the chart id. |
value |
|
Pass chart element value which you want to update. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), chartId = "Spreadsheet_chart1", value = ej.Spreadsheet.ChartProperties.PrimaryHorizontal;
excelObj.XLChart.updateChartElement(chartId, value); // To update chart property.
</script>
ChartProperties
Specifies the chart element value in the Spreadsheet,
Name | Description |
---|---|
DataLabelCenter | Specifies to make the data label center of the chart. |
DataLabelInsideBase | Specifies to make the data label inside base of the chart. |
DataLabelInsideEnd | Specifies to make the data label inside end of the chart. |
DataLabelNone | Specifies to make the data label none of the chart. |
DataLabelOutsideEnd | Specifies to make the data label outside end of the chart. |
LegendsBottom | Specifies to make the legends to bottom of the chart. |
LegendsLeft | Specifies to make the legends to left of the chart. |
LegendsNone | Specifies to make the legends to none of the chart. |
LegendsRight | Specifies to make the legends to right of the chart. |
LegendsTop | Specifies to make the legends to top of the chart. |
PrimaryHorizontal | To set the primary horizontal of the chart. |
PrimaryHorizontalAxisTitle | To set the primary horizontal axis title of the chart. |
PrimaryMajorHorizontal | To set the primary major horizontal of the chart. |
PrimaryMajorVertical | To set the primary major vertical of the chart. |
PrimaryMinorHorizontal | To set the primary minor horizontal of the chart. |
PrimaryMinorVertical | To set the primary minor vertical of the chart. |
PrimaryVertical | To set the primary vertical of the chart. |
PrimaryVerticalAxisTitle | To set the primary vertical axis title of the chart. |
TitleCenter | Specifies to make the title to center of the chart. |
TitleFar | Specifies to make the title to far of the chart. |
TitleNear | Specifies to make the title to near of the chart. |
TitleNone | Specifies to make the title to none of the chart. |
XLChart.switchRowColumn(chartId)
This method is used switch row to columns and vice versa for chart in the Spreadsheet. So that the data is displayed in the chart the way you want.
Name | Type | Description |
---|---|---|
chartId | string | Pass the chart id. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), chartId = "Spreadsheet_chart1";
excelObj.XLChart.switchRowColumn(chartId);
</script>
XLClipboard
XLClipboard.copy()
This method is used to copy the selected cells in the Spreadsheet.
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To copy cells in a Spreadsheet.
excelObj.XLClipboard.copy();
</script>
XLClipboard.cut()
This method is used to cut the selected cells in the Spreadsheet.
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To cut cells in a Spreadsheet
excelObj.XLClipboard.cut();
</script>
XLClipboard.paste()
This method is used to paste the cut or copied cells data in the Spreadsheet.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To paste data in Spreadsheet
excelObj.XLClipboard.paste();
</script>
XLComment
XLComment.deleteComment([range], [sheetIdx], [skipHiddenRow])
This method is used to delete the comment in the specified range in Spreadsheet.
Name | Type | Description |
---|---|---|
range | Array|string | Optional. If range is specified, it will delete comments for the specified range else it will use the current selected range. |
sheetIdx | number | Optional. If sheetIdx is specified, it will delete comment in specified sheet else it will use active sheet. |
skipHiddenRow | boolean |
Optional. Pass
|
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Sends a delete comment request to the Spreadsheet.
excelObj.XLComment.deleteComment("A1:D3", 1, true);
</script>
XLComment.editComment([targetCell])
This method is used to edit the comment in the target Cell in Spreadsheet.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
targetCell |
|
Optional. Pass Object CellIndex.
|
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Sends an edit comment request to the Spreadsheet.
excelObj.XLComment.editComment({rowIndex: 1, colIndex: 1});
</script>
XLComment.findNextComment()
This method is used to find the next comment from the active cell in Spreadsheet.
Returns:
boolean
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Sends a find next comment request to the Spreadsheet.
excelObj.XLComment.findNextComment();
</script>
XLComment.findPrevComment()
This method is used to find the previous comment from the active cell in Spreadsheet.
Returns:
boolean
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Sends a find previous comment request to the Spreadsheet.
excelObj.XLComment.findPrevComment();
</script>
XLComment.getComment(cell)
This method is used to get comment data for the specified cell.
Name | Type | Description |
---|---|---|
cell | Element | Pass the DOM element to get comment data as object. |
Returns:
Object
Example
<div id="Spreadsheet"></div>
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.getComment(excelObj.getCell(1, 5)); // Get the specified cell comment data.
</script>
<script>
// Get the specified cell comment data
$("#Spreadsheet").ejSpreadsheet("getComment", excelObj.getCell(1, 5));
</script>
XLComment.setComment([range], [data], [showEditPanel], [showUserName])
This method is used to set new comment in Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Optional. If we pass the range comment will set in the range otherwise it will set with selected cells. |
data | string | Optional. Pass the comment data. |
showEditPanel | boolean |
Optional. Pass
|
showUserName | boolean |
Optional. Pass
|
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To set comment in Spreadsheet.
excelObj.XLComment.setComment("A2", "Spreadsheet Comment!");
</script>
XLComment.showAllComments()
This method is used to show all the comments in the Spreadsheet.
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Sends a show all comment request to the Spreadsheet.
excelObj.XLComment.showAllComments();
</script>
XLComment.showHideComment([targetCell])
This method is used to show or hide the specific comment in the Spreadsheet.
Name | Type | Description |
---|---|---|
targetCell | Element | Optional. Pass the cell DOM element to show or hide its comment. If pass empty argument active cell will processed. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To show/hide comment request in Spreadsheet.
excelObj.XLComment.showHideComment(excelObj.getCell(1, 5));
</script>
XLCMenu
XLCMenu.addItem(target, itemColl, operation, itemIdx)
This method is used to dynamically add items in the context menu.
Name | Type | Description |
---|---|---|
target |
|
Specifies the context menu type in which the item to be inserted. |
itemColl | Array | Pass the items to be inserted |
operation | string | Specifies the type of operation to be performed |
itemIdx | Number | Specifies the item index |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To add an item in the context menu at last position.
excelObj.XLCMenu.addItem(ej.Spreadsheet.ContextMenu.Cell, [{"text":"Added item 1!!!", "url":"#", "id": "Added item1", "spriteCssClass": "e-icon e-ss-cut" }], 'insert');
// To add an item in the context menu before the particular item index.
excelObj.XLCMenu.addItem(ej.Spreadsheet.ContextMenu.Cell, [{"text":"Added item 2!!!", "url":"#", "id": "Added item2", "spriteCssClass": "e-icon e-ss-copy" }], 'insertbefore', 2);
// To add an item in the context menu after the particular item index.
excelObj.XLCMenu.addItem(ej.Spreadsheet.ContextMenu.Cell, [{"text":"Added item 3!!!", "url":"#", "id": "Added item3", "spriteCssClass": "e-icon e-ss-paste" }], 'insertafter', 2);
</script>
ContextMenuType
Specifies the Context Menu type in the Spreadsheet,
Name | Description |
---|---|
Cell | To specify cell Context Menu. |
RowHeader | To specify row header Context Menu. |
ColumnHeader | To specify column header Context Menu. |
Footer | To specify footer Context Menu. |
XLCMenu.changeDataSource(target, data)
This method is used to change data source in the context menu.
Name | Type | Description |
---|---|---|
target | string | Specifies the context menu type to bind the data source. |
data | Array | Pass the data source to be binded |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLCMenu.changeDataSource(ej.Spreadsheet.ContextMenu.Cell,[{ id: "Comment", text: 'cmnt', parentId: null, spriteCssClass: "e-icon e-ss-newcmnt" }]); // To change the data source in the context menu.
</script>
XLCMenu.disableItem(target, idxColl)
This method is used to disable the items in the context menu.
Name | Type | Description |
---|---|---|
target | string | Specifies the context menu type in which the item to be disabled. |
idxColl | Array | Specifies the Menu Item id collection to be disabled |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLCMenu.disableItem(ej.Spreadsheet.ContextMenu.Cell, ["Cut", "Insert", "Copy"]); // To disable the item in the context menu.
</script>
XLCMenu.enableItem(target, idxColl)
This method is used to enable the items in the context menu.
Name | Type | Description |
---|---|---|
target | string | Specifies the context menu type in which the item to be enabled. |
idxColl | Array | Specifies the Menu Item id collection to be enabled |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLCMenu.enableItem(ej.Spreadsheet.ContextMenu.Cell, ["Cut", "Insert", "Copy"]); // To enable the item in the context menu.
</script>
XLCMenu.hideItem(cMenuType, idxColl)
This method is used to hide the items in the context menu.
Name | Type | Description |
---|---|---|
cMenuType | string | Specifies the context menu type in which the item to be hides. |
idxColl | Array | Specifies the Menu Item id collection to be hides |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLCMenu.hideItem(ej.Spreadsheet.ContextMenu.Cell, ["Cut", "Insert", "Copy"]); // To hide the item in the context menu.
</script>
XLCMenu.removeItem(target, idxColl)
This method is used to remove the items in the context menu.
Name | Type | Description |
---|---|---|
target | string | Specifies the context menu type in which the item to be removed. |
idxColl | Array | Specifies the Menu Item id collection to be removed |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLCMenu.removeItem(ej.Spreadsheet.ContextMenu.Cell, ["Cut", "Insert", "Copy"]); // To remove the item in the context menu.
</script>
XLCMenu.showItem(cMenuType, idxColl)
This method is used to show the items in the context menu.
Name | Type | Description |
---|---|---|
cMenuType | string | Specifies the context menu type in which the item to be shown. |
idxColl | Array | Specifies the Menu Item id collection to be shown |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLCMenu.showItem(ej.Spreadsheet.ContextMenu.Cell, ["Cut", "Insert", "Copy"]); // To show the item in the context menu.
</script>
XLDragDrop
XLDragDrop.moveRangeTo(sourceRange, destinationRange)
This method is used to drag and drop the selected range of cells to destination range in the Spreadsheet.
Name | Type | Description |
---|---|---|
sourceRange | Object|Array | Pass the source range to perform drag and drop. |
destinationRange | Object|Array | Pass the destination range to drop the dragged cells. |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var options = {sourcerange: [1,2,1,2], destinationrange: [1,3,1,6]}
excelObj.XLDragDrop.moveRangeTo(options.sourcerange, options.destinationrange); // To perform drag and drop in Spreadsheet.
</script>
XLDragFill
XLDragFill.autoFill(options)
This method is used to perform auto fill in Spreadsheet.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
options |
|
Pass Object AutoFillObject.
|
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var options = {dataRange: "A1:B1", direction: "right", fillRange:"A1:B2"}
excelObj.XLDragFill.autoFill(options); // To perform autofill in Spreadsheet.
</script>
XLDragFill.hideAutoFillElement()
This method is used to hide the auto fill element in the Spreadsheet.
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To hide auto fill element in Spreadsheet
excelObj.XLDragFill.hideAutoFillElement();
</script>
XLDragFill.hideAutoFillOptions()
This method is used to hide the auto fill options in the Spreadsheet.
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To hide auto fill element in Spreadsheet
excelObj.XLDragFill.hideAutoFillOptions();
</script>
XLDragFill.positionAutoFillElement(isDragFill)
This method is used to set position of the auto fill element in the Spreadsheet.
Name | Type | Description |
---|---|---|
isDragFill | boolean | Pass the isDragFill option as
|
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLDragFill.positionAutoFillElement(false); // set position of the autofill element in Spreadsheet.
</script>
XLEdit
XLEdit.calcNow([sheetIdx])
This method is used to calculate formulas in the specified sheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Optional. If sheet index is specified, then it will calculate formulas in the specified sheet only else it will calculate formulas in all sheets. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLEdit.calcNow(1); // To calculate formulas in specified sheet in Spreadsheet.
</script>
XLEdit.editCell(rowIdx, colIdx, oldData)
This method is used to edit a particular cell based on the row index and column index in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Pass the row index to edit particular cell. |
colIdx | number | Pass the column index to edit particular cell. |
oldData | boolean | Pass
|
Example
<script>
//initialize the Spreadsheet object.
var spreadsheetObj = $("#Spreadsheet").data("ejSpreadsheet");
// Edit particular cell based on row index and column index.
spreadsheetObj.XLEdit.editCell(1, 1, true);
</script>
<script>
// Edit particular cell based on row index and column index.
$("#Spreadsheet").ejSpreadsheet("XLEdit").editCell(1, 1, true);
</script>
XLEdit.getColumnIndexByField(field)
This method is used to get the column index with specified field value in Spreadsheet.
Name | Type | Description |
---|---|---|
field | string | Pass the column field value |
Example
<script>
//initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To get the column index
excelObj.XLEdit.getColumnIndexByField("D");
</script>
XLEdit.getPropertyValue(rowIdx, colIdx, [prop], [sheetIdx])
This method is used to get the property value of particular cell, based on the row and column index in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Pass the row index to get the property value. |
colIdx | number | Pass the column index to get the property value. |
prop | string | Optional. Pass the property name that you want("value", "value2", "type", |
sheetIdx | number | Optional. Pass the index of the sheet. |
Returns:
Object|string|Array
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var ssObj = $("#Spreadsheet").data("ejSpreadsheet");
// Get the cell property value.
excelObj.XLEdit.getPropertyValue(1, 1, "value", 1);
</script>
XLEdit.getPropertyValueByElem(elem, property, sheetIdx)
This method is used to get the property value in specified cell in Spreadsheet.
Name | Type | Description |
---|---|---|
elem | Element | Pass the cell element to get property value. |
property | string | Pass the property name that you want ("value", "value2", "type", |
sheetIdx | number | Pass the index of sheet. |
Returns:
Object|string|Array
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Get the cell property value based on tag element.
excelObj.XLEdit.getPropertyValueByElem(excelObj.getCell(1, 1), "value", 1);
</script>
XLEdit.saveCell()
This method is used to save the edited cell value in the Spreadsheet.
Example
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Save the edited cell value
excelObj.XLEdit.saveCell();
</script>
XLEdit.updateCell(cell, value)
This method is used to update a particular cell value in the Spreadsheet.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
cell |
|
Pass Object CellIndex.
|
|||||||||
value | string|number | Pass the cell value. |
Example
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Update the specified values to a particular cell.
excelObj.XLEdit.updateCell({rowIndex: 1, colIndex: 1}, "product");
</script>
XLEdit.updateCellValue(cellIdx, val, formatClass, sheetIdx)
This method is used to update a particular cell value and its format in the Spreadsheet.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
cellIdx |
|
Pass Object CellIndex.
|
|||||||||
val | string|number | Pass the cell value. | |||||||||
formatClass | string | Pass the class name to update format. | |||||||||
sheetIdx | number | Pass sheet index. |
Example
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), className;
className = excelObj.XLFormat.getFormatHashCode({ color: "#FF0000" });
// To update the specified values in a particular cell.
excelObj.XLEdit.updateCellValue({rowIndex: 1, colIndex: 1}, "product", className, 1);
</script>
XLEdit.updateValue(aRange, val, formatClass, sheetIdx)
This method is used to update a particular cell value and its format in the Spreadsheet.
Name | Type | Description |
---|---|---|
aRange | string | Pass the range. |
val | string|number | Pass the cell value. |
formatClass | string | Pass the class name to update format. |
sheetIdx | number | Pass sheet index. |
Example
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), className;
className = excelObj.XLFormat.getFormatHashCode({ color: "#FF0000" });
// To update the specified values in a particular cell.
excelObj.XLEdit.updateValue("A1", "product", className, 1);
</script>
XLExport
XLExport.export(type, fileName)
This method is used to save the sheet data as Excel ,CSV or PDF document (.xls, .xlsx .csv, .pdf) in Spreadsheet.
NOTE
To use export, user must provide the
excelUrl
,csvUrl
, andpdfUrl
property inexportSettings
.
Name | Type | Description |
---|---|---|
type | string | Pass the export type that you want. |
fileName |
|
Pass the export filename that you want. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To save the worksheet on Excel format.
excelObj.XLExport.export("Excel", "sample");
</script>
ExportType
Specifies the export type in the Spreadsheet,
Name | Description |
---|---|
Excel | Specifies to export the file in Excel format. |
Csv | Specifies to export the file in Csv format. |
Specifies to export the file in Pdf format. |
XLExport.getExportProps()
This method is used to get the export properties in the Spreadsheet.
Returns:
Object
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLExport.getExportProps(); // Gets export properties in Spreadsheet.
</script>
<script>
// Gets export properties in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("getExportProps");
</script>
XLFilter
XLFilter.clearFilter()
This method is used to clear the filter in filtered columns in the Spreadsheet.
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Clear filter for columns in the sheet.
excelObj.XLFilter.clearFilter();
</script>
XLFilter.filter(range)
This method is used to apply filter for the selected range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Pass the range of the selected cells. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To apply filter for specified range of cells.
excelObj.XLFilter.filter("A3:C8");
</script>
XLFilter.filterByActiveCell()
This method is used to apply filter for the column by active cell’s value in the Spreadsheet.
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Apply filter for columns with active cell in the sheet.
excelObj.XLFilter.filterByActiveCell();
</script>
XLFormat
XLFormat.addCustomFormatSpecifier(name, formatSpecifier, type)
This method is used to add the custom Date & Time format and recognize it as a preferred pattern in spreadsheet.
Name | Type | Description |
---|---|---|
name | string | Pass the name for custom format. |
formatSpecifier | string | Pass the custom format string. |
type | string | Pass the type for custom format. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.addCustomFormatSpecifier("formatStr","dd/MM/yyyy", ej.Spreadsheet.CellType.Date) // To add the custom date format in spreadsheet
</script>
XLFormat.addFontFamily(fontName)
This method is used to add the font to the Ribbon font family dropdown.
Name | Type | Description |
---|---|---|
fontName | string | Font name which needs to add into the font family option. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.addFontFamily("Gisha"); // To add the font name into font family option.
</script>
XLFormat.addNewCustomStyle(styleName, options)
This method is used to add the new custom cell style in spreadsheet.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
styleName | string | Pass the style name. | |||||||||
options |
|
Pass Object NumberFormatOptions.
|
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To add the new custom cell style
excelObj.XLFormat.addNewCustomStyle("StyleName",{NumFormat:{type: "currency", decimalPlaces: 4, thousandSeparator: false, formatStr: "{0:C4}"},style:{"background-color": "#cccbcb", color: "#f16667", "font-family": "Arial", "font-size": "16pt", "font-style": "normal", "font-weight": "Bold","text-decoration": "line-through"}})
</script>
XLFormat.applyCustomCellStyle(styleName, range)
This method is used to apply the custom cell style in the specified range.
Name | Type | Description |
---|---|---|
styleName | string | Pass the style name. |
range | String | Pass the range to applied. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To apply the custom cell style to specified range
excelObj.XLFormat.applyCustomCellStyle("StyleName","A1:B4");
</script>
XLFormat.convertToRange(options)
This method is used to convert table range to normal range.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
options |
|
Pass Object TableIDOptions.
|
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.convertToRange({sheetIdx: 1, tableId: 1}); // To convert table range to normal range.
</script>
XLFormat.createTable( tableObject, [range])
This method is used to create a table for the selected range of cells in the Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
tableObject |
|
Pass Object TableOptions.
|
||||||||||||
range | string|Array | Optional. If the range is specified, then it will create table in the specified range else it will use the current selected range. |
Returns:
string
Example
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), tableObj;
tableObj = { header: true, name: "Table1", formatName: "TableStyleLight1"};
// Sends a create table request to the Spreadsheet
excelObj.XLFormat.createTable(tableObj, "A1:C6");
</script>
XLFormat.deleteCustomStyle(styleName)
This method is used to delete the added custom cell style in spreadsheet.
Name | Type | Description |
---|---|---|
styleName | String | Pass the cell style name |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.deleteCustomStyle("StyleName") // To delete the custom cell style.
</script>
XLFormat.format(formatObj, range)
This method is used to set format style and values in a cell or range of cells.
Name | Type | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
formatObj |
|
Pass Object FormatObjectOptions.
|
||||||||||||||||||
range | string | Pass the range to format cells. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sends a format request to the Spreadsheet.
excelObj.XLFormat.format({style:{ "background-color": "#C0C0C0"}}, "A1:C10");
//Default Number format - decimal places is 2.
excelObj.XLFormat.format({ "type": "number" }, "B1");
//Number format with 3 decimal places by default thousandseparator as true.
excelObj.XLFormat.format({ "type": "number", "decimalPlaces": 3, "thousandSeparator": false }, "B3");
//Currency format with formatStr property.
excelObj.XLFormat.format({ "type": "currency", "formatStr":"{0:C3}" }, "B1");
//Accounting format with formatStr property.
excelObj.XLFormat.format({ "type": "accounting", "formatStr":"{0:C2}" }, "A1");
//Percentage format with formatStr property.
excelObj.XLFormat.format({ "type": "percentage", "formatStr":"{0:P2}" }, "B1");
//Short date format with formatStr property.
excelObj.XLFormat.format({ "type": "shortdate", "formatStr":"{0:MM/dd/yyyy}" }, "C1");
excelObj.XLFormat.format({ "type": "longdate" }, "D1");
//Time format with formatStr property.
excelObj.XLFormat.format({ "type": "time", "formatStr":"{0:hh:mm:ss tt}" }, "E1");
excelObj.XLFormat.format({ "type": "scientific" }, "F1");
excelObj.XLFormat.format({ "type": "fraction" }, "G1");
</script>
XLFormat.getBorderFromHashCode(code, isComplete)
This method is used to get the border from hashcode in the Spreadsheet.
Name | Type | Description |
---|---|---|
code | String | Pass the border hashcode. |
isComplete | boolean | Optional. pass `true` to get the complete border object |
Returns:
Object
Example
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To get the border object from the Spreadsheet
excelObj.XLFormat.getBorderFromHashCode("e-border1N1N6N1N1N6N210000001N1N6N",true);
</script>
XLFormat.getFormatClass(classname, isborder)
This method is used to get the format class in spreadsheet.
Name | Type | Description |
---|---|---|
classname | string | Pass the style name. |
isborder | boolean | Optional. Pass true to get the border class. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.getFormatClass(excelObj.getCell(1,1)[0].className);// to get the format class
</script>
XLFormat.getFormatFromHashCode(hashCode)
This method is used to get the format from the given hashcode in spreadsheet.
Name | Type | Description |
---|---|---|
hashCode | String | Pass the hashcode. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.getFormatFromHashCode(excelObj.XLFormat.getFormatClass(excelObj.getCell(1,1)[0].className)); // To get the format from the given hashcode
</script>
XLFormat.getFormatHashCode(style)
This method is used to get the hashcode from the given style object in spreadsheet.
Name | Type | Description | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
style |
|
Pass Object FontOptions.
|
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.getFormatHashCode({"font-family": "Arial Black", "font-weight": "normal", "font-style": "Italic", "font-size": "9pt", "text-decoration": "line-through",color:"#FFFF00"}); // To get the hashcode from the given style object
</script>
XLFormat.getHashCodeClassAsArray(range)
This method is used to get the format as array from the given specified range in spreadsheet.
Name | Type | Description |
---|---|---|
range | String | Pass the range. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.getHashCodeClassAsArray("A1:B4"); // To get the format as an array from the given range.
</script>
XLFormat.modifyCustomStyle(oldStyleName, options, newStyleName)
This method is used to modify the added custom cell style in spreadsheet.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
oldStyleName | string | pass the old style name | |||||||||
options |
|
Pass Object CustomStyleOptions.
|
|||||||||
newStyleName | string | pass the new style name |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.modifyCustomStyle("NewStyle",{ NumFormat: {type: "currency", decimalPlaces: 4, thousandSeparator: false, formatStr: "{0:C4}"},style:{color: "#f16667", "font-family": "Arial", "font-size": "16pt", "font-weight": "Bold"}},"StyleName")); // To modify the custom cell style.
</script>
XLFormat.removeFontFamily(fontName)
This method is used to remove the font from the Ribbon font family dropdown.
Name | Type | Description |
---|---|---|
fontName | string | Font name which needs to remove from the font family drop down. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLFormat.removeFontFamily("Gisha"); // To remove the name from the font family drop down.
</script>
XLFormat.removeStyle(range,options)
This method is used to remove the style in the specified range.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
range | Array|string | Pass the cell range . | |||||||||||||||
options |
|
Optional. Pass Object FormatOptions.
|
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sends a remove style request to the Spreadsheet.
excelObj.XLFormat.removeStyle("E4:F13",{ cellStyle: true, tableStyle: true, format: true, border: true });
</script>
XLFormat.removeTable(tableId)
This method is used to remove table with specified tableId in the Spreadsheet.
Name | Type | Description |
---|---|---|
tableId | number | Pass the tableId that you want to remove. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sends a remove table request to the Spreadsheet.
excelObj.XLFormat.removeTable(1);
</script>
XLFormat.updateDecimalPlaces(type, range)
This method is used to update the decimal places for numeric value for the selected range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
type | string | Pass the decimal places type in IncreaseDecimal/DecreaseDecimal. |
range | string|Array | Pass the range. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To update decimal place value in the range of cells in the Spreadsheet.
excelObj.XLFormat.updateDecimalPlaces("IncreaseDecimal", "A1:C3");
</script>
XLFormat.updateFormat(formatObj, [range])
This method is used to update the format for the selected range of cells in the Spreadsheet.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
formatObj |
|
Pass Object BorderOptions.
|
||||||||||||
range | Array | Optional. If the range is specified, then it will update format in the specified range else it will use the current selected range. |
Example
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), formatObj;
formatObj = { format: ["e-formatFFFF006N2N2N1N1N1N1N2N1N", "", "e-formatFFFF006N2N2N1N1N1N1N2N1N"], leftborder: ["","",""], topborder: ["e-border1N1N6N1N1N6NS1N1N6N11000000"]};
// To update a format in the specified range of cells.
excelObj.XLFormat.updateFormat(formatObj, [1, 0, 3, 0]);
</script>
XLFormat.updateUniqueFormat(formatClass, [range])
This method is used to update the unique format for selected range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
formatClass | string | Pass the unique format class. |
range | Array | Optional. If the range is specified, then it will update format in the specified range else it will use the current selected range. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To update the unique format.
excelObj.XLFormat.updateUniqueFormat("e-formatFFFF006N2N2N251N1N2N", [1, 0, 4, 0]);
</script>
XLFreeze
XLFreeze.freezeColumns(colIdx)
This method is used to freeze columns upto the specified column index in the Spreadsheet.
Name | Type | Description |
---|---|---|
colIdx | number | Index of the column to be freeze. |
Example
<div id="Spreadsheet"></div>
<script>
//Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Freeze a column in the sheet.
excelObj.XLFreeze.freezeColumns(2);
</script>
XLFreeze.freezeLeftColumn()
This method is used to freeze the first column in the Spreadsheet.
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Freeze the first column in the sheet.
excelObj.XLFreeze.freezeLeftColumn();
</script>
XLFreeze.freezePanes(rowIdx, colIdx)
This method is used to freeze rows and columns before the specified cell in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Index of the row to be freeze. |
colIdx | number | Index of the column to be freeze. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Freeze some rows and columns in the sheet.
excelObj.XLFreeze.freezePanes(2, 3);
</script>
XLFreeze.freezeRows(rowIdx)
This method is used to freeze rows upto the specified row index in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Index of the row to be freeze. |
Example
<script>
//initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Freeze a row in the sheet.
excelObj.XLFreeze.freezeRows(2);
</script>
XLFreeze.freezeTopRow()
This method is used to freeze the top row in the Spreadsheet.
Example
<div id="Spreadsheet"></div>
<script>
//Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Freeze the top row in the sheet.
excelObj.XLFreeze.freezeTopRow();
</script>
XLFreeze.unfreezePanes()
This method is used to unfreeze the frozen rows and columns in the Spreadsheet.
Example
<script>
//Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To unfreeze the frozen rows and columns in the sheet.
excelObj.XLFreeze.unfreezePanes();
</script>
XLPivot
XLPivot.clearPivotFieldList(pivotName)
This property is used to clear the pivot table list in Spreadsheet.
Name | Type | Description |
---|---|---|
pivotName | string | Pass the name of the pivot table. |
Example
<script>
// Create Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLPivot.clearPivotFieldList("name"); // Sends a clear pivot field list request to the Spreadsheet.
</script>
XLPivot.createPivotTable(range,location,name,settings,pvt)
This method is used to create pivot table.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
range | string | It specifies the range for which the pivot table is created. | |||||||||||||||
location | string | It specifies the location in which the pivot table is created. | |||||||||||||||
name | string | It specifies the name of the pivot table. | |||||||||||||||
settings |
|
Pass Object PivotOptions.
|
|||||||||||||||
pvt | Object | Pass the pivot range, sheet index, address and data source . |
Returns:
string
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// create a pivot table in the sheet.
var settings = {
rows: [ {fieldName: "Country",},{fieldName: "State",}],
columns: [{fieldName: "Product",}],
values: [{fieldName: "Amount", },{fieldName: "Quantity", } ],
filters: [ {fieldName: "Date", }] };
excelObj.XLPivot.createPivotTable("Sheet1!$A$1:$F$25","Sheet1!$A$1", null, settings);
</script>
XLPivot.deletePivotTable(pivotName)
This method is used to delete the pivot table which is selected.
Name | Type | Description |
---|---|---|
pivotName | string | Pass the name of the pivot table. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Delete pivot table in the sheet.
excelObj.XLPivot.deletePivotTable("name");
</script>
XLPivot.refreshDataSource(name, sheetIdx)
This method is used to refresh data in pivot table.
Name | Type | Description |
---|---|---|
name | string | Optional. Pass the name of the pivot table. |
sheetIdx | number | Optional. Pass the index of the sheet. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLPivot.refreshDataSource(); // Sends a refresh data source request to the Spreadsheet.
</script>
XLPrint
XLPrint.printSelection()
This method is used to print the selected contents in the Spreadsheet.
Example
<script>
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Print the selected area in the sheet.
excelObj.XLPrint.printSelection();
</script>
XLPrint.printSheet()
This method is used to print the entire contents in the active sheet.
Example
<script>
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//Sends a print entire sheet request to the Spreadsheet.
excelObj.XLPrint.printSheet();
</script>
XLResize
XLResize.fitHeight([rowIndexes])
This method is used to fit the height of rows in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIndexes | Array | Optional. Pass row index collection that you want to fit its height. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To fit the height of the rows in Spreadsheet.
excelObj.XLResize.fitHeight([2,3,4,5])
</script>
XLResize.fitWidth([colIndexes])
This method is used to fit the width of columns in the Spreadsheet.
Name | Type | Description |
---|---|---|
colIndexes | Array | Optional. Pass column index collection that you want to fit its width. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To fit the width of the columns in Spreadsheet.
excelObj.XLResize.fitWidth([2,3,4,5])
</script>
XLResize.getColWidth(colIdx)
This method is used to get the column width of the specified column index in the Spreadsheet.
Name | Type | Description |
---|---|---|
colIdx | number | Pass the column index. |
Returns:
number
Example
<div id="Spreadsheet"></div>
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Gets the particular column width in Spreadsheet.
excelObj.XLResize.getColWidth(2);
</script>
XLResize.getRowHeight(rowIdx)
This method is used to get the row height of the specified row index in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Pass the row index which you want to find its height. |
Returns:
number
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Gets the particular row height in Spreadsheet.
excelObj.XLResize.getRowHeight(2);
</script>
<script>
// Gets the particular row height in Spreadsheet.
$("#Spreadsheet").ejSpreadsheet("XLResize.getRowHeight",2);
</script>
XLResize.setColWidth(colIdx, size)
This method is used to set the column width of the specified column index in the Spreadsheet.
Name | Type | Description |
---|---|---|
colIdx | number | Pass the column index. |
size | number | Pass the width value that you want to set. |
Example
<script>
// Initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sets the column width in the Spreadsheet.
excelObj.XLResize.setColWidth(2, 100);
</script>
XLResize.setRowHeight(rowIdx, size)
This method is used to set the row height of the specified row index in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Pass the row index. |
size | number | Pass the height value that you want to set. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// Sets the row height in Spreadsheet.
excelObj.XLResize.setRowHeight(2, 100);
</script>
XLRibbon
XLRibbon.addBackStageItem(pageItem, index)
This method is used to add a new item in the backstage.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
pageItem |
|
Pass Object BackstageOptions.
|
||||||||||||
index | number | pass the index of the item to be added in the backstage. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var addBackStage = { id: "File", text: "File", itemType: ej.Ribbon.ItemType.Tab};
excelObj.XLRibbon.addBackStageItem(addBackStage, 1); // To add a item in the backstage.
</script>
XLRibbon.addContextualTabs(contextualTabSet, index)
This method is used to dynamically add the contextual tabs in the ribbon.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
contextualTabSet |
|
Pass Object ContextualTabOptions.
|
||||||||||||
index | number | pass the index of the contextual tab. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var cTab = { backgroundColor: "#FCFBEB", borderColor: "#F2CC1C", tabs: [{id: "Design", text: "DESIGN",groups: [{ text: "Table Style",type: "custom", contentID: "design" }]}] };
excelObj.XLRibbon.addContextualTabs(cTab, 7); // To add a contextual tab in the ribbon.
</script>
XLRibbon.addMenuItem(item, index)
This method is used to dynamically add the menu item in the file menu.
Name | Type | Description |
---|---|---|
item | Array | Specifies the item to be added |
index | number | pass the index of the menu item. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.addMenuItem([{ id: "SignIn1", text: "AddMenuItem", parentId: "FILE", spriteCssClass: "e-icon e-ssr-cut" }], 2); // To add the menu item in the ribbon.
</script>
XLRibbon.addNamedRange(name, refersTo, [comment], [sheetIdx], [scope])
This method is used to add a new name in the Spreadsheet name manager.
Name | Type | Description |
---|---|---|
name | string | Pass the name that you want to define in name manager. |
refersTo | string | Pass the cell reference. |
comment | string | Optional. Pass comment, if you want. |
sheetIdx | number | Optional. Pass the sheet index. |
scope | string | Optional. Pass the scope of the name manager. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To define new name in the Spreadsheet.
excelObj.XLRibbon.addNamedRange("PRICE_LIST", "=Sheet1!$A$2:$A$7", "Month_Wise", 1, "Sheet1");
</script>
XLRibbon.addTab(tabText, ribbonGroups, index)
This method is used to dynamically add the tab in the ribbon.
Name | Type | Description |
---|---|---|
tabText | string | Specifies the text to be displayed in the tab. |
ribbonGroups | Array | pass the groups to be displayed in the ribbon tab. |
index | number | pass the index of the tab. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var tabGroup = [{ alignType: ej.Ribbon.AlignType.Rows, content: [{ groups: [{ id: "new", text: "New", toolTip: "New",
buttonSettings: {
contentType: ej.ContentType.ImageOnly,
imagePosition: ej.ImagePosition.ImageTop,
prefixIcon: "e-icon e-ssr-cut",
click: "executeAction"
}}], defaults: { type: ej.Ribbon.Type.Button, width: 60, height: 70} }] }];
excelObj.XLRibbon.addTab("Tab2", tabGroup, 2); // To add the tab in the ribbon.
</script>
XLRibbon.addTabGroup(tabIndex, tabGroup, groupIndex)
This method is used to dynamically add the tab group in the ribbon.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
tabIndex | number | Specifies the ribbon tab index. | |||||||||||||||
tabGroup |
|
Pass Object TabOptions.
|
|||||||||||||||
groupIndex | number | pass the index of the ribbon group. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
var ribbonGrp = { text: "Cut", alignType: ej.Ribbon.AlignType.Rows, content: [{ groups: [{ id: "new", text: "CUT",buttonSettings: {
contentType: ej.ContentType.TextAndImage,
imagePosition: ej.ImagePosition.ImageTop,
prefixIcon: "e-icon e-ssr-cut",
click: "executeAction"
} }], defaults: { type: ej.Ribbon.Type.Button, width: 60, height: 70 } }] };
excelObj.XLRibbon.addTabGroup(1, ribbonGrp, 0); // To add the tab group in the ribbon.
</script>
XLRibbon.autoSum(type, range)
This method is used to insert the few type (SUM, MAX, MIN, AVG, COUNT) of formulas in the selected range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
type | string | To pass the type("SUM","MAX","MIN","AVG","COUNT"). |
range | string|Array | If range is specified, it will apply auto sum for the specified range else it will use the current selected range. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.autoSum("SUM", "A2:A6"); // To insert the formula after selected range of cells in Spreadsheet.
</script>
XLRibbon.changeDimension(options)
This method is used to change the dimensions for chart/picture.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
options |
|
Pass Object ShapeOptions.
|
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To change chart/picture dimensions.
excelObj.XLRibbon.changeDimension({id:"Spreadsheet_chart1" , width: 330, height: 500, shapeType: "chart"});
</script>
XLRibbon.disableRibbonItems([idCollection])
This method is used to disable ribbon items in the Spreadsheet.
Name | Type | Description |
---|---|---|
idCollection | Array | Pass the id's of the ribbon items. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.disableRibbonItems(["Spreadsheet_Ribbon_Insert_Illustrations_Pictures"]); // To disable ribbon items.
</script>
XLRibbon.enableRibbonItems([idCollection])
This method is used to enable ribbon items in the Spreadsheet.
Name | Type | Description |
---|---|---|
idCollection | Array | Pass the id's of the ribbon items. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.enableRibbonItems(["Spreadsheet_Ribbon_Insert_Illustrations_Pictures"]); // To enable ribbon items.
</script>
XLRibbon.hideMenu()
This method is used to hide the file menu in the ribbon tab.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.hideMenu(); // To hide the file menu option in the Spreadsheet.
</script>
XLRibbon.removeBackStageItem(index)
This method is used to remove the item from the backstage in the spreadsheet.
Name | Type | Description |
---|---|---|
index | number | Specifies the index of the item to be removed from backstage. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.removeBackStageItem(2); // To remove the item in the backstage.
</script>
XLRibbon.removeMenuItem(index)
This method is used to remove the menu item form file menu in spreadsheet.
Name | Type | Description |
---|---|---|
index | number | Specifies the index of the item to be removed from the file menu. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.removeMenuItem(2); // To remove the item in the file menu.
</script>
XLRibbon.removeNamedRange(name, [scope])
This method is used to delete the defined name in the Spreadsheet name manager.
Name | Type | Description |
---|---|---|
name | string | Pass the defined name that you want to remove from name manager. |
scope | string | Optional. Pass the scope of the name manager. |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To remove the define name in Spreadsheet name manager.
excelObj.XLRibbon.removeNamedRange("PRICE_LIST","Sheet1");
</script>
XLRibbon.removeTab(index, isRemoveMenu)
This method is used to remove the tab form ribbon in the spreadsheet.
Name | Type | Description |
---|---|---|
index | number | Specifies the index of the tab to be removed from the ribbon. |
isRemoveMenu | boolean | pass the boolean value to remove the tab from ribbon |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.removeTab(2, true) // To remove the tab from the ribbon.
</script>
XLRibbon.removeTabGroup(tabIndex, groupText)
This method is used to remove the tab group form ribbon in the spreadsheet.
Name | Type | Description |
---|---|---|
tabIndex | number | Specifies the index of the tab group to be removed from the ribbon. |
groupText | string | Specifies the text to be displayed in the tab group |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.removeTabGroup(1,"Clipboard"); // To remove the tab group from the ribbon.
</script>
XLRibbon.showMenu()
This method is used to show the file menu in the ribbon tab.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.showMenu(); // To show the file menu option in the Spreadsheet.
</script>
XLRibbon.updateMenuItem(item, index)
This method is used to update the menu item in the file menu.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
item |
|
Pass Object MenuItemOptions.
|
|||||||||||||||
index | number | pass the index of the item to be updated |
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLRibbon.updateMenuItem([{ id: "SignIn1", text: "AddMenuItem", parentId: "FILE", spriteCssClass: "e-icon e-ssr-cut"}], 3); // To update the menu item in the file menu
</script>
XLRibbon.updateRibbonIcons()
This method is used to update the ribbon icons in the Spreadsheet.
Example
<script>
//initialize the Spreadsheet object
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To update the ribbon icons in the spreadsheet based on the active sheet index.
excelObj.XLRibbon.updateRibbonIcons();
</script>
XLScroll
XLScroll.scrollToCell(cellAddr)
This method is used to scroll the sheet content to the specified cell address in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string | Pass the cell address that you want to scroll to it. |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To scroll the sheet to the specified cell address.
excelObj.XLScroll.scrollToCell("A30");
</script>
XLSearch
XLSearch.findNext(value, options, sIndex)
This method is used to find the next occurrence of given value in the sheet/workbook.
Name | Type | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
value | string | Pass the value to search. | ||||||||||||||||||
options |
|
Pass Object SearchOptions.
|
||||||||||||||||||
sIndex | number | Pass the sheet index. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To find the next occurrence of given value.
excelObj.XLSearch.findNext("g", {isCSen: false, isEMatch: false, type: "value", mode: "sheet", searchBy: "rows"}, 1);
</script>
XLSearch.findPrevious(value, options, sIndex)
This method is used to find the previous occurrence of given value in the sheet/workbook.
Name | Type | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
value | string | Pass the value to search. | ||||||||||||||||||
options |
|
Pass Object SearchOptions.
|
||||||||||||||||||
sIndex | number | Pass the sheet index. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To find the previous occurrence of given value.
excelObj.XLSearch.findPrevious("g", {isCSen: true, isEMatch: false, type: "value", mode: "sheet", searchBy: "columns"}, 1);
</script>
XLSearch.goTo(range)
This method is used to perform goto operation in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string | Pass the range to perform goto operation. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSearch.goTo("L4"); // To perform goto operation.
</script>
XLSearch.goToSpecial(type, options)
This method is used to perform goto special operation in the Spreadsheet.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
type | string | Pass the type of the cell. | |||||||||||||||
options |
|
Pass Object GotoOptions.
|
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To perform goto special operation.
excelObj.XLSearch.goToSpecial("comments",{isNumber:true, isText:true, isLogical:true, isError: true});
</script>
XLSearch.replaceAllByBook(findData, replaceData, isCSen, isEMatch)
This method is used to find and replace all data by workbook in the Spreadsheet.
Name | Type | Description |
---|---|---|
findData | string | Pass the search data. |
replaceData | string | Pass the replace data. |
isCSen | boolean | Pass
|
isEMatch | boolean | Pass
|
Example
<script>
// initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSearch.replaceAllByBook("Sheet", "Spreadsheet", true, false); // Sends a replace all by workbook request to the Spreadsheet.
</script>
XLSearch.replaceAllBySheet(findData, replaceData, isCSen, isEMatch)
This method is used to find and replace all data by sheet in Spreadsheet.
Name | Type | Description |
---|---|---|
findData | string | Pass the search data. |
replaceData | string | Pass the replace data. |
isCSen | boolean | Pass
|
isEMatch | boolean | Pass
|
Example
<script>
// initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSearch.replaceAllBySheet("Sheet", "Spreadsheet", true, false); // Sends a replace all by sheet request to Spreadsheet
</script>
XLSelection
XLSelection.clearAll()
This method is used to clear the selection of the active sheet in the Spreadsheet.
Example
<div id="Spreadsheet"></div>
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSelection.clearAll(); // To clear selection.
</script>
XLSelection.getSelectedCells(sheetIdx)
This method is used to get the selected cells element based on specified sheet index in the Spreadsheet.
Name | Type | Description |
---|---|---|
sheetIdx | number | Pass the sheet index to get the cells element. |
Returns:
Element
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSelection.getSelectedCells(1); //Gets the selected cells element.
</script>
XLSelection.refreshSelection([range])
This method is used to refresh the selection in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | Array|string | Optional. Pass range to refresh selection. |
Example
<script>
//initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To defined refresh selection in Spreadsheet.
excelObj.XLSelection.refreshSelection("A1:D3");
</script>
XLSelection.selectColumn(colIdx)
This method is used to select a single column in the Spreadsheet.
Name | Type | Description |
---|---|---|
colIdx | number | Pass the column index value. |
Example
<script>
// initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSelection.selectColumn(1); //To select a single column in the active sheet.
</script>
XLSelection.selectColumns(startIdx, endIdx)
This method is used to select entire columns in a specified range (start index and end index) in the Spreadsheet.
Name | Type | Description |
---|---|---|
startIdx | number | Pass the column start index. |
endIdx | number | Pass the column end index. |
Example
<script>
// initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSelection.selectColumns(2, 4); //To select entire columns in the specified range.
</script>
XLSelection.selectRange(range)
This method is used to select the specified range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string | Pass range which want to select. |
Example
<script>
// initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSelection.selectRange("A1:B2"); //To select range of cells in Spreadsheet.
</script>
XLSelection.selectRow(rowIdx)
This method is used to select a single row in the Spreadsheet.
Name | Type | Description |
---|---|---|
rowIdx | number | Pass the row index value. |
Example
<script>
// initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSelection.selectRow(1); //To select a single row in the specified sheet index in Spreadsheet.
</script>
XLSelection.selectRows(startIdx, endIdx)
This method is used to select entire rows in a specified range (start index and end index) in the Spreadsheet.
Name | Type | Description |
---|---|---|
startIdx | number | Pass the start row index. |
endIdx | number | Pass the end row index. |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSelection.selectRows(2, 4); //To select entire rows in the specified range.
</script>
XLSelection.selectSheet()
This method is used to select all cells in active sheet.
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLSelection.selectSheet(); //To select all cells in a sheet
</script>
XLShape
XLShape.changePicture(pictureId, url)
This method is used to change the picture.
Name | Type | Description |
---|---|---|
pictureId | string | Pass the id of the picture. |
url | string | Pass the relative path of the picture. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLShape.changePicture('Spreadsheet_picture1', "img.png"); // To change the picture.
</script>
XLShape.changePictureBorder(pictureId, width, style, color)
This method is used to change the border of the picture.
Name | Type | Description |
---|---|---|
pictureId | string | Pass the id of the picture. |
width | string | Pass the width of the border. |
style | string | Pass the style of the border. |
color | string | Pass the color of the border. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To change the border of the picture.
excelObj.XLShape.changePictureBorder("Spreadsheet_picture1", "1px", "solid", "#89c987");
</script>
XLShape.resetPicture(pictureId, action)
This method is used to reset the picture.
Name | Type | Description |
---|---|---|
pictureId | string | Pass the id of the picture. |
action | string | Pass the type of action. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet"), pictureId = "Spreadsheet_picture1";
excelObj.XLShape.resetPicture(pictureId, "resetSize"); // To reset the picture.
</script>
XLShape.setPicture(range, url, width, height, top, left)
This method is used to set a picture in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string | Pass the range of the cell. |
url | string | Pass the path of the specified image. |
width | number | Optional. Pass the width of the image that you want to set. |
height | number | Optional. Pass the height of the image that you want to set. |
top | number | Optional. Pass the top of the image that you want to set. |
left | number | Optional. Pass the left of the image that you want to set. |
Returns:
string
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To set the picture in the Spreadsheet.
excelObj.XLShape.setPicture('D4', "../images/spreadsheet/ladybug.png", 538, 319);
</script>
XLSort
XLSort.sortByColor(operation, color, range)
This method is used to sort a particular range of cells based on its cell or font color in the Spreadsheet.
Name | Type | Description | |||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
operation | string | Pass
|
|||||||||
color |
|
Pass Object ColorOptions.
|
|||||||||
range | string | Pass the range |
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To sort range based on cell color in the Spreadsheet.
excelObj.XLSort.sortByColor("PutCellColor", {"background-color" : "#EC2024", color: "#6N2N2N"}, "D2:D8"); </script>
XLSort.sortByRange(range, columnName, direction)
This method is used to sort a particular range of cells based on its values in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | Array|string | Pass the range to sort. |
columnName | string | Pass the column name. |
direction | string | Pass the direction to sort |
Returns:
boolean
Example
<script>
// Initialize Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
//To sort the cell value in a specified range in the Spreadsheet.
excelObj.XLSort.sortByRange("A1:D3", "B", "ascending");
</script>
XLSparkline
XLSparkline.createSparkline(dataRange, locationRange, type, options, [sheetIndex])
This method used for creating the sparkline chart for specified range in spreadsheet.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
dataRange | String | Pass the data range | |||||||||||||||
locationRange | String | Pass the location range | |||||||||||||||
type | String | Pass the sparkline chart type | |||||||||||||||
options |
|
Pass Object SparklineOptions.
|
|||||||||||||||
sheetIndex | Number | Pass the sheetIndex |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To create the sparkline chart
excelObj.XLSparkline.createSparkline("C3:C5", "F10", "Line", {markerSettings:{visible:true},highPointColor: "red", negativePointColor: "black", startPointColor: "green"} ); // To create sparkline in Spreadsheet.
</script>
XLSparkline.changePointColor(sparklineId, option, [sheetIdx])
This method used to change the sparkline color and marker point color in the spreadsheet.
Name | Type | Description |
---|---|---|
sparklineId | String | Pass the sparkline ID |
option | Object | pass the high point color as object. |
sheetIdx | Number | Optional. Pass the sheet index |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To change the sparkline color and marker color
excelObj.XLSparkline.changePointColor("Spreadsheet_S1_Column_2_6", {highPointColor: "red"});
</script>
XLSparkline.changeType(sparklineId, type, [sheetIdx])
This method used to change the sparkline type in the spreadsheet.
Name | Type | Description |
---|---|---|
sparklineId | String | Pass the sparkline ID |
type | string | Pass the sparkline type |
sheetIdx | Number | Optional. Pass the sheet index |
Example
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To change the sparkline chart type
excelObj.XLSparkline.changeType("Spreadsheet_S1_Column_2_6", "Line");
</script>
XLValidate
XLValidate.applyDVRules(range, values, type, required, showErrorAlert)
This method is used to apply data validation rules in a selected range of cells based on the defined condition in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | If range is specified, it will apply rules for the specified range else it will use the current selected range. |
values | Array | Pass the validation condition, value1 and value2. |
type | string | Pass the data type. |
required | boolean | Pass
|
showErrorAlert | boolean | Pass
|
Example
<script>
//Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
// To apply validation rule to allow whole number between 15 and 20.
excelObj.XLValidate.applyDVRules("A1:D3", ["Between", "15", "20"], "number" ,true, true);
</script>
XLValidate.clearDV([range])
This method is used to clear the applied validation rules in a specified range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Optional. If range is specified, it will clear rules for the specified range else it will use the current selected range. |
Example
<script>
// Initialize the Spreadsheet object.
var spreadsheetObj = $("#Spreadsheet").data("ejSpreadsheet");
// To clear validation rules in selected cells.
spreadsheetObj.XLValidate.clearDV("A2:A7");
</script>
XLValidate.clearHighlightedValData(range)
This method is used to clear invalid data highlights in the given range.
Name | Type | Description |
---|---|---|
range | string | Pass the range to clear highlights. |
Example
<div id="Spreadsheet"></div>
<script>
// Initialize the Spreadsheet object.
var excelObj = $("#Spreadsheet").data("ejSpreadsheet");
excelObj.XLValidate.clearHighlightedValData("A1:K15"); // To clear highlighted data.
</script>
XLValidate.highlightInvalidData([range])
This method is used to highlight invalid data in a specified range of cells in the Spreadsheet.
Name | Type | Description |
---|---|---|
range | string|Array | Optional. If range is specified, it will clear rules for the specified range else it will use the current selected range. |
Example
<div id="Spreadsheet"></div>
<script>
//initialize the Spreadsheet object
var spreadsheetObj = $("#Spreadsheet").data("ejSpreadsheet");
// To highlight invalid cell data
spreadsheetObj.XLValidate.highlightInvalidData("A2:A7");
</script>
Events
actionBegin
Triggered for every action before its starts.
Name | Type | Description | |||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when actionBegin event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//actionBegin event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
actionBegin: function (args){}
});
</script>
actionComplete
Triggered for every action complete.
Name | Type | Description | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when actionComplete event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//actionComplete event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
actionComplete: function (args){}
});
</script>
autoFillBegin
Triggered when the auto fill operation begins.
Name | Type | Description | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when autoFillBegin event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//autoFillBegin event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
autoFillBegin: function (args){}
});
</script>
autoFillComplete
Triggered when the auto fill operation completes.
Name | Type | Description | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when autoFillComplete event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//autoFillComplete event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
autoFillComplete: function (args){}
});
</script>
beforeBatchSave
Triggered before the batch save.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when beforeBatchSave event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//beforeBatchSave event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
beforeBatchSave: function (args){}
});
</script>
beforeCellFormat
Triggered before the cells to be formatted.
Name | Type | Description | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when beforeCellFormat event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//beforeCellFormat event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
beforeCellFormat: function (args){}
});
</script>
beforeCellSelect
Triggered before the cell selection.
Name | Type | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when beforeCellSelect event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// beforeCellSelect event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
beforeCellSelect: function (args){}
});
</script>
beforeDrop
Triggered before the selected cells are dropped.
Name | Type | Description | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when beforeDrop event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//beforeDrop event for Spreadsheet.
$("#Spreadsheet").ejSpreadsheet({
beforeDrop: function (args){}
});
</script>
beforeEditComment
Triggered while start to edit the comment.
Name | Type | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when beforeEditComment event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// beforeEditComment event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
beforeEditComment: function (args){}
});
</script>
beforeOpen
Triggered before the contextmenu is open.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when beforeOpen event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//beforeOpen event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
beforeOpen: function (args){}
});
</script>
beforePanelOpen
Triggered before the activation panel is open.
Name | Type | Description | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when beforePanelOpen event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// beforePanelOpen event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
beforePanelOpen: function (args){}
});
</script>
cellClick
Triggered when click on sheet cell.
Name | Type | Description | |||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when cellClick event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//cellClick event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
cellClick: function (args){}
});
</script>
cellEdit
Triggered when the cell is edited.
Name | Type | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when cellEdit event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//cellEdit event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
cellEdit: function (args){}
});
</script>
cellFormatting
Triggered while cell is formatting.
Name | Type | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when cellFormatting event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//cellFormatting event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
cellFormatting: function (args){}
});
</script>
cellHover
Triggered when mouse hover on cell in sheets.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when cellHover event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// cellHover event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
cellHover: function (args){}
});
</script>
cellSave
Triggered when save the edited cell.
Name | Type | Description | ||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when cellSave event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//cellSave event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
cellSave: function (args){}
});
</script>
cellSelected
Triggered when the cell is selected.
Name | Type | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when cellSelected event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//cellSelected event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
cellSelected: function (args){}
});
</script>
contextMenuClick
Triggered when click the contextmenu items.
Name | Type | Description | |||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when contextMenuClick event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// contextMenuClick event for Spreadsheet.
$('#Spreadsheet').ejSpreadsheet({
contextMenuClick: function (args){}
});
</script>
drag
Triggered when the selected cells are being dragged.
Name | Type | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when drag event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//drag event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
drag: function (args){}
});
</script>
dragShape
Triggered when you start to drag the picture or chart.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when dragShape event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// dragShape event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
dragShape: function (args){}
});
</script>
dragStart
Triggered when the selected cells are initiated to drag.
Name | Type | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when dragStart event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//dragStart event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
dragStart: function (args){}
});
</script>
drop
Triggered when the selected cells are dropped.
Name | Type | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when drop event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//drop event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
drop: function (args){}
});
</script>
editRangeBegin
Triggered before the range editing starts.
Name | Type | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when editRangeBegin event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// editRangeBegin event for Spreadsheet.
$('#Spreadsheet').ejSpreadsheet({
editRangeBegin: function (args){}
});
</script>
editRangeComplete
Triggered after range editing completes.
Name | Type | Description | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when editRangeComplete event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// editRangeComplete event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
editRangeComplete: function (args){}
});
</script>
keyDown
Triggered when the key is pressed down.
Name | Type | Description | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when keyDown event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// keyDown event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
keyDown: function (args){}
});
</script>
keyUp
Triggered when the key is released.
Name | Type | Description | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when keyUp event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// keyUp event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
keyUp: function (args){}
});
</script>
load
Triggered before the sheet is loaded.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when load event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//load event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
load: function (args){}
});
</script>
loadComplete
Triggered after the sheet is loaded.
Name | Type | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when loadComplete event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// loadComplete event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
loadComplete: function (args){}
});
</script>
menuClick
Triggered every click of the menu item.
Name | Type | Description | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when menuClick event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// menuClick event for Spreadsheet.
$('#Spreadsheet').ejSpreadsheet({
menuClick: function (args){}
});
</script>
onImport
Triggered when a file is imported.
Name | Type | Description | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when onImport event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//onImport event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
onImport: function (args){}
});
</script>
openFailure
Triggered when import sheet is failed to open.
Name | Type | Description | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when openFailure event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
//openFailure event for Spreadsheet
$("#Spreadsheet").ejSpreadsheet({
openFailure: function (args){}
});
</script>
pagerClick
Triggered when pager item is clicked in the Spreadsheet.
Name | Type | Description | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when pagerClick event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// pagerClick event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
pagerClick: function (args){}
});
</script>
resizeStart
Triggered when you start resizing the chart, picture, row and column.
Name | Type | Description | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when resizeStart event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// resizeStart event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
resizeStart: function (args){}
});
</script>
resizeEnd
Triggered after end of resizing the chart, picture, row and column.
Name | Type | Description | |||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when resizeEnd event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// resizeEnd event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
resizeEnd: function (args){}
});
</script>
ribbonClick
Triggered when click on the ribbon.
Name | Type | Description | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
argument | Object | Arguments when ribbonClick event is triggered.
|
Example
<div id="Spreadsheet"></div>
<script>
// ribbonClick event for Spreadsheet
$('#Spreadsheet').ejSpreadsheet({
ribbonClick: function (args){}
});
</script>
scrollStop
Triggered after end of vertical and horizontal scrolling.
Name | Type | Description |
---|---|---|
argument | Object |