Export to ExcelML is blank

6 posts, 0 answers
  1. Jeremy
    Jeremy avatar
    96 posts
    Member since:
    Mar 2010

    Posted 23 Nov 2010 Link to this post

    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();
    }
  2. Daniel
    Admin
    Daniel avatar
    4945 posts

    Posted 23 Nov 2010 Link to this post

    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
    Browse the vast support resources we have to jumpstart your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Jeremy
    Jeremy avatar
    96 posts
    Member since:
    Mar 2010

    Posted 23 Nov 2010 Link to this post

    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);
        }
    }


  5. Jeremy
    Jeremy avatar
    96 posts
    Member since:
    Mar 2010

    Posted 24 Nov 2010 Link to this post

    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.
  6. Karlo
    Karlo avatar
    4 posts
    Member since:
    Mar 2012

    Posted 04 Apr 2012 Link to this post

    @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.
  7. Daniel
    Admin
    Daniel avatar
    4945 posts

    Posted 09 Apr 2012 Link to this post

    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
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017