[Solved] Change Grid structure/data on Export to Excel

[Clinton Smyth]

Hi

We're currently allowing our users to export data in a RadGrid to Excel using the RadGrid1.MasterTableView.ExportToExcel() method.  The users can group the data in the grid while using the web application.  We need to make a few modifications to the grid - both structure and data - when the users clicks an 'Export to Excel' button.

While we know how the logic for making the changes below, our big issue is that none of these changes take effect during the 'Export to Excel' button click event.

We're facing a few issues:

1. For the export we need to normalise the grid, removing any grouping, to a flat structure (i.e. columns and rows only).  This includes making a column visible that's not visible while the grouping is in effect.

2. The grid contains images (eg. a check mark for a boolean field).  All the images appear as broken links in the Excel file.  We'd like to simply replace the image with a 'Yes' / 'No' or 'True' / 'False' as text.  Note that logic depends on the boolean values returned from the database, so this replacement must be done on the fly during the export (i.e. we can't change the backing value in the grid).

3. The sortable columns have column names displayed in hyperlinks in the Excel file.  While they work fine in the browser, they are broken in the Excel file.  We need to switch the hyperlink off and just have normal text headings (in bold possibly?).

4. Command line (specified as a CommandItemTemplate) appears in the spreadsheet.  We need to prevent this from appearing.

5. When opening the Excel file, the following error message appears: 'The file you are trying to open is in a different format than specified by the file extension ..' and warns the user of possible corruption and security concerns.  Although it is mentioned on http://www.telerik.com/help/aspnet-ajax/grdexport.html, it doesn't address the issue.  Is there a way to prevent this from appearing?

We've tried a number of options but to no avail.  Do we need to rebind the grid?  While testing, a grid.Rebind() causes a null exception.  Obviously any changes that are made must only apply to the exported Excel file and not have any side effect on the grid state as displayed in the browser.

We've also tried using ExportToCsv() and while this addresses some of the issues above, it doesn't help with the structure change & data modification.

Thanks

[Frank]

Hi there,

I'm looking forward to any reply on this post.
I have a more simple - but similar - problem.

I need to change some column -names on exporting to CSV.

How can that be done?

Best,
Frank

[Clinton Smyth]

I've since gone through http://www.telerik.com/help/aspnet-ajax/grdexporttipstricks.html and none of the tips suggested affects the export.  We're managing the grid with a RadAjaxManager - could this be affecting the problem?

[Daniel]

Hello Clinton,

Straight onto your questions:

1. For the export we need to normalise the grid, removing any grouping, to a flat structure (i.e. columns and rows only).  This includes making a column visible that's not visible while the grouping is in effect.
You can show all hidden columns and temporary disable grouping and any other unwanted features just before the export.

protected void Button1_Click(object sender, EventArgs e)
{
    RadGrid1.GroupingEnabled = false;
    foreach (GridColumn column in RadGrid1.MasterTableView.Columns)
        column.Visible = true;
    RadGrid1.Rebind(); //this is needed to show the invisible columns
    //RadGrid1.ExportSettings.IgnorePaging = true;  //this setting will have the same effect since it will cause your RadGrid to rebind
    RadGrid1.MasterTableView.ExportToExcel();
}

2. The grid contains images (eg. a check mark for a boolean field).  All the images appear as broken links in the Excel file.  We'd like to simply replace the image with a 'Yes' / 'No' or 'True' / 'False' as text.  Note that logic depends on the boolean values returned from the database, so this replacement must be done on the fly during the export (i.e. we can't change the backing value in the grid).
On ItemCreated event you could remove the images and replace them with plain text.

bool isExport = false;
protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
{
    if (e.Item is GridDataItem && isExport)
    {
        GridDataItem item = e.Item as GridDataItem;
        bool bValue = (bool)DataBinder.Eval(item.DataItem, "Bool");
        item["myBoolColumn"].Controls.Clear();
        item["myBoolColumn"].Text = bValue.ToString();
    }
}
protected void Button1_Click(object sender, EventArgs e)
{
    RadGrid1.ExportSettings.IgnorePaging = true;
    isExport = true;
    RadGrid1.MasterTableView.ExportToExcel();
}

3. The sortable columns have column names displayed in hyperlinks in the Excel file.  While they work fine in the browser, they are broken in the Excel file.  We need to switch the hyperlink off and just have normal text headings (in bold possibly?).
As in the previous suggestion, the underlying controls can be removed. Alternatively, the ExportOnlyData="true" will have the same effect.

4. Command line (specified as a CommandItemTemplate) appears in the spreadsheet.  We need to prevent this from appearing.
Simply set e.Item.Visible = false on ItemCreated event when e.Item is GridCommandItem. Again, ExportOnlyData will affect the visibility of this item.

protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)
{
    if (e.Item is GridCommandItem && isExport)
        e.Item.Visible = false;
}

5. When opening the Excel file, the following error message appears: 'The file you are trying to open is in a different format than specified by the file extension ..' and warns the user of possible corruption and security concerns.  Although it is mentioned on http://www.telerik.com/help/aspnet-ajax/grdexport.html, it doesn't address the issue.  Is there a way to prevent this from appearing?
I recommend you examine the following blog post:
"The file you are trying to open, '[filename]', is in a different format" Excel Error

Best regards
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Frank]

Hi,

I have read the post, and I think I can use the idea about hiding / showing columns...?

- Like I wrote earlier, I need to change column names on exporting -  but a solution could be, to include the columns in the datasource, and not display them when viewing the grid, but make them visible before exporting - AND make the other columns invisible on exporting....
Can you follow me - sorry if I not make myself clear...

The invisible columns has a Display="false" in design view, and first of all I'm trying to get them displayed when exporting, but the code below does not work, and also the 'column.Display = True' - doesn't work either.

- Any ideas?

 Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        RadGrid2.GroupingEnabled = False
        For Each column As GridColumn In RadGrid2.MasterTableView.Columns
            column.Visible = True
        Next
        RadGrid2.Rebind()
        'this is needed to show the invisible columns
        'RadGrid1.ExportSettings.IgnorePaging = true;  //this setting will have the same effect since it will cause your RadGrid to rebind
        RadGrid2.MasterTableView.ExportToExcel()

    End Sub

[Daniel]

Hello Frank,

Please test the attached demo and let me know if you have any questions.

Best regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Frank]

Thanks a lot.
Very simple, and works fine.
I simply have visible and not visible columns - when exporting I switch between the visible and in-visible columns..
Cool:

 For Each col As GridColumn In RadGrid2.MasterTableView.Columns
            If col.Visible = False Then
                col.Visible = True
                col.HeaderText = "Changed " & col.HeaderText
            Else
                col.Visible = False
            End If
        Next

Best regards,
Frank