Search results

Open and Save in ASP.NET Core Spreadsheet control

14 Apr 2021 / 3 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 code example shows Open option in the Spreadsheet component.

tagHelper
opencontroller.cs
Copied to clipboard
<ejs-spreadsheet id="spreadsheet"  openUrl="Open" "allowOpen" = "true">

</ejs-spreadsheet>
Copied to clipboard
public IActionResult Open(IFormCollection openRequest)
{
    OpenRequest open = new OpenRequest();
    open.File = openRequest.Files[0];
    return Content(Workbook.Open(open));
}
  • 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.

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 code example shows Save option in the Spreadsheet component.

tagHelper
savecontroller.cs
Copied to clipboard
<ejs-spreadsheet id="spreadsheet" saveUrl="Save" allowSave = "true">

</ejs-spreadsheet>
Copied to clipboard
public void Save(SaveSettings saveSettings)
{
    Workbook.Save(saveSettings);
}
  • 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.

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.

tagHelper
openSaveController.cs
Copied to clipboard
<button id="xlsx" class="e-btn"> Save As xlsx</button>
<button id="xls" class="e-btn"> Save As xls</button>
<button id="csv" class="e-btn"> Save As csv</button>
<button id="pdf" class="e-btn"> Save As pdf</button>

<ejs-spreadsheet id="spreadsheet">
    <e-spreadsheet-sheets>
        <e-spreadsheet-sheet>
            <e-spreadsheet-ranges>
                <e-spreadsheet-range dataSource="ViewBag.DefaultData"></e-spreadsheet-range>
            </e-spreadsheet-ranges>
        </e-spreadsheet-sheet>
    </e-spreadsheet-sheets>
</ejs-spreadsheet>

<script>
    document.getElementById("xlsx").addEventListener('click', xlsx);
    document.getElementById("xls").addEventListener('click', xls);
    document.getElementById("csv").addEventListener('click', csv);
    document.getElementById("pdf").addEventListener('click', pdf);
    var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');

    function xlsx() {
        spreadsheet.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: "Sample", saveType: "Xlsx"});
    }
    function xls() {
        spreadsheet.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: "Sample", saveType: "Xls"});
    }
    function csv() {
        spreadsheet.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: "Sample", saveType: "Csv"});
    }
    function pdf() {
        spreadsheet.save({url: 'https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save', fileName: "Sample", saveType: "Pdf"});
    }
</script>
Copied to clipboard
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();
        }

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.

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)

See Also