Export Groups to separate Excel Worksheets
DESCRIPTION
Export Groups into separate Worksheets when Exporting the RadGrid to Excel (Xlsx).
RadGrid by default exports its data into a single Excel Worksheet.
You can, however, bypass the the built-in Export functionality and build the Excel Document manually.
SOLUTION
Cancel the Grid's built-in Export event, and using Telerik Document Processing Libraries (DPL) you can build the Excel document manually by looping through the Grid's Groups and creating separate Worksheets for each.
Step 1
Reference the required Assemblies for the Excel (Xlsx) Export.
Step 2
Add the following using
/ Import
statements
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.IO;
using Telerik.Web.UI;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
using xlsx = Telerik.Windows.Documents.Spreadsheet.Model;
Imports System.Data
Imports System.Linq
Imports System.Collections.Generic
Imports System.IO
Imports Telerik.Web.UI
Imports Telerik.Windows.Documents.Spreadsheet.FormatProviders
Imports Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx
Imports xlsx = Telerik.Windows.Documents.Spreadsheet.Model
Step 3
Add the following RadGrid definition to the page
<telerik:RadGrid ID="RadGrid1" runat="server" AllowPaging="True" Width="800px" OnNeedDataSource="RadGrid1_NeedDataSource" OnItemCommand="RadGrid1_ItemCommand">
<ExportSettings ExportOnlyData="true" HideStructureColumns="true" IgnorePaging="true">
<Excel Format="Xlsx" />
</ExportSettings>
<MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top" CommandItemSettings-ShowExportToExcelButton="true">
<GroupByExpressions>
<telerik:GridGroupByExpression>
<GroupByFields>
<telerik:GridGroupByField FieldName="GroupField" />
</GroupByFields>
<SelectFields>
<telerik:GridGroupByField FieldName="GroupField" HeaderText="Grouped by" />
</SelectFields>
</telerik:GridGroupByExpression>
</GroupByExpressions>
<Columns>
<telerik:GridBoundColumn DataField="OrderID" DataType="System.Int32"
FilterControlAltText="Filter OrderID column" HeaderText="OrderID"
ReadOnly="True" SortExpression="OrderID" UniqueName="OrderID">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="GroupField" HeaderText="Group Name"></telerik:GridBoundColumn>
<telerik:GridDateTimeColumn DataField="OrderDate" DataType="System.DateTime"
FilterControlAltText="Filter OrderDate column" HeaderText="OrderDate"
SortExpression="OrderDate" UniqueName="OrderDate">
</telerik:GridDateTimeColumn>
<telerik:GridNumericColumn DataField="Freight" DataType="System.Decimal"
FilterControlAltText="Filter Freight column" HeaderText="Freight"
SortExpression="Freight" UniqueName="Freight">
</telerik:GridNumericColumn>
<telerik:GridBoundColumn DataField="ShipName"
FilterControlAltText="Filter ShipName column" HeaderText="ShipName"
SortExpression="ShipName" UniqueName="ShipName">
</telerik:GridBoundColumn>
<telerik:GridBoundColumn DataField="ShipCountry"
FilterControlAltText="Filter ShipCountry column" HeaderText="ShipCountry"
SortExpression="ShipCountry" UniqueName="ShipCountry">
</telerik:GridBoundColumn>
</Columns>
</MasterTableView>
</telerik:RadGrid>
Step 4
Handle the Data Binding
protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
(sender as RadGrid).DataSource = OrdersTable();
}
private DataTable OrdersTable()
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("OrderID", typeof(int)));
dt.Columns.Add(new DataColumn("OrderDate", typeof(DateTime)));
dt.Columns.Add(new DataColumn("Freight", typeof(decimal)));
dt.Columns.Add(new DataColumn("ShipName", typeof(string)));
dt.Columns.Add(new DataColumn("ShipCountry", typeof(string)));
dt.Columns.Add(new DataColumn("GroupField", typeof(string)));
dt.PrimaryKey = new DataColumn[] { dt.Columns["OrderID"] };
for (int i = 0; i < 9; i++)
{
int index = i + 1;
DataRow row = dt.NewRow();
row["OrderID"] = index;
row["OrderDate"] = DateTime.Now.Date.AddDays(index);
row["Freight"] = index * 0.1 + index * 0.01;
row["ShipName"] = "Name " + index;
row["ShipCountry"] = "Country " + index;
row["GroupField"] = "Group " + index % 3;
dt.Rows.Add(row);
}
return dt;
}
Protected Sub RadGrid1_NeedDataSource(ByVal sender As Object, ByVal e As GridNeedDataSourceEventArgs)
CType(sender, RadGrid).DataSource = OrdersTable()
End Sub
Private Function OrdersTable() As DataTable
Dim dt As DataTable = New DataTable()
dt.Columns.Add(New DataColumn("OrderID", GetType(Integer)))
dt.Columns.Add(New DataColumn("OrderDate", GetType(DateTime)))
dt.Columns.Add(New DataColumn("Freight", GetType(Decimal)))
dt.Columns.Add(New DataColumn("ShipName", GetType(String)))
dt.Columns.Add(New DataColumn("ShipCountry", GetType(String)))
dt.Columns.Add(New DataColumn("GroupField", GetType(String)))
dt.PrimaryKey = New DataColumn() {dt.Columns("OrderID")}
For i As Integer = 0 To 9 - 1
Dim index As Integer = i + 1
Dim row As DataRow = dt.NewRow()
row("OrderID") = index
row("OrderDate") = DateTime.Now.Date.AddDays(index)
row("Freight") = index * 0.1 + index * 0.01
row("ShipName") = "Name " & index
row("ShipCountry") = "Country " & index
row("GroupField") = "Group " & index Mod 3
dt.Rows.Add(row)
Next
Return dt
End Function
Step 5
Build an excel document manually using the DPL.
Within the ItemCommand event capture the ExportToExcel command, and looping through the Grid Groups. For each Group create a new Worksheet and fill it with data.
At the end of the logic, there are couple of helper functions that will trigger the Download automatically.
protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
{
RadGrid grid = (RadGrid)sender;
if (e.CommandName == RadGrid.ExportToExcelCommandName && grid.ExportSettings.Excel.Format == GridExcelExportFormat.Xlsx)
{
// Cancel the original event
e.Canceled = true;
// Disable Paging, so all Group across all pages will be available
grid.AllowPaging = false;
// Refresh the changes
grid.Rebind();
// Create a Workbook object
xlsx.Workbook myWorkbook = new xlsx.Workbook();
// Get all the Group Headers
GridItem[] groupHeaderItems = grid.MasterTableView.GetItems(GridItemType.GroupHeader);
// Get all the Column unoqueNames
List<string> columnUniqueNames = grid.MasterTableView.RenderColumns.OfType<IGridDataColumn>().Select(col => (col as GridColumn).UniqueName).ToList();
foreach (GridGroupHeaderItem groupHeaderItem in groupHeaderItems)
{
// Create Worksheet object for the Workbook
xlsx.Worksheet myWorksheet = myWorkbook.Worksheets.Add();
// Parse the Group Header Text to fetch the Group Value
string groupValue = groupHeaderItem.DataCell.Text.Split(':')[1].Trim();
// Name the Worksheet by the Group value
// Name must not exceed 33 characters & must not contain special characters
myWorksheet.Name = groupValue;
// Get the List of Group Items
List<GridDataItem> groupItems = groupHeaderItem.GetChildItems().Select(item => (GridDataItem)item).ToList();
// Create the Column Headers for the Current Sheet
// Loop through the Column Unique Names
for (int colIndex = 0; colIndex < columnUniqueNames.Count; colIndex++)
{
// Mark the cell for the Column Header
xlsx.CellSelection headerCell = myWorksheet.Cells[0, colIndex];
// Set the Cell value
headerCell.SetValue(columnUniqueNames[colIndex]);
}
// Loop through the Group Items
for (int rowIndex = 0; rowIndex < groupItems.Count; ++rowIndex)
{
// Loop through the Column Unique Names
for (int colIndex = 0; colIndex < columnUniqueNames.Count; colIndex++)
{
// Get reference to the GridDataItem
GridDataItem groupItem = groupItems[rowIndex];
// Get the Column name
string columnUniqueName = columnUniqueNames[colIndex];
// Fetch the text from the GridDataItem cell by Column UniqueName
string cellText = groupItem[columnUniqueName].Text;
// Create a CellSelection
xlsx.CellSelection dataCell = myWorksheet.Cells[rowIndex + 1, colIndex];
// Set the Cell Value
dataCell.SetValue(cellText);
}
}
}
// Once the Workbook object is ready, export it manually.
DownloadXlsxFile(WorkbookToByteArray(myWorkbook), grid.ExportSettings.FileName);
//Note: None of changes made above will affect the Grid because the Headers are cleared upon exporting and so the Response will not update the Grid at the end of the PostBack. There is no need to turn paging back or re-set any of the properties.
}
}
#region Helper Functions
private byte[] WorkbookToByteArray(xlsx.Workbook workbook)
{
byte[] output = null;
var thread = new System.Threading.Thread(() =>
{
IWorkbookFormatProvider formatProvider = new XlsxFormatProvider();
using (MemoryStream ms = new MemoryStream())
{
formatProvider.Export(workbook, ms);
output = ms.ToArray();
}
});
thread.SetApartmentState(System.Threading.ApartmentState.STA);
thread.Start();
thread.Join();
return output;
}
private void DownloadXlsxFile(byte[] output, string fileName, bool shouldOpenInNewWindow = true)
{
var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var fileExtension = ".xlsx";
Response.Clear();
Response.Buffer = true;
Response.ContentType = contentType;
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.Charset = "";
if (Request.Browser.Browser.IndexOf("IE") > -1 || Request.Browser.Browser.IndexOf("InternetExplorer") > -1)
{
fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
}
var responseFileName = fileName + (fileName.EndsWith(fileExtension) ? string.Empty : fileExtension);
//sanitize input file name
responseFileName = responseFileName.Replace("\n", " ").Replace("\r", " ");
if (shouldOpenInNewWindow)
{
Response.AddHeader("Content-Disposition", "attachment;filename=\"" + responseFileName + "\"");
}
else
{
Response.AddHeader("Content-Disposition", "inline;filename=\"" + responseFileName + "\"");
}
Response.BinaryWrite(output);
Response.End();
}
#endregion
Protected Sub RadGrid1_ItemCommand(ByVal sender As Object, ByVal e As GridCommandEventArgs)
Dim grid As RadGrid = CType(sender, RadGrid)
If e.CommandName = RadGrid.ExportToExcelCommandName AndAlso grid.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx Then
'Cancel the original event
e.Canceled = True
'Disable Paging, so all Group across all pages will be available
grid.AllowPaging = False
'Refresh the changes
grid.Rebind()
'Create a Workbook object
Dim myWorkbook As xlsx.Workbook = New xlsx.Workbook()
'
Dim groupHeaderItems As GridItem() = grid.MasterTableView.GetItems(GridItemType.GroupHeader)
'Get all the Column unoqueNames
Dim columnUniqueNames As List(Of String) = grid.MasterTableView.RenderColumns.OfType(Of IGridDataColumn)().[Select](Function(col) (TryCast(col, GridColumn)).UniqueName).ToList()
For Each groupHeaderItem As GridGroupHeaderItem In groupHeaderItems
'Create Worksheet object for the Workbook
Dim myWorksheet As xlsx.Worksheet = myWorkbook.Worksheets.Add()
'Parse the Group Header Text to fetch the Group Value
Dim groupValue As String = groupHeaderItem.DataCell.Text.Split(":"c)(1).Trim()
'Name the Worksheet by the Group value
'Name must Not exceed 33 characters & must Not contain special characters
myWorksheet.Name = groupValue
'Get the List of Group Items
Dim groupItems As List(Of GridDataItem) = groupHeaderItem.GetChildItems().[Select](Function(item) CType(item, GridDataItem)).ToList()
'Create the Column Headers for the Current Sheet
'Loop through the Column Unique Names
For colIndex As Integer = 0 To columnUniqueNames.Count - 1
'Mark the cell for the Column Header
Dim headerCell As xlsx.CellSelection = myWorksheet.Cells(0, colIndex)
'Set the Cell value
headerCell.SetValue(columnUniqueNames(colIndex))
Next
'Loop through the Group Items
For rowIndex As Integer = 0 To groupItems.Count - 1
'Loop through the Column Unique Names
For colIndex As Integer = 0 To columnUniqueNames.Count - 1
'Get reference to the GridDataItem
Dim groupItem As GridDataItem = groupItems(rowIndex)
'Get the Column name
Dim columnUniqueName As String = columnUniqueNames(colIndex)
'Fetch the text from the GridDataItem cell by Column UniqueName
Dim cellText As String = groupItem(columnUniqueName).Text
'Create a CellSelection
Dim dataCell As xlsx.CellSelection = myWorksheet.Cells(rowIndex + 1, colIndex)
'Set the Cell Value
dataCell.SetValue(cellText)
Next
Next
Next
'Once the Workbook object is ready, export it manually.
DownloadXlsxFile(WorkbookToByteArray(myWorkbook), grid.ExportSettings.FileName)
'Note: None of changes made above will affect the Grid because the Headers are cleared upon exporting and so the Response will not update the Grid at the end of the PostBack. There is no need to turn paging back or re-set any of the properties.
End If
End Sub
Private Function WorkbookToByteArray(ByVal workbook As xlsx.Workbook) As Byte()
Dim output As Byte() = Nothing
Dim thread = New System.Threading.Thread(Sub()
Dim formatProvider As IWorkbookFormatProvider = New XlsxFormatProvider()
Using ms As MemoryStream = New MemoryStream()
formatProvider.Export(workbook, ms)
output = ms.ToArray()
End Using
End Sub)
thread.SetApartmentState(System.Threading.ApartmentState.STA)
thread.Start()
thread.Join()
Return output
End Function
Private Sub DownloadXlsxFile(ByVal output As Byte(), ByVal fileName As String, ByVal Optional shouldOpenInNewWindow As Boolean = True)
Dim contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Dim fileExtension = ".xlsx"
Response.Clear()
Response.Buffer = True
Response.ContentType = contentType
Response.ContentEncoding = System.Text.Encoding.UTF8
Response.Charset = ""
If Request.Browser.Browser.IndexOf("IE") > -1 OrElse Request.Browser.Browser.IndexOf("InternetExplorer") > -1 Then
fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)
End If
Dim responseFileName = fileName & (If(fileName.EndsWith(fileExtension), String.Empty, fileExtension))
responseFileName = responseFileName.Replace(vbLf, " ").Replace(vbCr, " ")
If shouldOpenInNewWindow Then
Response.AddHeader("Content-Disposition", "attachment;filename=""" & responseFileName & """")
Else
Response.AddHeader("Content-Disposition", "inline;filename=""" & responseFileName & """")
End If
Response.BinaryWrite(output)
Response.End()
End Sub
For more details, check out the following Help articles:
- Excel-Xlsx (OOXML) Export
- Integration with Telerik Document Processing Libraries (DPL)
- ItemCommand Event
- Document Processing Libraries (DPL)