Exporting grid in server in ASP.NET Core Grid component

11 Mar 202524 minutes to read

The Syncfusion Grid component in ASP.NET Core provides a powerful option to export data to Excel on the server side using the Grid server export library. This allows you to perform Excel export operations on the server, providing additional security and flexibility. To enable server-side Excel exporting, you need to configure the server dependencies and implement the necessary server configuration.

Server dependencies

To enable the server-side export functionality for the Syncfusion Grid component in ASP.NET Core, you need to include the required dependencies for Grid server-side Excel exporting. These dependencies are essential for handling the export process on the server-side.

The server-side export functionality is provided in the Syncfusion.EJ2.GridExport package, which is available in Essential Studio and can also be obtained from nuget.org.

The following list of dependencies is required for Grid server-side Excel exporting action:

  • Syncfusion.EJ2: This is the core package that includes the main components and functionalities of Syncfusion Essential JS 2.

  • Syncfusion.EJ2.GridExport: This package contains the server-side export functionality specifically for the Grid component. It provides the necessary APIs and tools to handle data export to Excel on the server.

Server configuration

To export the grid data to a Excel document on the server side, you need to perform the following server configuration using an ASP.NET Core Controller Action:

  1. Set up the necessary dependencies and imports in your server-side code.

  2. Define a controller action that handles the server-side Excel export. This action should receive the Grid properties from the client-side and initiate the Excel export operation on the server.

  3. Use the serverExcelExport method to pass the Grid properties to the server exporting action. This method allows you to specify the server action URL and other export options.

The following example shows server configuration using ASP.NET Core Controller Action.

<ejs-grid id="grid" dataSource="@ViewBag.dataSource" height="400px" toolbarClick="toolbarClick" toolbar=@(new List<string>() { "ExcelExport" })>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="CustomerID" width="100"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" format="C2" width="100" textAlign="Right"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

<script>
    function toolbarClick(args) {
        var grid = document.getElementById("grid").ej2_instances[0];
        grid.serverExcelExport("/Home/ExcelExport");
    }
</script>
public ActionResult Index()
{
    ViewBag.dataSource = OrdersDetails.GetAllRecords();
    return View();
}
public ActionResult ExcelExport(string gridModel)
{
    GridExcelExport excelExporter = new GridExcelExport();
    Grid gridProperties = ConvertGridObject(gridModel);
    return excelExporter.ExcelExport<OrdersDetails>(gridProperties, OrdersDetails.GetAllRecords());
}
private Grid ConvertGridObject(string gridProperty)
{
    Grid GridModel = (Grid)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(Grid));
    GridColumnModel gridColumns = (GridColumnModel)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(GridColumnModel));
    GridModel.Columns = gridColumns.columns;
    return GridModel;
}
public class GridColumnModel
{
    public List<GridColumn> columns { get; set; }
}

Server configuration

NOTE

Refer to the GitHub sample for quick implementation and testing from here.

CSV Export in server side

To perform CSV export on the server-side, you can use the serverCsvExport method, which sends the Grid properties to the server for processing and generating the CSV file.

To initiate the CSV export, you can invoke the serverCsvExport method within the ToolbarClick event. Upon triggering the event, the server will deserialize the Grid properties and pass them to the CsvExport method, which will handle exporting the properties to the CSV format.

Here’s an example of how you can accomplish CSV export on the server-side:

<ejs-grid id="grid" dataSource="@ViewBag.dataSource" height="400px" toolbarClick="toolbarClick" toolbar=@(new List<string>() { "CsvExport" })>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="CustomerID" width="100"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" format="C2" width="100" textAlign="Right"></e-grid-column>
    </e-grid-columns>
</ejs-grid>

<script>
    function toolbarClick(args) {
        var grid = document.getElementById("grid").ej2_instances[0];
        grid.serverCsvExport("/Home/CsvGridExport");
    }
</script>
public IActionResult Index()
{
    ViewBag.dataSource = OrdersDetails.GetAllRecords();
    return View();
}
public ActionResult CsvGridExport([FromForm] string gridModel)
{
    GridExcelExport excelExporter = new GridExcelExport();
    Grid gridProperties = ConvertGridObject(gridModel);
    return excelExporter.CsvExport<OrdersDetails>(gridProperties, OrdersDetails.GetAllRecords());
}
private Grid ConvertGridObject(string gridProperty)
{
    Grid GridModel = (Grid)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(Grid));
    GridColumnModel gridColumns = (GridColumnModel)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(GridColumnModel));
    GridModel.Columns = gridColumns.columns;
    return GridModel;
}

public class GridColumnModel
{
    public List<GridColumn> columns { get; set; }
}

CSV Export in server side

Export Grid as memory stream

The Syncfusion ASP.NET Core Grid component offers an option to export the data as a memory stream instead of downloading it as a file in the browser. To obtain the memory stream of the exported Grid, set the AsMemoryStream parameter to true in the ExcelExport and CsvExport methods.

The following code demonstrates how to get the memory stream of exported Grid.

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" height="400" toolbarClick="toolbarClick" toolbar=@(new List<string>() { "ExcelExport", "CsvExport" })>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="CustomerID" width="100"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" format="C2" width="100" textAlign="Right"></e-grid-column>
    </e-grid-columns>
</ejs-grid>
<script>
    function toolbarClick(args) {
        var grid = document.getElementById("Grid").ej2_instances[0];
        if (args.item.id === 'Grid_excelexport') {
            grid.serverExcelExport("/Home/ExcelExport");
        }
        if (args.item.id === 'Grid_csvexport') {
            grid.serverCsvExport("/Home/CsvExport");
        }
    }
</script>
public IActionResult Index()
{
    ViewBag.DataSource = orddata;
    return View();
}

public object ExcelExport([FromForm] string gridModel)
{
    GridExcelExport exp = new GridExcelExport();
    Grid gridProperty = ConvertGridObject(gridModel);
    // pass thrid parameter as true to get the Memory Stream of exported Grid data.
    return (MemoryStream)exp.ExcelExport<OrdersDetails>(gridProperty, orddata, true);
}

public object CsvExport([FromForm] string gridModel)
{
    GridExcelExport exp = new GridExcelExport();
    Grid gridProperty = ConvertGridObject(gridModel);
    // pass thrid parameter as true to get the Memory Stream of exported Grid data.
    return (MemoryStream)exp.CsvExport<OrdersDetails>(gridProperty, orddata, true);
}

private Grid ConvertGridObject(string gridProperty)
{
    Grid GridModel = (Grid)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(Grid));
    GridColumnModel cols = (GridColumnModel)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(GridColumnModel));
    GridModel.Columns = cols.columns;
    return GridModel;
}

public class GridColumnModel
{
    public List<GridColumn> columns { get; set; }
}

Merge Grid’s memory stream

The Essential XlsIO library is used to merge multiple memory streams into a single stream. To learn more about the merge option, please refer to this documentation.

You can merge a memory stream, a file stream, and a local file with the Grid’s memory stream in the following ways:

Merging with an existing memory stream

If you already have a memory stream, you can directly use it to merge with the Grid’s memory stream.

In the following code, ExcelEngine and AddCopy method of Worksheets are used to merge the Grid’s memory stream with an existing memory stream.

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" height="400" toolbarClick="toolbarClick" toolbar=@(new List<string>() { "ExcelExport" })>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="CustomerID" width="100"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" format="C2" width="100" textAlign="Right"></e-grid-column>
    </e-grid-columns>
</ejs-grid>
<script>
    function toolbarClick(args) {
        var grid = document.getElementById("Grid").ej2_instances[0];
        grid.serverExcelExport("/Home/ExcelExport");
    }
</script>
using Syncfusion.XlsIO;

public MemoryStream ms1; // defines existing memory stream.

public IActionResult Index()
{
    ViewBag.DataSource = orddata;
    return View();
}

object object ExcelExport([FromForm] string gridModel)
{
    GridExcelExport exp = new GridExcelExport();
    Grid gridProperty = ConvertGridObject(gridModel);
    // get the memory stream of exported Grid data.
    MemoryStream ms2 = (MemoryStream)exp.ExcelExport<OrdersDetails>(gridProperty, orddata, true);
    //New instance of ExcelEngine is created equivalent to launching Microsoft Excel with no workbooks open.
    ExcelEngine excelEngine = new ExcelEngine();
    //Instantiate the Excel application object.
    IApplication application = excelEngine.Excel;
    //Assigns default application version.
    application.DefaultVersion = ExcelVersion.Xlsx;
    //open an workbook of existing memory stream and Grid's memory stream through Open method of IWorkbooks.
    IWorkbook sourceWorkbook = application.Workbooks.Open(ms1);
    IWorkbook destinationWorkbook = application.Workbooks.Open(ms2);
    //Copy all the worksheet from the Source workbook to the destination workbook.
    for (int i = 0; i < sourceWorkbook.Worksheets.Count; i++)
    {
        destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[i]);
    }
    destinationWorkbook.ActiveSheetIndex = 1;
    //Saving the workbook as stream.
    MemoryStream ms3 = new MemoryStream();
    destinationWorkbook.SaveAs(ms3);
    ms3.Position = 0;
    //Dispose the instance of ExcelEngine.
    excelEngine.Dispose();
    //Dispose the streams.
    ms1.Dispose();
    ms2.Dispose();
    return ms3;
}

private Grid ConvertGridObject(string gridProperty)
{
    Grid GridModel = (Grid)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(Grid));
    GridColumnModel cols = (GridColumnModel)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(GridColumnModel));
    GridModel.Columns = cols.columns;
    return GridModel;
}

public class GridColumnModel
{
    public List<GridColumn> columns { get; set; }
}

Merging with an existing file stream

If you already have a file stream, you can directly use it to merge with the Grid’s memory stream. In the following code, the existing file stream is merged with the Grid’s memory stream.

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" height="400" toolbarClick="toolbarClick" toolbar=@(new List<string>() { "ExcelExport" })>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="CustomerID" width="100"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" format="C2" width="100" textAlign="Right"></e-grid-column>
    </e-grid-columns>
</ejs-grid>
<script>
    function toolbarClick(args) {
        var grid = document.getElementById("Grid").ej2_instances[0];
        grid.serverExcelExport("/Home/ExcelExport");
    }
</script>
using Syncfusion.XlsIO;

public FileStream fs1; // defines existing file stream.

public IActionResult Index()
{
    ViewBag.DataSource = orddata;
    return View();
}

public object ExcelExport([FromForm] string gridModel)
{
    GridExcelExport exp = new GridExcelExport();
    Grid gridProperty = ConvertGridObject(gridModel);
    MemoryStream ms1 = (MemoryStream)exp.ExcelExport<OrdersDetails>(gridProperty, orddata, true);
    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    //fs1 and ms1 represents the existing stream and Grid's stream.
    IWorkbook sourceWorkbook = application.Workbooks.Open(fs1);
    IWorkbook destinationWorkbook = application.Workbooks.Open(ms1);
    for (int i = 0; i < sourceWorkbook.Worksheets.Count; i++)
    {
        destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[i]);
    }
    destinationWorkbook.ActiveSheetIndex = 1;
    //Saving the workbook as stream.
    MemoryStream ms3 = new MemoryStream();
    destinationWorkbook.SaveAs(ms3);
    ms3.Position = 0;
    return ms3;
}

private Grid ConvertGridObject(string gridProperty)
{
    Grid GridModel = (Grid)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(Grid));
    GridColumnModel cols = (GridColumnModel)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(GridColumnModel));
    GridModel.Columns = cols.columns;
    return GridModel;
}

public class GridColumnModel
{
    public List<GridColumn> columns { get; set; }
}

Merging with a local file

To merge a local file with the Grid’s memory stream, you need to convert it into a file stream before merging. In the following code, the existing local file is merged with the Grid’s memory stream.

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" height="400" toolbarClick="toolbarClick" toolbar=@(new List<string>() { "ExcelExport" })>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="CustomerID" width="100"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" format="C2" width="100" textAlign="Right"></e-grid-column>
    </e-grid-columns>
</ejs-grid>
<script>
    function toolbarClick(args) {
        var grid = document.getElementById("Grid").ej2_instances[0];
        grid.serverExcelExport("/Home/ExcelExport");
    }
</script>
using Syncfusion.XlsIO;

// get the file stream of local file.
public FileStream fs1 = new FileStream("D:/ExcelDoc.xlsx", FileMode.Open, FileAccess.Read); // ExcelDoc.xlsx is a local file which is located in local disk D.

public IActionResult Index()
{
    ViewBag.DataSource = orddata;
    return View();
}

public object ExcelExport([FromForm] string gridModel)
{
    GridExcelExport exp = new GridExcelExport();
    Grid gridProperty = ConvertGridObject(gridModel);
    MemoryStream ms1 = (MemoryStream)exp.ExcelExport<OrdersDetails>(gridProperty, orddata, true);
    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    //fs1 and ms1 represents the local file's stream and Grid's stream.
    IWorkbook sourceWorkbook = application.Workbooks.Open(fs1);
    IWorkbook destinationWorkbook = application.Workbooks.Open(ms1);
    for (int i = 0; i < sourceWorkbook.Worksheets.Count; i++)
    {
        destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[i]);
    }
    destinationWorkbook.ActiveSheetIndex = 1;
    MemoryStream ms3 = new MemoryStream();
    destinationWorkbook.SaveAs(ms3);
    ms3.Position = 0;
    return ms3;
}

private Grid ConvertGridObject(string gridProperty)
{
    Grid GridModel = (Grid)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(Grid));
    GridColumnModel cols = (GridColumnModel)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(GridColumnModel));
    GridModel.Columns = cols.columns;
    return GridModel;
}

public class GridColumnModel
{
    public List<GridColumn> columns { get; set; }
}

Downloading the merged memory stream

You can download the merged memory stream by converting it into a FileStreamResult. In the following code, the merged memory stream is downloaded to the browser.

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource" height="400" toolbarClick="toolbarClick" toolbar=@(new List<string>() { "ExcelExport" })>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="CustomerID" width="100"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" format="C2" width="100" textAlign="Right"></e-grid-column>
    </e-grid-columns>
</ejs-grid>
<script>
    function toolbarClick(args) {
        var grid = document.getElementById("Grid").ej2_instances[0];
        grid.serverExcelExport("/Home/ExcelExport");
    }
</script>
using Syncfusion.XlsIO;

public MemoryStream ms1; // defines existing memory stream.

public IActionResult Index()
{
    ViewBag.DataSource = orddata;
    return View();
}

public ActionResult ExcelExport([FromForm] string gridModel)
{
    GridExcelExport exp = new GridExcelExport();
    Grid gridProperty = ConvertGridObject(gridModel);
    MemoryStream ms2 = (MemoryStream)exp.ExcelExport<OrdersDetails>(gridProperty, orddata, true);
    ExcelEngine excelEngine = new ExcelEngine();
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    //open an workbook of streams through Open method of IWorkbooks.
    IWorkbook sourceWorkbook = application.Workbooks.Open(ms1);
    IWorkbook destinationWorkbook = application.Workbooks.Open(ms2);
    for (int i = 0; i < sourceWorkbook.Worksheets.Count; i++)
    {
        destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[i]);
    }
    destinationWorkbook.ActiveSheetIndex = 1;
    MemoryStream ms3 = new MemoryStream();
    destinationWorkbook.SaveAs(ms3);
    ms3.Position = 0;
    // Save the MemoryStream into FileStreamResult.
    FileStreamResult fileStreamResult = new FileStreamResult(ms3, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    fileStreamResult.FileDownloadName = "Export.xlsx";
    //Dispose the instance of ExcelEngine.
    excelEngine.Dispose();
    //Dispose the streams.
    ms1.Dispose();
    ms2.Dispose();
    // return the file.
    return fileStreamResult;
}

private Grid ConvertGridObject(string gridProperty)
{
    Grid GridModel = (Grid)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(Grid));
    GridColumnModel cols = (GridColumnModel)Newtonsoft.Json.JsonConvert.DeserializeObject(gridProperty, typeof(GridColumnModel));
    GridModel.Columns = cols.columns;
    return GridModel;
}

public class GridColumnModel
{
    public List<GridColumn> columns { get; set; }
}

Rotate a header text in the exported grid

The Grid provides support to customize the column header styles, including changing text orientation, font color, and other visual aspects, in the exported Excel file on the server-side. This feature is particularly useful when you want to enhance the appearance of the exported data and create a unique representation of the Grid in the Excel document.

To achieve this requirement, you can use the ExcelHeaderQueryCellInfo event of the Grid. This event is triggered when creating column headers for the Excel document to be exported on the server-side. In this event, you can collect the column header details and handle customizations.

In the following demo, using the HeaderCellRotate method of the GridExcelExport class in the ServerExcelHeaderQueryCellInfo event, you can rotate the header text of the column header in the excel exported document.

<ejs-grid id="grid" dataSource="@ViewBag.dataSource" height="400px" toolbarClick="toolbarClick" toolbar=@(new List<string>() { "ExcelExport" })>
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="CustomerID" headerText="Customer ID" width="100"></e-grid-column>
        <e-grid-column field="OrderDate" headerText="Order Date" format="yMd" width="100" textAlign="Right"></e-grid-column>
        <e-grid-column field="Freight" headerText="Freight" format="C2" width="100" textAlign="Right"></e-grid-column>
    </e-grid-columns>
</ejs-grid>
<script>
    function toolbarClick(args) {
        var grid = document.getElementById("grid").ej2_instances[0];
        grid.serverExcelExport("/Home/ExcelExport");
    }
</script>
public ActionResult ExcelExport(string gridModel)
{
    GridExcelExport excelExporter = new GridExcelExport();
    Grid gridProperties = ConvertGridObject(gridModel);
    gridProperties.ServerExcelHeaderQueryCellInfo = ExcelHeaderQueryCellInfo;
    IEnumerable data = Utils.DataTableToJson(ordersDataTable);
    var result = exp.ExcelExport<dynamic>(gridProperty, data);
    return result;
}
private void ExcelHeaderQueryCellInfo(object excel)
{
    ServerExcelHeaderQueryCellInfoEventArgs name = (ServerExcelHeaderQueryCellInfoEventArgs)excel;
    headerValues.Add(name.Column.HeaderText);
    var longestHeaderText = headerValues.Where(s => s.Length == headerValues.Max(m => m.Length)).First();
    GridExcelExport excelExporter = new GridExcelExport();
    var size = excelExporter.ExcelTextSize(name.Style.Font.FontName, (float)name.Style.Font.Size, longestHeaderText);
    name.Cell.RowHeight = size.Width;
    excelExporter.HeaderCellRotate(name, 45); // Give the rotate degree value by the user.  
    name.Style.Borders.LineStyle = Syncfusion.XlsIO.ExcelLineStyle.None;
}

Rotate a header text in the exported grid

Limitations

  • The export feature for detail and caption templates is not supported in server-side exporting.
  • Multiple grids exporting feature is not supported with server side exporting.