RadGridView for WinForms

Contents
  • Overview
  • What's new
  • Release Notes
  • Installation and Deployment
  • Licensing and Distribution
  • Buttons
  • Calendar
  • Carousel
  • Chart
  • ComboBox and ListBox
  • Dock
  • Editors
  • Forms and Dialogs
  • GridView
    • Overview
    • Tutorial
    • Fundamentals
    • View Definitions
    • Columns
    • Rows
    • Cells
    • Editors
    • Context Menus
    • Populating with Data
    • Hierarchical Grid
    • Scrolling
    • Sorting
    • Grouping
    • Filtering
    • Insert/Update/Delete Records
    • Virtual Mode
    • Export to Excel
    • Design-Time Support
    • End-user Capabilities
    • Styling and Appearance
    • Localization
    • Save and Load Layout
  • Menus
  • MultiColumnComboBox
  • Panels and Labels
  • PanelBar
  • RibbonBar
  • Rotator
  • Scheduler
  • ShortCuts
  • SplitContainer
  • TabStrip
  • ToolStrip
  • Track and Status Controls
  • TreeView
  • Telerik Presentation Framework
  • Themes
  • Tools
  • API Reference
  • For More Help
RadControls Send comments on this topic.
Export to Excel
GridView > Export to Excel > Export to Excel

Glossary Item Box

RadGridView supports two methods for exporting data to Microsoft Excel format:

  • ExcelML
  • Primary Interop Assemblies 

Required namespaces

The Excel export functionality is located in the following namespaces - Telerik.WinControls.Data, and Telerik.WinControls.UI.Export.

Export in ExcelML format

This method offers excellent export performance and does not require MS Office installation on users' machines. The ExcelML format can be read from MS Excel 2002 (MS Office XP) and above.

 

Initialize ExportToExcelML object

Before running export to ExcelML, you have to initialize the ExportToExcelML class. The constructor takes one parameter: the RadGridView that will be exported:

[C#] ExportToExcelML initialization Copy Code
ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);  
[VB.NET] ExportToExcelML initialization Copy Code
Dim exporter As ExportToExcelML = New ExportToExcelML(Me.RadGridView1)

 

Hidden columns and rows option

You can choose one of the three options below which will allow you to have different behavior for the hidden column/rows. You can choose these options by HiddenColumnOption and HiddenRowOption properties:

  • ExportAlways
  • DoNotExport
  • ExportAsHidden (default)

 

MS Excel does not support other ways of hiding a column 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:

[C#] Setting the HiddenColumnOption Copy Code
ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
exporter.HiddenColumnOption = Telerik.WinControls.UI.Export.HiddenOption.DoNotExport;   
[VB.NET] Setting the HiddenColumnOption Copy Code
Dim exporter As ExportToExcelML = New ExportToExcelML(Me.RadGridView1)
exporter.HiddenColumnOption = Telerik.WinControls.UI.Export.HiddenOption.DoNotExport

 

Exporting Visual Settings

Using the ExcelML method allows you to export the visual settings (themes) to the Excel file. ExcelML has also a visual representation of the alternating row color. This feature works only if EnableAlternatingRow property is set to true. Note that it does not transfer the alternating row settings that come from control theme. RadGridView will also export all conditional formatting to the Excel file. The row height is exported with the default DPI transformation (60pixels = 72points).

 

You can enable exporting visual settings through the ExportVisualSettings property. By default the value of this property is false.
[C#] Setting ExportVisualSettings Copy Code
ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
exporter.ExportVisualSettings = true;   
[VB.NET] Setting ExportVisualSettings Copy Code
Dim exporter As ExportToExcelML = New ExportToExcelML(Me.RadGridView1)
exporter.ExportVisualSettings = True

 

MS Excel Max Rows Settings

RadGridView splits data on separate sheets if the number of rows is greater than Excel maximum. You can control the maximum number of rows through a SheetMaxRows property:

  • 1048576 (Max rows for Excel 2007)
  • 65536 (Max rows for previous versions of Excel) (default)
[C#] Setting Maximum Number of Rows Copy Code
ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
exporter.SheetMaxRows = ExcelMaxRows._1048576;   
[VB.NET] Setting Maximum Number of Rows Copy Code
Dim exporter As ExportToExcelML = New ExportToExcelML(Me.RadGridView1)
exporter.SheetMaxRows = ExcelMaxRows._1048576

 

MS Excel Sheet Name
You can specify the sheet name through SheetName property. 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.

[C#] Setting the SheetName Copy Code
ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
exporter.SheetName = “Sheet”  
[VB.NET] Setting the SheetName Copy Code
Dim exporter As ExportToExcelML = New ExportToExcelML(Me.RadGridView1)
exporter.SheetName = "Sheet"

 

Summaries export option

You can use SummariesExportOption property to specify how to export summary items. There are fourth option to chose:

  • ExportAll (default)
  • ExportOnlyTop
  • ExportOnlyBottom
  • DoNotExport
[C#] Setting SummariesExportOption Copy Code
ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
exporter.SummariesExportOption = SummariesOption.DoNotExport;   
[VB.NET] Setting SummariesExportOption Copy Code
Dim exporter As ExportToExcelML = New ExportToExcelML(Me.RadGridView1)
exporter.SummariesExportOption = SummariesOption.DoNotExport

 

RunExport method

Exporting data to Excel is done through the RunExport method of ExportToExcelML object. The RunExport method accepts the following parameter:

  • fileName - the name of the exported file

 

Consider the code sample below:

[C#] Export to Excel in ExcelML format Copy Code
private void Form3_Load(object sender, EventArgs e)
{
   ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
   exporter.SheetMaxRows = ExcelMaxRows._1048576;
   exporter.ExportVisualSettings = true;
   string fileName = "ExportedData.xls";
   exporter.RunExport(fileName);
}
[VB.NET] Export to Excel in ExcelML format Copy Code
Private Sub Form3_Load(ByVal sender As Object, ByVal e As EventArgs)
    Dim exporter As New ExportToExcelML(Me.radGridView1)
    exporter.SheetMaxRows = ExcelMaxRows._1048576
    exporter.ExportVisualSettings = True
    Dim fileName As String = "ExportedData.xls"

    exporter.RunExport(fileName)
End Sub

Format Dates

There are two properties in GridViewDataColumn object: ExcelExportType and ExcelExportFormatString. You can use them to specify the format of the exported column in the result excel file. Consider the code below:

[C#] Fomatting dates Copy Code
this.radGridView1.Columns["Date"].ExcelExportType = DisplayFormatType.Custom;
this.radGridView1.Columns["Date"].ExcelExportFormatString = "dddd, dd.MM.yyyy";
[VB.NET] Fomatting dates Copy Code
Me.RadGridView1.Columns("Date").ExcelExportType = DisplayFormatType.Custom
Me.RadGridView1.Columns("Date").ExcelExportFormatString = "dddd, dd.MM.yyyy"

 

Events

ExcelCellFormating event:

It gives an access to a single cell’s  SingleStyleElement that allows you to make additional formatting (adding border, setting alignment, text font, colours, changing cell value, etc.) for every excel cell related to the exported RadGridView:
[C#] Handling the ExcelCellFormatting event Copy Code
void exporter_ExcelCellFormatting(object sender,                                                 ExcelCellFormattingEventArgs e)
{
   if (e.GridRowInfoType == typeof(GridViewTableHeaderRowInfo))
   {
       BorderStyles border = new BorderStyles();
       border.Color = Color.Black;
       border.Weight = 2;
       border.LineStyle = LineStyle.Continuous;
       border.PositionType = PositionType.Bottom;
       e.ExcelStyleElement.Borders.Add(border);                
   }
   else if (e.GridRowIndex == 2 && e.GridColumnIndex == 1)
   {
       e.ExcelStyleElement.InteriorStyle.Color = Color.Yellow;
       e.CancelConditionalFormatting = true;
       e.ExcelStyleElement.AlignmentElement.WrapText = true;
   }
}
[VB.NET] Handling the ExcelCellFormatting event Copy Code
Private Sub exporter_ExcelCellFormatting(ByVal sender As Object, ByVal e As ExcelCellFormattingEventArgs)
 If e.GridRowInfoType Is GetType(GridViewTableHeaderRowInfo) Then
  Dim border As BorderStyles = New BorderStyles()
  border.Color = Color.Black
  border.Weight = 2
  border.LineStyle = LineStyle.Continuous
  border.PositionType = PositionType.Bottom
  e.ExcelStyleElement.Borders.Add(border)
 ElseIf e.GridRowIndex = 2 AndAlso e.GridColumnIndex = 1 Then
  e.ExcelStyleElement.InteriorStyle.Color = Color.Yellow
  e.CancelConditionalFormatting = True
  e.ExcelStyleElement.AlignmentElement.WrapText = True
 End If
End Sub

 

ExcelTableCreated event:

It can be used in together with the public method AddCustomExcelRow. It allows adding and formatting new custom rows on the top of the every sheet (it could be specified as a header in the excel sheet):

 

[C#] Handling the ExcelTableCreated event Copy Code
void exporter_ExcelTableCreated(object sender, ExcelTableCreatedEventArgs e)
{            
   string headerText = "Custom added header text.";
   SingleStyleElement style =
       ((ExportToExcelML)sender).AddCustomExcelRow(e.ExcelTableElement, 50, headerText);
   style.FontStyle.Bold = true;
   style.FontStyle.Size = 18;
   style.FontStyle.Color = Color.White;
   style.InteriorStyle.Color = Color.Red;
   style.InteriorStyle.Pattern = InteriorPatternType.Solid;
   style.AlignmentElement.HorizontalAlignment = HorizontalAlignmentType.Center;
   style.AlignmentElement.VerticalAlignment = VerticalAlignmentType.Center;
}

 

[VB.NET] Handling the ExcelTableCreated event Copy Code
Private Sub exporter_ExcelTableCreated(ByVal sender As Object, ByVal e As ExcelTableCreatedEventArgs)
 Dim headerText As String = "Custom added header text."
 Dim style As SingleStyleElement = (CType(sender, ExportToExcelML)).AddCustomExcelRow(e.ExcelTableElement, 50, headerText)
 style.FontStyle.Bold = True
 style.FontStyle.Size = 18
 style.FontStyle.Color = Color.White
 style.InteriorStyle.Color = Color.Red
 style.InteriorStyle.Pattern = InteriorPatternType.Solid
 style.AlignmentElement.HorizontalAlignment = HorizontalAlignmentType.Center
 style.AlignmentElement.VerticalAlignment = VerticalAlignmentType.Center
End Sub

 

Exporting through Primary Interop Assemblies

The second method for exporting to Excel involves a small intermediate export assembly, which accepts RadGridView to be exported as a parameter. At present the exported data is optimized for black & white printing. Prerequisites for using this method include a present installation of:

MS Office 2007
or
MS Office 2003 with installed Primary Interop Assemblies (PIA). Office 2003 PIAs are installed only in case of a complete setup. To install the PIAs, please visit http://msdn2.microsoft.com/en-us/library/aa159923(office.11).aspx

Exporting the data is done through the Export method of RadGridViewExcelExporter object. The method accepts three parameters:

  • radGridView - the instance of RadGridView to export
  • path - the file name of the exported file.
  • sheetName - the name of the Excel sheet where the data will be exported
[C#] Export to Excel through Interop assemblies Copy Code
RadGridViewExcelExporter exporter2 = new RadGridViewExcelExporter();
exporter2.Export(this.radGridView1, fileName, "Sheet1");
[VB] Export to Excel through Interop assemblies Copy Code
Dim exporter2 As New RadGridViewExcelExporter()
exporter2.Export(Me.radGridView1, fileName, "Sheet1")