JavaScript
EditEdit This Document
Demos
Support
Forum
Upgrade Guide
FREE TRIAL
Search results

Open and Save in JavaScript Spreadsheet control

06 Feb 2023 / 12 minutes to read

The native data format for Spreadsheet is JSON. When you open an excel file, it needs to be read and converted to client side Spreadsheet model. The converted client side Spreadsheet model is sent as JSON which is used to render Spreadsheet. Similarly, when you save the Spreadsheet, the client Spreadsheet model is sent to the server as JSON for processing and saved as Excel file formats. Server configuration is used for this process.

Open

The Spreadsheet control opens an Excel document with its data, style, format, and more. To enable this feature, set allowOpen as true and assign service url to the openUrl property.

User Interface:

In user interface you can open an Excel document by clicking File > Open menu item in ribbon.

The following sample shows the Open option by using the openUrl property in the Spreadsheet control. You can also use the beforeOpen event to trigger before opening an Excel file.

Source
Preview
app.ts
index.html
Copied to clipboard
import { Spreadsheet, BeforeOpenEventArgs } from '@syncfusion/ej2-spreadsheet';

//Initialize the Spreadsheet control
let spreadsheet: Spreadsheet = new Spreadsheet({
    allowOpen: true,
    openUrl: 'https://ej2services.syncfusion.com/development/web-services/api/spreadsheet/open',
    beforeOpen: (args: BeforeOpenEventArgs) => {
        // your code snippets here
    }
});

//Render the initialized Spreadsheet
spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-grids/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

Please find the below table for the beforeOpen event arguments.

Parameter Type Description
file FileList or string or File To get the file stream. FileList - contains length and item index.
File - specifies the file lastModified and file name.
cancel boolean To prevent the open operation.
requestData object To provide the Form data.
  • Use Ctrl + O keyboard shortcut to open Excel documents.
  • The default value of the allowOpen property is true. For demonstration purpose, we have showcased the allowOpen property in previous code snippet.

Open an external URL excel file while initial load

You can achieve to access the remote excel file by using the created event. In this event you can fetch the excel file and convert it to a blob. Convert this blob to a file and open this file by using Spreadsheet component open method.

Source
Preview
app.ts
index.html
Copied to clipboard
import { Spreadsheet } from '@syncfusion/ej2-spreadsheet';

//Initialize the Spreadsheet control
    let spreadsheet: Spreadsheet = new Spreadsheet({
        openUrl: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/open',
        saveUrl: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',
        created: (): void => {
            fetch("https://js.syncfusion.com/demos/ejservices/data/Spreadsheet/LargeData.xlsx") // fetch the remote url
                .then((response) => {
                    response.blob().then((fileBlob) => { // convert the excel file to blob
                    let file = new File([fileBlob], "Sample.xlsx"); //convert the blob into file
                    spreadsheet.open({ file: file }); // open the file into Spreadsheet
                    })
                })
        }
    });
//Render the initialized Spreadsheet
spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-grids/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

To add custom header during open

You can add your own custom header to the open action in the Spreadsheet. For processing the data, it has to be sent from server to client side and adding customer header can provide privacy to the data with the help of Authorization Token. Through the beforeOpen event, the custom header can be added to the request during open action.

Source
Preview
app.ts
index.html
Copied to clipboard
import { BeforeOpenEventArgs, Spreadsheet } from '@syncfusion/ej2-spreadsheet';

//Initialize the Spreadsheet control
    let spreadsheet: Spreadsheet = new Spreadsheet({
        openUrl: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/open',
        saveUrl: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',
        beforeOpen: (args: BeforeOpenEventArgs) => {
            args.requestData['headers'] = {
            Authorization: 'YOUR TEXT',
            }
        }
    });
//Render the initialized Spreadsheet
spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-grids/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

Open excel file into a read-only mode

You can open excel file into a read-only mode by using the openComplete event. In this event, you must protect all the sheets and lock its used range cells by using protectSheet and lockCells methods.

Source
Preview
app.ts
index.html
Copied to clipboard
import {
  Spreadsheet,
  ProtectSettingsModel,
  SheetModel,
  getRangeAddress,
} from '@syncfusion/ej2-spreadsheet';

//Initialize Spreadsheet component

let spreadsheet: Spreadsheet = new Spreadsheet({
  openUrl:
    'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/open',
  saveUrl:
    'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',

  openComplete: (): void => {
    let sheets: SheetModel[] = spreadsheet.sheets;
    for (let index: number = 0; index < sheets.length; index++) {
      let name: string = spreadsheet.sheets[index].name;
      let protectSetting: ProtectSettingsModel = {
        selectCells: true,
        formatCells: false,
      };

      //To protect the sheet using sheet name
      spreadsheet.protectSheet(name, protectSetting);
      let address: string = getRangeAddress([
        0,
        0,
        sheets[index].usedRange.rowIndex,
        sheets[index].usedRange.colIndex,
      ]);
      //To lock the used range cells
      spreadsheet.lockCells(name + '!' + address, true);
    }
  },
});

spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-grids/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

Supported file formats

The following list of Excel file formats are supported in Spreadsheet:

  • MS Excel (.xlsx)
  • MS Excel 97-2003 (.xls)
  • Comma Separated Values (.csv)
  • Excel Macro-Enabled Workbook (.xlsm)
  • Excel Binary Workbook(.xlsb)

Save

The Spreadsheet control saves its data, style, format, and more as Excel file document. To enable this feature, set allowSave as true and assign service url to the saveUrl property.

User Interface:

In user interface, you can save Spreadsheet data as Excel document by clicking File > Save As menu item in ribbon.

The following sample shows the Save option by using the saveUrl property in the Spreadsheet control. You can also use the beforeSave event to trigger before saving the Spreadsheet as an Excel file.

Source
Preview
app.ts
index.html
Copied to clipboard
import { Spreadsheet, SheetModel, BeforeSaveEventArgs } from '@syncfusion/ej2-spreadsheet';
let sheet: SheetModel[] = [{
    rows: [{
            cells: [
                { index: 0, value: 'Order ID',  style: { fontWeight: 'bold' }},
                { value: 'Customer ID', style: { fontWeight: 'bold' }},
                { value: 'Employee ID', style: { fontWeight: 'bold' }},
                { value: 'Ship Name', style: { fontWeight: 'bold' }},
                { value: 'Ship City', style: { fontWeight: 'bold' }},
                { value: 'Ship Address', style: { fontWeight: 'bold' }}
            ]
        },
        {
            cells: [
                { value: '10248' },
                { value: 'VINET' },
                { value: '5' },
                { value: 'Vins et alcools Chevalier' },
                { value: 'Reims' },
                { value: '59 rue de lAbbaye' }
            ]
        },
        {
            cells: [
                { value: '10249' },
                { value: 'TOMSP' },
                { value: '6' },
                { value: 'Toms Spezialitäten' },
                { value: 'Münster' },
                { value: 'Luisenstr. 48' }
            ]
        },
        {
            cells: [
                { value: '10250' },
                { value: 'HANAR' },
                { value: '4' },
                { value: 'Hanari Carnes' },
                { value: 'Rio de Janeiro' },
                { value: 'Rua do Paço, 67' }
            ]
        },
        {
            cells: [
                { value: '10251' },
                { value: 'VICTE' },
                { value: '3' },
                { value: 'Victuailles en stock' },
                { value: 'Lyon' },
                { value: '2, rue du Commerce' }
            ]
        }],
columns: [
        { width: 80 }, { width: 80 }, { width: 82 },
        { width: 160 }, { width: 110 }, { width: 130 }
    ]
}];
//Initialize the Spreadsheet control
let spreadsheet: Spreadsheet = new Spreadsheet({
sheets: sheet,
allowSave: true,
saveUrl: 'https://ej2services.syncfusion.com/development/web-services/api/spreadsheet/save',
beforeSave: (args: BeforeSaveEventArgs) => {
    // your code snippets here
}
});
//Render the initialized Spreadsheet
spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-grids/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

Please find the below table for the beforeSave event arguments.

Parameter Type Description
url string Specifies the save url.
fileName string Specifies the file name.
saveType SaveType Specifies the saveType like Xlsx, Xls, Csv and Pdf.
customParams object Passing the custom parameters from client to server while performing save operation.
isFullPost boolean It sends the form data from client to server, when set to true. It fetches the data from client to server and returns the data from server to client, when set to false.
needBlobData boolean You can get the blob data if set to true.
cancel boolean To prevent the save operations.
  • Use Ctrl + S keyboard shortcut to save the Spreadsheet data as Excel file.
  • The default value of allowSave property is true. For demonstration purpose, we have showcased the allowSave property in previous code snippet.
  • Demo purpose only, we have used the online web service url link.

To send and receive custom params from client to server

Passing the custom parameters from client to server by using beforeSave event.

Source
Preview
app.ts
index.html
Copied to clipboard
import { Spreadsheet } from '@syncfusion/ej2-spreadsheet';
import { data } from './datasource.ts';

//Initialize the SpreadSheet control
let spreadsheet: Spreadsheet = new Spreadsheet({
  sheets: [{
            ranges: [{ dataSource: data }],
            columns: [{ width: 80 }, { width: 80 },{ width: 80},
                      { width: 160 }, { width: 100 }, {width: 150}]
        }],
        saveUrl: 'https://ej2services.syncfusion.com/development/web-services/api/spreadsheet/save',
        beforeSave: (args: BeforeSaveEventArgs) => {
           args.customParams = { customParams: 'you can pass custom params in server side'}; // you can pass the custom params
        }
  }
});
spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-grids/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

Server side code snippets:

Copied to clipboard
public IActionResult Save(SaveSettings saveSettings, string customParams)
    {
        Console.WriteLine(customParams); // you can get the custom params in controller side
        return Workbook.Save(saveSettings);
    }

To add custom header during save

You can add your own custom header to the save action in the Spreadsheet. For processing the data, it has to be sent from client to server side and adding customer header can provide privacy to the data with the help of Authorization Token. Through the fileMenuItemSelect event, the custom header can be added to the request during save action.

Source
Preview
app.ts
index.html
Copied to clipboard
import { createElement } from '@syncfusion/ej2-base';
import { Spreadsheet, SheetModel } from '@syncfusion/ej2-spreadsheet';

import {
  MenuSelectEventArgs,
} from '@syncfusion/ej2-spreadsheet';

let sheet: SheetModel[] = [{
  rows: [{
cells: [
  { index: 0, value: 'Order ID', style: { fontWeight: 'bold' } },
  { value: 'Customer ID', style: { fontWeight: 'bold' } },
  { value: 'Employee ID', style: { fontWeight: 'bold' } },
  { value: 'Ship Name', style: { fontWeight: 'bold' } },
  { value: 'Ship City', style: { fontWeight: 'bold' } },
  { value: 'Ship Address', style: { fontWeight: 'bold' } }
]
  },
  {
cells: [
  { value: '10248' },
  { value: 'VINET' },
  { value: '5' },
  { value: 'Vins et alcools Chevalier' },
  { value: 'Reims' },
  { value: '59 rue de lAbbaye' }
]
  },
  {
cells: [
  { value: '10249' },
  { value: 'TOMSP' },
  { value: '6' },
  { value: 'Toms Spezialitäten' },
  { value: 'Münster' },
  { value: 'Luisenstr. 48' }
]
  },
  {
cells: [
  { value: '10250' },
  { value: 'HANAR' },
  { value: '4' },
  { value: 'Hanari Carnes' },
  { value: 'Rio de Janeiro' },
  { value: 'Rua do Paço, 67' }
]
  },
  {
cells: [
  { value: '10251' },
  { value: 'VICTE' },
  { value: '3' },
  { value: 'Victuailles en stock' },
  { value: 'Lyon' },
  { value: '2, rue du Commerce' }
]
  }],
  columns: [
{ width: 80 }, { width: 80 }, { width: 82 },
{ width: 160 }, { width: 110 }, { width: 130 }
  ]
}];
//Initialize the Spreadsheet control
let spreadsheet: Spreadsheet = new Spreadsheet({
  sheets: sheet,
  openUrl: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/open',
  saveUrl: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',
  fileMenuItemSelect: (args: MenuSelectEventArgs) => {
if (args.item.text === 'Microsoft Excel') {
  args.cancel = true;
  spreadsheet.saveAsJson().then((response) => {
    var formData = new FormData();
    formData.append(
      'JSONData',
      JSON.stringify(response.jsonObject.Workbook)
    );
    formData.append('fileName', 'Sample');
    formData.append('saveType', 'Xlsx');
    fetch(
      'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',
      {
        method: 'POST',
        headers: { Authorization: 'YOUR TEXT' },
        body: formData,
      }
    ).then((response) => {
      response.blob().then((data) => {
        var anchor = createElement('a', {
          attrs: { download: 'Sample.xlsx' },
        });
        var url = URL.createObjectURL(data);
        anchor.href = url;
        document.body.appendChild(anchor);
        anchor.click();
        URL.revokeObjectURL(url);
        document.body.removeChild(anchor);
      });
    });
  });
}
  },
});
//Render the initialized Spreadsheet
spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-grids/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

Supported file formats

The following list of Excel file formats are supported in Spreadsheet:

  • MS Excel (.xlsx)
  • MS Excel 97-2003 (.xls)
  • Comma Separated Values (.csv)
  • Portable Document Format (.pdf)

Methods

To save the Spreadsheet document as an xlsx, xls, csv, or pdf file, by using save method should be called with the url, fileName and saveType as parameters. The following code example shows to save the spreadsheet file as an xlsx, xls, csv, or pdf in the button click event.

Source
Preview
app.ts
index.html
Copied to clipboard
import { Spreadsheet, ColumnModel } from '@syncfusion/ej2-spreadsheet';
import { data } from './datasource.ts';
import { DropDownButton, ItemModel } from "@syncfusion/ej2-splitbuttons";

//Initialize action items.
let items: ItemModel[] = [
  {
text: "Save As xlsx"
  },
  {
text: "Save As xls"
  },
  {
text: "Save As csv"
  },
  {
text: "Save As pdf"
  }
];

// Initialize the DropDownButton component.
let drpDownBtn: DropDownButton = new DropDownButton({
  items: items,
  cssClass: "e-round-corner",
  select: (args: MenuEventArgs) => {
if (args.item.text === 'Save As xlsx')
  spreadsheet.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: "Sample", saveType: "Xlsx"});
if (args.item.text === 'Save As xls')
  spreadsheet.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: "Sample", saveType: "Xls"});
if (args.item.text === 'Save As csv')
  spreadsheet.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',fileName: "Sample", saveType: "Csv"});
if (args.item.text === 'Save As pdf')
  spreadsheet.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',fileName: "Sample", saveType: "Pdf"});
  }
});

// Render initialized DropDownButton.
drpDownBtn.appendTo("#element");


let columns: ColumnModel[] = [{ width: 100 }, { width: 130 },{ width: 96},
{ width: 130 }, { width: 130 },{ width: 96},
{ width: 100 }, { width: 100 },{ width: 110}, { width: 100 }, { width: 130 },{ width: 150}]

let spreadsheet: Spreadsheet = new Spreadsheet({
sheets: [{ ranges: [{ dataSource: data }], columns: columns }],
allowSave: true
});

//Render the initialized Spreadsheet.
spreadsheet.appendTo('#spreadsheet');
Copied to clipboard
<!DOCTYPE html>
<html lang="en">

<head>
            
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <meta name="description" content="Typescript UI Controls" />
        <meta name="author" content="Syncfusion" />
        <link rel="shortcut icon" href="resources/favicon.ico" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-base/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-inputs/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-buttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-lists/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-navigations/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-popups/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-dropdowns/styles/material.css" rel="stylesheet" />
        <link href="//cdn.syncfusion.com/ej2/20.4.48/ej2-spreadsheet/styles/material.css" rel="stylesheet" />
        <link href="styles.css" rel="stylesheet" />
        <script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="system.config.js"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
       <!--Element which is going to render-->
       <div id='loader'>Loading....</div>
       <button id='element'>Save</button>
       <div id='container'>
       <div id="spreadsheet"></div>
       </div>
</body>

</html>

Server Configuration

Import and export are processed in server-side using Spreadsheet server library. The following code snippets shows server configuration using WebAPI service,

Copied to clipboard
[Route("api/[controller]")]
public class SpreadsheetController : Controller
{
    //To open excel file
    [AcceptVerbs("Post")]
    [HttpPost]
    [EnableCors("AllowAllOrigins")]
    [Route("Open")]
    public IActionResult Open(IFormCollection openRequest)
    {
        OpenRequest open = new OpenRequest();
        open.File = openRequest.Files[0];
        return Content(Workbook.Open(open));
    }

    //To save as excel file
    [AcceptVerbs("Post")]
    [HttpPost]
    [EnableCors("AllowAllOrigins")]
    [Route("Save")]
    public IActionResult Save(SaveSettings saveSettings)
    {
        return Workbook.Save(saveSettings);
    }
}

Server Dependencies

Open and save helper functions are shipped in the Syncfusion.EJ2.Spreadsheet package, which is available in Essential Studio and nuget.org. Following list of dependencies required for Spreadsheet open and save operations.

  • Syncfusion.EJ2
  • Syncfusion.EJ2.Spreadsheet
  • Syncfusion.Compression.Base
  • Syncfusion.XlsIO.Base

And also refer this for more information.

See Also

Previous
Next