Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Formulas in Cells / Accessing Data from Header or Footer
In This Topic
    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";
    

     

    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"
    

    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.
    See Also