Add Document Header and Footer to Exported Excel XLSX File

DESCRIPTION

Add Document Header and Footer to Exported Excel XLSX File

SOLUTION

This KB demonstrates how to add a document header and/or footer to exported file when exporting RadGrid into Excel format XLSX by using the Telerik Document Processing Library, precisely RadSpreadProcessing.

Requirements

The RadSpreadProcessing library requires the following Assembly References.

Consider the following RadGid wired up with the OnGridExporting server event to be used to format the Excel document before it is finally saved.

<telerik:RadGrid ID="RadGrid1" runat="server" AllowPaging="True" OnNeedDataSource="RadGrid1_NeedDataSource" OnGridExporting="RadGrid1_GridExporting">
    <ExportSettings HideStructureColumns="true" ExportOnlyData="true" IgnorePaging="true" OpenInNewWindow="false"
        Excel-Format="Xlsx">
    </ExportSettings>
    <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top">
        <ColumnGroups>
            <telerik:GridColumnGroup HeaderText="Details" Name="Details" HeaderStyle-HorizontalAlign="Center"></telerik:GridColumnGroup>
        </ColumnGroups>
        <CommandItemTemplate>
            <telerik:RadToolBar ID="RadToolBar1" runat="server" AutoPostBack="true">
                <Items>
                    <telerik:RadToolBarButton Text="Export to Excel" CommandName="ExportToExcel"></telerik:RadToolBarButton>
                </Items>
            </telerik:RadToolBar>
        </CommandItemTemplate>
        <Columns>
            <telerik:GridBoundColumn DataField="OrderID" DataType="System.Int32"
                FilterControlAltText="Filter OrderID column" HeaderText="OrderID"
                ReadOnly="True" SortExpression="OrderID" UniqueName="OrderID">
            </telerik:GridBoundColumn>
            <telerik:GridDateTimeColumn DataField="OrderDate" DataType="System.DateTime"
                FilterControlAltText="Filter OrderDate column" HeaderText="OrderDate"
                SortExpression="OrderDate" UniqueName="OrderDate" ColumnGroupName="Details">
            </telerik:GridDateTimeColumn>
            <telerik:GridNumericColumn DataField="Freight" DataType="System.Decimal"
                FilterControlAltText="Filter Freight column" HeaderText="Freight"
                SortExpression="Freight" UniqueName="Freight" ColumnGroupName="Details">
            </telerik:GridNumericColumn>
            <telerik:GridBoundColumn DataField="ShipCountry"
                FilterControlAltText="Filter ShipCountry column" HeaderText="ShipCountry"
                SortExpression="ShipCountry" UniqueName="ShipCountry" ColumnGroupName="Details">
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

Code behind

protected void RadGrid1_GridExporting(object sender, GridExportingArgs e)
{
    Byte[] GridExportedFile = System.Text.Encoding.Default.GetBytes(e.ExportOutput);
    XlsxFormatProvider provider = new XlsxFormatProvider();
    var workbook = provider.Import(GridExportedFile);
    HeaderFooterSettings settings = workbook.Worksheets.First().WorksheetPageSetup.HeaderFooterSettings;
    string header = "Custom Header";
    string footer = "Custom Footer";
    settings.Header.CenterSection.Text = header;
    settings.Footer.CenterSection.Text = footer;
    GridExportedFile = provider.Export(workbook);
    var fileName = (sender as RadGrid).ExportSettings.FileName;
    WriteFileToResponse(GridExportedFile,fileName);
}

private void WriteFileToResponse(byte[] content, string fileName)
{
    Response.ContentType = ContentType;
    Response.Headers.Remove("Content-Disposition");
    Response.AppendHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", fileName));
    Response.BinaryWrite(content);
    Response.End();
}

    Protected Sub RadGrid1_GridExporting(ByVal sender As Object, ByVal e As GridExportingArgs)
        Dim GridExportedFile As Byte() = System.Text.Encoding.[Default].GetBytes(e.ExportOutput)
        Dim provider As XlsxFormatProvider = New XlsxFormatProvider()
        Dim workbook = provider.Import(GridExportedFile)
        Dim settings As HeaderFooterSettings = workbook.Worksheets.First().WorksheetPageSetup.HeaderFooterSettings
        Dim header As String = "Custom Header"
        Dim footer As String = "Custom Footer"
        settings.Header.CenterSection.Text = header
        settings.Footer.CenterSection.Text = footer
        GridExportedFile = provider.Export(workbook)
        Dim fileName = (TryCast(sender, RadGrid)).ExportSettings.FileName
        WriteFileToResponse(GridExportedFile, fileName)
    End Sub

    Private Sub WriteFileToResponse(ByVal content As Byte(), ByVal fileName As String)
        Response.ContentType = ContentType
        Response.Headers.Remove("Content-Disposition")
        Response.AppendHeader("Content-Disposition", String.Format("attachment; filename={0}.xlsx", fileName))
        Response.BinaryWrite(content)
        Response.[End]()
    End Sub

Visit the following link to find out more information on formatting the Headers and Footers.