Spread Export
ListViewSpreadExport utilizes our RadSpreadProcessing libraries to export the contents of RadListView to xlsx, csv, pdf and txt formats.
As of R3 2020 SP1 ListViewSpreadExport also supports exporting to xls.
The list view is exported according to the current ViewType. All three view types can be exported. This article will explain in details the SpreadExport's abilities and will demonstrate how you can use it.
Here is how the following RadListView, looks when it is exported.
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 ListViewSpreadExport object should be created, passing as a parameter the RadListView instance to export. Afterwards, the RunExport method will trigger the export process. The latter method accepts as parameter a filename of the file to be exported.
You should pass an instance of a SpreadExportRenderer to the export method as well.
ListViewSpreadExport exporter = new ListViewSpreadExport(this.radListView1);
SpreadExportRenderer renderer = new SpreadExportRenderer();
exporter.RunExport(@"C:\ExportedFile.xlsx", renderer);
Dim exporter As New ListViewSpreadExport(Me.radListView1)
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.ListViewSpreadExport spreadExporter = new Telerik.WinControls.Export.ListViewSpreadExport(radListView1);
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.ListViewSpreadExport(Me.radListView1)
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 RadListView 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. RadListView 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.
ExportImages: Gets or sets a value indicating whether to export images.
ExportChildItemsGrouped: Gets or sets a value indicating whether to export hierarchy and group child rows grouped.
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:
DataItem: Gives you access to the underling ListViewDataItem 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 list view:
void exporter_CellFormatting(object sender, ListViewSpreadExportCellFormattingEventArgs 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 ListViewSpreadExportCellFormattingEventArgs)
e.ExportCell.BackColor = ColorTranslator.FromHtml("#F4FFEC")
e.ExportCell.Font = New Font("Consolas", 10, FontStyle.Underline)
End Sub
- ExportCompleted: This event is triggered when the export operation completes.
Async Spread Export
RadListView provides functionality for asynchronous spread export. This feature can be utilized by calling the RunExportAsync method on the ListViewSpreadExport 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.
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)
{
ListViewSpreadExport spreadExporter = new ListViewSpreadExport(this.radListView1);
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 ListViewSpreadExport(Me.radListView1)
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.ListViewSpreadExport spreadExporter = new Telerik.WinControls.Export.ListViewSpreadExport(this.radListView1);
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.ListViewSpreadExport(Me.radListView1)
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 ListViewSpreadExport 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.