Spread export
GridViewSpreadExport utilizes our RadSpreadProcessing library to export the content of RadGridView to xlsx, csv, pdf and txt formats.
As of R3 2020 SP1 GridViewSpreadExport also supports exporting to xls.
This article explains in detail the SpreadExport abilities and demonstrates how to use it for:
The following images show how a grid looks when you export it:
The spread export functionality requires the TelerikExport.dll assembly. To access the types in TelerikExport, you must include the assembly in your project and reference the Telerik.WinControls.Export namespace.
The spread export functionality also requires the RadSpreadProcessing Library. To use this Library, you must reference the following assemblies:
TelerikExport
Telerik.Windows.Documents.Core
Telerik.Windows.Documents.Fixed
Telerik.Windows.Documents.Spreadsheet
Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml
Telerik.Windows.Documents.Spreadsheet.FormatProviders.Pdf
Telerik.Windows.Zip
Exporting
To use the spread export functionality:
Create an instance of the GridViewSpreadExport object.
Pass the RadGridView instance that you want to export as a parameter.
Use the RunExport method to trigger the export process. RunExport accepts a filename as a parameter for the exported file.
GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridView1);
SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
spreadExporter.RunExport("D:\\exportedFile.xlsx", exportRenderer);
Dim spreadExporter As GridViewSpreadExport = New GridViewSpreadExport(radGridView1)
Dim exportRenderer As New SpreadExportRenderer()
spreadExporter.RunExport("D:\exportedFile.xlsx", exportRenderer)
Running export synchronously using a stream
The RunExport method has several overloads that allow the user to export using a stream as well:
string exportFile = @"..\..\exportedData.xlsx";
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
Telerik.WinControls.Export.GridViewSpreadExport exporter = new Telerik.WinControls.Export.GridViewSpreadExport(this.radGridView1);
Telerik.WinControls.Export.SpreadExportRenderer renderer = new Telerik.WinControls.Export.SpreadExportRenderer();
exporter.RunExport(ms, renderer);
using (System.IO.FileStream fileStream = new System.IO.FileStream(exportFile, FileMode.Create, FileAccess.Write))
{
ms.WriteTo(fileStream);
}
}
Dim exportFile As String = "..\..\exportedData.xlsx"
Using ms As New System.IO.MemoryStream()
Dim exporter As New Telerik.WinControls.Export.GridViewSpreadExport(Me.radGridView1)
Dim renderer As New Telerik.WinControls.Export.SpreadExportRenderer()
exporter.RunExport(ms, renderer)
Using fileStream As New System.IO.FileStream(exportFile, FileMode.Create, FileAccess.Write)
ms.WriteTo(fileStream)
End Using
End Using
Image exporting
GridViewSpreadExport allows you to export images. Observe the following specifics when you use GridViewSpreadExport:
Image exporting requires GridViewSpreadExport R1 2018 SP1 or later.
The ImageAlignment property of the column is respected.
ImageLayout.Tile is not supported.
Image export is not available in the XLS format provider. See the Xls Features article for a list of all supported features.
Figure: Image Export
Properties
ExportFormat: Defines the format the grid will be exported to. The available values are Xslx, Xls, Pdf, Csv, Txt. The default value of the property is Xslx, hence if not other specified, the exporter will export to Xslx.
ExportVisualSettings: Allows you to export the visual settings (themes) to the exported file. RadGridView will also export all formatting to the Excel file. The column width and row height will also be matched in the exported file.
ExportHierarchy: Defines whether the exporter will export hierarchical data or not.
-
ChildViewExportMode: Defines which child view of a hierarchy row to be exported. Available modes are:
ExportFirstView: The exporter exports the first view.
ExportCurrentlyActiveView: The exporter exports the view that is actived in the grid.
SelectViewToExport: In this mode the ChildViewExporing event is fired. The event allows to choose the view to export in row by row basis.
ExportAllViews: In this mode all child views are exported. Available in GridViewSpreadExport and GridViewPdfExport.
You can either export all child views or just one. You can't export some specific child views.
-
HiddenColumnOption: Defines whether hidden columns will be exported. Available options are:
ExportAlways: The exporter will export hidden columns as well.
DoNotExport: The exporter will not export hidden columns.
ExportAsHidden: The exporter will export hidden columns as hidden in excel.
-
HiddenRowOption: Defines whether hidden rows will be exported. Available options are:
ExportAlways: The exporter will export hidden rows as well.
DoNotExport: The exporter will not export hidden rows.
ExportAsHidden: The exporter will export hidden rows as hidden in excel.
MS Excel does not support other ways of hiding a column/row different from setting its width to zero. To avoid including hidden columns or rows in the exported excel file you could set HiddenColumnOption or HiddenRowOption property to DoNotExport.
-
PagingExportOption: Defines which pages to be exported, when paging is used in RadGridView.
CurrentPageOnly: The exporter will export only the data on the current page.
AllPages: The exporter will export the data from all pages.
-
SummariesExportOption: Allows to specify how to export summary items. There are four options to choose:
ExportAll (default): The exporter will export all summary rows. This is the default setting.
ExportOnlyTop: The exporter will export only the top summary rows.
ExportOnlyBottom: The exporter will export only the bottom summary rows.
DoNotExport: The exporter will not export any summary rows.
RadGridViewToExport: This property is used to set the instance of RadGridView to export.
-
SheetMaxRows: Тhe exporter splits the data on separate sheets if the number of rows is greater than the Excel maximum. You can control the maximum number of rows through this SheetMaxRows property. Available options are:
1048576: Max rows for Excel 2007 and above
65536 (default): Max rows for previous versions of Excel. This is the default setting.
SheetName: Defines the sheet name of the sheet to export to. If your data is large enough to be split on more than one sheets, then the export method adds index to the names of the next sheets.
-
FileExportMode: This property determines whether the data will be exported into an existing or a new file. If new is chosen and such exists it will be overridden. Available options are:
NewSheetInExistingFile: This option will create a new sheet in an already existing file.
CreateOrOverrideFile: Creates new or overrides an existing file.
ExportViewDefinition: Gets or sets a value indicating whether to export the view definition.
View definition is currently exported only for the master level. It is not supported for child templates in a hierarchical grid.
Exported Cells Data Type
Each column in RadGridView has an ExcelExportType property that you can use to explicitly set the data type of the cells in the exported document. To change the format of the exported data:
Set the ExcelExportType property of the specific column to Custom.
Specify the ExcelExportFormatString property with the desired format.
For more details on specifying the desired format, see Format Codes.
Events
CellFormatting
This event is used to format the cells to be exported. The event arguments provide:
CellSelection: After casting to the CellSelection class, it provides access to the excel cells selection. More information about the abilities this object introduces, can be found here: Get, Set and Clear Cell Properties.
CellStyleInfo: This object is a wrapper of the CellSelection object, and allows to easier modification of the most common appearance settings.
GridCellInfo: Provides access to the grid cell to be exported.
GridColumnIndex: Returns the index of the column to be exported.
GridRowIndex: Returns the index of the row to be exported.
GridRowInfoType: Returns the type of the row to be exported.
Here is an example of formatting the exported grid:
void spreadExporter_CellFormatting(object sender, Telerik.WinControls.Export.CellFormattingEventArgs e)
{
if (e.GridRowInfoType == typeof(GridViewTableHeaderRowInfo))
{
e.CellStyleInfo.Underline = true;
if (e.GridCellInfo.RowInfo.HierarchyLevel == 0)
{
e.CellStyleInfo.BackColor = Color.DeepSkyBlue;
}
else if (e.GridCellInfo.RowInfo.HierarchyLevel == 1)
{
e.CellStyleInfo.BackColor = Color.LightSkyBlue;
}
}
if (e.GridRowInfoType == typeof(GridViewHierarchyRowInfo))
{
if (e.GridCellInfo.RowInfo.HierarchyLevel == 0)
{
e.CellStyleInfo.IsItalic = true;
e.CellStyleInfo.FontSize = 12;
e.CellStyleInfo.BackColor = Color.GreenYellow;
}
else if (e.GridCellInfo.RowInfo.HierarchyLevel == 1)
{
e.CellStyleInfo.ForeColor = Color.DarkGreen;
e.CellStyleInfo.BackColor = Color.LightGreen;
}
}
}
Private Sub spreadExporter_CellFormatting(ByVal sender As Object, ByVal e As Telerik.WinControls.Export.CellFormattingEventArgs)
If e.GridRowInfoType Is GetType(GridViewTableHeaderRowInfo) Then
e.CellStyleInfo.Underline = True
If e.GridCellInfo.RowInfo.HierarchyLevel = 0 Then
e.CellStyleInfo.BackColor = Color.DeepSkyBlue
ElseIf e.GridCellInfo.RowInfo.HierarchyLevel = 1 Then
e.CellStyleInfo.BackColor = Color.LightSkyBlue
End If
End If
If e.GridRowInfoType Is GetType(GridViewHierarchyRowInfo) Then
If e.GridCellInfo.RowInfo.HierarchyLevel = 0 Then
e.CellStyleInfo.IsItalic = True
e.CellStyleInfo.FontSize = 12
e.CellStyleInfo.BackColor = Color.GreenYellow
ElseIf e.GridCellInfo.RowInfo.HierarchyLevel = 1 Then
e.CellStyleInfo.ForeColor = Color.DarkGreen
e.CellStyleInfo.BackColor = Color.LightGreen
End If
End If
End Sub
WorkbookCreated
This event is triggered on the SpreadExportRenderer object when the workbook is ready to be exported. Allows to introduce final customizations (for example you can add header and footer). More information on how to work with Workbook is available here: Working with Workbooks.
ChildViewExporting
This event is used to specify which child view to be exported, for each exported row, during the export. It will be triggered only when the ChildViewExportMode is set to SelectViewToExport. The event arguments provide the ParentRow which active view should be set via the ActiveViewIndex property.
ExportCompleted
This event is triggered when the export operation completes.
Exporting Grouped Data
RadGridView can export its grouped content by simply setting the ExportChildRowsGrouped property of the GridViewSpreadExport object to true.
Figure: Exporting Grouped Data
Exporting Grouped Data
GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridView1);
spreadExporter.ExportChildRowsGrouped = true;
SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
spreadExporter.RunExport(@"..\..\exportedFile.xlsx", exportRenderer);
Dim spreadExporter As New GridViewSpreadExport(Me.radGridView1)
spreadExporter.ExportChildRowsGrouped = True
Dim exportRenderer As New SpreadExportRenderer()
spreadExporter.RunExport("..\..\exportedFile.xlsx", exportRenderer)
Async Spread Export
RadGridView can export its content asynchronously. This feature can be utilized by calling the RunExportAsync method on the GridViewSpreadExport object.
To achieve optimal performance and to decrease the consumed memory, the visual settings and view definitions in RadGridView are not exported when the operation is run asynchronously.
Methods
The following methods of the GridViewSpreadExport class are responsible for exporting the data:
RunExportAsync: Starts an export operation which runs in a background thread.
CancelExportAsync: Cancels an export operation.
Events
The following events provide information about the state of the export operation:
AsyncExportProgressChanged: Occurs when the progress of an asynchronous export operation changes.
AsyncExportCompleted: Occurs when an async export operation is completed.
Exporting Data Asynchronously
This example demonstrates how to combine the async spread export feature with a RadProgressBar control to deliver better user experience.
Figure: Exporting Data Asynchronously
1. Bind RadGridView and define the initial settings.
public AsyncSpreadExport()
{
InitializeComponent();
this.BindGrid();
this.radProgressBar1.Minimum = 0;
this.radProgressBar1.Maximum = 100;
this.radProgressBar1.ShowProgressIndicators = true;
this.radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
this.btnExportAsync.Click += btnExportAsync_Click;
}
private void BindGrid()
{
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Id", typeof(int));
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("IsValid", typeof(bool));
dataTable.Columns.Add("Date", typeof(DateTime));
for (int i = 0; i < 50000; i++)
{
dataTable.Rows.Add(i, "Name " + i, i % 2 == 0, DateTime.Now.AddDays(i));
}
this.radGridView1.DataSource = dataTable;
}
Public Sub New()
InitializeComponent()
Me.BindGrid()
Me.RadProgressBar1.Minimum = 0
Me.RadProgressBar1.Maximum = 100
Me.RadProgressBar1.ShowProgressIndicators = True
Me.RadGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill
AddHandler Me.BtnExportAsync.Click, AddressOf BtnExportAsync_Click
End Sub
Private Sub BindGrid()
Dim dataTable As New DataTable()
dataTable.Columns.Add("Id", GetType(Integer))
dataTable.Columns.Add("Name", GetType(String))
dataTable.Columns.Add("IsValid", GetType(Boolean))
dataTable.Columns.Add("Date", GetType(DateTime))
For i As Integer = 0 To 49999
dataTable.Rows.Add(i, "Name " & i, i Mod 2 = 0, DateTime.Now.AddDays(i))
Next
Me.RadGridView1.DataSource = dataTable
End Sub
2. Start export and subscribe to the progress notification events.
private void btnExportAsync_Click(object sender, EventArgs e)
{
GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridView1);
spreadExporter.AsyncExportProgressChanged += spreadExporter_AsyncExportProgressChanged;
spreadExporter.AsyncExportCompleted += spreadExporter_AsyncExportCompleted;
SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
spreadExporter.RunExportAsync(@"..\..\exportedFile.xlsx",exportRenderer);
}
Private Sub BtnExportAsync_Click(sender As Object, e As EventArgs)
Dim spreadExporter As New GridViewSpreadExport(Me.RadGridView1)
AddHandler spreadExporter.AsyncExportProgressChanged, AddressOf spreadExporter_AsyncExportProgressChanged
AddHandler spreadExporter.AsyncExportCompleted, AddressOf spreadExporter_AsyncExportCompleted
Dim exportRenderer As New SpreadExportRenderer()
spreadExporter.RunExportAsync("..\..\exportedFile.xlsx", exportRenderer)
End Sub
3. Handle the notification events and report progress.
private void spreadExporter_AsyncExportProgressChanged(object sender, ProgressChangedEventArgs e)
{
this.radProgressBar1.Value1 = e.ProgressPercentage;
}
private void spreadExporter_AsyncExportCompleted(object sender, AsyncCompletedEventArgs e)
{
RadMessageBox.Show("Async Spread Export Completed!");
this.radProgressBar1.Value1 = 0;
}
Private Sub spreadExporter_AsyncExportProgressChanged(sender As Object, e As ProgressChangedEventArgs)
Me.RadProgressBar1.Value1 = e.ProgressPercentage
End Sub
Private Sub spreadExporter_AsyncExportCompleted(sender As Object, e As AsyncCompletedEventArgs)
RadMessageBox.Show("Async Spread Export Completed!")
Me.RadProgressBar1.Value1 = 0
End Sub
The RunExportAsync method has several overloads allowing the user to export using a stream as well:
private void button1_Click(object sender, EventArgs e)
{
System.IO.MemoryStream ms = new System.IO.MemoryStream();
Telerik.WinControls.Export.GridViewSpreadExport exporter = new Telerik.WinControls.Export.GridViewSpreadExport(this.radGridView1);
Telerik.WinControls.Export.SpreadExportRenderer renderer = new Telerik.WinControls.Export.SpreadExportRenderer();
exporter.AsyncExportCompleted += exporter_AsyncExportCompleted;
exporter.RunExportAsync(ms, renderer);
}
private void exporter_AsyncExportCompleted(object sender, AsyncCompletedEventArgs e)
{
RunWorkerCompletedEventArgs args = e as RunWorkerCompletedEventArgs;
string exportFile = @"..\..\exportedAsyncData.xlsx";
using (System.IO.FileStream fileStream = new System.IO.FileStream(exportFile, FileMode.Create, FileAccess.Write))
{
MemoryStream ms = args.Result as MemoryStream;
ms.WriteTo(fileStream);
ms.Close();
}
}
Private Sub button1_Click(sender As Object, e As EventArgs)
Dim ms As New System.IO.MemoryStream()
Dim exporter As New Telerik.WinControls.Export.GridViewSpreadExport(Me.radGridView1)
Dim renderer As New Telerik.WinControls.Export.SpreadExportRenderer()
AddHandler exporter.AsyncExportCompleted, AddressOf exporter_AsyncExportCompleted
exporter.RunExportAsync(ms, renderer)
End Sub
Private Sub exporter_AsyncExportCompleted(sender As Object, e As AsyncCompletedEventArgs)
Dim args As RunWorkerCompletedEventArgs = TryCast(e, RunWorkerCompletedEventArgs)
Dim exportFile As String = "..\..\exportedAsyncData.xlsx"
Using fileStream As New System.IO.FileStream(exportFile, FileMode.Create, FileAccess.Write)
Dim ms As MemoryStream = TryCast(args.Result, MemoryStream)
ms.WriteTo(fileStream)
ms.Close()
End Using
End Sub