Custom header in Excel export

2 posts, 0 answers
  1. dan
    dan avatar
    11 posts
    Member since:
    Jul 2017

    Posted 25 Feb 2019 Link to this post

    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


  2.
  3. Attila Antal
    Admin
    Attila Antal avatar
    609 posts

    Posted 28 Feb 2019 Link to this post

    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.
Back to Top