Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Sheets / Displaying a Footer for Columns or Groups
In This Topic
    Displaying a Footer for Columns or Groups
    In This Topic

    You can show a column footer, a group footer, or both for the sheet and put information in the footer such as formulas or text. The column footer is an area at the bottom of the sheet. The group footer is an extra row of footer cells at the bottom of a sheet with grouping, if you are using the grouping feature.

    For details on the API, refer to the ColumnFooter property of the SheetView class and the various members of the ColumnFooter class.

    To calculate the column footer or group footer result with a formula, set the SetAggregationType method of the ColumnFooter object to the correct formula type for that column. The following figure displays a group bar and a column footer with a formula in the column:

    Spread control with red grid lines

    The group footer is an extra row that is displayed below the group after grouping by a column header. The GroupFooterVisible property must be set to true after the group has been created. The Grouped event can be used to put information in the group footer after a user has created the group.

    For more information on grouping, refer to Managing Grouping of Rows of User Data.

    Properties Window

    1. At design time, in the Properties window, select the Sheets property for the FpSpread component.
    2. Click the button to display the SheetView Collection Editor.
    3. Select the ColumnFooter property or the GroupFooter property or both in the Property list and set Visible to true.
    4. Click OK to close the editor.

    Using a Shortcut

    Set the Visible property of the ColumnFooter for the sheet.

    Example

    This example code displays a column footer and sets a span and a text color.

    fpSpread1.Sheets[0].RowCount = 10;
    fpSpread1.Sheets[0].ColumnCount = 15;
    // Show the column footer.
    fpSpread1.Sheets[0].ColumnFooter.Visible = true;
    fpSpread1.Sheets[0].ColumnFooter.RowCount = 2;
    fpSpread1.Sheets[0].ColumnFooter.DefaultStyle.ForeColor = Color.Purple;
    fpSpread1.Sheets[0].ColumnFooter.Columns[12].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left;
    fpSpread1.Sheets[0].ColumnFooter.Cells[0, 12].RowSpan = 2;
    fpSpread1.Sheets[0].ColumnFooter.Cells[0, 0].Value = "test";
    
    fpSpread1.Sheets(0).RowCount = 10
    fpSpread1.Sheets(0).ColumnCount = 15
    ' Show the footer.
    fpSpread1.Sheets(0).ColumnFooter.Visible = true
    fpSpread1.Sheets(0).ColumnFooter.RowCount = 2
    fpSpread1.Sheets(0).ColumnFooter.DefaultStyle.ForeColor = Color.Purple
    fpSpread1.Sheets(0).ColumnFooter.Columns(12).HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left
    fpSpread1.Sheets(0).ColumnFooter.Cells(0, 12).RowSpan = 2
    fpSpread1.Sheets(0).ColumnFooter.Cells(0, 0).Value = "test
    

    Method to set

    1. Set the Visible property of the ColumnFooter for the sheet.
    2. Set the SetAggregationType method for the column.

    Example

    This example sums the values in the first column and displays them in the column footer. The example also sums the values in the second group and puts them in the group footer.

    private void Form1_Load(object sender, System.EventArgs e)
     {
     fpSpread1.Sheets[0].RowCount=8;
     fpSpread1.Sheets[0].ColumnCount = 15;
     fpSpread1.Sheets[0].GroupBarInfo.Visible = true;
     fpSpread1.Sheets[0].AllowGroup = true;
     fpSpread1.Sheets[0].GroupFooterVisible = true;
     fpSpread1.Sheets[0].ColumnFooter.Visible = true;
     fpSpread1.Sheets[0].ColumnFooter.RowCount = 2;
     fpSpread1.Sheets[0].ColumnFooter.Columns[12].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left;
     fpSpread1.Sheets[0].ColumnFooter.Cells[0, 12].RowSpan = 2;
     //Value
     for (int r = 0; r < fpSpread1.Sheets[0].RowCount; r++)
     {
     for (int j = 0; j < fpSpread1.Sheets[0].ColumnCount; j++)
     {
     fpSpread1.Sheets[0].Models.Data.SetValue(r, j, j + r * fpSpread1.Sheets[0].ColumnCount);
     }
     }
    int i = 0;
     fpSpread1.Sheets[0].ColumnFooter.SetAggregationType(0,1, FarPoint.Win.Spread.Model.AggregationType.Sum);
     fpSpread1.Sheets[0].ColumnFooter.Cells[0, i].Value = "Sum";
     }
     private void fpSpread1_Grouped(object sender, EventArgs e)
     FarPoint.Win.Spread.Model.GroupDataModel gdm;
    gdm = (FarPoint.Win.Spread.Model.GroupDataModel)fpSpread1.ActiveSheet.Models.Data;
     gdm.GroupFooterVisible = true;
     FarPoint.Win.Spread.Model.Group g1 = (FarPoint.Win.Spread.Model.Group)gdm.Groups[1];
     ((FarPoint.Win.Spread.Model.IAggregationSupport)g1.GroupFooter.DataModel).SetCellAggregationType(0, 0,
     FarPoint.Win.Spread.Model.AggregationType.Sum);
     fpSpread1.ActiveSheet.Models.Data = gdm;
     }
    
     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
     fpSpread1.Sheets(0).RowCount = 8
     fpSpread1.Sheets(0).ColumnCount = 15
     fpSpread1.Sheets(0).GroupBarInfo.Visible = True
     fpSpread1.Sheets(0).AllowGroup = True
     fpSpread1.Sheets(0).GroupFooterVisible = True
     fpSpread1.Sheets(0).ColumnFooter.Visible = True
     fpSpread1.Sheets(0).ColumnFooter.RowCount = 2
     fpSpread1.Sheets(0).ColumnFooter.Columns(12).HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left
     'Value
     Dim r As Integer
     Dim j As Integer
     For r = 0 To fpSpread1.Sheets(0).RowCount
     For j = 0 To fpSpread1.Sheets(0).ColumnCount
     fpSpread1.Sheets(0).Models.Data.SetValue(r, j, j + r * fpSpread1.Sheets(0).ColumnCount)
     Next j
     Next r
     Dim i As Integer
    i = 0
     fpSpread1.Sheets(0).ColumnFooter.SetAggregationType(0, 1, FarPoint.Win.Spread.Model.AggregationType.Sum)
     fpSpread1.Sheets(0).ColumnFooter.Cells(0, i).Value = "Sum"
     End Sub
     Private Sub fpSpread1_Grouped(ByVal sender As Object, ByVal e As System.EventArgs) Handles fpSpread1.Grouped
     Dim gdm As FarPoint.Win.Spread.Model.GroupDataModel
     Dim g1 As FarPoint.Win.Spread.Model.Group
    gdm = fpSpread1.Sheets(0).Models.Data
     gdm.GroupFooterVisible = True
     g1 = gdm.Groups(1)
     CType(g1.GroupFooter.DataModel, FarPoint.Win.Spread.Model.IAggregationSupport).SetCellAggregationType(0, 0, FarPoint.Win.Spread.Model.AggregationType.Sum)
     fpSpread1.ActiveSheet.Models.Data = gdm
     End Sub
    

    Example

    This example code displays a column footer and adds a formula from a different sheet.

     fpSpread1.Sheets.Count = 3;
     fpSpread1.Sheets[0].RowCount = 8;
     fpSpread1.Sheets[0].ColumnCount = 15;
     fpSpread1.Sheets[0].GroupBarInfo.Visible = true;
     fpSpread1.Sheets[0].AllowGroup = true;
     fpSpread1.Sheets[0].GroupFooterVisible = true;
     fpSpread1.Sheets[0].ColumnFooter.Visible = true;
     fpSpread1.Sheets[0].ColumnFooter.RowCount = 2;
     fpSpread1.Sheets[0].ColumnFooter.Columns[12].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left;
     fpSpread1.Sheets[0].ColumnFooter.Cells[0, 12].RowSpan = 2;
     //Value
     for (int r = 0; r < fpSpread1.Sheets[0].RowCount; r++)
     {
         for (int j = 0; j < fpSpread1.Sheets[0].ColumnCount; j++)
         {
             fpSpread1.Sheets[0].Models.Data.SetValue(r, j, j + r * fpSpread1.Sheets[0].ColumnCount);
         }
     }
    int i = 0;       
     fpSpread1.Sheets[1].RowCount = 10;
     fpSpread1.Sheets[1].ColumnCount = 15;
     fpSpread1.Sheets[1].GroupBarInfo.Visible = true;
     fpSpread1.Sheets[1].AllowGroup = true;
     fpSpread1.Sheets[1].GroupFooterVisible = true;
     fpSpread1.Sheets[1].ColumnFooter.Visible = true;
     fpSpread1.Sheets[1].ColumnFooter.RowCount = 2;
     fpSpread1.Sheets[1].ColumnFooter.Columns[12].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left;
     fpSpread1.Sheets[1].ColumnFooter.Cells[0, 12].RowSpan = 2;
     //Value
     for (int r = 0; r < fpSpread1.Sheets[1].RowCount; r++)
     {
         for (int j = 0; j < fpSpread1.Sheets[1].ColumnCount; j++)
         {
             fpSpread1.Sheets[1].Models.Data.SetValue(r, j, j + r * fpSpread1.Sheets[1].ColumnCount);
         }
     }
             
     fpSpread1.Sheets[0].ColumnFooter.Cells[0, i].Value = "2ndSum";
     fpSpread1.Sheets[0].ColumnFooter.Cells[0, 1].Formula = "SUM(Sheet2!A:A)";
    
     fpSpread1.Sheets.Count = 3
     fpSpread1.Sheets(0).RowCount = 8
     fpSpread1.Sheets(0).ColumnCount = 15
     fpSpread1.Sheets(0).GroupBarInfo.Visible = True
     fpSpread1.Sheets(0).AllowGroup = True
     fpSpread1.Sheets(0).GroupFooterVisible = True
     fpSpread1.Sheets(0).ColumnFooter.Visible = True
     fpSpread1.Sheets(0).ColumnFooter.RowCount = 2
     fpSpread1.Sheets(0).ColumnFooter.Columns(12).HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left
     'Value
     Dim r As Integer
     Dim j As Integer
     For r = 0 To fpSpread1.Sheets(0).RowCount
         For j = 0 To fpSpread1.Sheets(0).ColumnCount
             fpSpread1.Sheets(0).Models.Data.SetValue(r, j, j + r * fpSpread1.Sheets(0).ColumnCount)
         Next j
     Next r
     fpSpread1.Sheets(1).RowCount = 10
     fpSpread1.Sheets(1).ColumnCount = 15
     fpSpread1.Sheets(1).GroupBarInfo.Visible = True
     fpSpread1.Sheets(1).AllowGroup = True
     fpSpread1.Sheets(1).GroupFooterVisible = True
     fpSpread1.Sheets(1).ColumnFooter.Visible = True
     fpSpread1.Sheets(1).ColumnFooter.RowCount = 2
     fpSpread1.Sheets(1).ColumnFooter.Columns(12).HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left
     fpSpread1.Sheets(1).ColumnFooter.Cells(0, 12).RowSpan = 2
     'Value
     For r = 0 To fpSpread1.Sheets(1).RowCount
         For j = 0 To fpSpread1.Sheets(1).ColumnCount
             fpSpread1.Sheets(1).Models.Data.SetValue(r, j, j + r * fpSpread1.Sheets(1).ColumnCount)
         Next j
     Next r
     Dim i As Integer
    i = 0
     fpSpread1.Sheets(0).ColumnFooter.Cells(0, i).Value = "2ndSum"
     fpSpread1.Sheets(0).ColumnFooter.Cells(0, 1).Formula = "SUM(Sheet2!A:A)"
    

    Set Column Footer Format

    In Spread, you can show the column footer format in flat style using the NumberFormat property of the IRange interface.

    For example, you can display a comma as a thousands separator in the footer number format.

    // Set flat style in Spread
    fpSpread1.LegacyBehaviors = LegacyBehaviors.None;
    fpSpread1.Reset();
    fpSpread1.Sheets[0].Cells[0, 1, 10, 1].Value = 1000;
    fpSpread1.Sheets[0].ColumnFooterVisible = true;
    fpSpread1.Sheets[0].ColumnFooterRowCount = 1;
    fpSpread1.Sheets[0].ColumnFooter.SetAggregationType(0, 1, FarPoint.Win.Spread.Model.AggregationType.Sum);
    // Set number format in column footer
    fpSpread1.AsWorkbook().ActiveSheet.ColumnFooter.Cells[0, 1].NumberFormat = "#,##0";
    
    ' Set flat style in Spread
    fpSpread1.LegacyBehaviors = LegacyBehaviors.None
    fpSpread1.Reset()
    fpSpread1.Sheets(0).Cells(0, 1, 10, 1).Value = 1000
    fpSpread1.Sheets(0).ColumnFooterVisible = True
    fpSpread1.Sheets(0).ColumnFooterRowCount = 1
    fpSpread1.Sheets(0).ColumnFooter.SetAggregationType(0, 1, FarPoint.Win.Spread.Model.AggregationType.Sum)
    ' Set number format in column footer
    fpSpread1.AsWorkbook().ActiveSheet.ColumnFooter.Cells(0, 1).NumberFormat = "#,##0"
    

    Wrap Text in Column Footers

    You can customize text wrap in a column footer by setting the WrapText property of IRange Interface to true. Note that to view the wrapped text properly in the cell, you need to adjust the row height using the RowHeight property in the ColumnFooter class.

    Before applying this WrapText property, ensure that the value of ColumnHeaderRenderer.WordWrap2 property of the  SpreadSkin class is set to null. By default, the ColumnHeaderRenderer.WordWrap2 value is null for the default skin only.

    The following image depicts a preview of the wrapped text in the column footer.

    Use the sample codes below to wrap the column footer text using the IRange.WrapText property.

    // Wrap text in column footer
    activeSheet.ColumnFooter.Visible = true;
    activeSheet.ColumnFooter.Cells.RowHeight = 90;
    activeSheet.ColumnFooter.Cells[0, 0].Value = "This is a long footer text";
    activeSheet.ColumnFooter.Cells[0, 0].WrapText = true;
    
    ' Wrap text in column footer  
    activeSheet.ColumnFooter.Visible = True
    activeSheet.ColumnFooter.Cells.RowHeight = 90
    activeSheet.ColumnFooter.Cells(0, 0).Value = "This is a long footer text"
    activeSheet.ColumnFooter.Cells(0, 0).WrapText = True
    

    Set Column Style in Group Footer

    In Spread, the you can customize the appearance of columns on the group footers using the StyleManager property of FarPoint.Win.Spread.ModelGroupFooter class of the IGroupFooterStyleSupport interface.

    You can use the sample codes below to set column style in the group footer using the StyleManager property.

    // Set column style in group footer
    SheetView STestActiveSheet = fpSpread1.ActiveSheet;
    IWorksheet TestActiveSheet = fpSpread1.AsWorkbook().ActiveSheet;
    STestActiveSheet.RowCount = 6;
    STestActiveSheet.ColumnCount = 4;
    STestActiveSheet.Cells[0, 0, 3, 0].Value = 1;
    STestActiveSheet.Cells[4, 0, 5, 0].Value = 2;
    STestActiveSheet.Cells[0, 2, 3, 2].Value = DateTime.Today;
    DefaultGroupFooterCollection defaultGroupFooterCol = new DefaultGroupFooterCollection(6, 4);
    DefaultGroupFooter defaultGroupFooter = defaultGroupFooterCol[0];
    ISheetDataModel model = defaultGroupFooter.DataModel as ISheetDataModel;
    (model as IAggregationSupport).SetCellAggregationType(0, 2, AggregationType.Avg);
    fpSpread1.ActiveSheet.DefaultGroupFooter = defaultGroupFooterCol;
    GroupDataModel gdm = new GroupDataModel(STestActiveSheet.Models.Data);
    STestActiveSheet.Models.Data = gdm;
    gdm.Group(new SortInfo[] { new SortInfo(0, true) }, null);
    fpSpread1.ActiveSheet.GroupFooterVisible = true;
    var style = new StyleInfo();
    style.BackColor = System.Drawing.Color.LightBlue;
    DateTimeCellType dt = new DateTimeCellType();
    dt.DateTimeFormat = DateTimeFormat.UserDefined;
    dt.UserDefinedFormat = "dd.MM.yyyy HH:mm:ss";
    style.CellType = dt;
    style.HorizontalAlignment = CellHorizontalAlignment.Left;
    for (int i = 0; i < gdm.Groups.Count; i++)
    {
        var group = (Group)gdm.Groups[i];
        group.GroupFooter.StyleManager.SetColumnStyle(2, style);
    }
    
    ' Set column style in group footer
    Dim STestActiveSheet As SheetView = fpSpread1.ActiveSheet
    Dim TestActiveSheet As IWorksheet = fpSpread1.AsWorkbook().ActiveSheet
    STestActiveSheet.RowCount = 6
    STestActiveSheet.ColumnCount = 4
    STestActiveSheet.Cells(0, 0, 3, 0).Value = 1
    STestActiveSheet.Cells(4, 0, 5, 0).Value = 2
    STestActiveSheet.Cells(0, 2, 3, 2).Value = Date.Today
    Dim defaultGroupFooterCol As DefaultGroupFooterCollection = New DefaultGroupFooterCollection(6, 4)
    Dim defaultGroupFooter As DefaultGroupFooter = defaultGroupFooterCol(0)
    Dim model As ISheetDataModel = TryCast(defaultGroupFooter.DataModel, ISheetDataModel)
    TryCast(model, IAggregationSupport).SetCellAggregationType(0, 2, AggregationType.Avg)
    fpSpread1.ActiveSheet.DefaultGroupFooter = defaultGroupFooterCol
    Dim gdm As GroupDataModel = New GroupDataModel(STestActiveSheet.Models.Data)
    STestActiveSheet.Models.Data = gdm
    gdm.Group(New SortInfo() {New SortInfo(0, True)}, Nothing)
    fpSpread1.ActiveSheet.GroupFooterVisible = True
    Dim style = New StyleInfo()
    style.BackColor = Drawing.Color.LightBlue
    Dim dt As DateTimeCellType = New DateTimeCellType()
    dt.DateTimeFormat = DateTimeFormat.UserDefined
    dt.UserDefinedFormat = "dd.MM.yyyy HH:mm:ss"
    style.CellType = dt
    style.HorizontalAlignment = CellHorizontalAlignment.Left
    For i As Integer = 0 To gdm.Groups.Count - 1
       Dim group = CType(gdm.Groups(i), Group)
       group.GroupFooter.StyleManager.SetColumnStyle(2, style)
    Next
    

    The following image depicts the result of the above code.

    Limitation

    This feature doesn’t support painting for graphical cell types.

    See Also