Export to excel in hierarchical RadGrid, hide headers

[Jan Pfeffer]

Hey at Telerik

I'm struggling to get an export of a hierarchical RadGrid to work properly.

I have included an export example of how it looks now and one how i would like it to look.

Both files can be found here
http://kort2.lifa.dk/publicdownload/ExportToExcel.rar

I would like to hide the column headers of the childtemplates when exporting. Have tried with the ViewCellFormatting and RowCellFormatting events but with no luck.

Hope you can help me with this.

Sincerly Jan

[Ivan Petrov]

Hello Jan,

Thank you for writing.

To achieve your goal, you have to hide the header rows of the child templates. You can do that through the ShowColumnHeaders property of the child template. After the export you can restore the headers:

this.radGridView1.MasterTemplate.Templates[0].ShowColumnHeaders = false;

ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
//....
exporter.RunExport(filePath);

this.radGridView1.MasterTemplate.Templates[0].ShowColumnHeaders = true;

I hope this will be useful. Should you have further questions, I would be glad to help.

Regards,
Ivan Petrov
Telerik

[Jan Pfeffer]

Hey Ivan

I managed to remove the headers from the child templates, but i still need a custom row inserted at index 1, with the child headers.

I have used the method AddCustomExcelRow in the ExcelTableCreated event, but it's inserted at index 0, i need it to be inserted at index 1 like the attached example shows.

Sincerly

Jan

[Ivan Petrov]

Hello Jan,

Thank you for writing back.

The layout you want can be achieved by hiding all header rows and adding two custom rows to the top of the excel table. To do that you should you use the ExcelTableCreated event of the exporter. Here is an example of how to use it:

this.radGridView1.MasterTemplate.ShowColumnHeaders = false;
this.radGridView1.MasterTemplate.Templates[0].ShowColumnHeaders = false;

ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
string filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "grid.xls");
exporter.ExcelTableCreated += exporter_ExcelTableCreated;
exporter.ExportHierarchy = true;
exporter.RunExport(filePath);

this.radGridView1.MasterTemplate.ShowColumnHeaders = true;
this.radGridView1.MasterTemplate.Templates[0].ShowColumnHeaders = true;

private void exporter_ExcelTableCreated(object sender, ExcelTableCreatedEventArgs e)
{
    RowElement excelRowElement1 = new RowElement();
    excelRowElement1.Attributes.Add("ss:Height", "15");

    RowElement excelRowElement2 = new RowElement();
    excelRowElement2.Attributes.Add("ss:Height", "15");

    CellElement cellElement = new CellElement();

    string mainGroupStyleId = "MyUniqueStyleIdString"; //This should be unique so styles do not mix
    SingleStyleElement excelStyleElement = new SingleStyleElement(mainGroupStyleId);
    excelStyleElement.AlignmentElement.HorizontalAlignment = HorizontalAlignmentType.Center;

    foreach (GridViewColumn col in this.radGridView1.MasterTemplate.Columns)
    {
        cellElement = new CellElement();
        cellElement.Data.DataItem = col.HeaderText;
        cellElement.StyleValue = mainGroupStyleId;
        excelRowElement1.Cells.Add(cellElement);
    }

    cellElement = new CellElement();
    cellElement.Data.DataItem = "";

    excelRowElement2.Cells.Add(cellElement);

    foreach (GridViewColumn col in this.radGridView1.MasterTemplate.Templates[0].Columns)
    {
        cellElement = new CellElement();
        cellElement.Data.DataItem = col.HeaderText;
        cellElement.StyleValue = mainGroupStyleId;
        excelRowElement2.Cells.Add(cellElement);
    }

    e.ExcelTableElement.Workbook.Styles.Add(excelStyleElement);
    e.ExcelTableElement.Rows.Add(excelRowElement1);
    e.ExcelTableElement.Rows.Add(excelRowElement2);
}

I hope this will help. Feel free to write back with any further questions.

Regards,
Ivan Petrov
Telerik