Edit this page

Spread export

GridViewSpreadExport utilizes our RadSpreadProcessing library to export the content of RadGridView to xlsx, csv, pdf and txt formats. This article will explain in detail the SpreadExport abilities and will demonstrate how to use it.

Here is how the following grid, looks when exported.
gridview-exporting-data-spread-export 001gridview-exporting-data-spread-export 002

note

The spread export functionality is located in the TelerikExport.dll assembly which must be added to your project. You need to include the following namespace in order to access the types contained in TelerikExport:

  • Telerik.WinControls.Export
important

Since this functionality is using the RadSpreadProcessing Library 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.Zip

Exporting

To use the spread export functionality, an instance of the GridViewSpreadExport object should be created, passing as parameter the RadGridView 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.


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)

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.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

Properties

  • ExportFormat: Defines the format the grid 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. 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.

  • 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.

    note

    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.

Exported Cells Data Type

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. The following help article is quite useful for specifying the desired format: 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.

  • GridColumnIndex: 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

gridview-exporting-data-spread-export 003

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

gridview-exporting-data-spread-export 005

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 contents asynchronously. This feature can be utilized by calling the RunExportAsync method on the GridViewSpreadExport object.

important

Due to performance considerations and in order to decrease the consumed memory the visual settings and view definitions in RadGridView are not exported when the operation is run async.

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 will demonstrate how the async spread export feature can be combined with a RadProgressBar control to deliver better user experience.

Figure: Exporting Data Asynchronously

gridview-exporting-data-async-spread-export 001

1. Bind RadGridView and define 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

See Also