Custom header in Excel export

[dan]

Hi,

I'm using the following method to export a radgrid report to Excel:

ExportSettings-Excel-Format="Xlsx"

and in the codebehind:

protected void btnExport_Click(object sender, EventArgs e)
{
    rgLTDAccount.ExportSettings.OpenInNewWindow = true;
    rgLTDAccount.ExportSettings.IgnorePaging = true;
    rgLTDAccount.ExportSettings.UseItemStyles = true;
    rgLTDAccount.ExportSettings.FileName = lblFileName.Text;
    rgLTDAccount.ExportSettings.ExportOnlyData = false;
    rgLTDAccount.MasterTableView.ExportToExcel();
}

 

I'm trying to insert a custom header at the top of the report with a few rows of contextual data from codebehind.  I've seen some samples for html and biff, but not xlsx format.  I can adjust a few things like row height in the InfrastructureExporting method, but I can't figure out if it's possible to insert rows and add text to them.  Any help greatly appreciated.

Thanks.

-Dan

[Attila Antal]

Hi Dan,

When exporting RadGrid to Xlsx (Open XML) format, the RadGrid uses the Telerik Document Processing library which has its own documentation. Telerik Document Processing. To modify the output of the exported file you can use the InfrastructureExporting event of the RadGrid, access the structure created by the grid and use the APIs from the RadSpreadProcessing to modify it.

For example:

protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    // using directives to shorten the code.

    // using ExportStructure = Telerik.Web.UI.ExportInfrastructure;
    // using XLSX = Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
    // using WB = Telerik.Windows.Documents.Spreadsheet.Model;

    // Structure generated by RadGrid
    ExportStructure.ExportStructure structure = e.ExportStructure;

    // Render the structure to XLSX format and return it to a byte array
    ExportStructure.XlsxRenderer renderer = new ExportStructure.XlsxRenderer(structure);
    byte[] xlsxByteArray = renderer.Render();

    // create an format provider instance
    XLSX.XlsxFormatProvider formatProvider = new XLSX.XlsxFormatProvider();
    // import the newly created document into a Workbook
    WB.Workbook myWorkbook = formatProvider.Import(xlsxByteArray);


    // Modify the Workbook using the Documents Processing APIs

    // once finished with changes convert the workbook back to byte array
    byte[] data = formatProvider.Export(myWorkbook);

    // return the byte array for downloading.
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.Headers.Remove("Content-Disposition");
    Response.AppendHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", RadGrid1.ExportSettings.FileName));
    Response.BinaryWrite(data);
    Response.End();
}

I hope this will prove helpful.

Kind regards,
Attila Antal
Progress Telerik