Exporting to Excel...With multiline headers

9 posts, 1 answers
  1. Vassili King
    Vassili King avatar
    57 posts
    Member since:
    May 2010

    Posted 12 Jan 2011 Link to this post

    Hello,

    I'm trying to Export to Excel a GridView that has double header. The grid shows language skills for a list of people based on a selected value of a drop-down list that contains a list of available languages. Once a language is selected and the submit button is pressed, the grid is bound to a data set returned from my database. I have options to Print, Export to Excel and Export to Word available on the page. While I have no problem with Print (other than the mind numbing and utterly frustrating slowness for larger data sets), I can't figure out how to export the language selected AND the grid to an Excel file.

    On the grid itself, use ColumnGroupBehaviors like this:

                    var behaviour = new ColumnGroupsBehavior
                    {
                        CommonHeaders = new ObservableCollection<CommonHeader>
                        {
                            new CommonHeader { StartColumnIndex = 0, Caption = "", ColumnSpan = 4 },
                            new CommonHeader { StartColumnIndex = 4, Caption = "Reading", ColumnSpan = 3 },
                            new CommonHeader { StartColumnIndex = 7, Caption = "Speaking", ColumnSpan = 3 },
                            new CommonHeader { StartColumnIndex = 10, Caption = "Writing", ColumnSpan = 3 }
                        }
                    };
                    behaviour.Attach(rgv);

    Actually, I do it in the XAML, but it's the same thing. Anyway, I can add a language name in the first header (first cell), and then have the rest of the headers defined as usual.  However, if I use the code above in my btnExportToExcel_Click event (where I first create a grid, then add the behavior, then do the exporting to Excel routine described in so many posts in this forum), the two-header thing isn't exported (e.g., the behavior is not exportable).

    I don't really care if the language name is displayed in the header of the GridView - just as long as it is displayed somewhere above the grid. Any suggestions?

    I'll appreciate any help. Thank you!
    -VK.

  2. Veselin Vasilev
    Admin
    Veselin Vasilev avatar
    2992 posts
    Member since:
    Jul 2012

    Posted 13 Jan 2011 Link to this post

    Hi Vassili King,

    You can use the ElementExported event in this case to write your custom text above the gridview.
    Here is a sample code:

    private void gridView_ElementExported(object sender, GridViewElementExportedEventArgs e)
    {
        if (e.Element == ExportElement.HeaderRow)
        {  
            e.Writer.WriteLine("<tr><td colspan='4' style='color:red; font-weight:bold;'>my custom header</td></tr></table>");
            //export the gridview's header
            string header = "<table style='border-collapse:collapse' border='1'><tr>";
     
            foreach (var col in gridView.Columns)
            {
                header += "<td style='width:80px'>";
                header += col.Header;
                header += "</td>";
            }
     
            header += "</tr>";
     
            e.Writer.Write(header);
        }
    }


    Greetings,
    Veselin Vasilev
    the Telerik team
    Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>

  3. DevCraft Release
  4. Vassili King
    Vassili King avatar
    57 posts
    Member since:
    May 2010

    Posted 13 Jan 2011 Link to this post

    Thank you Veselin,

    This looks good, but there is one problem: the main header now prints twice in the following manner:


    --------------------------------------------------------
    |   Name   |   Phone   |   Address   |   Whatever   |
    --------------------------------------------------------
    |   My custom header                                       |
    --------------------------------------------------------
    |   Name   |   Phone   |   Address   |   Whatever   |
    --------------------------------------------------------
    |   ...........|  ..........   |   ...........   |   ..............   |

    How can I prevent the duplication?

    Thank you!
    -VK.

  5. Veselin Vasilev
    Admin
    Veselin Vasilev avatar
    2992 posts
    Member since:
    Jul 2012

    Posted 14 Jan 2011 Link to this post

    Hi Vassili King,

    You need to cancel the ElementExporting event if the e.Element is HeaderRow.


    Best wishes,
    Veselin Vasilev
    the Telerik team

    Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>

  6. Vassili King
    Vassili King avatar
    57 posts
    Member since:
    May 2010

    Posted 14 Jan 2011 Link to this post

    Hi Veselin,

    If I use e.Cancel = true, all three headers are gone (code below). Is there a way to cancel only the first of the three?

            private void Grid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
            {
                try
                {
                    if (e.Element == ExportElement.HeaderRow)
                    {
                        e.Cancel = true;
                        //e.Background = Colors.LightGray;
                        //e.Foreground = Colors.Black;
                        //e.FontWeight = FontWeights.Bold;
                    }

                }
                catch (Exception ex)
                {
                    //catching an exception
                }
            }

    Thank you!
    -VK.

  7. Veselin Vasilev
    Admin
    Veselin Vasilev avatar
    2992 posts
    Member since:
    Jul 2012

    Posted 19 Jan 2011 Link to this post

    Hi Vassili King,

    Then more appropriate solution would be to check if the element is HeaderCell and cancel it (you can use a simple counter to help you cancel it only for the first cell).

    If I am missing something - please send us a sample project so we can have a better idea of your case.

    Greetings,
    Veselin Vasilev
    the Telerik team

    Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>

  8. Vassili King
    Vassili King avatar
    57 posts
    Member since:
    May 2010

    Posted 19 Jan 2011 Link to this post

    Hi Veselin,

    Every column has a header when it is added to the grid:

        RadGridView rgv = new RadGridView();
        GridViewDataColumn gvdc; 

        //Name
        gvdc = new GridViewDataColumn() { Header = "Name", DataType = typeof(string), DataMemberBinding = new Binding("Name"), CellStyle = (Style)Application.Current.Resources["ArrowStyle"], UniqueName = "Name", HeaderTextAlignment = TextAlignment.Center, TextAlignment = TextAlignment.Center };
        gvdc.Width = new GridViewLength(200);
        rgv.Columns.Add(gvdc);
        ....

    I'm handling Exporting and Exported events for the grid:

        {
            ...
            rgv.ElementExporting += this.Grid_ElementExporting;
            rgv.ElementExported += this.Grid_ElementExported;
            ....
        }
        ....
        private void Grid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
        {
            try
            {
                if (e.Element == ExportElement.HeaderRow) 
                    {
                        e.Cancel = true;

                        //e.Background = Colors.LightGray;
                        //e.Foreground = Colors.Black;
                        //e.FontWeight = FontWeights.Bold;
                    }

            }
            catch (Exception ex)
            {
                    ...
            }
        }

        private void Grid_ElementExported(object sender, GridViewElementExportedEventArgs e)
        {
            if (e.Element == ExportElement.HeaderRow)
            {
                    string MySecondaryHeader = "My Secondary Header";
                    e.Writer.WriteLine("<tr><td colspan='8' style='color:black; font-weight:bold;'>Language: " + MySecondaryHeader + "</td></tr></table>");

                    //export the gridview's header
                    string header = "<table style='border-collapse:collapse' border='1'><tr>";

                    foreach (var col in ((RadGridView)sender).Columns)
                    {
                        header += "<td style='width:80px'>";
                        header += col.Header;
                        header += "</td>";
                    }

                    header += "</tr>";

                    e.Writer.Write(header);
            }
        }

    My exporting code is strainght from your tutorials:

        ...
        //start exporting steps
        string extension = "xls";
        string selectedItem = "Excel";
        ExportFormat format = ExportFormat.Html; 
        SaveFileDialog dialog = new SaveFileDialog();

        rgv.ShowGroupPanel = false;
        rgv.ShowColumnHeaders = true;
        rgv.CanUserFreezeColumns = false;
        rgv.IsFilteringAllowed = false;
        rgv.AutoExpandGroups = true;
        rgv.AutoGenerateColumns = false;
                   
        dialog.DefaultExt = extension;
        dialog.Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, selectedItem);
        dialog.FilterIndex = 1;

        if (dialog.ShowDialog() == true)
        {
            using (Stream stream = dialog.OpenFile())
            {
                            GridViewExportOptions options = new GridViewExportOptions();
                            options.Format = format;
                            options.ShowColumnHeaders = true;
                            options.Encoding = Encoding.UTF8;
                            rgv.Export(stream, options);
            }
        }
        ...

    In the ElementExporting method, if I comment out the "e.Cancel = true;" line and instead uncomment the styling settings, I get 3 headers (the regular column header, then my custom header, than the regular colum header again, but without styling applied). If I uncomment the "e.Cancel = true;" and comment out styling settings (unimportant at this point), I get no headers at all. What I'm trying to achieve is to have the two headers: the recular column headers and my secondary header displayed (the order is of no importance - custom header can be above or below the standard header).

    I hope this makes is a bit more clear. I'm attaching an image showing the two cases (outcomes) described above.

    Thank you!
    -VK.
       

  9. Answer
    Veselin Vasilev
    Admin
    Veselin Vasilev avatar
    2992 posts
    Member since:
    Jul 2012

    Posted 24 Jan 2011 Link to this post

    Hello Vassili King,

    Actually, if you cancel the ElementExporting event for any element (like HeaderRow) the corresponding ElementExported event is not fired for that element. That is why you get no headers.

    So, please cancel the ElementExporting if the element is HeaderCell, not HeaderRow and leave the ElementExported event intact.

    Best wishes,
    Veselin Vasilev
    the Telerik team

    Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>

  10. Vassili King
    Vassili King avatar
    57 posts
    Member since:
    May 2010

    Posted 25 Jan 2011 Link to this post

    Hi Veselin,

    This sure did work. Thank you!

    -VK.

Back to Top
DevCraft Release