Export to Excel failing with buttons in the header.

[Dustin Dodson]

OK this is a long one to explain. I have a grid that I have a custom column template that has 2 dates under one heading, but each date has to be sortable. So I followed the demo found here: http://demos.telerik.com/aspnet-ajax/grid/examples/generalfeatures/columntypes/defaultcs.aspx 

That part works. The problem comes when I need to export to Excel. I am trying to export on the server side like this:

protected void btnXLS_Click(object sender, EventArgs e)
{
    rgvNextActionDue.ExportSettings.FileName = "NextActionDue";
    rgvNextActionDue.ExportSettings.OpenInNewWindow = true;
    rgvNextActionDue.ExportSettings.ExportOnlyData = true;
    rgvNextActionDue.ExportSettings.IgnorePaging = true;
    rgvNextActionDue.ExportSettings.Excel.FileExtension = "xls";
    rgvNextActionDue.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
    rgvNextActionDue.MasterTableView.ExportToExcel();
}

It exports fine, but when I try to open it I get an error that says "unknown" check some temporary file at C:\Documents and Settings\username\Local Settings\Temporary Internet Files\Content.MSO\SomeRandomNamedErrorFile.txt

I checked the randomly named error file and it says:

XML PARSE ERROR:  Missing end-tag

  Error occurs at or below this element stack:

    <div>

     <span>

      <table>

       <tr>

        <td>

         <nobr>

and that's it.

It took me forever to figure out that it was the buttons that let me sort the custom column that were causing the error in the first place. So I tried a couple things and finally got it to export to excel by exporting it like this:

protected void btnXLS_Click(object sender, EventArgs e)
{
    rgvNextActionDue.ExportSettings.FileName = "NextActionDue";
    rgvNextActionDue.ExportSettings.OpenInNewWindow = true;
    rgvNextActionDue.ExportSettings.ExportOnlyData = true;
    rgvNextActionDue.ExportSettings.IgnorePaging = true;
    rgvNextActionDue.ExportSettings.Excel.FileExtension = "xls";
    rgvNextActionDue.ExportSettings.Excel.Format = GridExcelExportFormat.Html;
    rgvNextActionDue.MasterTableView.ExportToExcel();
}

for some reason it didn't like exporting the buttons using ExcelML as the format. I have no problem with exporting it as HTML, but now
the problem is that the exported excel file has the headers of the custom column backwards.

For example,
The grid has:
Period
    Start Date - End Date

The excel file has:

row1 :         Period
row2 :               -
row3:       End Date   Start Date

(obviously I added the rows as reference to which excel row they show up in)

If it helps I have the column set up like this in the aspx file:

<tlk:GridTemplateColumn UniqueName="PeriodTemplate" Groupable="false">
  <HeaderTemplate>
    <table id="tblPeriod" cellspacing="0" width="100%">
      <tr>
        <td colspan="3" align="center">
          <b>Period</b>
        </td>
      </tr>
      <tr>
        <td style="width:49%" align="center">
          <asp:LinkButton CssClass="Button" ID="btnPStart" Text="Start"
              ToolTip="Sort by Period Start" CommandName="Sort"
              CommandArgument="EvFYStart" runat="server" />
        </td>
        <td align="center" style="width:1%">-</td>
        <td style="width:50%" align="center">
          <asp:LinkButton CssClass="Button" ID="btnPEnd" Text="End"
             ToolTip="Sort by Period End" CommandName="Sort"
             CommandArgument="EvFYEnd" runat="server" />
        </td>
      </tr>
    </table>
  </HeaderTemplate>
  <HeaderStyle Width="120px" />
  <ItemTemplate>
    <table cellspacing="0" width="100%" border="0">
      <tr>
        <td style="width:49%; border:0;" align="center">
           <%# Eval("EvFYStart") %>
        </td>
        <td align="center" style="width:1%; border:0;">-</td>
        <td style="width:50%; border:0;" align="center">
          <%# Eval("EvFYEnd") %>
        </td>
      </tr>
    </table>
  </ItemTemplate>
  <ItemStyle HorizontalAlign="Center" />
</tlk:GridTemplateColumn>

So if anyone has any idea of how to fix this I would appreciate any help you could provide.

Thanks,

Dustin

[Daniel]

Hello Dustin,

Please try the following approach:

bool isExport = false;

protected void Button1_Click(object sender, EventArgs e)
{
    isExport = true;
    RadGrid1.ExportSettings.ExportOnlyData = false;
    RadGrid1.ExportSettings.IgnorePaging = true;
    RadGrid1.MasterTableView.ExportToExcel();
}

protected void RadGrid1_ItemDataBound(object sender, GridItemEventArgs e)
{
    if (!(e.Item is GridDataItem || e.Item is GridHeaderItem) && isExport)
        e.Item.Visible = false;
}

Note that the ExportOnlyData is set to False and the unnecessary items are removed manually.

Let me know whether this helps.

Best regards,
Daniel
the Telerik team

[Dustin Dodson]

Awesome.. that worked great! One question though...

When the excel file exported the links for sorting are still there, of course they don't work or go anywhere, is there a way to turn those off when exporting as well?

Thanks,

Dustin

[Daniel]

Hello Dustin,

You can get the text of the LinkButtons and set it to the corresponding TableCells. In turn this will clear the Controls collection and hence you will remove the unwanted links.

if (e.Item is GridHeaderItem && isExport)
    foreach (TableCell cell in e.Item.Cells)
        if (cell.HasControls() && cell.Controls[0] is LinkButton)
            cell.Text = (cell.Controls[0] as LinkButton).Text;

Kind regards,
Daniel
the Telerik team

[Dustin Dodson]

Thanks for the reply.

I got it to work using your idea, the only issue I ran into was if the control in the header was a link button in a custom template.

Because you were checking the first control only in the header, if it was a linkbutton in a table, it wasn't finding it. So I did some messing around to try and figure out how to make the other linkbuttons not be links, but keep what the whole cell says.

Here is what I have so hopefully this will help someone else as well.
(This is inside the grids ItemDataBound event)

if (!(e.Item is GridDataItem || e.Item is GridHeaderItem) && isExport)
    e.Item.Visible = false;

//turn off the hyperlinks in header in the excel file.
if (e.Item is GridHeaderItem && isExport)
    foreach (TableCell cell in e.Item.Cells)
    {
        if (cell.HasControls() && cell.Controls[0] is LinkButton)
            cell.Text = (cell.Controls[0] as LinkButton).Text;
        //if first control in cell is not linkbutton.
        else if (cell.HasControls())
        {
            string txtTot = string.Empty;
            foreach (Control con in cell.Controls)
            {
                if (con is LiteralControl)
                    txtTot += (con as LiteralControl).Text;
                if (con is LinkButton)
                    //it looses formatting for some reason, so added bold tags.
                    txtTot += "<b>" + (con as LinkButton).Text + "</b>";
            }
            cell.Text = txtTot;
        } //end of if first control in cell is not linkbutton.
    }// end of for each cell in header

Thanks Again!

Dustin