Hide Duplicate Page Headers While Exporting Report to Excel

The Problem

This article is in response to a query that our customers ask most frequently about Excel Exporting in ActiveReports. The query goes something like “How do I export my Report to Excel without duplicate Page Headers” or “I want the Page Header to only appear once in the exported Excel file". Before I begin answering this question, let’s see why the default behavior of exporting a report to Excel may pose such a question. All export filters in their essence behave similarly, i.e. all pages of a report will be exported to a specified file format based on the Export Type. Each exported file will therefore include all sections of a report that were a part of the original report. This behavior may be applicable well for most export types but since Excel does not have a concept of Paging, we may not get the results we hoped for. So, when all pages of a report are exported to a single sheet (It is also possible to export each page of a report to a separate sheet by setting the MultiSheet property of the Excel filter to True), you will have the Page Header of the report repeated quite a number of times on the excel sheet after some rows. This is the behavior that most customers want turned off as they need the Page Header to be rendered only once on a sheet.

The Simple Solution

Turning a Page Header's visibility to False after first Page Header has been rendered, is simply a matter of setting the Visible property of the PageHeader Section to False. This can be done in the Page**Footer's Format event when the value of PageNumber** property is greater than 1.

private void PageHeader_Format(object sender, EventArgs e)  
{  
   {  
      if (this.PageNumber > 1)  
      {  
         this.PageHeader.Visible = false;  
      }  
   }  
}

Output of the above implementation will generate a report with a Page Header only on the first Page . So when the report is exported to Excel, the result will be exactly what our customers wished for.

Additional Problems

This solution may seem comprehensive; but there would be problems if our customers wanted this behavior only when the report is exported to Excel but the viewer should show the report with Page Headers rendered on each page. This requirement may seem mundane at first, however hiding a specific section/control or changing report properties when a report is exported to a specific format would require changing the report properties after the report has been run (The export method of any export filter takes two parameters: "The Report Document" which is empty until the report is Run and the "__File Path" where the exported file is to be saved). Since the Report Document is a read only document and cannot be edited once the report has been run, changing the visibility of Page Header while exporting a report is something that cannot be done in the current scenario.

The Complete Solution

However, this limitation does not mean, we cannot achieve this requirement. In order to work around this we just need to run the report twice. Let’s see "Why and How"

1. When the report is run the first time, the Page Header section should have the Visible property set to True for all pages. This will render a report which will have Page Header section visible on each page. This report will be the one which will be shown on the Report Viewer.
2. Now, since we need to show the Page Header only on the first Page and hide it in subsequent pages in the exported Excel file, we will need to invoke the Run method again to create a new document which will only be used to export the report to Excel. However, when the run method is invoked this time, we will need to ensure that the Page Header is visible only on the first page. This can be done by creating a Public Property that would enable this behavior.

The solution for this is very trivial and does not account for much complexity. For better explanation, a sample application in both C# and VB.NET can be downloaded from the links provided below. PageHeaderExcelExport_C# PageHeaderExcelExport_VB.NET