Telerik UI for WinForms

Overview

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

Note

The HTML export functionality is located in the TelerikData.dll assembly. You need to include the following namespaces in order to access the types contained in TelerikData:

  • Telerik.WinControls.Data

  • Telerik.WinControls.UI.Export

Exporting Data

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:

Copy[C#] ExportToExcelIML initialization
ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
Copy[VB.NET] ExportToExcelIML initialization
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:

Copy[C#] Setting the hidden column option
exporter.HiddenColumnOption = Telerik.WinControls.UI.Export.HiddenOption.DoNotExport;
Copy[VB.NET] Setting the hidden column option
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. ExportVisualSettingsfalse

Copy[C#] Setting ExportVisualSettings
exporter.ExportVisualSettings = true;
Copy[VB.NET] Setting ExportVisualSettings
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)

Copy[C#] Setting Maximum Number of Rows
exporter.SheetMaxRows = ExcelMaxRows._1048576;
Copy[VB.NET] Setting Maximum Number of Rows
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.

Copy[C#] Setting the SheetName
exporter.SheetName = "Sheet";
Copy[VB.NET] Setting the SheetName
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

Copy[C#] Setting SummariesExportOption
exporter.SummariesExportOption = SummariesOption.DoNotExport;
Copy[VB.NET] Setting SummariesExportOption
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:

Copy[C#] Export to Excel in ExcelML format
string fileName = "C:\\ExportedData123.xls";
exporter.RunExport(fileName);
Copy[VB.NET] Export to Excel in ExcelML format
Dim fileName As String = "C:\\ExportedData.xls"
exporter.RunExport(fileName)

Format Codes

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. To get the desired formatting in Excel, the ExcelExportFormatString should be set to a valid Excel format code. A list of all format codes for Excel is available on the following link – Microsoft Office Excel Format Codes

Here is an example for a date time formatting:

Copy[C#] Fomatting dates
this.radGridView1.Columns["Date"].ExcelExportType = DisplayFormatType.Custom;
this.radGridView1.Columns["Date"].ExcelExportFormatString = "yyyy.MMMM.dd hh:mm:ss AM/PM";
Copy[VB.NET] Fomatting dates
Me.RadGridView1.Columns("Date").ExcelExportType = DisplayFormatType.Custom
Me.RadGridView1.Columns("Date").ExcelExportFormatString = " yyyy.MMMM.dd hh:mm:ss AM/PM "

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, colors, changing cell value, etc.) for every excel cell related to the exported RadGridView:

Copy[C#] Handling the ExcelCellFormatting event
void exporter_ExcelCellFormatting(object sender, Telerik.WinControls.UI.Export.ExcelML.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.ExcelStyleElement.AlignmentElement.WrapText = true;
    }
}
Copy[VB.NET] Handling the ExcelCellFormatting event
Private Sub exporter_ExcelCellFormatting(ByVal sender As Object, ByVal e As Telerik.WinControls.UI.Export.ExcelML.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.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):

 

Copy[C#] Handling the ExcelTableCreated event
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;
}
Copy[VB.NET] Handling the ExcelTableCreated event
Private Sub exporter_ExcelTableCreated(ByVal sender As Object, ByVal e As Telerik.WinControls.UI.Export.ExcelML.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

 

RELATED VIDEOS

Export to Excel with RadGridView for WinForms In this RadTip, John Kellar demonstrates how you can export data stored in a RadGridView for Windows Forms to Excel using the ExcelML export options. (Runtime: 08:53)

gridview-exporting-data-export-to-excel-via-excelml-format 001