Export to ExcelML is blank

[Jeremy]

Hi

I am trying to export to excel from an ajaxified radgrid with template columns. When I use Excel.Format = GridExcelExportFormat.ExcelML I get a blank spreadsheet. If I use GridExcelExportFormat.Html it prints out fine, but with an extra blank row between the header row and the rest of the data. I checked the ExportOutput in RadGrid1_GridExporting method and the thead has 2 rows in it for some reason?? The 2nd row is full of blank <th> tags.

I can't use the GridExcelExportFormat.Html format because I have multiple controls in each template column.

Here is my export function:

protected void lnkExport_Click(object sender, EventArgs e)
{
    RadGrid1.MasterTableView.GetColumn("ClientSelectColumn").Visible = false;
    RadGrid1.ExportSettings.ExportOnlyData = true;
    RadGrid1.ExportSettings.IgnorePaging = true;
    RadGrid1.ExportSettings.OpenInNewWindow = true;
    RadGrid1.ExportSettings.FileName = "MyExport_" + DateTime.Now.ToString("dd_MMM_yyyy");
    RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
    RadGrid1.MasterTableView.ExportToExcel();
}

[Daniel]

Hello Jeremy,

RadGrid ExcelML export does not export template columns since it gets the data directly from the datasource and ignores non-data contents. You could add these columns on your own as demonstrated in the attached sample project.

Best regards,
Daniel
the Telerik team

[Jeremy]

Hi Daniel

Not much luck with this code, although the headers now appear in the spreadsheet. Below is an abbreviated version of what I've attempted. Not really happy with having to explicitly specify the columns, as 1 of the grids I use has 36 columns!
I am still confused about your first sentence though, as I am successfully exporting a grid with template columns in a different project?

Markup:

<telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false"
        OnExcelMLExportRowCreated="RadGrid1_ExcelMLExportRowCreated"
        OnNeedDataSource="RadGrid1_NeedDataSource">
    <MasterTableView CommandItemDisplay="Top" UseAllDataFields="true" DataKeyNames="userID">
        <Columns>
            <telerik:GridTemplateColumn UniqueName="Logon" HeaderText="Logon" SortExpression="logon">
                <ItemTemplate>
                    <asp:Label ID="lblLogon" runat="server" Text='<%# Eval("logon") %>' />
                </ItemTemplate>
            </telerik:GridTemplateColumn>
            <telerik:GridTemplateColumn UniqueName="fname" HeaderText="First Name" SortExpression="fname">
                <ItemTemplate>
                    <asp:Label ID="lblfname" runat="server" Text='<%# Eval("fname") %>' />
                </ItemTemplate>
            </telerik:GridTemplateColumn>
            <telerik:GridTemplateColumn UniqueName="lname" HeaderText="Last Name" SortExpression="lname">
                <ItemTemplate>
                    <asp:Label ID="lbllname" runat="server" Text='<%# Eval("lname") %>' />
                </ItemTemplate>
            </telerik:GridTemplateColumn>
        </Columns>
        <CommandItemTemplate>
            <asp:LinkButton ID="lnkExport" runat="server" CommandName="ExportToExcel" OnClick="lnkExport_Click">  Export</asp:LinkButton>
        </CommandItemTemplate>
    </MasterTableView>
</telerik:RadGrid>

<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server" OnAjaxRequest="RadAjaxManager1_AjaxRequest">
    <AjaxSettings>
        <telerik:AjaxSetting AjaxControlID="RadGrid1">
            <UpdatedControls>
                <telerik:AjaxUpdatedControl ControlID="RadGrid1"  />
            </UpdatedControls>
        </telerik:AjaxSetting>
        <telerik:AjaxSetting AjaxControlID="RadAjaxManager1">
            <UpdatedControls>
                <telerik:AjaxUpdatedControl ControlID="RadGrid1" />
            </UpdatedControls>
        </telerik:AjaxSetting>
    </AjaxSettings>
    <ClientEvents OnRequestStart="onRequestStart" />
</telerik:RadAjaxManager>

<telerik:RadCodeBlock ID="RadCodeBlock1" runat="server">
    <script type="text/javascript">
        function onRequestStart(sender, args) {
            if (args.get_eventTarget().indexOf("lnkExport") >= 0)
                args.set_enableAjax(false);
        }
     </script>
</telerik:RadCodeBlock>

Code Behind:

public class DummyData
{
    public int userID { get; set; }
    public string logon { get; set; }
    public string fname { get; set; }
    public string lname { get; set; }
}

private List<DummyData> GetData()
{
    List<DummyData> lstdum = new List<DummyData>();
    lstdum.Add(new DummyData() { userID = 1, logon = "jsmith", fname = "john", lname = "smith" });
    lstdum.Add(new DummyData() { userID = 2, logon = "asmith", fname = "anne", lname = "smith" });
    return lstdum;
}

protected void RadGrid1_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
    RadGrid1.DataSource = GetData();
}

protected void RadAjaxManager1_AjaxRequest(object sender, Telerik.Web.UI.AjaxRequestEventArgs e)
{
    //used to update values in DB/cancel changes/show loading panel during initial page load
}

protected void lnkExport_Click(object sender, EventArgs e)
{
    RadGrid1.ExportSettings.ExportOnlyData = true;
    RadGrid1.ExportSettings.IgnorePaging = true;
    RadGrid1.ExportSettings.OpenInNewWindow = true;
    RadGrid1.ExportSettings.FileName = "MyExport" + DateTime.Now.ToString("dd_MMM_yyyy");
    RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.ExcelML;
    RadGrid1.MasterTableView.ExportToExcel();
}

protected void RadGrid1_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)
{
    if (e.RowType == GridExportExcelMLRowType.HeaderRow)
    {
        //workaround to get the template column's content
        RadGrid1.Rebind();

        //create new column element and a header cell
        e.Worksheet.Table.Columns.Add(new ColumnElement());
        CellElement cell2 = new CellElement();
        //populate the header cell
        cell2.Data.DataItem = (RadGrid1.MasterTableView.GetItems(GridItemType.Header)[0] as GridHeaderItem)["logon"].Text.Trim();
        e.Row.Cells.Add(cell2);

        //create new column element and a header cell
        e.Worksheet.Table.Columns.Add(new ColumnElement());
        CellElement cell3 = new CellElement();
        //populate the header cell
        cell3.Data.DataItem = (RadGrid1.MasterTableView.GetItems(GridItemType.Header)[0] as GridHeaderItem)["fname"].Text.Trim();
        e.Row.Cells.Add(cell3);

        //create new column element and a header cell
        e.Worksheet.Table.Columns.Add(new ColumnElement());
        CellElement cell4 = new CellElement();
        //populate the header cell
        cell4.Data.DataItem = (RadGrid1.MasterTableView.GetItems(GridItemType.Header)[0] as GridHeaderItem)["lname"].Text.Trim();
        e.Row.Cells.Add(cell4);

        //correct the autofilter
        e.Worksheet.AutoFilter.Range = String.Format("R{0}C{1}:R{0}C{2}", 1, 1, e.Worksheet.Table.Columns.Count + 1);
    }
    else if (e.RowType == GridExportExcelMLRowType.DataRow)
    {
        int currentRow = e.Worksheet.Table.Rows.IndexOf(e.Row) - 1;

        //create cell for the current row
        CellElement cell2 = new CellElement();
        //populate the data cell
        cell2.Data.DataItem = RadGrid1.MasterTableView.Items[currentRow]["logon"].Text;
        e.Row.Cells.Add(cell2);

        //create cell for the current row
        CellElement cell3 = new CellElement();
        //populate the data cell
        cell3.Data.DataItem = RadGrid1.MasterTableView.Items[currentRow]["fname"].Text;
        e.Row.Cells.Add(cell3);

        //create cell for the current row
        CellElement cell4 = new CellElement();
        //populate the data cell
        cell4.Data.DataItem = RadGrid1.MasterTableView.Items[currentRow]["lname"].Text;
        e.Row.Cells.Add(cell4);
    }
}

[Jeremy]

Found the missing link! Exporting using ExcelML format and template columns is simple!!

Don't need any of that ExcelMLExportRowCreated code - just need to add the DataField property to the column.

[Karlo]

@Jeremy:

I tried adding the DataField property to the column and it worked for export to excel. Although, the problem is still there in Export to PDF and Export to CSV.

[Daniel]

Hello Karlo,

Please post the markup of your template columns and I will do my best to provide a solution for you.

Regards,
Daniel
the Telerik team