RadControls for ASP.NET AJAX RadGrid is able to export your data to Word and Excel using HTML/CSS-based format. It is not the native binary format used by Microsoft Office but it is a popular way to export data and it is also officially supported by Microsoft:
Microsoft® Office HTML and XML Reference
Styles / Appearance
Due to the fact that this format is based on standard HTML/CSS it is quite straightforward to control the appearance of the output using inline styles. You can apply CSS tags/classes to the cells/rows/tables, etc.
 Caution |
|---|
Microsoft Word 2003 displays grid lines by default. These lines just shows where the table/cell/row borders are. If you want to remove them, you should choose Table>>Show Gridlines (uncheck this option).
|
ExportCellFormating / ExcelExportCellFormatting events
In order to aid the developers, we exposed the ExportCellFormatting event. It fires for each cell in each data item in RadGrid.
 Note |
|---|
Please note that the ExcelExportCellFormatting event (Excel-specific) is marked as obsolete as from RadControls for ASP.NET AJAX Q1 2011.
|
There are two important members exposed by the ExportCellFormattingEventArgs:
CopyC#
GridDataItem item = e.Cell.Parent as GridDataItem;
CopyVB.NET
Dim item As GridDataItem = TryCast(e.Cell.Parent, GridDataItem)
CopyC#
GridColumn column = e.FormattedColumn as GridColumn;
string columnName = column.UniqueName;
CopyVB.NET
Dim column As GridColumn = TryCast(e.FormattedColumn, GridColumn)
Dim columnName As String = column.UniqueName
HTMLExporting event
The purpose of this event is to allow the developer to insert global styles (CSS) or configuration options (XML) to the exported file.
A possible application for this event is to enable the grid lines for the current worksheet:
CopyC#
protected void RadGrid1_HTMLExporting(object sender, GridHTMLExportingEventArgs e)
{
e.Styles.Append("body { border:solid 0.1pt #CCCCCC; }");
}CopyVB.NET
Protected Sub RadGrid1_HTMLExporting(ByVal sender As Object, ByVal e As GridHTMLExportingEventArgs) Handles RadGrid1.HTMLExporting
e.Styles.Append("body { border:solid 0.1pt #CCCCCC; }")
End SubStyling rows/cells
Thanks to the ExcelExportCellFormatting event it is really easy to apply custom styles to the rows/cells. The following code-snippet demonstrates how to style the alternating items:
CopyC#
protected void RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e)
{
GridDataItem item = e.Cell.Parent as GridDataItem;
if (item.ItemType == GridItemType.AlternatingItem)
item.Style["background-color"] = "#359AFF";
else
item.Style["background-color"] = "#2D62FF";
}CopyVB.NET
Protected Sub RadGrid1_ExcelExportCellFormatting(ByVal source As Object, ByVal e As ExcelExportCellFormattingEventArgs) Handles RadGrid1.ExcelExportCellFormatting
Dim item As GridDataItem = TryCast(e.Cell.Parent, GridDataItem)
If item.ItemType = GridItemType.AlternatingItem Then
item.Style("background-color") = "#359AFF"
Else
item.Style("background-color") = "#2D62FF"
End If
End SubSometimes the developer needs to highlight the negative values (for example: -1, -5, -10.5) - this could be achieved in the same event handler:
CopyC#
protected void RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e)
{
if (e.FormattedColumn.UniqueName == "MyColumn" && double.Parse(e.Cell.Text) < 0)
e.Cell.Style["background-color"] = "#FA2020";
}CopyVB.NET
Protected Sub RadGrid1_ExcelExportCellFormatting(ByVal source As Object, ByVal e As ExcelExportCellFormattingEventArgs) Handles RadGrid1.ExcelExportCellFormatting
If e.FormattedColumn.UniqueName = "MyColumn" AndAlso Double.Parse(e.Cell.Text) < 0 Then
e.Cell.Style("background-color") = "#FA2020"
End If
End SubUsing ItemCreated/ItemDataBound
These events are useable in different scenarios, for example:
to apply styles to items other than GridDataItem
to format Word document, since the ExcelExportCellFormatting event is Excel-specific
They are not as convenient as the ExcelExportCellFormatting event because the developer should use flag to distinguish whether the current item/cell is being exported or displayed.
Please keep in mind that if you don't use IgnorePaging="true"RadGrid will be exported directly and the ItemCreated/ItemDataBound events won't be fired.
CopyC#
bool isExport = false;
void Button1_Click(object sender, EventArgs e)
{
isExport = true;
RadGrid1.MasterTableView.ExportToExcel();
}
protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)
{
if (e.Item is GridHeaderItem && isExport)
e.Item.Style["background-color"] = "#EEAAEC";
}CopyVB.NET
Private isExport As Boolean = False
Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
isExport = True
RadGrid1.MasterTableView.ExportToExcel()
End Sub
Protected Sub RadGrid1_ItemCreated(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles RadGrid1.ItemCreated
If TypeOf e.Item Is GridHeaderItem AndAlso isExport Then
e.Item.Style("background-color") = "#EEAAEC"
End If
End Sub
When IgnorePaging="false" you should rebind RadGrid manually, otherwise this approach won't work. The above code needs only one change - put RadGrid1.Rebind() after the point where you set the flag.
Number formats / Formulas
Microsoft Office HTML format allows you to define custom number formats by using the mso-number-format style attribute. Several examples are shown below:
CopyC#
protected void RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e)
{
switch (e.FormattedColumn.UniqueName)
{
case "C1":
e.Cell.Style["mso-number-format"] = @"\@";
break;
case "C2":
e.Cell.Style["mso-number-format"] = @"$0.00";
break;
case "C3":
e.Cell.Style["mso-number-format"] = @"0000";
break;
case "C4":
e.Cell.Style["mso-number-format"] = @"mm\/dd\/yyyy";
break;
}
}CopyVB.NET
Protected Sub RadGrid1_ExcelExportCellFormatting(ByVal source As Object, ByVal e As ExcelExportCellFormattingEventArgs) Handles RadGrid1.ExcelExportCellFormatting
Select Case e.FormattedColumn.UniqueName
Case "C1"
e.Cell.Style("mso-number-format") = "\@"
Exit Select
Case "C2"
e.Cell.Style("mso-number-format") = "$0.00"
Exit Select
Case "C3"
e.Cell.Style("mso-number-format") = "0000"
Exit Select
Case "C4"
e.Cell.Style("mso-number-format") = "mm\/dd\/yyyy"
Exit Select
End Select
End Sub
You can see some of the most common custom number formats and their description in the following table:
| Format | Description |
|---|
| \@ | text |
| "0\.000" | 3 decimals |
| \#\,\#\#0\.000 | comma separators (and 3 decimals) |
| "mm\/dd\/yy" | Date format |
| "d\\-mmm\\-yyyy" | another date format |
| Percent | percent |
Another interesting feature, supported by the Excel export are the formulas. You can assign the desired formula to a specific cell by applying the formula attribute.
| Note |
|---|
The formula attribute is valid in the context of the Office HTML only. You shouldn't apply this attribute unconditionally in order to prevent validation problems.
|
CopyC#
protected void RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e)
{
GridDataItem item = e.Cell.Parent as GridDataItem;
if (e.FormattedColumn.UniqueName == "C5")
e.Cell.Attributes["formula"] = "=E1*2.14";
if (e.FormattedColumn.UniqueName == "C1")
e.Cell.Attributes["formula"] = String.Format("=SUM(B{0}:D{0})", item.ItemIndex);
}CopyVB.NET
Protected Sub RadGrid1_ExcelExportCellFormatting(ByVal source As Object, ByVal e As ExcelExportCellFormattingEventArgs) Handles RadGrid1.ExcelExportCellFormatting
Dim item As GridDataItem = TryCast(e.Cell.Parent, GridDataItem)
If e.FormattedColumn.UniqueName = "C5" Then
e.Cell.Attributes("formula") = "=E1*2.14"
End If
If e.FormattedColumn.UniqueName = "C1" Then
e.Cell.Attributes("formula") = [String].Format("=SUM(B{0}:D{0})", item.ItemIndex)
End If
End SubHiding columns
You can use the HideStructureColumns property to hide GridRowIndicatorColumn, GridExpandColumn and GridGroupSplitterColumn. For the other columns types, you can use the following approach:
CopyC#
protected void Button1_Click(object sender, EventArgs e)
{
RadGrid1.MasterTableView.GetColumn("C2").Visible = false;
RadGrid1.MasterTableView.ExportToWord();
}CopyVB.NET
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
RadGrid1.MasterTableView.GetColumn("C2").Visible = False
RadGrid1.MasterTableView.ExportToWord()
End SubHiding items
There are two common ways to hide an item.
CopyC#
protected void RadGrid1_ItemCommand(object source, GridCommandEventArgs e)
{
if (e.CommandName == RadGrid.ExportToWordCommandName)
RadGrid1.MasterTableView.Items[2].Visible = false;
}CopyVB.NET
Protected Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs) Handles RadGrid1.ItemCommand
If e.CommandName = RadGrid.ExportToWordCommandName Then
RadGrid1.MasterTableView.Items(2).Visible = False
End If
End SubCopyC#
protected void Button1_Click(object sender, EventArgs e)
{
RadGrid1.MasterTableView.Items[2].Visible = false;
RadGrid1.MasterTableView.ExportToWord();
}CopyVB.NET
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
RadGrid1.MasterTableView.Items(2).Visible = False
RadGrid1.MasterTableView.ExportToWord()
End SubCopyC#
bool isWordExport = false;
protected void RadGrid1_ItemCommand(object source, GridCommandEventArgs e)
{
if (e.CommandName == RadGrid.ExportToWordCommandName)
isWordExport = true;
}
protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)
{
if (isWordExport && e.Item.ItemIndex == 2)
e.Item.Visible = false;
}CopyVB.NET
Private isWordExport As Boolean = False
Protected Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs)
If e.CommandName = RadGrid.ExportToWordCommandName Then
isWordExport = True
End If
End Sub
Protected Sub RadGrid1_ItemCreated(ByVal sender As Object, ByVal e As GridItemEventArgs)
If isWordExport AndAlso e.Item.ItemIndex = 2 Then
e.Item.Visible = False
End If
End SubResizing/Aligning Columns
There are various ways to set the width of a given column.
CopyASPX
<telerik:GridBoundColumn ... HeaderStyle-Width="20px" />
CopyC#
protected void Button1_Click(object sender, EventArgs e)
{
RadGrid1.MasterTableView.GetColumn("C1").HeaderStyle.Width = Unit.Pixel(20);
RadGrid1.MasterTableView.ExportToExcel();
}CopyVB.NET
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
RadGrid1.MasterTableView.GetColumn("C1").HeaderStyle.Width = Unit.Pixel(20)
RadGrid1.MasterTableView.ExportToExcel()
End SubCopyC#
protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)
{
if (e.Item is GridHeaderItem && isExport)
{
foreach (TableCell cell in e.Item.Cells)
cell.Style["width"] = "20px";
}
}CopyVB.NET
Protected Sub RadGrid1_ItemCreated(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles RadGrid1.ItemCreated
If TypeOf e.Item Is GridHeaderItem AndAlso isExport Then
For Each cell As TableCell In e.Item.Cells
cell.Style("width") = "20px"
Next
End If
End SubAlignment
You can specify the horizontal alignment, using the text-alignCSS attribute. Please note that it is not possible to apply this attribute to the whole header row - you should set it to each cell (TH) separately. The aforementioned limitation concerns only to the header items.
Unsupported scenarios
There are several limitations that you should have in mind:
embedded images are not supported
exporting invisible RadGrid - to avoid problems with missing content, you should temporary show RadGrid before export
OpenOffice, AbiWord, etc doesn't support this standard so they won't show the files properly
in theory all Microsoft Office versions from 2000 up might work, although we don't guarantee that any version, prior to 2003 will display the Office HTML formats as expected