Accessing Data from Header or Footer

In This Topic

You can add formulas to headers and footers while performing spreadsheet calculations. These formulas can contain references to a cell, cell range, other worksheets, or to itself. In addition to this, the data available in the header or footer can be accessed from any worksheet within the workbook in order to ensure it can be used anywhere in the formulas as and when required.

Different keywords are supported for different regions of a worksheet. These keywords are used in formulas to retrieve data available in specific regions. The table shared below lists the keywords that are supported for a valid formula:

Keyword	Description
#Headers	This keyword refers to the column header rows area.
#Data	This keyword refers to the spreadsheet rows area.
#Totals	This keyword refers to the column footer rows area.
#RowHeaders	This keyword refers to the row header rows area.

The Formula property can be used to add formulas for header and footer. When values of referenced cells in source worksheets are modified (using the copy, move, insert or delete operations), the formulas are automatically updated along with the calculated values in the worksheet.

After applying formulas, you can save the workbook to an excel file along with the flags.

Refer to the following table for the detailed list and description of flags that can be applied while saving to an excel file.

SaveCustomRowHeaders flag	Row header is changed to a new range, and the row header formula is saved.
SaveCustomColumnHeaders flag	Column header is changed to a new range, and the column header formula is saved.
Other flags	Only the data area is saved.

Users can also make use of structured reference syntax in order to refer to the header or footer area. For more information, refer to the topic Using Structured References in the documentation.

Using Code

You can add formula by specifying the Formula property for header or footer.

Example

This example shows how to set a formula in different scenarios.

C#	Copy Code
// Sheets refer to headers or footers fpSpread1.Sheets[1].Cells[0, 0].Formula = "SUM(Sheet1[[#Headers],$A$1:$B$2])"; fpSpread1.Sheets[1].Cells[0, 2].Formula = "Sheet2[[#Totals],$A$1]"; // Headers or footers refer to itself fpSpread1.Sheets[2].ColumnHeader.Cells[0, 3].Formula = "Sheet1[[#Headers],[$B$2]]"; fpSpread1.Sheets[2].ColumnFooter.Cells[0, 5].Formula = "Sheet1[[#Totals],[A3])"; // ColumnFooter refers to sheets fpSpread1.ActiveSheet.ColumnFooter.Cells[0, 3].Formula = "$A$1";

Copy Code

// Sheets refer to headers or footers
fpSpread1.Sheets[1].Cells[0, 0].Formula = "SUM(Sheet1[[#Headers],$A$1:$B$2])";
fpSpread1.Sheets[1].Cells[0, 2].Formula = "Sheet2[[#Totals],$A$1]";

// Headers or footers refer to itself
fpSpread1.Sheets[2].ColumnHeader.Cells[0, 3].Formula = "Sheet1[[#Headers],[$B$2]]";
fpSpread1.Sheets[2].ColumnFooter.Cells[0, 5].Formula = "Sheet1[[#Totals],[A3])";

// ColumnFooter refers to sheets
fpSpread1.ActiveSheet.ColumnFooter.Cells[0, 3].Formula = "$A$1";

VB	Copy Code
' Sheets refer to headers or footers fpSpread1.Sheets(1).Cells(0, 0).Formula = "SUM(Sheet1[[#Headers],$A$1:$B$2])" fpSpread1.Sheets(1).Cells(0, 2).Formula = "Sheet2[[#Totals],$A$1]" ' Headers or footers refer to itself fpSpread1.Sheets(2).ColumnHeader.Cells(0, 3).Formula = "Sheet1[[#Headers],[$B$2]]" fpSpread1.Sheets(2).ColumnFooter.Cells(0, 5).Formula = "Sheet1[[#Totals],[A3])" ' ColumnFooter refers to sheets fpSpread1.ActiveSheet.ColumnFooter.Cells(0, 3).Formula = "$A$1"

Copy Code

' Sheets refer to headers or footers
fpSpread1.Sheets(1).Cells(0, 0).Formula = "SUM(Sheet1[[#Headers],$A$1:$B$2])"
fpSpread1.Sheets(1).Cells(0, 2).Formula = "Sheet2[[#Totals],$A$1]"

' Headers or footers refer to itself
fpSpread1.Sheets(2).ColumnHeader.Cells(0, 3).Formula = "Sheet1[[#Headers],[$B$2]]"
fpSpread1.Sheets(2).ColumnFooter.Cells(0, 5).Formula = "Sheet1[[#Totals],[A3])"

' ColumnFooter refers to sheets
fpSpread1.ActiveSheet.ColumnFooter.Cells(0, 3).Formula = "$A$1"

Example

This example shows how to set a formula in RowHeader in order to refer to the data available in a table.

C#	Copy Code
// RowHeader refers to table fpSpread1.ActiveSheet.RowHeader.Cells[9, 0].Formula = "SUM(Table1[[#Totals])";

VB	Copy Code
' RowHeader refers to table fpSpread1.ActiveSheet.RowHeader.Cells(9, 0).Formula = "SUM(Table1[[#Totals])"

Example

This example shows how to use a custom name for the formula.

C#	Copy Code
fpSpread1.AsWorkbook().Names.Add("name_1","Sheet2[[#Headers],$A$1:$B$5]"); fpSpread1.Sheets[1].ColumnHeader.Cells[0, 3].Formula = "name_1 + 2";

VB	Copy Code
fpSpread1.AsWorkbook().Names.Add("name_1","Sheet2[[#Headers],$A$1:$B$5]") fpSpread1.Sheets(1).ColumnHeader.Cells(0, 3).Formula = "name_1 + 2"

Note: The worksheet header or footer formulas doesn't support external reference i.e. reference to a cell or a cell range of another workbook is not supported. Also, the references to column footer cannot be exported to XLSX format.