Export to Excel - Hide CommandItemDisplay

[GP]

I have a radgrid that I am trying to export using HTML export.  However, the command item row is exported to excel with the export button as well.  I cannot delete the button from excel, so it's permanent fixture in the worksheet.

Is there any way to hide the command item display or at least the button when exporting to excel?  I have tried the following two methods, and neither work:

Protected Sub RadGrid1_ExcelExportCellFormatting(ByVal source As Object, ByVal e As ExcelExportCellFormattingEventArgs) Handles RadGrid1.ExcelExportCellFormatting
        RadGrid1.MasterTableView.CommandItemSettings.ShowExportToExcelButton = False
    End Sub

Also tried:

    Protected Sub RadGrid1_ExcelExportCellFormatting(ByVal source As Object, ByVal e As ExcelExportCellFormattingEventArgs) Handles RadGrid1.ExcelExportCellFormatting
        RadGrid1.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None
    End Sub

Nope.  Neither worked.

Attached is a screen shot of the spreadsheet.  (slightly modified to hide data, but format and style are the same.)

Thank you for your help in this matter.

GP

[Daniel]

Hello GP,

Please try one of the following options:

1) ExportOnlyData="true"

<ExportSettings ExportOnlyData="true" IgnorePaging="true" OpenInNewWindow="true" Excel-Format="Html" />

or

2) Remove the item on programmatically

protected void RadGrid1_ItemCommand(object source, GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.ExportToExcelCommandName)
    {
        foreach (GridCommandItem item in e.Item.OwnerTableView.GetItems(GridItemType.CommandItem))
            item.Visible = true;
    }
}

Regards,
Daniel
the Telerik team

[GP]

Thank you, Daniel.  The second method worked just as I needed it to.  I had to tweak it a bit to get it to vb.net syntax, and set the visible to false since I didn't want the command item to be seen in the export.  Here's the code for anyone looking for the vb.net version.

    Protected Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As GridCommandEventArgs) Handles RadGrid1.ItemCommand
        If (e.CommandName = RadGrid.ExportToExcelCommandName) Then
            For Each item As GridCommandItem In e.Item.OwnerTableView.GetItems(GridItemType.CommandItem)
                item.Visible = False
            Next
        End If
    End Sub

I appreciate your help. 

[Raymond]

I just tried the programatic method and it seems to be ignoring it as the commend item with export button still shows up in the export.  I can step through the code and see it find the command item and set visible = false...but it shows up in the export anyway.  

 

I also tried setting ExportDataOnly=true...​but that prevents the strikeout font attribute from being exported.

 

Any ideas?

[Kostadin]

Hi Raymond,

Could you please let me know whether you have enabled IgnorePaging property or not? In case you have enable it then you can use the following approach to hide the CommandItem.

bool isExport = false;
protected void RadGrid1_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.ExportToExcelCommandName)
    {
        isExport = true;
    }
}
protected void RadGrid1_ItemCreated(object sender, Telerik.Web.UI.GridItemEventArgs e)
{
    if (e.Item is GridCommandItem && isExport)
    {
        e.Item.Visible = false;
    }
}

The approach will work even if you do not enabled the IgnorePaging property but in such case you need to rebind the grid.

Regards,
Kostadin
Telerik

[Raymond]

Ok...that makes the command button go away.  But for some reason it also removes the font strikeout attribute.  Is there any way to removed the command button...and keep the font strikeout attribute?

 

Below is sample code to demonstrate the problem.  As is, the code exports the strikeout attribute and the export command button.  But if you uncomment the line in the ItemCreated event so that it hides the command button...then does remove the command button.  But it also removes the font strikeout attribute from the export file. 

VB File:

 Imports Telerik.Web.UI
Partial Class Testpage
Inherits MyProcs
Property TheData As DataTable
Get
Dim RetVal As DataTable = TryCast(Session("MyData"), DataTable)
Return RetVal
End Get
Set(value As DataTable)
Session("MyData") = value
End Set
End Property
Dim IsExport As Boolean = False
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
GetData()
End If
End Sub
Private Sub Getdata()
Dim T As DataTable = New DataTable("Data")
T.Columns.Add("Data", Type.GetType("System.String"))
T.Columns.Add("Obsolete", Type.GetType("System.String"))
T.Rows.Add({"One", "N"})
T.Rows.Add({"Two", "N"})
T.Rows.Add({"Three", "Y"})
T.Rows.Add({"Four", "N"})
TheData = T
End Sub
Protected Sub rgReport_NeedDataSource(sender As Object, e As Telerik.Web.UI.GridNeedDataSourceEventArgs)
rgReport.DataSource = TheData
End Sub
Protected Sub rgReport_ItemDataBound(sender As Object, e As Telerik.Web.UI.GridItemEventArgs)
If TypeOf (e.Item) Is GridDataItem Then
Dim item As GridDataItem = CType(e.Item, GridDataItem)
If item("Obsolete").Text = "Y" Then
e.Item.Font.Strikeout = True
End If
End If
End Sub
Protected Sub rgReport_ItemCommand(sender As Object, e As GridCommandEventArgs)
If e.CommandName = RadGrid.ExportToExcelCommandName Then
IsExport = True
End If
End Sub
Protected Sub rgReport_ItemCreated(sender As Object, e As GridItemEventArgs)
If TypeOf (e.Item) Is GridCommandItem AndAlso IsExport Then
'e.Item.Visible = False
End If
End Sub
End Class

 ASPX File:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="TestPage.aspx.vb" Inherits="TestPage" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<telerik:RadScriptManager ID="ScriptManager1" runat="server" />
<telerik:RadGrid
ID="rgReport"
runat="server"
Width="100%"
Height="100%"
AutoGenerateColumns="false"
OnNeedDataSource="rgReport_NeedDataSource"
OnItemDataBound="rgReport_ItemDataBound"
OnItemCommand="rgReport_ItemCommand"
OnItemCreated="rgReport_ItemCreated"
>
<ExportSettings IgnorePaging="true" ExportOnlyData="false" OpenInNewWindow="true" />
<MasterTableView
CommandItemDisplay="Top"
>
<Columns>
<telerik:GridBoundColumn
UniqueName="Data"
DataField="Data"
HeaderText="Data"
>
</telerik:GridBoundColumn>
<telerik:GridBoundColumn
UniqueName="Obsolete"
DataField="Obsolete"
HeaderText="Obsolete"
>
</telerik:GridBoundColumn>
</Columns>
<CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false" ShowRefreshButton="false" />
</MasterTableView>
</telerik:RadGrid>
<br />
</div>
</form>
</body>
</html>

[Kostadin]

Hi Raymond,

A possible solution is to add the text-decoration attribute manually on each cell. This way the exported text will be strikeout. Please check out the following code snippet.

Protected Sub rgReport_ItemDataBound(sender As Object, e As Telerik.Web.UI.GridItemEventArgs)
    If TypeOf (e.Item) Is GridDataItem Then
        Dim item As GridDataItem = CType(e.Item, GridDataItem)
        If item("Obsolete").Text = "Y" Then
            For Each cell As TableCell In item.Cells
                cell.Style.Add("text-decoration", "line-through")
            Next
        End If
    End If
End Sub

Regards,
Kostadin
Telerik