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:
- Cell - this is a reference to the current TableCell. You can use it to apply specific CSS style or to gain access to the GridDataItem object:
CopyC#
GridDataItem item = e.Cell.Parent as GridDataItem;
CopyVB.NET
Dim item As GridDataItem = TryCast(e.Cell.Parent, GridDataItem)
- FormattedColumn - this property returns an object of type GridColumn. It helps to distinguish to which column the current cell belongs to.
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 Sub
Styling 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 Sub
Using 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 Sub
Hiding 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 Sub
Hiding items
There are two common ways to hide an item.
- Directly - in the button handler when exporting from a button or on ItemCommand when exporting from the built-in buttons
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 Sub
CopyC#
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 Sub- On ItemCreated / ItemDataBound - this approach should be used when IgnorePaging="true" or when you call RadGrid.Rebind before exporting.
CopyC#
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 Sub
Resizing/Aligning Columns
There are various ways to set the width of a given column.
CopyASPX
<telerik:GridBoundColumn ... HeaderStyle-Width="20px" />
- in code-behind - before export:
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 Sub-
in code-behind - on TH elements
CopyC#
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 Sub
Alignment
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