Excel Export in Blazor DataGrid Component
19 Mar 202424 minutes to read
The excel export allows exporting DataGrid data to Excel document. You need to use the
ExcelExport method for exporting. To enable Excel export in the datagrid, set the AllowExcelExport property as true.
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowSorting="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
await this.DefaultGrid.ExcelExport();
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}To customize excel export
The excel export provides an option to customize mapping of the datagrid to excel document.
Export current page
The excel export provides an option to export the current page into excel. To export current page, define exportType to CurrentPage.
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowSorting="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExportProperties = new ExcelExportProperties();
ExportProperties.ExportType = ExportType.CurrentPage;
await this.DefaultGrid.ExcelExport(ExportProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Export hidden columns
The excel export provides an option to export hidden columns of DataGrid by defining IncludeHiddenColumn as true.
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowSorting="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Visible="false" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExportProperties = new ExcelExportProperties();
ExportProperties.IncludeHiddenColumn = true;
await this.DefaultGrid.ExcelExport(ExportProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Export the selected records only
The Grid has an option to export the selected records in an excel exported document. This can be achieved by using the DataSource property of the ExcelExportProperties class and the GetSelectedRecordsAsync method of the Grid.
In the following sample, selected records will be gotten from the GetSelectedRecordsAsync method and provided to the DataSource property of ExcelExportProperties. Then pass this ExcelExportProperties class to the ExportToExcelAsync method to get the selected records in the exported document.
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowExcelExport="true" AllowPaging="true"
Toolbar="@(new List<string>() { "ExcelExport" })" AllowSelection="true">
<GridEvents OnToolbarClick="OnToolbarClick" TValue="Order"></GridEvents>
<GridSelectionSettings Type="SelectionType.Multiple"></GridSelectionSettings>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task OnToolbarClick(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") // Id is combination of Grid's ID and itemname.
{
ExcelExportProperties ExcelProperties = new ExcelExportProperties();
var selectedRecord = await DefaultGrid.GetSelectedRecordsAsync();
if(selectedRecord.Count() > 0)
{
ExcelProperties.DataSource = selectedRecord;
}
else
{
ExcelProperties.DataSource = Orders;
}
await this.DefaultGrid.ExportToExcelAsync(ExcelProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 15).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
ShipCountry = (new string[] { "Germany", "UK", "USA", "Italy", "France" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public string ShipCountry { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Add custom text in header/footer
You can add text and customize its styles either in the header or footer of an exported excel document by using the Header and Footer properties of the ExcelExportProperties class.
The following sample code demonstrates adding custom text and customizing its styles in the header section of the exported document,
@using Syncfusion.Blazor.Grids
@using Syncfusion.Blazor.Calendars
<SfDateRangePicker @ref="DateRange" TValue="DateTime?" StartDate="@StartValue" EndDate="@EndValue"></SfDateRangePicker>
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfDateRangePicker<DateTime?> DateRange;
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public DateTime? StartValue { get; set; } = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 28);
public DateTime? EndValue { get; set; } = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 29);
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExportProperties = new ExcelExportProperties ();
ExcelHeader header = new ExcelHeader();
header.HeaderRows = 2;
List<ExcelCell> cell = new List<ExcelCell>
{
new ExcelCell() { RowSpan= 2,ColSpan=5 , Value= "PO Report from " + DateRange.StartDate.ToString() + " to " + DateRange.EndDate.ToString() + " Vendor All", Style = new ExcelStyle() { Bold = true, FontSize = 13, Italic= true } }
};
List<ExcelRow> HeaderContent = new List<ExcelRow>
{
new ExcelRow() { Cells = cell, Index = 1 }
};
header.Rows = HeaderContent;
ExportProperties.Header = header;
await this.DefaultGrid.ExcelExport(ExportProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}
Theme
The excel export provides an option to include theme for exported excel document.
To apply theme in exported Excel, define the theme in export properties.
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowSorting="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExcelProperties = new ExcelExportProperties();
ExcelTheme Theme = new ExcelTheme();
ExcelStyle ThemeStyle = new ExcelStyle()
{
FontName = "Segoe UI",
FontColor = "#666666",
FontSize = 20
};
Theme.Header = ThemeStyle;
Theme.Record = ThemeStyle;
Theme.Caption = ThemeStyle;
ExcelProperties.Theme = Theme;
await this.DefaultGrid.ExcelExport(ExcelProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}NOTE
By default, material theme is applied to exported excel document.
Customizing the background color for a Grid in an exported Excel document
You can set the background color for a Grid in the exported document using the Theme property of ExcelExportProperties class.
This can be demonstrated using the following sample:
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowSorting="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExcelProperties = new ExcelExportProperties();
ExcelTheme Theme = new ExcelTheme();
ExcelStyle ThemeStyle = new ExcelStyle()
{
BackColor = "#C67878"
};
Theme.Header = ThemeStyle;
Theme.Record = ThemeStyle;
Theme.Caption = ThemeStyle;
ExcelProperties.Theme = Theme;
await this.DefaultGrid.ExcelExport(ExcelProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}
NOTE
Customizing the background color for a particular column in an exported Excel document
You can set the background color for a particular column in the exported document. This can be achieved by the ExcelQueryCellInfoEvent of the Grid.
This can be demonstrated using the following sample:
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowSorting="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents ExcelQueryCellInfoEvent="ExcelQueryCellInfoHandler" OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public void ExcelQueryCellInfoHandler(ExcelQueryCellInfoEventArgs<Order> args)
{
// Here you can customize your code
if (args.Column.Field == "CustomerID") {
if (args.Data.CustomerID == "ALFKI")
{
args.Cell.CellStyle.BackColor = "#DC143C";
}
else if (args.Data.CustomerID == "ANANTR")
{
args.Cell.CellStyle.BackColor = "#FF0000";
}
else if (args.Data.CustomerID == "ANTON")
{
args.Cell.CellStyle.BackColor = "#FFD700";
}
else if (args.Data.CustomerID == "BLONP")
{
args.Cell.CellStyle.BackColor = "#FFFF00";
}
else if (args.Data.CustomerID == "BOLID")
{
args.Cell.CellStyle.BackColor = "#7CFC00";
}
}
}
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
await this.DefaultGrid.ExcelExport();
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}
NOTE
File name for exported document
You can assign the file name for the exported document by defining fileName property in excel export properties.
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowSorting="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExcelProperties = new ExcelExportProperties();
ExcelProperties.FileName = "new.xlsx";
await this.DefaultGrid.ExcelExport(ExcelProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Add additional worksheets to excel file while exporting
Additional worksheets can be added to the excel file while exporting using the ExcelExportProperties.
In the following sample, you can add the additional worksheets to excel file by creating a workbook using ExcelExportProperties. Additional sheets will be added along with the Grid data.
@using Syncfusion.Blazor.Grids
@using Syncfusion.ExcelExport
@{
var Tool = (new List<string>() { "ExcelExport"});
}
<SfGrid @ref="DefaultGrid" DataSource="@Orders" Toolbar=@Tool AllowExcelExport="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
public async Task ToolbarClickHandler()
{
ExcelExportProperties ExportProperties = new ExcelExportProperties();
// Add a new workbook to the excel file that contains only 1 worksheet.
ExportProperties.Workbook = new Workbook();
// Add additional worksheets.
ExportProperties.Workbook.Worksheets.Add();
ExportProperties.Workbook.Worksheets.Add();
// Define the Gridsheet index where Grid data must be exported.
ExportProperties.GridSheetIndex = 0;
// Export the document.
await this.DefaultGrid.ExcelExport(ExportProperties);
}
}Exporting grouped records
The excel export provides outline option for grouped records which hides the detailed data for better viewing.
In datagrid, we have provided the outline option for the exported document when the data’s are grouped.
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowGrouping="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridGroupSettings Columns="@(new string[] {"OrderDate"})"></GridGroupSettings>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Visible="false" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
await this.DefaultGrid.ExcelExport();
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 75).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Limitations
Microsoft Excel permits up to seven nested levels in outlines. So that in the datagrid we can able to provide only up to seven nested levels and if it exceeds more than seven levels then the document will be exported without outline option. Refer the Microsoft Limitation
How to export the Grid with specific columns
You can export the Excel grid with specific columns instead of all columns which are defined in the Grid definition. To achieve this scenario by using Columns property of the ExcelExportProperties class.
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowExcelExport="true" AllowPaging="true" Toolbar="@(new List<string>() { "ExcelExport" })">
<GridEvents OnToolbarClick="OnToolbarClick" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task OnToolbarClick(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExportProperties = new ExcelExportProperties();
List<GridColumn> ExportColumns = new List<GridColumn>();
#pragma warning disable BL0005
ExportColumns.Add(new GridColumn() { Field = "CustomerID", HeaderText = "Customer Name", Width = "100" });
ExportColumns.Add(new GridColumn() { Field = "OrderDate", HeaderText = "Date", Width = "120", Format = "d" });
ExportColumns.Add(new GridColumn() { Field = "Freight", HeaderText = "Freight", Width = "120", Format = "C2", TextAlign = TextAlign.Right });
#pragma warning restore BL0005
ExportProperties.Columns = ExportColumns;
await this.DefaultGrid.ExcelExport(ExportProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 15).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
ShipCountry = (new string[] { "Germany", "UK", "USA", "Italy", "France" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public string ShipCountry { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Custom data source
Excel export provides an option to define the datasource dynamically before exporting. To export data dynamically, define it in the DataSource property of the ExcelExportProperties class.
The following sample code demonstrates dynamically modifying the data source before exporting it,
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowExcelExport="true" AllowPaging="true" Toolbar="@(new List<string>() { "ExcelExport" })">
<GridEvents OnToolbarClick="OnToolbarClick" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task OnToolbarClick(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExcelProperties = new ExcelExportProperties();
ExcelProperties.DataSource = Orders;
await this.DefaultGrid.ExcelExport(ExcelProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 15).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
ShipCountry = (new string[] { "Germany", "UK", "USA", "Italy", "France" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public string ShipCountry { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Customizing columns
Excel export provides an option to define the columns dynamically before exporting. You can define the dynamic columns using Columns property of the ExcelExportProperties class.
The following sample code demonstrates dynamically adding ShipCountry column in the exported excel file,
@using Syncfusion.Blazor.Grids
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowExcelExport="true" AllowPaging="true" Toolbar="@(new List<string>() { "ExcelExport" })">
<GridEvents OnToolbarClick="OnToolbarClick" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task OnToolbarClick(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
ExcelExportProperties ExportProperties = new ExcelExportProperties();
var columns = await DefaultGrid.GetColumns(); //get the columns available in Grid
columns.Add(new GridColumn() { Field = "ShipCountry" }); //adding additional column
ExportProperties.Columns = columns;
await DefaultGrid.ExcelExport(ExportProperties);
}
}
protected override void OnInitialized()
{
Orders = Enumerable.Range(1, 15).Select(x => new Order()
{
OrderID = 1000 + x,
CustomerID = (new string[] { "ALFKI", "ANANTR", "ANTON", "BLONP", "BOLID" })[new Random().Next(5)],
ShipCountry = (new string[] { "Germany", "UK", "USA", "Italy", "France" })[new Random().Next(5)],
Freight = 2.1 * x,
OrderDate = DateTime.Now.AddDays(-x),
}).ToList();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public string ShipCountry { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}NOTE
You can refer to our Blazor DataGrid feature tour page for its groundbreaking feature representations. You can also explore our Blazor DataGrid example to understand how to present and manipulate data.
Exporting Grid Data as Stream
Exporting Grid Data as Memory Stream
This section shows how to invoke a ExportToExcelAsync method to export an excel workbook as a memory stream.
To obtain the export file as a memory stream, set the asMemoryStream parameter to true within the ExportToExcelAsync method.
The provided example showcases the process of exporting the file as a memory stream and sending the byte to initiate a browser download.
Step 1: Create a JavaScript file to execute browser downloads and copy the code below
function saveAsFile(filename, bytesBase64) {
var link = document.createElement('a');
link.download = filename;
link.href = "data:application/octet-stream;base64," + bytesBase64;
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}Step 2: Invoke the JavaScript file to carry out browser downloads using the memory stream
@using Syncfusion.Blazor.Grids
@using System.IO;
@using Syncfusion.Pdf
@using Syncfusion.XlsIO
@using Syncfusion.PdfExport;
@using Syncfusion.ExcelExport;
@inject IJSRuntime JSRuntime
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" Toolbar="@(new List<string>() { "ExcelExport"})" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
MemoryStream streamDoc = await DefaultGrid.ExportToExcelAsync(asMemoryStream: true);
await JSRuntime.InvokeVoidAsync("saveAsFile", new object[] { "ExcelStream.xlsx", Convert.ToBase64String(streamDoc.ToArray()), true });
}
}
public List<Order> GetAllRecords()
{
List<Order> data = new List<Order>();
int count = 1000;
for (int i = 0; i < 15; i++)
{
data.Add(new Order() { OrderID = count + 1, CustomerID = "ALFKI", OrderDate = new DateTime(1995, 05, 15), Freight = 25.7 * 2 });
data.Add(new Order() { OrderID = count + 2, CustomerID = "ANANTR", OrderDate = new DateTime(1994, 04, 04), Freight = 26.7 * 2 });
data.Add(new Order() { OrderID = count + 3, CustomerID = "BLONP", OrderDate = new DateTime(1993, 03, 10), Freight = 27.7 * 2 });
data.Add(new Order() { OrderID = count + 4, CustomerID = "ANTON", OrderDate = new DateTime(1992, 02, 14), Freight = 28.7 * 2 });
data.Add(new Order() { OrderID = count + 5, CustomerID = "BOLID", OrderDate = new DateTime(1991, 01, 18), Freight = 29.7 * 2 });
count += 5;
}
return data;
}
protected override void OnInitialized()
{
Orders = GetAllRecords();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Converting Memory Stream to File Stream for Excel Export
This section explains the process of converting a memory stream obtained from the ExportToExcelAsync method into a file stream to export excel workbook.
The example provided demonstrates this process of exporting the excel workboox from the file stream.
@using Syncfusion.Blazor.Grids
@using System.IO;
@using Syncfusion.Pdf
@using Syncfusion.XlsIO
@using Syncfusion.PdfExport;
@using Syncfusion.ExcelExport;
@inject IJSRuntime JSRuntime
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" Toolbar="@(new List<string>() { "ExcelExport"})" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
MemoryStream streamDoc = await DefaultGrid.ExportToExcelAsync(asMemoryStream: true);
//Create a copy of streamDoc1 to read the memory stream
MemoryStream copyOfStreamDoc1 = new MemoryStream(streamDoc.ToArray());
//For creating the exporting location with file name, for this need to specify the physical exact path of the file
string filePaths = "C:Users/abc/Downloads/SampleTestExcel.xlsx";
// Create a FileStream to write the memoryStream contents to a file
using (FileStream fileStream = File.Create(filePaths))
{
// Copy the MemoryStream data to the FileStream
copyOfStreamDoc1.CopyTo(fileStream);
}
}
}
public List<Order> GetAllRecords()
{
List<Order> data = new List<Order>();
int count = 1000;
for (int i = 0; i < 15; i++)
{
data.Add(new Order() { OrderID = count + 1, CustomerID = "ALFKI", OrderDate = new DateTime(1995, 05, 15), Freight = 25.7 * 2 });
data.Add(new Order() { OrderID = count + 2, CustomerID = "ANANTR", OrderDate = new DateTime(1994, 04, 04), Freight = 26.7 * 2 });
data.Add(new Order() { OrderID = count + 3, CustomerID = "BLONP", OrderDate = new DateTime(1993, 03, 10), Freight = 27.7 * 2 });
data.Add(new Order() { OrderID = count + 4, CustomerID = "ANTON", OrderDate = new DateTime(1992, 02, 14), Freight = 28.7 * 2 });
data.Add(new Order() { OrderID = count + 5, CustomerID = "BOLID", OrderDate = new DateTime(1991, 01, 18), Freight = 29.7 * 2 });
count += 5;
}
return data;
}
protected override void OnInitialized()
{
Orders = GetAllRecords();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}Merging Two Excel Memory Streams
This section explains the process of combining two memory stream files and exporting the resulting merged file as a excel workbook, To copy a worksheet between workbooks or within the same workbook, utilize the capabilities of the package Syncfusion.Blazor.XlslO.
The example below demonstrates how to merge two memory streams and export that merged memory stream for browser download.
In this example, there are two memory streams: streamDoc1 and streamDoc2. streamDoc1 represents the normal grid memory stream, while streamDoc2 contains the memory stream of a customized grid using the ExcelExportProperties class. The merging process combines the contents of streamDoc1 with streamDoc2, resulting in a combined workbook saved as a memory stream. This merged memory stream is then utilized to initiate the browser download.
@using Syncfusion.Blazor.Grids
@using System.IO;
@using Syncfusion.Pdf
@using Syncfusion.XlsIO
@using Syncfusion.PdfExport;
@using Syncfusion.ExcelExport;
@inject IJSRuntime JSRuntime
<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" Toolbar="@(new List<string>() { "ExcelExport"})" AllowExcelExport="true" AllowPaging="true">
<GridEvents OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>
<GridColumns>
<GridColumn Field=@nameof(Order.OrderID) HeaderText="Order ID" TextAlign="TextAlign.Right" Width="120"></GridColumn>
<GridColumn Field=@nameof(Order.CustomerID) HeaderText="Customer Name" Width="150"></GridColumn>
<GridColumn Field=@nameof(Order.OrderDate) HeaderText=" Order Date" Format="d" Type="ColumnType.Date" TextAlign="TextAlign.Right" Width="130"></GridColumn>
<GridColumn Field=@nameof(Order.Freight) HeaderText="Freight" Format="C2" TextAlign="TextAlign.Right" Width="120"></GridColumn>
</GridColumns>
</SfGrid>
@code{
private SfGrid<Order> DefaultGrid;
public List<Order> Orders { get; set; }
public async Task ToolbarClickHandler(Syncfusion.Blazor.Navigations.ClickEventArgs args)
{
if (args.Item.Id == "Grid_excelexport") //Id is combination of Grid's ID and itemname
{
//Merging two memory stream for excel export
MemoryStream mergedStream = new MemoryStream();
MemoryStream streamDoc1 = await DefaultGrid.ExportToExcelAsync(asMemoryStream: true);
//Create a copy of streamDoc1 to access the memory stream
MemoryStream copyOfStreamDoc1 = new MemoryStream(streamDoc1.ToArray());
//Cusotmized grid memory stream
ExcelExportProperties ExcelProperties = new ExcelExportProperties();
ExcelTheme Theme = new ExcelTheme();
ExcelStyle ThemeStyle = new ExcelStyle()
{
BackColor = "#C67878"
};
Theme.Header = ThemeStyle;
Theme.Record = ThemeStyle;
ExcelProperties.Theme = Theme;
MemoryStream streamDoc2 = await DefaultGrid.ExportToExcelAsync(asMemoryStream: true, ExcelProperties);
//Create a copy of streamDoc2 to access the memory stream
MemoryStream copyOfStreamDoc2 = new MemoryStream(streamDoc2.ToArray());
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook1 = application.Workbooks.Open(copyOfStreamDoc1);
IWorkbook workbook2 = application.Workbooks.Open(copyOfStreamDoc2);
//Copy first worksheet from the workbook1 workbook to the workbook2 workbook
workbook2.Worksheets.AddCopy(workbook1.Worksheets[0]);
workbook2.ActiveSheetIndex = 1;
//Saving merged workbook as memorystream
workbook2.SaveAs(mergedStream);
}
await JSRuntime.InvokeVoidAsync("saveAsFile", new object[] { "MemoryStreamMerge.xlsx", Convert.ToBase64String(mergedStream.ToArray()), true });
}
}
public List<Order> GetAllRecords()
{
List<Order> data = new List<Order>();
int count = 1000;
for (int i = 0; i < 15; i++)
{
data.Add(new Order() { OrderID = count + 1, CustomerID = "ALFKI", OrderDate = new DateTime(1995, 05, 15), Freight = 25.7 * 2 });
data.Add(new Order() { OrderID = count + 2, CustomerID = "ANANTR", OrderDate = new DateTime(1994, 04, 04), Freight = 26.7 * 2 });
data.Add(new Order() { OrderID = count + 3, CustomerID = "BLONP", OrderDate = new DateTime(1993, 03, 10), Freight = 27.7 * 2 });
data.Add(new Order() { OrderID = count + 4, CustomerID = "ANTON", OrderDate = new DateTime(1992, 02, 14), Freight = 28.7 * 2 });
data.Add(new Order() { OrderID = count + 5, CustomerID = "BOLID", OrderDate = new DateTime(1991, 01, 18), Freight = 29.7 * 2 });
count += 5;
}
return data;
}
protected override void OnInitialized()
{
Orders = GetAllRecords();
}
public class Order
{
public int? OrderID { get; set; }
public string CustomerID { get; set; }
public DateTime? OrderDate { get; set; }
public double? Freight { get; set; }
}
}