Rank 1
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.
8 Answers, 1 is accepted
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
Rank 1
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.
You need to cancel the ElementExporting event if the e.Element is HeaderRow.
Best wishes,
Veselin Vasilev
the Telerik team
Rank 1
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.
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
Rank 1
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.
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
Rank 1
This sure did work. Thank you!
-VK.