Spread Export
PropertyGridSpreadExport utilizes our RadSpreadProcessing libraries to export the contents of RadPropertyGrid to xlsx, csv, pdf and txt formats.
As of R3 2020 SP1 PropertyGridSpreadExport also supports exporting to xls.
This article will explain in detail the SpreadExport abilities and will demonstrate how you can use it.
Here is how the following RadPropertyGrid, looks when it is exported.
Figure 1: Before and After Export
The spread export functionality is located in the TelerikExport.dll assembly. You need to include the following namespace in order to access the types contained in TelerikExport:
- Telerik.WinControls.TelerikExport
Since this functionality is using the RadSpreadProcessingLibrary you need to reference the following assemblies as well:
- 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.Maths
- Telerik.Windows.Zip
Exporting Data
To use the spread export functionality, an instance of the PropertyGridSpreadExport object should be created, passing as parameter the RadPropertyGrid instance to export. Afterwards, the RunExport method will trigger the export process. The latter method accepts as parameter the filename of the file to be exported.
You should pass an instance of a SpreadExportRenderer to the export method as well.
PropertyGridSpreadExport exporter = new PropertyGridSpreadExport(this.radPropertyGrid1);
SpreadExportRenderer renderer = new SpreadExportRenderer();
exporter.RunExport(@"C:\ExportedFile.xlsx", renderer);
Dim exporter As New PropertyGridSpreadExport(Me.radPropertyGrid1)
Dim renderer As New SpreadExportRenderer()
exporter.RunExport("C:\ExportedFile.xlsx", renderer)
The RunExport method has several overloads allowing the user to export using a stream as well:
Running Export Synchronously Using a Stream
string exportFile = @"..\..\exportedData.xlsx";
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
Telerik.WinControls.Export.PropertyGridSpreadExport spreadExporter = new Telerik.WinControls.Export.PropertyGridSpreadExport(this.radPropertyGrid1);
Telerik.WinControls.Export.SpreadExportRenderer spreadRenderer = new Telerik.WinControls.Export.SpreadExportRenderer();
spreadExporter.RunExport(ms, spreadRenderer);
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 spreadExporter As New Telerik.WinControls.Export.PropertyGridSpreadExport(Me.radPropertyGrid1)
Dim spreadRenderer As New Telerik.WinControls.Export.SpreadExportRenderer()
spreadExporter.RunExport(ms, spreadRenderer)
Using fileStream As New System.IO.FileStream(exportFile, FileMode.Create, FileAccess.Write)
ms.WriteTo(fileStream)
End Using
End Using
Properties
ExportFormat: Defines the format the TreeView will be exported to. The available values are Xslx, 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. RadPropertyGrid will also export all formatting to the Excel file.
-
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. ExportChildItemsGrouped: Gets or sets a value indicating whether to export child items grouped.
ExportDescriptions: Gets or sets a value indicating whether to export item descriptions.
ItemIndent: Gets or sets the indent of child items.
CollapsedItemOption: Gets or sets a value indicating how children of collapsed items are exported.
Events
-
CellFormatting: This event is used to format the cells to be exported. The event arguments provide:
Item: Gives you access to the currently exported item.
ExportCell: Allows you to set the styles of the exported cell.
RowIndex: The index of the currently exported row. Here is an example of formatting the exported TreeView:
void exporter_CellFormatting(object sender, PropertyGridSpreadExportCellFormattingEventArgs e)
{
e.ExportCell.BackColor = ColorTranslator.FromHtml("#F4FFEC");
e.ExportCell.Font = new Font("Consolas", 10, FontStyle.Underline);
}
Private Sub exporter_CellFormatting(ByVal sender As Object, ByVal e As PropertyGridSpreadExportCellFormattingEventArgs)
e.ExportCell.BackColor = ColorTranslator.FromHtml("#F4FFEC")
e.ExportCell.Font = New Font("Consolas", 10, FontStyle.Underline)
End Sub
Figure 2: Export Using Formating
- ExportCompleted: This event is triggered when the export operation completes.
Async Spread Export
RadPropertyGrid provides functionality for asynchronous spread export. This feature can be utilized by calling the RunExportAsync method on the PropertyGridSpreadExport object.
If the ExportVisualSettings property is set to true the UI can be freezed at some point. This is expected since exporting the visual settings requires creating visual elements for all items and updating the exported control UI.
The following example will demonstrate how the async spread export feature can be combined with a RadProgressBar control to deliver better user experience.
Fig.3 Exporting Data Asynchronously
1. The following code shows how you can subscribe to the notification events and start the async export operation.
private void btnExportAsync_Click(object sender, EventArgs e)
{
PropertyGridSpreadExport spreadExporter = new PropertyGridSpreadExport(this.radPropertyGrid1);
spreadExporter.AsyncExportProgressChanged += spreadExporter_AsyncExportProgressChanged;
spreadExporter.AsyncExportCompleted += spreadExporter_AsyncExportCompleted;
SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
spreadExporter.RunExportAsync(@"..\..\ExportedFile.xlsx", exportRenderer);
}
Private Sub btnExportAsync_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim spreadExporter As New PropertyGridSpreadExport(Me.radPropertyGrid1)
AddHandler spreadExporter.AsyncExportProgressChanged, AddressOf spreadExporter_AsyncExportProgressChanged
AddHandler spreadExporter.AsyncExportCompleted, AddressOf spreadExporter_AsyncExportCompleted
Dim exportRenderer As New SpreadExportRenderer()
spreadExporter.RunExportAsync("..\..\ExportedFile.xlsx", exportRenderer)
End Sub
2. 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(ByVal sender As Object, ByVal e As ProgressChangedEventArgs)
Me.radProgressBar1.Value1 = e.ProgressPercentage
End Sub
Private Sub spreadExporter_AsyncExportCompleted(ByVal sender As Object, ByVal 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:
Running Export Asynchronously Using a Stream
private void buttonRunExportAsync_Click(object sender, EventArgs e)
{
System.IO.MemoryStream ms = new System.IO.MemoryStream();
Telerik.WinControls.Export.PropertyGridSpreadExport spreadExporter = new Telerik.WinControls.Export.PropertyGridSpreadExport(this.radPropertyGrid1);
Telerik.WinControls.Export.SpreadExportRenderer spreadRenderer = new Telerik.WinControls.Export.SpreadExportRenderer();
spreadExporter.AsyncExportCompleted += exporter_AsyncExportCompleted;
spreadExporter.RunExportAsync(ms, spreadRenderer);
}
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 buttonRunExportAsync_Click(sender As Object, e As EventArgs)
Dim ms As New System.IO.MemoryStream()
Dim spreadExporter As New Telerik.WinControls.Export.PropertyGridSpreadExport(Me.radPropertyGrid1)
Dim spreadRenderer As New Telerik.WinControls.Export.SpreadExportRenderer()
AddHandler spreadExporter.AsyncExportCompleted, AddressOf exporter_AsyncExportCompleted
spreadExporter.RunExportAsync(ms, spreadRenderer)
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
Async Export Methods and Events
Methods
The following methods of the TreeViewSpreadExport 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.