Workbook

23 Sep 202524 minutes to read

Represents the Workbook.

Properties

activeSheetIndex number

Specifies the active sheet index in the workbook.

<ejs-spreadsheet [activeSheetIndex]="1">
  <e-sheets>
    <e-sheet name="Car Sales Report"> </e-sheet>
    <e-sheet name="Car Stocks Report"> </e-sheet>
  </e-sheets>
</ejs-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 a 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 data validation to the spreadsheet cells.

Defaults to true

allowDelete boolean

It allows you to delete rows, columns, and sheets from a 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 and replace with its functionalities.

Defaults to true

allowFreezePane boolean

It allows to enable/disable freeze pane functionality in spreadsheet.

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 a spreadsheet.

Defaults to true

allowInsert boolean

It allows you to insert rows, columns, and sheets into 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

allowPrint boolean

Enables or disables the printing functionality in the spreadsheet.

Defaults to true

allowSave boolean

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

Defaults to true

allowSorting boolean

It allows to enable/disable sort and its functionalities.

Defaults to true

autoFillSettings AutoFillSettingsModel

Configures the auto fill settings.
The autoFillSettings fillType property has FOUR types and it is described below:

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

<ejs-spreadsheet [autoFillSettings]="autoFillSettings" [allowAutoFill]="true">
</ejs-spreadsheet>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { AutoFillSettingsModel } from '@syncfusion/ej2-angular-spreadsheet';
@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  // Configures the autoFillSettings.
  public autoFillSettings: AutoFillSettingsModel = {
    fillType: 'CopyCells',
    showFillOptions: true,
  };
}

The allowAutoFill property should be true.

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

calculationMode CalculationMode

Specifies the mode of calculation within the spreadsheet.
Setting the calculation mode to Manual can enhance performance,
particularly when working with multiple sheets at the same time.

• Automatic: Calculations are performed automatically whenever a cell value changes.
• Manual: Calculations are performed only when explicitly triggered, improving performance
  when loading or working with large spreadsheets.

Defaults to ‘Automatic’

cellStyle CellStyleModel

Specifies the cell style options.

<ejs-spreadsheet [cellStyle]="cellStyle">
</ejs-spreadsheet>

import { Component, ViewEncapsulation } from '@angular/core';
import { CellStyleModel } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  // Specifies the cell style options.
  public cellStyle: CellStyleModel = {
    fontWeight: 'bold',
    fontSize: '12pt',
    fontStyle: 'italic',
    textIndent: '2pt',
    backgroundColor: '#4b5366',
    color: '#ffffff',
  };
}

Defaults to {}

definedNames DefineNameModel[]

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

<ejs-spreadsheet [definedNames]="definedNames"> </ejs-spreadsheet>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { DefineNameModel } from '@syncfusion/ej2-spreadsheet';
@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  // Set the specified cell range's name to 'Group1'.
  public definedNames: DefineNameModel[] = [
    { name: 'Group1', refersTo: 'Sheet1!A1:B5' },
  ];
}

Defaults to []

enableNotes boolean

Enables or disables the ability to add or show notes in the Spreadsheet. If the property is set to false, the Spreadsheet will not add notes in the cells and the notes in the existing cells will not be visible.

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.

<ejs-spreadsheet height="600px"> </ejs-spreadsheet>

Defaults to ‘100%’

isProtected boolean

Specifies to protect the workbook.

Defaults to false

listSeparator string

Specifies the list separator which is used as the formula argument separator.

Defaults to ’,’

locale string

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

Defaults to ’’

openSettings OpenSettingsModel

Specifies the options for configuration when opening a document.

<ejs-spreadsheet [openUrl]="openUrl" [saveUrl]="saveUrl" [openSettings]="openSettings">
</ejs-spreadsheet>

import { Component, ViewEncapsulation } from '@angular/core';
import { OpenSettingsModel } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  // Configure the open settings.
  public openSettings: OpenSettingsModel = {
    chunkSize: 100000,
    retryCount: 3
  };
  public openUrl: string =
    'https://services.syncfusion.com/angular/production/api/spreadsheet/open';
  public saveUrl: string =
    'https://services.syncfusion.com/angular/production/api/spreadsheet/save';
}

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 ’’

sheets SheetModel[]

Configures sheets and its options.

<ejs-spreadsheet #spreadsheet>
  <e-sheets>
    <e-sheet name="Car Sales Report">
      <e-ranges>
        <e-range [dataSource]="salesData"></e-range>
      </e-ranges>
      <e-rows>
        <e-row [index]="30">
          <e-cells>
            <e-cell [index]="4" value="Total Amount:"></e-cell>
            <e-cell formula="=SUM(F2:F30)"></e-cell>
          </e-cells>
        </e-row>
      </e-rows>
    </e-sheet>
    <e-sheet name="Car Stock Report"> </e-sheet>
  </e-sheets>
</ejs-spreadsheet>

import { Component, ViewEncapsulation } from '@angular/core';
import { getSalesData } from './data';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  public salesData: Object[] = getSalesData();
}

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.

<ejs-spreadsheet width="600px"> </ejs-spreadsheet>

Defaults to ‘100%’

Methods

Unfreeze

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

Parameter	Type	Description
sheet (optional)	number | string	Specifies the sheet name or index in which the unfreeze operation will perform. By default, active sheet will be considered.

Returns void

addCustomFunction

To add custom library function.

| Parameter | Type | Description |
|——|——|————-|
| functionHandler | string | Function | Custom function handler name |
| functionName (optional) | string | Custom function name |
| formulaDescription (optional) | string | Specifies formula description.

<ejs-spreadsheet #spreadsheet (created)="onCreated()" ></ejs-spreadsheet>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  SQRTHandler(number: number): number {
    return Math.sqrt(number);
  }
  onCreated(): void {
    // Add custom library function to compute the square root of a number.
    this.spreadsheetObj.addCustomFunction(this.SQRTHandler, 'SQRT');
  }
}
``` |

Returns *void*

### addDefinedName

Adds the defined name to the Spreadsheet.

| Parameter | Type | Description |
|------|------|-------------|
| definedName |  [`DefineNameModel`](./defineNameModel) | Specifies the name. |

Returns *boolean*

### addEventListener

Adds the handler to the given event listener.

| Parameter | Type | Description |
|------|------|-------------|
| eventName |  `string` | A String that specifies the name of the event |
| handler |  `Function` | Specifies the call to run when the event occurs. |

Returns *void*

### attachUnloadEvent

Adding unload event to persist data when enable persistence true

Returns *void*

### autoFill

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

| Parameter | Type | Description |
|------|------|-------------|
| fillRange |  `string` | Specifies the fill range. |
| dataRange (*optional*) |  `string` | Specifies the data range. |
| direction (*optional*) |  [`AutoFillDirection`](./autoFillDirection) | Specifies the direction("Down","Right","Up","Left") to be filled. |
| fillType (*optional*) |  [`AutoFillType`](./autoFillType) | Specifies the fill type("FillSeries","CopyCells","FillFormattingOnly","FillWithoutFormatting") for autofill action. |

Returns *void*

### calculateNow

The `calculateNow` method is used to calculate any uncalculated formulas in a spreadsheet.
This method accepts an option to specify whether the calculation should be performed for the entire workbook or a specific sheet.

| Parameter | Type | Description |
|------|------|-------------|
| scope (*optional*) |  `string` | Specifies the scope of the calculation. Acceptable values are `Sheet` or `Workbook`.<br>If not provided, the default scope is `Sheet`.<br>* `Sheet`: Calculates formulas only on the current sheet or a specified sheet.<br>* `Workbook`: Calculates formulas across the entire workbook. |
| sheet (*optional*) |  `number` &#124;  `string` | The index or name of the sheet to calculate if the scope is set to `Sheet`.<br>If not provided and the scope is `Sheet`, the current active sheet will be used. |

Returns *Promise*

### cellFormat

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

```html
<ejs-spreadsheet #spreadsheet></ejs-spreadsheet>
<button (click)="onClick()">To Apply Cell Format</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent, CellStyleModel } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
    selector: 'app-root',
    templateUrl: 'app.component.html',
    styleUrls: ['app.component.css'],
    encapsulation: ViewEncapsulation.None
})

export class AppComponent {
    @ViewChild('spreadsheet')
    public spreadsheetObj: SpreadsheetComponent;
    onClick(): void {
        // Apply the styles to the cells in the specified range.
        this.spreadsheetObj.cellFormat({ fontWeight: 'bold', fontSize: '12pt', backgroundColor: '#279377', color: '#ffffff' }, 'A2:E2');
        this.spreadsheetObj.cellFormat({ verticalAlign: 'middle', fontFamily: 'Axettac Demo' }, 'A2:E12');
        this.spreadsheetObj.cellFormat({ textAlign: 'center' }, 'A2:A12');
        // Setting text-indent to 2 and 4 column.
        let style: CellStyleModel = { textAlign: 'left', textIndent: '8pt' };
        this.spreadsheetObj.cellFormat(style, 'B2:B12');
        this.spreadsheetObj.cellFormat(style, 'D2:D12');
        this.spreadsheetObj.cellFormat({ fontStyle: 'italic', textAlign: 'right' }, 'C3:C12');
        this.spreadsheetObj.cellFormat({ textAlign: 'center' }, 'E2:E12');
    }
}

Parameter	Type	Description
style	CellStyleModel	Specifies the cell style.
range (optional)	string	Specifies the address for the range of cells.

Returns void

clear

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

Parameter	Type	Description
options	ClearOptions	Options for clearing the content, formats and hyperlinks in spreadsheet.

Returns void

computeExpression

Used to compute the specified expression/formula.

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Compute Expression</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Calculate the result of an arithmetic expression or formula.
    let result: string | number =
      this.spreadsheetObj.computeExpression('522+1');
    let value: string | number =
      this.spreadsheetObj.computeExpression('=SUM(F2:F3)');
  }
}

Parameter	Type	Description
formula	string	Specifies the formula(=SUM(A1:A3)) or expression(2+3).

Returns string | number

dataBind

When invoked, applies the pending property changes immediately to the component.

Returns void

delete

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

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Delete</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // To delete rows, columns, and sheets with the specified index.
    this.spreadsheetObj.delete(1, 1, 'Row', 'Sheet1');
    this.spreadsheetObj.delete(2, 2, 'Column', 'Sheet1');
  }
}

Parameter	Type	Description
startIndex (optional)	number	Specifies the start sheet / row / column index.
endIndex (optional)	number	Specifies the end sheet / row / column index.
model (optional)	ModelType	Specifies the delete model type. By default, the model is considered as Sheet. The possible values are, - Row: To delete rows. - Column: To delete columns. - Sheet: To delete sheets.
sheet (optional)	number | string	Specifies the sheet name or index in which the delete operation will perform. By default, active sheet will be considered. It is applicable only for model type Row and Column.

Returns void

deleteChart

Used to delete the chart from spreadsheet.

 <ejs-spreadsheet #spreadsheet (created)="created()"></ejs-spreadsheet>
 <button (click)="onClick()">To Delete Chart</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Delete the chart with the specified chart element ID from the spreadsheet.
    this.spreadsheetObj.deleteChart('Chart');
  }
  created(): void {
    // Insert a chart into a spreadsheet using the chart options specified.
    this.spreadsheetObj.insertChart([
      {
        type: 'Line',
        theme: 'Material',
        isSeriesInRows: false,
        range: 'A1:B5',
        id: 'Chart',
      },
    ]);
  }
}

Parameter	Type	Description
id (optional)	string	Specifies the chart element id.

Returns void

destroy

Destroys the Workbook library.

Returns void

detachUnloadEvent

Removing unload event to persist data when enable persistence true

Returns void

duplicateSheet

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

 <ejs-spreadsheet #spreadsheet></ejs-spreadsheet>
 <button (click)="onClick()">Make Duplicate Sheet</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Make a duplicate/copy of the active sheet in the spreadsheet.
    this.spreadsheetObj.duplicateSheet();
    // Make a duplicate/copy of the specified sheet index in the spreadsheet.
    this.spreadsheetObj.duplicateSheet(0);
  }
}

Parameter	Type	Description
sheetIndex (optional)	number	Specifies the index of the sheet to be duplicated. By default, the active sheet will be duplicated.

Returns void

filter

Filters the range of cells in the sheet.

Parameter	Type	Description
filterOptions (optional)	FilterOptions	Specifies the filterOptions
range (optional)	string	Specifies the range

Returns Promise

freezePanes

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

Parameter	Type	Description
row	number	Specifies the freezed row count.
column	number	Specifies the freezed column count.
sheet (optional)	number | string	Specifies the sheet name or index in which the freeze operation will perform. By default, active sheet will be considered.

Returns void

getData

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

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Get Data</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Return the specified cell address data as JSON.
    this.spreadsheetObj
      .getData('Sheet1!A1:B2')
      .then((data) => console.log(data));
    this.spreadsheetObj.getData('A1:B2').then((data) => console.log(data));
  }
}

Parameter	Type	Description
address	string	Specifies the address for range of cells.

Returns Promise

getDisplayText

Gets the formatted text of the cell.

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Get Display Text</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import {
  SpreadsheetComponent,
  CellModel,
  getCell,
} from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Get the cell model for the given row, column, and sheet indexes.
    let cell: CellModel = getCell(0, 0, this.spreadsheetObj.getActiveSheet());
    // To get the formatted cell value, specify the cell model.
    console.log(this.spreadsheetObj.getDisplayText(cell));
  }
}

Parameter	Type	Description
cell	CellModel	Specifies the cell.

Returns string

getLocalData

Returns the persistence data for component

Returns any

getRootElement

Returns the route element of the component

Returns HTMLElement

getRowData

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

Parameter	Type	Description
index (optional)	number	Specifies the row index.
sheetIndex (optional)	number	Specifies the sheet index. By default, it consider the active sheet index.

Returns Object[]

handleUnload

Handling unload event to persist data when enable persistence true

Returns void

hideColumn

Used to hide/show the columns in spreadsheet.

Parameter	Type	Description
startIndex	number	Specifies the start column index.
endIndex	number	Specifies the end column index.
hide	boolean	Set true / false to hide / show the columns.

Returns void

hideRow

Used to hide/show the rows in spreadsheet.

Parameter	Type	Description
startIndex	number	Specifies the start row index.
endIndex	number	Specifies the end row index.
hide	boolean	To hide/show the rows in specified range.

Returns void

insertChart

Used to set the chart in spreadsheet.

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Insert Chart</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Insert a chart into a spreadsheet using the chart options specified.
    this.spreadsheetObj.insertChart([
      {
        type: 'Line',
        theme: 'Material',
        isSeriesInRows: false,
        range: 'A1',
        id: 'Chart',
      },
    ]);
  }
}

Parameter	Type	Description
chart (optional)	ChartModel[]	Specifies the options to insert chart in spreadsheet

Returns void

insertColumn

Used to insert columns in to the spreadsheet.

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Insert Column</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Insert the column at the starting column index specified.
    this.spreadsheetObj.insertColumn([{ index: 1, width: 95 }], 1);
  }
}

Parameter	Type	Description
startColumn (optional)	number | ColumnModel[]	Specifies the start column index / column model which needs to be inserted.
endColumn (optional)	number	Specifies the end column index.
sheet (optional)	number | string	Specifies the sheet name or index in which the insert operation will perform. By default, active sheet will be considered.

Returns void

insertImage

Used to set the image in spreadsheet.

Parameter	Type	Description
images	ImageModel[]	Specifies the options to insert image in spreadsheet.
range (optional)	string	Specifies the range in spreadsheet.

Returns void

insertRow

Used to insert rows in to the spreadsheet.

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Insert Row</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Insert the row at the starting row index specified.
    this.spreadsheetObj.insertRow(4, 4);
  }
}

Parameter	Type	Description
startRow (optional)	number | RowModel[]	Specifies the start row index / row model which needs to be inserted.
endRow (optional)	number	Specifies the end row index.
sheet (optional)	number | string	Specifies the sheet name or index in which the insert operation will perform. By default, active sheet will be considered.

Returns void

insertSheet

Used to insert sheets in to the spreadsheet.

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Insert Sheet</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Insert the sheet at the starting sheet index specified.
    this.spreadsheetObj.insertSheet(0, 2);
  }
}

Parameter	Type	Description
startSheet (optional)	number | SheetModel[]	Specifies the start sheet index / sheet model which needs to be inserted.
endSheet (optional)	number	Specifies the end sheet index.

Returns void

isValidCell

To determine whether the cell value in a data validation applied cell is valid or not.

Parameter	Type	Description
cellAddress (optional)	string	Address of the cell.

Returns boolean

lockCells

Applies cell lock to the specified range of cells.

<ejs-spreadsheet #spreadsheet>
  <e-sheets>
    <e-sheet name="Car Sales Report"> </e-sheet>
  </e-sheets>
</ejs-spreadsheet>
<button (click)="onClick()">To Lock Cells</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import {
  ProtectSettingsModel,
  SpreadsheetComponent,
} from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    //To protect the worksheet.
    let protectSetting: ProtectSettingsModel = {
      selectCells: true,
      formatCells: false,
      formatRows: false,
      formatColumns: false,
      insertLink: false,
    };
    this.spreadsheetObj.protectSheet('Car Sales Report', protectSetting);
    // To unlock the A2:AZ100 cell range.
    this.spreadsheetObj.lockCells('A2:AZ100', false);
    // To lock the A1:Z1 cell range.
    this.spreadsheetObj.lockCells('A1:Z1', true);
  }
}

Parameter	Type	Description
range (optional)	string	Specifies the address for the range of cells.
isLocked (optional)	boolean	-Specifies the cell is locked or not.

Returns void

merge

Used to merge the range of cells.

<ejs-spreadsheet #spreadsheet (created)="created()"> </ejs-spreadsheet>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  created(): void {
    // To merge the cells in the specified range.
    this.spreadsheetObj.merge('B1:E2');
  }
}

Parameter	Type	Description
range (optional)	string	Specifies the range of cells as address.
type (optional)	MergeType	Specifies the merge type. The possible values are, - All: Merge all the cells between provided range. - Horizontally: Merge the cells row-wise. - Vertically: Merge the cells column-wise.

Returns void

moveSheet

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

 <ejs-spreadsheet #spreadsheet>
     <e-sheets>
         <e-sheet></e-sheet>
         <e-sheet></e-sheet>
         <e-sheet></e-sheet>
    </e-sheets>
 </ejs-spreadsheet>
 <button (click)="onClick()">To Move Sheet</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Moves the active sheet to the specified position.
    this.spreadsheetObj.moveSheet(1);
    // Moves the list of specified sheets to the specified position.
    this.spreadsheetObj.moveSheet(0, [1, 2]);
  }
}

Parameter	Type	Description
position	number	Specifies the position to move a sheet in the list of sheets.
sheetIndexes (optional)	number[]	Specifies the indexes of the sheet to be moved. By default, the active sheet will be moved.

Returns void

numberFormat

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

<ejs-spreadsheet #spreadsheet (created)="created()"> </ejs-spreadsheet>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import {
  getFormatFromType,
  SpreadsheetComponent,
} from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  created(): void {
    // Apply the number format to the specified range of cells.
    this.spreadsheetObj.numberFormat('$#,##0.00', 'E1:E3');
    // Apply the accounting format to the specified range of cells.
    this.spreadsheetObj.numberFormat(getFormatFromType('Accounting'), 'C3:E10');
    // Apply the percentage format to the specified range of cells.
    this.spreadsheetObj.numberFormat('0%', 'F3:F10');
  }
}

Parameter	Type	Description
format	string	Specifies the number format code.
range (optional)	string	Specifies the address of the range of cells.

Returns void

open

Opens the specified excel file or stream.

Parameter	Type	Description
options	OpenOptions	Options for opening the excel file.

Returns void

openFromJson

Opens the specified JSON object.

 <ejs-spreadsheet #spreadsheet [openUrl]="openUrl" [saveUrl]="saveUrl"></ejs-spreadsheet>
 <button (click)="save()">Save Json</button>
 <button (click)="open()">Load Json</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  public openUrl: string =
    'https://services.syncfusion.com/angular/production/api/spreadsheet/open';
  public saveUrl: string =
    'https://services.syncfusion.com/angular/production/api/spreadsheet/save';
  public response;
  save(): void {
    // Save the spreadsheet data as JSON.
    this.spreadsheetObj.saveAsJson().then((Json) => (this.response = Json));
  }
  open(): void {
    // Load the JSON data to the spreadsheet.
    this.spreadsheetObj.openFromJson({ file: this.response.jsonObject });
  }
}

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.
• triggerEvent: Specifies whether to trigger the openComplete event or not.

Parameter	Type	Description
options	Object	Options for opening the JSON object.
jsonConfig (optional)	SerializationOptions	Specify the serialization options to customize the loading of the JSON data.

Returns void

protectSheet

Protect the active sheet based on the protect sheetings.

Parameter	Type	Description
sheet (optional)	number | string	Specifies the sheet to protect.
protectSettings (optional)	ProtectSettingsModel	Specifies the protect settings of the sheet.
password (optional)	string	Specifies the password to protect

Returns void

refresh

Applies all the pending property changes and render the component again.

Returns void

removeDefinedName

Removes the defined name from the Spreadsheet.

Parameter	Type	Description
definedName	string	Specifies the name.
scope	string	Specifies the scope of the defined name.

Returns boolean

removeEventListener

Removes the handler from the given event listener.

Parameter	Type	Description
eventName	string	A String that specifies the name of the event to remove
handler	Function	Specifies the function to remove

Returns void

save

Saves the Spreadsheet data to Excel file.

 <ejs-spreadsheet #spreadsheet [openUrl]="openUrl" [saveUrl]="saveUrl"></ejs-spreadsheet>
 <button (click)="onClick()">To Save</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  public openUrl: string =
    'https://services.syncfusion.com/angular/production/api/spreadsheet/open';
  public saveUrl: string =
    'https://services.syncfusion.com/angular/production/api/spreadsheet/save';
  onClick(): void {
    // Save the spreadsheet data to an Excel file with the filename and extension you specify.
    this.spreadsheetObj.save({
      url: 'https://services.syncfusion.com/angular/production/api/spreadsheet/save',
      fileName: 'Worksheet',
      saveType: 'Xlsx',
    });
  }
}

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.

Parameter	Type	Description
saveOptions	SaveOptions	Options for saving the excel file.
jsonConfig (optional)	SerializationOptions	Specify the serialization options to customize the JSON output.

Returns void

saveAsJson

Saves the Spreadsheet data as JSON object.

 <ejs-spreadsheet #spreadsheet [openUrl]="openUrl" [saveUrl]="saveUrl"></ejs-spreadsheet>
 <button (click)="save()">Save Json</button>
 <button (click)="open()">Load Json</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  public openUrl: string =
    'https://services.syncfusion.com/angular/production/api/spreadsheet/open';
  public saveUrl: string =
    'https://services.syncfusion.com/angular/production/api/spreadsheet/save';
  public response;
  save(): void {
    // Save the spreadsheet data as JSON.
    this.spreadsheetObj.saveAsJson().then((Json) => (this.response = Json));
  }
  open(): void {
    // Load the JSON data to the spreadsheet.
    this.spreadsheetObj.openFromJson({ file: this.response.jsonObject });
  }
}

Parameter	Type	Description
jsonConfig (optional)	SerializationOptions	Specify the serialization options to customize the JSON output.

Returns Promise

setBorder

Sets the border to specified range of cells.

 <ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
 <button (click)="onClick()">To Set Border</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Sets the border to the specified cell range.
    this.spreadsheetObj.setBorder(
      { border: '1px solid #000000' },
      'C6:G8',
      'Outer'
    );
    this.spreadsheetObj.setBorder({ border: '1px solid #000000' });
  }
}

Parameter	Type	Description
style	CellStyleModel	Specifies the style property which contains border value.
range (optional)	string	Specifies the range of cell reference. If not specified, it will considered the active cell reference.
type (optional)	BorderType	Specifies the range of cell reference. If not specified, it will considered the active cell reference.
isUndoRedo (optional)	boolean	Specifies is undo redo or not.

Returns void

sort

Sorts the range of cells in the active Spreadsheet.

Parameter	Type	Description
sortOptions (optional)	SortOptions	options for sorting.
range (optional)	string	address of the data range.
previousSort (optional)	SortCollectionModel[]	specifies previous sort collection.

Returns Promise

unMerge

Used to split the merged cell into multiple cells.

<ejs-spreadsheet #spreadsheet (created)="onCreated()"></ejs-spreadsheet>
<button (click)="onClick()">To UnMerge</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    //To split the merged cell into multiple cells.
    this.spreadsheetObj.unMerge('B1:E2');
  }
  onCreated(): void {
    this.spreadsheetObj.merge('B1:E2');
  }
}

Parameter	Type	Description
range (optional)	string	Specifies the range of cells as address.

Returns void

unfreezePanes

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

Parameter	Type	Description
sheet (optional)	number | string	Specifies the sheet name or index in which the unfreeze operation will perform. By default, active sheet will be considered.

Returns void

unprotectSheet

Unprotect the active sheet.

Parameter	Type	Description
sheet	number | string	Specifies the sheet to Unprotect.

Returns void

updateCell

Updates the properties of a specified cell.

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Update Cell</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // Update the properties of a cell to the specified cell address.
    this.spreadsheetObj.updateCell({ value: 'Custom Value' }, 'A3');
    this.spreadsheetObj.updateCell({ value: 'Custom Value' }, 'Sheet1!A4');
  }
}

Parameter	Type	Description
cell	CellModel	The properties to update for the specified cell.
address (optional)	string	The address of the cell to update. If not provided, the active cell’s address will be used.
enableDependentCellUpdate (optional)	boolean	Specifies whether dependent cells should also be updated. Default value is true.

Returns void

updateRange

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

Returns void

wrap

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

<ejs-spreadsheet #spreadsheet ></ejs-spreadsheet>
<button (click)="onClick()">To Wrap</button>

import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
  selector: 'app-root',
  templateUrl: 'app.component.html',
  styleUrls: ['app.component.css'],
  encapsulation: ViewEncapsulation.None,
})
export class AppComponent {
  @ViewChild('spreadsheet')
  public spreadsheetObj: SpreadsheetComponent;
  onClick(): void {
    // To wrap/unwrap the cell's text content with the specified address.
    this.spreadsheetObj.wrap('B5', true);
  }
}

Parameter	Type	Description
address	string	Address of the cell to be wrapped.
wrap	boolean	Set false if the text content of the cell to be unwrapped.

Returns void

Inject

Dynamically injects the required modules to the component.

Parameter	Type	Description
moduleList	Function[]	?

Returns void

Events

beforeCellFormat EmitType<BeforeCellFormatArgs>

Triggers before the cell format applied to the cell.

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

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

beforeCellUpdate EmitType<BeforeCellUpdateArgs>

Triggers before changing any cell properties.

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

 new Spreadsheet({
     beforeCellUpdate: (args: BeforeCellUpdateArgs) => {
     }
     ...
 }, '#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');

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');