Export to Excel

[jasear]

Hi,

I have a radgrid that contains columns where I trim the text I am displaying (to 50 characters)  in the Item databound event of the RadGrid.

However, the problem I have is that by doing that when I then export the grid data to excel the text also appears trimmed in there. Is it possible to trim the text in the UI without it getting trimmed when I export to Excel?

[Shinu]

Hi Jasear,

When you change a text in grid, it will be exported in that format. If you want to change any text during export, you can handle it in the OnExcelExportCellFormatting event of the grid.

C#:

protected void RadGrid1_ExcelExportCellFormatting(object sender, ExcelExportCellFormattingEventArgs e)
{
    GridDataItem item = e.Cell.Parent as GridDataItem;
    if (e.FormattedColumn.UniqueName == "ColumnUniqueName")
    {
        TableCell cell = item["ColumnUniqueName"];
        cell.Text = "Set the original text here";
    }
}

Thanks,
Shinu

[Richard]

Hi Shinu,

Does this event fire on a per column basis?

The problem is that the original value is in a datatable and I am not sure how I can get it in this event.

If this event was firing on a per row basis then maybe I could use the id to find the row in the datatable and then extract the original values from it and set it in this event. But if the event is firing on a per column basis then it seems like I will have to do an incredible amount of processing to set them to their original values.

[Shinu]

Hi Richard,

The ExportCellFormatting event fires for each cell in each data item in RadGrid. Please take a look at this article for more information on ExportCellFormating / ExcelExportCellFormatting events.

Thanks,
Shinu

[Richard]

Well that doesnt solve my issue then. I need an event that would fire on a per row basis where I can get the original data stored in the list and then restore the values.

[Shinu]

Hi Richard,

You can a take a look at the following code snippet that I tried, which exports the correct record.

ASPX:

<telerik:RadGrid ID="RadGrid1" runat="server" DataSourceID="SqlDataSource1" OnItemDataBound="RadGrid1_ItemDataBound" OnExcelExportCellFormatting="RadGrid1_ExcelExportCellFormatting">
    <ExportSettings ExportOnlyData="true" IgnorePaging="true" OpenInNewWindow="true">
    </ExportSettings>
    <MasterTableView DataKeyNames="OrderID" CommandItemDisplay="Top" CommandItemSettings-ShowExportToExcelButton="true">
        <Columns>
            <telerik:GridBoundColumn UniqueName="OrderID" DataField="OrderID" HeaderText="OrderID">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="ShipAddress" HeaderText="ShipAddress" UniqueName="ShipAddress" />
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

C#:

protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
{
  if (e.Item is GridDataItem)
  {
   GridDataItem dataItem = (GridDataItem)e.Item;
    {
      if (dataItem["ShipAddress"].Text.Length > 5)
     {
       dataItem["ShipAddress"].Text = dataItem["ShipAddress"].Text.Substring(0, 5);
     }
    }
  }
}
protected void RadGrid1_ExcelExportCellFormatting(object sender, ExcelExportCellFormattingEventArgs e)
{
  GridDataItem dataItem = e.Cell.Parent as GridDataItem;
  DataRowView dataRow = (DataRowView)dataItem.DataItem;
  dataItem["ShipAddress"].Text = dataRow["ShipAddress"].ToString();
}

Thanks,
Shinu

[Vaibhav]

Hi.
I need  to export only selected rows to excel.
I am able to hide columns before exporting.
I have searched a lot regarding hiding unselected rows/Exporting only selected rows in telerik threads.
In all my search results I found the below line of code. 

​ if (Grid.SelectedItems.Count != 0)
{
foreach (GridDataItem item in Grid.MasterTableView.Items)
{
if (!item.Selected)
item.Visible = false;
}
} My excel format is"BIFF".
Its very urgent.
Please help