SpreadsheetComponent

ejs-spreadsheet represents the VueJS Spreadsheet Component.

<ejs-spreadsheet></ejs-spreadsheet>

Properties

activeSheetIndex

number

Specifies active sheet index in workbook.

<div id='Spreadsheet'></div>

new Spreadsheet({
     activeSheetIndex: 2
...
 }, '#Spreadsheet');

Defaults to 0

allowAutoFill

boolean

It allows to enable/disable AutoFill functionalities.

Defaults to true

allowCellFormatting

boolean

It allows you to apply styles (font size, font weight, font family, fill color, and more) to the spreadsheet cells.

Defaults to true

allowChart

boolean

It allows you to insert the chart in spreadsheet.

Defaults to true

allowConditionalFormat

boolean

It allows you to apply conditional formatting to the sheet.

Defaults to true

allowDataValidation

boolean

It allows you to apply validation to the spreadsheet cells.

Defaults to true

allowDelete

boolean

It allows you to delete rows, columns and sheets from spreadsheet.

Defaults to true

allowEditing

boolean

It allows you to add new data or update existing cell data. If it is false, it will act as read only mode.

Defaults to true

allowFiltering

boolean

It allows to enable/disable filter and its functionalities.

Defaults to true

allowFindAndReplace

boolean

It allows to enable/disable find & replace with its functionalities.

Defaults to true

allowHyperlink

boolean

It allows to enable/disable Hyperlink and its functionalities.

Defaults to true

allowImage

boolean

It allows you to insert the image in spreadsheet.

Defaults to true

allowInsert

boolean

It allows you to insert rows, columns and sheets in to the spreadsheet.

Defaults to true

allowMerge

boolean

It allows you to merge the range of cells.

Defaults to true

allowNumberFormatting

boolean

It allows formatting a raw number into different types of formats (number, currency, accounting, percentage, short date, long date, time, fraction, scientific, and text) with built-in format codes.

Defaults to true

allowOpen

boolean

It allows you to open an Excel file (.xlsx, .xls, and .csv) in Spreadsheet.

Defaults to true

allowResizing

boolean

If allowResizing is set to true, spreadsheet columns and rows can be resized.

Defaults to true

allowSave

boolean

It allows you to save Spreadsheet with all data as Excel file (.xlsx, .xls, and .csv).

Defaults to true

allowScrolling

boolean

It specifies whether the Spreadsheet should be rendered with scrolling or not. To customize the Spreadsheet scrolling behavior, use the scrollSettings property.

Defaults to true

allowSorting

boolean

It allows to enable/disable sort and its functionalities.

Defaults to true

allowUndoRedo

boolean

It allows to enable/disable undo and redo functionalities.

Defaults to true

allowWrap

boolean

It allows to enable/disable wrap text feature. By using this feature the wrapping applied cell text can wrap to the next line, if the text width exceeds the column width.

Defaults to true

autoFillSettings

AutoFillSettingsModel

Configures the auto fill settings.

<div id='Spreadsheet'></div>

new Spreadsheet({
     autoFillSettings: {
         fillType: 'FillSeries',
         showFillOptions: true
     }
...
}, '#Spreadsheet');
The autoFillSettings `fillType` property has FOUR values and it is described below:

* CopyCells: To update the copied cells of the selected range.
* FillSeries: To update the filled series of the selected range.
* FillFormattingOnly: To fill the formats only for the selected range.
* FillWithoutFormatting: To fill without the format of the selected range.

Defaults to { fillType: ‘FillSeries’, showFillOptions: true }

cellStyle

CellStyleModel

Specifies the cell style options.

<div id='Spreadsheet'></div>

new Spreadsheet({
     ...
         cellStyle: { fontWeight: 'bold', fontSize: 12,
             fontStyle: 'italic', textIndent: '2pt'
             backgroundColor: '#4b5366', color: '#ffffff'
     },
     ...
 }, '#Spreadsheet');

Defaults to {}

cssClass

string

To specify a CSS class or multiple CSS class separated by a space, add it in the Spreadsheet root element. This allows you to customize the appearance of component.

<div id='spreadsheet'></div>

new Spreadsheet({
 cssClass: 'e-custom1 e-custom2',
 ...
}, '#spreadsheet');

Defaults to ”

definedNames

DefineNameModel[]

Specifies the name for a range and uses it in formula for calculation.

<div id='Spreadsheet'></div>

new Spreadsheet({
     ...
     definedNames: [{ name: 'namedRange1', refersTo: 'Sheet1!A1:B5' }],
     ...
 }, '#Spreadsheet');

Defaults to []

enableClipboard

boolean

It enables or disables the clipboard operations (cut, copy, and paste) of the Spreadsheet.

Defaults to true

enableContextMenu

boolean

It enables or disables the context menu option of spreadsheet. By default, context menu will opens for row header, column header, sheet tabs, and cell.

Defaults to true

enableKeyboardNavigation

boolean

It allows you to interact with cell, sheet tabs, formula bar, and ribbon through the keyboard device.

Defaults to true

enableKeyboardShortcut

boolean

It enables shortcut keys to perform Spreadsheet operations like open, save, copy, paste, and more.

Defaults to true

enablePersistence

boolean

Enable or disable persisting component’s state between page reloads.

Defaults to false

enableRtl

boolean

Enable or disable rendering component in right to left direction.

Defaults to false

height

string | number

Defines the height of the Spreadsheet. It accepts height as pixels, number, and percentage.

<div id='Spreadsheet'></div>

new Spreadsheet({
     height: '550px'
...
 }, '#Spreadsheet');

Defaults to ‘100%’

isProtected

boolean

Specifies to protect the workbook.

Defaults to false

locale

string

Overrides the global culture and localization value for this component. Default global culture is ‘en-US’.

Defaults to ”

openUrl

string

Specifies the service URL to open excel file in spreadsheet.

Defaults to ”

password

string

Specifies the password.

Defaults to ”

saveUrl

string

Specifies the service URL to save spreadsheet as Excel file.

Defaults to ”

scrollSettings

ScrollSettingsModel

Configures the scroll settings.

<div id='Spreadsheet'></div>

new Spreadsheet({
     scrollSettings: {
         isFinite: true,
         enableVirtualization: false
     }
...
 }, '#Spreadsheet');

The allowScrolling property should be true.

Defaults to { isFinite: false, enableVirtualization: true }

selectionSettings

SelectionSettingsModel

Configures the selection settings.

<div id='Spreadsheet'></div>

new Spreadsheet({
     selectionSettings: {
         mode: 'None'
     }
...
}, '#Spreadsheet');
The selectionSettings `mode` property has three values and it is described below:

* None: Disables UI selection.
* Single: Allows single selection of cell, row, or column and disables multiple selection.
* Multiple: Allows multiple selection of cell, row, or column and disables single selection.

Defaults to { mode: ‘Multiple’ }

sheets

SheetModel[]

Configures sheets and its options.

<div id='Spreadsheet'></div>

new Spreadsheet({
     sheets: [{
                 name: 'First Sheet',
                 range: [{ dataSource: data }],
                 rows: [{
                         index: 5,
                         cells: [{ index: 4, value: 'Total Amount:' },
                                 { formula: '=SUM(F2:F30)', style: { fontWeight: 'bold' } }]
                 }]
             }, {
                 name: 'Second Sheet',
                 columns: [{ width: 180 }, { index: 4, width: 130 }]
             }]
...
 }, '#Spreadsheet');

Defaults to []

showFormulaBar

boolean

It shows or hides the formula bar and its features.

Defaults to true

showRibbon

boolean

It shows or hides the ribbon in spreadsheet.

Defaults to true

showSheetTabs

boolean

It shows or hides the sheets tabs, this is used to navigate among the sheets and create or delete sheets by UI interaction.

Defaults to true

width

string | number

Defines the width of the Spreadsheet. It accepts width as pixels, number, and percentage.

<div id='Spreadsheet'></div>

new Spreadsheet({
     width: '550px'
...
 }, '#Spreadsheet');

Defaults to ‘100%’

Methods

Unfreeze

This method is used to unfreeze the frozen rows and columns from the active sheet.

Returns void

addContextMenuItems

To add context menu items.

Returns void

addCustomFunction

To add custom library function.

Returns void

addDataValidation

This method is used to add data validation.

Returns void

addDefinedName

Adds the defined name to the Spreadsheet.

Returns boolean

addFileMenuItems

To add custom file menu items.

Returns void

addHyperlink

To add the hyperlink in the cell

Returns void

addInvalidHighlight

This method is used to highlight the invalid data.

Returns void

addRibbonTabs

To add custom ribbon tabs.

Returns void

addToolbarItems

To add the custom items in Spreadsheet ribbon toolbar.

Returns void

appendTo

Appends the control within the given HTML Div element.

Returns void

applyFilter

Applies the filter UI in the range of cells in the sheet.

Returns void

autoFill

Used to perform autofill action based on the specified range in spreadsheet.

Returns void

autoFit

This method is used to autofit the range of rows or columns

Returns void

cellFormat

Applies the style (font family, font weight, background color, etc…) to the specified range of cells.

Returns void

clear

This method is used to Clear contents, formats and hyperlinks in spreadsheet.

Returns void

clearConditionalFormat

This method is used for remove conditional formatting.

Returns void

clearFilter

Clears the filter changes of the sheet.

Returns void

closeEdit

Cancels the edited state, this will not update any value in the cell.

Returns void

computeExpression

Used to compute the specified expression/formula.

Returns string | number

conditionalFormat

This method is used to add conditional formatting.

Returns void

copy

To copy the specified cell or cells properties such as value, format, style etc…

Returns Promise

cut

To cut the specified cell or cells properties such as value, format, style etc…

Returns Promise

delete

Used to delete rows, columns and sheets from the spreadsheet.

Returns void

deleteChart

Used to delete the chart from spreadsheet.

Returns void

deleteImage

Used to delete the image in spreadsheet.

Returns void

destroy

Destroys the component (detaches/removes all event handlers, attributes, classes, and empties the component element).

Returns void

duplicateSheet

Used to make a duplicate/copy of the sheet in the spreadsheet.

Returns void

enableContextMenuItems

To enable / disable context menu items.

Returns void

enableFileMenuItems

To enable / disable file menu items.

Returns void

enableRibbonTabs

To enable / disable the existing ribbon tabs.

Returns void

enableToolbarItems

Enables or disables the specified ribbon toolbar items or all ribbon items.

Returns void

endEdit

If Spreadsheet is in editable state, you can save the cell by invoking endEdit.

Returns void

find

To find the specified cell value.

Returns void | string

findAll

To Find All the Match values Address within Sheet or Workbook.

Returns string[]

freezePanes

This method is used to freeze rows and columns after the specified cell in the Spreadsheet.

Returns void

getData

Gets the range of data as JSON from the specified address.

Returns Promise

getDisplayText

Gets the formatted text of the cell.

Returns string

getRowData

Used to get a row data from the data source with updated cell value.

Returns Object[]

getSelectAllContent

Get the select all div of spreadsheet

Returns HTMLElement

goTo

Used to navigate to cell address within workbook.

Returns void

hideFileMenuItems

To show/hide the file menu items in Spreadsheet ribbon.

Returns void

hideRibbonTabs

To show/hide the existing ribbon tabs.

Returns void

hideSpinner

To hide showed spinner manually.

Returns void

hideToolbarItems

To show/hide the existing Spreadsheet ribbon toolbar items.

Returns void

insertChart

Used to set the chart in spreadsheet.

Returns void

insertColumn

Used to insert columns in to the spreadsheet.

Returns void

insertImage

Used to set the image in spreadsheet.

Returns void

insertRow

Used to insert rows in to the spreadsheet.

Returns void

insertSheet

Used to insert sheets in to the spreadsheet.

Returns void

lockCells

Applies cell lock to the specified range of cells.

Returns void

merge

Used to merge the range of cells.

Returns void

moveSheet

Used to move the sheets to the specified position in the list of sheets.

Returns void

numberFormat

Applies the number format (number, currency, percentage, short date, etc…) to the specified range of cells.

Returns void

open

Opens the Excel file.

Returns void

openFromJson

Opens the specified JSON object.

The available arguments in options are:

• file: Specifies the spreadsheet model as object or string. And the object contains the jsonObject, which is saved from spreadsheet using saveAsJson method.

Returns void

paste

This method is used to paste the cut or copied cells in to specified address.

Returns void

protectSheet

To protect the particular sheet.

Returns void

redo

To perform the redo operation in spreadsheet.

Returns void

refresh

Used to refresh the spreadsheet in UI level.

Returns void

removeContextMenuItems

To remove existing context menu items.

Returns void

removeDataValidation

This method is used for remove validation.

Returns void

removeDefinedName

Removes the defined name from the Spreadsheet.

Returns boolean

removeHyperlink

To remove the hyperlink in the cell

Returns void

removeInvalidHighlight

This method is used for remove highlight from invalid data.

Returns void

replace

To replace the specified cell value.

Returns void

resize

Used to resize the Spreadsheet.

Returns void

save

Saves the Spreadsheet data to Excel file.

The available arguments in saveOptions are:

• url: Specifies the save URL.
• fileName: Specifies the file name.
• saveType: Specifies the file type need to be saved.

Returns void

saveAsJson

Saves the Spreadsheet data as JSON object.

Returns Promise

selectRange

Selects the cell / range of cells with specified address.

Returns void

setBorder

Sets the border to specified range of cells.

Returns void

setColWidth

Set the width of column.

Returns void

setRowHeight

Set the height of row.

Returns void

showSpinner

By default, Spreadsheet shows the spinner for all its actions. To manually show spinner you this method at your needed time.

Returns void

sort

Sorts the range of cells in the active sheet.

Returns Promise

startEdit

Start edit the active cell.

Returns void

undo

To perform the undo operation in spreadsheet.

Returns void

unprotectSheet

To unprotect the particular sheet.

Returns void

updateAction

To update the action which need to perform.

Returns void

updateCell

To update a cell properties.

Returns void

updateRange

This method is used to update the Range property in specified sheetIndex.

Returns void

updateUndoRedoCollection

To update the undo redo collection in spreadsheet.

Returns void

wrap

This method is used to wrap/unwrap the text content of the cell.

Returns void

Events

actionBegin

EmitType<``BeforeCellFormatArgs|BeforeOpenEventArgs|BeforeSaveEventArgs|BeforeSelectEventArgs|BeforeSortEventArgs|CellEditEventArgs|MenuSelectEventArgs>

Triggers when the Spreadsheet actions (such as editing, formatting, sorting etc..) are starts.

<div id='Spreadsheet'></div>

new Spreadsheet({
      actionBegin: (args: BeforeCellFormatArgs|BeforeOpenEventArgs|BeforeSaveEventArgs|BeforeSelectEventArgs
                   |BeforeSortEventArgs|CellEditEventArgs|MenuSelectEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

actionComplete

EmitType<CellSaveEventArgs>|SaveCompleteEventArgs|Object

Triggers when the spreadsheet actions (such as editing, formatting, sorting etc..) gets completed.

<div id='Spreadsheet'></div>

new Spreadsheet({
      actionComplete: (args: SortEventArgs|CellSaveEventArgs|SaveCompleteEventArgs|Object) => {
      }
     ...
 }, '#Spreadsheet');

afterHyperlinkClick

EmitType<AfterHyperlinkArgs>

Triggers when the Hyperlink function gets completed.

<div id='Spreadsheet'></div>

new Spreadsheet({
      afterHyperlinkClick: (args: AfterHyperlinkArgs ) => {
      }
     ...
 }, '#Spreadsheet');

afterHyperlinkCreate

EmitType<AfterHyperlinkArgs>

Triggers after the hyperlink inserted.

<div id='Spreadsheet'></div>

new Spreadsheet({
      afterHyperlinkCreate: (args: afterHyperlinkArgs ) => {
      }
     ...
 }, '#Spreadsheet');

beforeCellFormat

EmitType<BeforeCellFormatArgs>

Triggers before the cell format applied to the cell.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeCellFormat: (args: BeforeCellFormatArgs) => {
      }
     ...
 }, '#Spreadsheet');

beforeCellRender

EmitType<CellRenderEventArgs>

Triggers before the cell appended to the DOM.

<div id='Spreadsheet'></div>

new Spreadsheet({
     beforeCellRender: (args: CellRenderEventArgs) => {
     }
     ...
 }, '#Spreadsheet');

beforeCellSave

EmitType<CellEditEventArgs>

Triggers when before the cell is saved.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeCellSave: (args: CellEditEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

beforeConditionalFormat

EmitType<ConditionalFormatEventArgs>

Triggers before apply or remove the conditional format from a cell in a range.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeConditionalFormat: (args: ConditionalFormatEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

beforeDataBound

EmitType<Object>

Triggers before the data is populated to the worksheet.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeDataBound: (args: Object) => {
      }
     ...
 }, '#Spreadsheet');

beforeHyperlinkClick

EmitType<BeforeHyperlinkArgs>

Triggers when the Hyperlink is clicked.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeHyperlinkClick: (args: BeforeHyperlinkArgs ) => {
      }
     ...
 }, '#Spreadsheet');

beforeHyperlinkCreate

EmitType<BeforeHyperlinkArgs>

Triggers before insert a hyperlink.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeHyperlinkCreate: (args: BeforeHyperlinkArgs ) => {
      }
     ...
 }, '#Spreadsheet');

beforeOpen

EmitType<BeforeOpenEventArgs>

Triggers before opening an Excel file.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeOpen: (args: BeforeOpenEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

beforeSave

EmitType<BeforeSaveEventArgs>

Triggers before saving the Spreadsheet as Excel file.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeSave: (args: BeforeSaveEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

beforeSelect

EmitType<BeforeSelectEventArgs>

Triggers before the cell or range of cells being selected.

<div id='Spreadsheet'></div>

new Spreadsheet({
     beforeSelect: (args: BeforeSelectEventArgs) => {
     }
     ...
 }, '#Spreadsheet');

beforeSort

EmitType<BeforeSortEventArgs>

Triggers before sorting the specified range.

<div id='Spreadsheet'></div>

new Spreadsheet({
      beforeSort: (args: BeforeSortEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

cellEdit

EmitType<CellEditEventArgs>

Triggers when the cell is being edited.

<div id='Spreadsheet'></div>

new Spreadsheet({
      cellEdit: (args: CellEditEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

cellEditing

EmitType<CellEditEventArgs>

Triggers every time a request is made to access cell information. This will be triggered when editing a cell.

<div id='Spreadsheet'></div>

new Spreadsheet({
      cellEditing: (args: CellEditEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

cellSave

EmitType<CellSaveEventArgs>

Triggers when the edited cell is saved.

<div id='Spreadsheet'></div>

new Spreadsheet({
      cellSave: (args: CellSaveEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

contextMenuBeforeClose

EmitType<BeforeOpenCloseMenuEventArgs>

Triggers before closing the context menu.

<div id='Spreadsheet'></div>

new Spreadsheet({
      contextMenuBeforeClose: (args: BeforeOpenCloseMenuEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

contextMenuBeforeOpen

EmitType<BeforeOpenCloseMenuEventArgs>

Triggers before opening the context menu and it allows customizing the menu items.

<div id='Spreadsheet'></div>

new Spreadsheet({
      contextMenuBeforeOpen: (args: BeforeOpenCloseMenuEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

contextMenuItemSelect

EmitType<MenuSelectEventArgs>

Triggers when the context menu item is selected.

<div id='Spreadsheet'></div>

new Spreadsheet({
      contextMenuItemSelect: (args: MenuSelectEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

created

EmitType<Event>

Triggers when the component is created.

<div id='Spreadsheet'></div>

new Spreadsheet({
      created: () => {
      }
     ...
 }, '#Spreadsheet');

dataBound

EmitType<Object>

Triggers when the data is populated in the worksheet.

<div id='Spreadsheet'></div>

new Spreadsheet({
      dataBound: (args: Object) => {
      }
     ...
 }, '#Spreadsheet');

dataSourceChanged

EmitType<DataSourceChangedEventArgs>

Triggers during data changes when the data is provided as dataSource in the Spreadsheet.

<div id='Spreadsheet'></div>

new Spreadsheet({
      dataSourceChanged: (args: DataSourceChangedEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

dialogBeforeOpen

EmitType<DialogBeforeOpenEventArgs>

Triggers before opening the dialog box.

<div id='Spreadsheet'></div>

new Spreadsheet({
      dialogBeforeOpen: (args: DialogBeforeOpenEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

fileMenuBeforeClose

EmitType<BeforeOpenCloseMenuEventArgs>

Triggers before closing the file menu.

<div id='Spreadsheet'></div>

new Spreadsheet({
      fileMenuBeforeClose: (args: BeforeOpenCloseMenuEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

fileMenuBeforeOpen

EmitType<BeforeOpenCloseMenuEventArgs>

Triggers before opening the file menu.

<div id='Spreadsheet'></div>

new Spreadsheet({
      fileMenuBeforeOpen: (args: BeforeOpenCloseMenuEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

fileMenuItemSelect

EmitType<MenuSelectEventArgs>

Triggers when the file menu item is selected.

<div id='Spreadsheet'></div>

new Spreadsheet({
      fileMenuItemSelect: (args: MenuSelectEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

openComplete

EmitType<Object>

Triggers when the spreadsheet importing gets completed.

<div id='Spreadsheet'></div>

new Spreadsheet({
      openComplete: (args: Object) => {
      }
     ...
 }, '#Spreadsheet');

openFailure

EmitType<OpenFailureArgs>

Triggers when the opened Excel file fails to load.

<div id='Spreadsheet'></div>

new Spreadsheet({
      openFailure: (args: OpenFailureArgs) => {
      }
     ...
 }, '#Spreadsheet');

queryCellInfo

EmitType<CellInfoEventArgs>

Triggered every time a request is made to access cell information.

<div id='Spreadsheet'></div>

new Spreadsheet({
     queryCellInfo: (args: CellInfoEventArgs) => {
     }
     ...
 }, '#Spreadsheet');

saveComplete

EmitType<SaveCompleteEventArgs>

Triggers after saving the Spreadsheet as Excel file.

<div id='Spreadsheet'></div>

new Spreadsheet({
      saveComplete: (args: SaveCompleteEventArgs) => {
      }
     ...
 }, '#Spreadsheet');

select

EmitType<SelectEventArgs>

Triggers after the cell or range of cells is selected.

<div id='Spreadsheet'></div>

new Spreadsheet({
     select: (args: SelectEventArgs) => {
     }
     ...
 }, '#Spreadsheet');

sortComplete

EmitType<SortEventArgs>

Triggers after sorting action is completed.

<div id='Spreadsheet'></div>

new Spreadsheet({
      sortComplete: (args: SortEventArgs) => {
      }
     ...
 }, '#Spreadsheet');