Edit this page

RadSpreadStreamProcessing Export

The GridViewSpreadStreamExport uses the RadSpreadStreamProcessing library which allows you to create big documents (without loading the entire document in the memory) and export them to the most common formats.

Figure 1: Exporting RadGridView.

gridview-exporting-data-spreadstream-export 001

note

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
important

Since this functionality is using the RadSpreadStreamProcessing library you need to reference the following assembly as well:

  • Telerik.Documents.SpreadsheetStreaming

Exporting

To use the spread export functionality create an instance of the GridViewSpreadStreamExport object. Pass as parameter the RadGridView 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 and a SpreadStreamExportRenderer instance.

Exporting the grid.

GridViewSpreadStreamExport spreadStreamExport = new GridViewSpreadStreamExport(this.radGridView1);
spreadStreamExport.ExportVisualSettings = true;
spreadStreamExport.RunExport(@"D:\StreamExport.xlsx", new SpreadStreamExportRenderer());

Dim spreadStreamExport As New GridViewSpreadStreamExport(Me.radGridView1)
spreadStreamExport.ExportVisualSettings = True
spreadStreamExport.RunExport("D:\StreamExport.xlsx", New SpreadStreamExportRenderer())

Properties

Property Description
ExportHierarchy Set it to true if you want to export the child templates/rows.
ExportVisualSettings Gets or sets a value indicating whether the visual settings should be exported.
SheetName Gets or sets the name of the sheet.
SheetMaxRows Gets or sets the maximum number of rows per sheet.
SummariesExportOption Gets or sets a value indicating how summary rows are exported.
HiddenColumnOption Gets or sets a value indicating how hidden columns are exported.
HiddenRowOption Gets or sets a value indicating how hidden rows are exported.
PagingExportOption Gets or sets a value indicating how the export behaves when paging is enabled.
ChildViewExportMode Gets or sets the child view export mode.
RadGridViewToExport Gets or sets the RadGridView to export.
ExportFormat Gets or sets the format of the exported file - XLSX or CSV.
ExportGroupedColumns Gets or sets a value indicating whether to export grouped columns.
FreezeHeaderRow Gets or sets a value indicating whether to freeze the header row in the exported file.
FreezePinnedRows Gets or sets a value indicating whether to freeze pinned rows.
FreezePinnedColumns Gets or sets a value indicating whether to freeze pinned columns.
ExportChildRowsGrouped Gets or sets a value indicating whether to export hierarchy and group child rows grouped.
ExportViewDefinition Gets or sets a value indicating whether to export view definition
FileExportMode This property determines whether the data will be exported into an existing or a new file. Available options are: NewSheetInExistingFile(this option will create a new sheet in an already existing file) and CreateOrOverrideFile which creates new or overrides the existing file.
important

Each column in RadGridView has ExcelExportType property which can be used for explicitly setting the data type of the cells in the exported document. In order to change the format of the exported data, you can set the ExcelExportType property of the specific column to Custom and specify the ExcelExportFormatString property with the desired format. Examples of such formats are available here: https://support.office.com/en-us/article/Format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e?ui=en-US&rs=en-US&ad=US

Events

CellFormatting

This event occurs for every cell that is being exported. It can be used for styling the cells or applying custom format to the cells values.

Using the CellFormatting event

private void SpreadStreamExport_CellFormatting(object sender, SpreadStreamCellFormattingEventArgs e)
{
    e.CellStyleInfo.BackColor = Color.Green;
    e.CellStyleInfo.ForeColor = Color.Red;
    e.CellStyleInfo.BottomBorder = new SpreadBorder(SpreadBorderStyle.Double, SpreadThemableColor.FromRgb(100, 100, 100));
    e.CellStyleInfo.TopBorder = Color.Black;
}

Private Sub SpreadStreamExport_CellFormatting(ByVal sender As Object, ByVal e As SpreadStreamCellFormattingEventArgs)
    e.CellStyleInfo.BackColor = Color.Green
    e.CellStyleInfo.ForeColor = Color.Red
    e.CellStyleInfo.BottomBorder = New SpreadBorder(SpreadBorderStyle.Double, SpreadThemableColor.FromRgb(100, 100, 100))
    e.CellStyleInfo.TopBorder = Color.Black
End Sub

RowCreated

Occurs when a new row is created in current worksheet. This is suitable place to set any row properties (like height) and/or add indent cells.

Using RowCreated to set the rows height.

private void SpreadStreamExport_RowCreated(object sender, SpreadStreamRowEventArgs e)
{
    if (e.GridRowInfoType == typeof(GridViewTableHeaderRowInfo))
    {
        var row = e.Row as Telerik.Documents.SpreadsheetStreaming.IRowExporter;
        row.SetHeightInPixels(50);
    }
}

Private Sub SpreadStreamExport_RowCreated(ByVal sender As Object, ByVal e As SpreadStreamRowEventArgs)
    If e.GridRowInfoType Is GetType(GridViewTableHeaderRowInfo) Then
        Dim row = TryCast(e.Row, Telerik.Documents.SpreadsheetStreaming.IRowExporter)
        row.SetHeightInPixels(50)
    End If
End Sub

RowExporting

Occurs before every spread row is exported. This is suitable place to add any additional cells at the end of the row.

private void SpreadStreamExport_RowExporting(object sender, SpreadStreamRowEventArgs e)
{
    if (e.GridRowIndex % 2 == 0)
    {
        var row = e.Row as IRowExporter;
        using (ICellExporter cell = row.CreateCellExporter())
        {
            SpreadCellFormat format = new SpreadCellFormat()
            {
                Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(100, 100, 100))
            };
            cell.SetValue("---");
            format.HorizontalAlignment = SpreadHorizontalAlignment.Center;
            format.VerticalAlignment = SpreadVerticalAlignment.Center;
            format.LeftBorder = new SpreadBorder(SpreadBorderStyle.Double, SpreadThemableColor.FromRgb(100, 100, 100));
            format.RightBorder = new SpreadBorder(SpreadBorderStyle.Double, SpreadThemableColor.FromRgb(100, 100, 100));
            cell.SetFormat(format);
        }
    }
}

Private Sub SpreadStreamExport_RowExporting(ByVal sender As Object, ByVal e As SpreadStreamRowEventArgs)
    If e.GridRowIndex Mod 2 = 0 Then
        Dim row = TryCast(e.Row, IRowExporter)
        Using cell As ICellExporter = row.CreateCellExporter()
            Dim format As New SpreadCellFormat() With {.Fill = SpreadPatternFill.CreateSolidFill(New SpreadColor(100, 100, 100))}
            cell.SetValue("---")
            format.HorizontalAlignment = SpreadHorizontalAlignment.Center
            format.VerticalAlignment = SpreadVerticalAlignment.Center
            format.LeftBorder = New SpreadBorder(SpreadBorderStyle.Double, SpreadThemableColor.FromRgb(100, 100, 100))
            format.RightBorder = New SpreadBorder(SpreadBorderStyle.Double, SpreadThemableColor.FromRgb(100, 100, 100))
            cell.SetFormat(format)
        End Using
    End If
End Sub

ExportCompleted

Occurs when the export process completes. This event is suitable for notifying the user that the exported operation is now completed.

Asynchronous Export

This feature can be utilized by calling the RunExportAsync method on the GridViewSpreadStreamExport object. In addition there are two events that fire when this export method is used:

  • AsyncExportCompleted: Fires when the export operation is completed.
  • AsyncExportProgressChanged: Fires repeatedly and passes the current export progress.
note

When you are exporting with visual settings the UI would be friezed for some time. This is necessary because the exporter need to retrieve all grid styles and this operation is executed on the UI thread.

Figure 2: The asynchronous export.

gridview-exporting-data-spreadstream-export 002

Exporting asynchronously and reporting the progress.

private void RadButton1_Click(object sender, EventArgs e)
{
    GridViewSpreadStreamExport spreadStreamExport = new GridViewSpreadStreamExport(this.radGridView1);
    spreadStreamExport.AsyncExportProgressChanged += SpreadStreamExport_AsyncExportProgressChanged;
    spreadStreamExport.AsyncExportCompleted += SpreadStreamExport_AsyncExportCompleted;
    spreadStreamExport.RunExportAsync(@"D:\StreamExport.xlsx", new SpreadStreamExportRenderer());
}
private void SpreadStreamExport_AsyncExportCompleted(object sender, AsyncCompletedEventArgs e)
{
    RadMessageBox.Show("Export Completed");
}
private void SpreadStreamExport_AsyncExportProgressChanged(object sender, ProgressChangedEventArgs e)
{
    radProgressBar1.Value1 = e.ProgressPercentage;
}

Private Sub RadButton1_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim spreadStreamExport As New GridViewSpreadStreamExport(Me.radGridView1)
    AddHandler spreadStreamExport.AsyncExportProgressChanged, AddressOf SpreadStreamExport_AsyncExportProgressChanged
    AddHandler spreadStreamExport.AsyncExportCompleted, AddressOf SpreadStreamExport_AsyncExportCompleted
    spreadStreamExport.RunExportAsync("D:\StreamExport.xlsx", New SpreadStreamExportRenderer())
End Sub
Private Sub SpreadStreamExport_AsyncExportCompleted(ByVal sender As Object, ByVal e As AsyncCompletedEventArgs)
    RadMessageBox.Show("Export Completed")
End Sub
Private Sub SpreadStreamExport_AsyncExportProgressChanged(ByVal sender As Object, ByVal e As ProgressChangedEventArgs)
    radProgressBar1.Value1 = e.ProgressPercentage
End Sub

See Also