MESCIUS SPREAD for Windows Forms 15.0J
列フッタ

シート下部に列フッタを表示し、数式やテキストなどの情報を表示できます。次の図は、数式を使用した列フッタを表示する例です。

フッタ表示

数式を使用して列フッタに計算結果を表示するには、ColumnFooterクラスのSetAggregationTypeメソッドで、対象の列に適した集計の種類を設定します。

グループ化を有効に設定している場合は、グループフッタを表示することもできます。詳しくは「グループ化」、「グループフッタ」を参照してください。

設定方法

ColumnFooterクラスのVisibleプロパティを設定します。

サンプルコード

次のサンプルコードは、列フッタを表示し、セル結合とテキスト色を設定します。

C#
コードのコピー
fpSpread1.Sheets[0].RowCount = 10;
fpSpread1.Sheets[0].ColumnCount = 15;
// 列フッタを表示します。
fpSpread1.Sheets[0].ColumnFooter.Visible = true;
fpSpread1.Sheets[0].ColumnFooter.RowCount = 2;
fpSpread1.Sheets[0].ColumnFooter.Cells[0, 0].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";
Visual Basic
コードのコピー
FpSpread1.Sheets(0).RowCount = 10
FpSpread1.Sheets(0).ColumnCount = 15
' 列フッタを表示します。
FpSpread1.Sheets(0).ColumnFooter.Visible = true
FpSpread1.Sheets(0).ColumnFooter.RowCount = 2
FpSpread1.Sheets(0).ColumnFooter.Cells(0, 0).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"

設定方法

  1. シートに対し、ColumnFooterのVisibleプロパティを設定します。
  2. 列のSetAggregationTypeメソッドを設定します。

サンプルコード

次のサンプルコードは、先頭列の値を合計し、列フッタに計算結果を表示します。また、2番目のグループの値を合計し、グループフッタに計算結果を表示します。

C#
コードのコピー
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;

    // 値
    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;
}
Visual Basic
コードのコピー
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

    ' 値
    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

サンプルコード

次のサンプルコードは、列フッタを表示し、別のシート内の数式を追加します。

C#
コードのコピー
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;

// 値
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);
    }
}

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;

// 値
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);
    }
}

int i = 0;
fpSpread1.Sheets[0].ColumnFooter.Cells[0, i].Value = "2ndSum";
fpSpread1.Sheets[0].ColumnFooter.Cells[0, 1].Formula = "SUM(Sheet2!A:A)";
Visual Basic
コードのコピー
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

' 値
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

' 値
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)"
参照

 

 


© MESCIUS inc. All rights reserved.