This is a migrated thread and some comments may be shown as answers.

Custom header in Excel export

1 Answer 69 Views
Grid
This is a migrated thread and some comments may be shown as answers.
dan
Top achievements
Rank 1
dan asked on 25 Feb 2019, 09:52 PM

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


1 Answer, 1 is accepted

Sort by
0
Attila Antal
Telerik team
answered on 28 Feb 2019, 05:41 PM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Tags
Grid
Asked by
dan
Top achievements
Rank 1
Answers by
Attila Antal
Telerik team
Share this question
or