ASP.NET MVC

Search results

Open and Save in ASP.NET MVC Spreadsheet control

22 Jul 2021 / 5 minutes to read

To import 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. Server configuration is used for this process.

Open

The Spreadsheet component 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.

razor

opencontroller.cs

@Html.EJS().Spreadsheet("spreadsheet").OpenUrl("Open").AllowOpen(true).BeforeOpen("beforeOpen").Render()

<script>

    function beforeOpen(args) {
        // your code snippets here
    }

</script>

public IActionResult Open(IFormCollection openRequest)
{
    OpenRequest open = new OpenRequest();
    open.File = openRequest.Files[0];
    return Content(Workbook.Open(open));
}

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.

razor

opencontroller.cs

@Html.EJS().Spreadsheet("spreadsheet").OpenUrl("Open").AllowOpen(true).Created("created").Render()

<script>

    function created() {
        var spreadsheet = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
        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
                    var file = new File([fileBlob], "Sample.xlsx"); //convert the blob into file
                    spreadsheet.open({ file: file }); // open the file into Spreadsheet
                })
            })
    }

</script>

public IActionResult Open(IFormCollection openRequest)
{
    OpenRequest open = new OpenRequest();
    open.File = openRequest.Files[0];
    return Content(Workbook.Open(open));
}

Save

The Spreadsheet component 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.

razor

savecontroller.cs

@Html.EJS().Spreadsheet("spreadsheet").SaveUrl("Save").AllowSave(true).Render()

public void Save(SaveSettings saveSettings)
{
    Workbook.Save(saveSettings);
}

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.

razor

customParamsController.cs

@Html.EJS().Spreadsheet("spreadsheet").SaveUrl("Save").AllowSave(true).BeforeSave("beforeSave").Sheets((sheet) =>
{
    sheet.Ranges((ranges) =>
    {
        ranges.DataSource(@ViewBag.DefaultData).Add();
    }).Add();
}).Render()

<script>

    function beforeSave(args) {
        args.customParams = { customParams: 'you can pass custom params in server side' }; // you can pass the custom params
    }

</script>

public IActionResult Index()
        {
            List<object> data = new List<object>()
            {
                new { CustomerName= "Romona Heaslip",  Model= "Taurus",  Color= "Aquamarine",  PaymentMode= "Debit Card",  DeliveryDate= "07/11/2015",  Amount= "8529.22" },
                new { CustomerName= "Clare Batterton",  Model= "Sparrow",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/13/2016",  Amount= "17866.19" },
                new { CustomerName= "Eamon Traise",  Model= "Grand Cherokee",  Color= "Blue",  PaymentMode= "Net Banking",  DeliveryDate= "09/04/2015",  Amount= "13853.09" },
                new { CustomerName= "Julius Gorner",  Model= "GTO",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/15/2017",  Amount= "2338.74" },
                new { CustomerName= "Jenna Schoolfield",  Model= "LX",  Color= "Yellow",  PaymentMode= "Credit Card",  DeliveryDate= "10/08/2014",  Amount= "9578.45" },
                new { CustomerName= "Marylynne Harring",  Model= "Catera",  Color= "Green",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/01/2017",  Amount= "19141.62" },
                new { CustomerName= "Vilhelmina Leipelt",  Model= "7 Series",  Color= "Goldenrod",  PaymentMode= "Credit Card",  DeliveryDate= "12/20/2015",  Amount= "6543.30" },
                new { CustomerName= "Barby Heisler",  Model= "Corvette",  Color= "Red",  PaymentMode= "Credit Card",  DeliveryDate= "11/24/2014",  Amount= "13035.06" },
                new { CustomerName= "Karyn Boik",  Model= "Regal",  Color= "Indigo",  PaymentMode= "Debit Card",  DeliveryDate= "05/12/2014",  Amount= "18488.80" },
                new { CustomerName= "Jeanette Pamplin",  Model= "S4",  Color= "Fuscia",  PaymentMode= "Net Banking",  DeliveryDate= "12/30/2014",  Amount= "12317.04" },
                new { CustomerName= "Cristi Espinos",  Model= "TL",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/18/2013",  Amount= "6230.13" },
                new { CustomerName= "Issy Humm",  Model= "Club Wagon",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "02/02/2015",  Amount= "9709.49" },
                new { CustomerName= "Tuesday Fautly",  Model= "V8 Vantage",  Color= "Crimson",  PaymentMode= "Debit Card",  DeliveryDate= "11/19/2014",  Amount= "9766.10" },
                new { CustomerName= "Rosemaria Thomann",  Model= "Caravan",  Color= "Violet",  PaymentMode= "Net Banking",  DeliveryDate= "02/08/2014",  Amount= "7685.49" },
            };
            ViewBag.DefaultData = data;
            return View();
        }
        
public void Save(SaveSettings saveSettings, string customParams)
{
    Workbook.Save(saveSettings);
}

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.

razor

openSaveController.cs

@Html.EJS().DropDownButton("element").Content("Save").Items((IEnumerable<object>)ViewBag.items).Select("itemSelect").Render()

@Html.EJS().Spreadsheet("spreadsheet").Sheets((sheet) =>
{
    sheet.Ranges((ranges) =>
    {
        ranges.DataSource(@ViewBag.defaultData).Add();
    }).Add();
}).Render()

<script>
    function itemSelect(args) {
    var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
    if (args.item.text === 'Save As xlsx')
      spreadsheetObj.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: "Sample", saveType: "Xlsx"});
    if (args.item.text === 'Save As xls')
      spreadsheetObj.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: "Sample", saveType: "Xls"});
    if (args.item.text === 'Save As csv')
      spreadsheetObj.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',fileName: "Sample", saveType: "Csv"});
    if (args.item.text === 'Save As pdf')
      spreadsheetObj.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save',fileName: "Sample", saveType: "Pdf"});
    }
</script>

public IActionResult Index()
        {
            List<object> data = new List<object>()
            {
                new { CustomerName= "Romona Heaslip",  Model= "Taurus",  Color= "Aquamarine",  PaymentMode= "Debit Card",  DeliveryDate= "07/11/2015",  Amount= "8529.22" },
                new { CustomerName= "Clare Batterton",  Model= "Sparrow",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/13/2016",  Amount= "17866.19" },
                new { CustomerName= "Eamon Traise",  Model= "Grand Cherokee",  Color= "Blue",  PaymentMode= "Net Banking",  DeliveryDate= "09/04/2015",  Amount= "13853.09" },
                new { CustomerName= "Julius Gorner",  Model= "GTO",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/15/2017",  Amount= "2338.74" },
                new { CustomerName= "Jenna Schoolfield",  Model= "LX",  Color= "Yellow",  PaymentMode= "Credit Card",  DeliveryDate= "10/08/2014",  Amount= "9578.45" },
                new { CustomerName= "Marylynne Harring",  Model= "Catera",  Color= "Green",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/01/2017",  Amount= "19141.62" },
                new { CustomerName= "Vilhelmina Leipelt",  Model= "7 Series",  Color= "Goldenrod",  PaymentMode= "Credit Card",  DeliveryDate= "12/20/2015",  Amount= "6543.30" },
                new { CustomerName= "Barby Heisler",  Model= "Corvette",  Color= "Red",  PaymentMode= "Credit Card",  DeliveryDate= "11/24/2014",  Amount= "13035.06" },
                new { CustomerName= "Karyn Boik",  Model= "Regal",  Color= "Indigo",  PaymentMode= "Debit Card",  DeliveryDate= "05/12/2014",  Amount= "18488.80" },
                new { CustomerName= "Jeanette Pamplin",  Model= "S4",  Color= "Fuscia",  PaymentMode= "Net Banking",  DeliveryDate= "12/30/2014",  Amount= "12317.04" },
                new { CustomerName= "Cristi Espinos",  Model= "TL",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/18/2013",  Amount= "6230.13" },
                new { CustomerName= "Issy Humm",  Model= "Club Wagon",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "02/02/2015",  Amount= "9709.49" },
                new { CustomerName= "Tuesday Fautly",  Model= "V8 Vantage",  Color= "Crimson",  PaymentMode= "Debit Card",  DeliveryDate= "11/19/2014",  Amount= "9766.10" },
                new { CustomerName= "Rosemaria Thomann",  Model= "Caravan",  Color= "Violet",  PaymentMode= "Net Banking",  DeliveryDate= "02/08/2014",  Amount= "7685.49" },
            };
            List<object> items = new List<object>();
            items.Add(new
            {
                text = "Save As xlsx"
            });
            items.Add(new
            {
                text = "Save As xls"
            });
            items.Add(new
            {
                text = "Save As csv"
            });
            items.Add(new
            {
                text = "Save As pdf"
            });
            ViewBag.items = items;
            ViewBag.DefaultData = data;
            return View();
        }

Server Configuration

In Spreadsheet component, import and export operation processed in server-side, to use importing and exporting in your projects, it is required to create a server with any of the following web services.

WebAPI
WCF Service
ASP.NET MVC Controller Action

Refer the above open and save operation to shows the create a server using WebAPI configuration for Excel import and export. In ASP.NET Core and ASP.NET MVC you can configure the server in controller.

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.

Platforms	Assembly	Nuget Package
ASP.NET Core (Targeting .NET Core)	Syncfusion.EJ2.AspNet.Core Syncfusion.EJ2.Spreadsheet.AspNet.Core Syncfusion.Compression.Net.Core Syncfusion.XlsIO.Net.Core Syncfusion.XlsIORenderer.Net.Core	Syncfusion.EJ2.Spreadsheet.AspNet.Core Syncfusion.XlsIORenderer.Net.Core
ASP.NET MVC4	Syncfusion.EJ2.MVC4 Syncfusion.EJ2.Spreadsheet.AspNet.MVC4 Syncfusion.Compression.Base Syncfusion.XlsIO.AspNet.Mvc4 Syncfusion.ExcelToPdfConverter.AspNet.Mvc4	Syncfusion.EJ2.Spreadsheet.AspNet.MVC4 Syncfusion.ExcelToPdfConverter.AspNet.Mvc4
ASP.NET MVC5	Syncfusion.EJ2.MVC5 Syncfusion.EJ2.Spreadsheet.AspNet.MVC5 Syncfusion.Compression.Base Syncfusion.XlsIO.AspNet.Mvc5 Syncfusion.ExcelToPdfConverter.AspNet.Mvc5	Syncfusion.EJ2.Spreadsheet.AspNet.MVC5 Syncfusion.ExcelToPdfConverter.AspNet.Mvc5

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)

Open and Save in ASP.NET MVC Spreadsheet control

Open

Open an external URL excel file while initial load

Save

To send and receive custom params from client to server

Methods

Server Configuration

Server Dependencies

Supported File Formats

See Also