Export to Excel

This method offers exporting functionality and does not require MS Office installation on users' machines. The PivotExcelML format can be read by MS Excel 2002 (MS Office XP) and above.

Figure 1: RadPivotGrid Export to Excel

Exporting Data

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

• C#
• VB.NET

PivotExportToExcelML exporter = new PivotExportToExcelML(this.radPivotGrid1);

Dim exporter As PivotExportToExcelML = New PivotExportToExcelML(Me.radPivotGrid1)

Exporting Visual Settings

Using the PivotExcelML method allows you to export the visual settings (themes) to the Excel file. ExcelML has also a visual representation of the alternating column color. 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 true:

• C#
• VB.NET

exporter.ExportVisualSettings = true;

exporter.ExportVisualSettings = True

Setting the sheet name

You can specify the sheet name through SheetName property. If your data is large enough to be split on more than one sheet, then the export method adds index to the names of the next sheets.

• C#
• VB.NET

exporter.SheetName = "Sheet";

exporter.SheetName = "Sheet"

RunExport method

Exporting data to Excel is done through the RunExport method of PivotExportToExcelML object. The RunExport method accepts a string parameter with a valid file path. Consider the code sample below:

• C#
• VB.NET

string fileName = "c:\\Sheet1.xls";
exporter.RunExport(fileName);

Dim fileName As String = "c:\Sheet1.xls"
exporter.RunExport(fileName)

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 RadPivotGrid:

• C#
• VB.NET

void exporter_PivotExcelCellFormatting(object sender, Telerik.WinControls.UI.Export.ExcelPivotCellExportingEventArgs e)  
{   
    decimal value = 0;        
    if (decimal.TryParse(e.Cell.Text, out value))           
    {
        if(value>1000)                   
            e.Cell.BackColor = System.Drawing.Color.Red;    
        if (value < 100)                   
            e.Cell.BackColor = System.Drawing.Color.Green;         
    } 
}

Private Sub exporter_PivotExcelCellFormatting(sender As Object, e As Telerik.WinControls.UI.Export.ExcelPivotCellExportingEventArgs)
    Dim value As Decimal = 0
    If Decimal.TryParse(e.Cell.Text, value) Then
        If value > 1000 Then
            e.Cell.BackColor = System.Drawing.Color.Red
        End If
        If value < 100 Then
            e.Cell.BackColor = System.Drawing.Color.Green
        End If
    End If
End Sub