MESCIUS SPREAD for Windows Forms 15.0J サンプルコード集
条件付きフォーマットを設定する

SPREAD for Windows Forms 7.0J以降で、新しい形式の「ルールによる条件付き書式」の機能が追加されました。SPREAD for Windows Forms 5.0Jで提供されている条件付き書式の機能は後方互換性を維持する目的で残されています。バージョン7.0J以降のバージョンでも問題なく動作しますが、古い形式として扱われ、コーディング時に以下のような警告が表示されます。

次の図は、古い形式の条件付き書式を使用している場合の例です。

下記サンプルは旧形式と新形式の2つの方法で条件付き書式を設定する方法です。

【実行例】

条件付きフォーマット

private void Form1_Load(object sender, EventArgs e)
{
// データの設定
fpSpread1.ActiveSheet.SetValue(1, 1, 5);
fpSpread1.ActiveSheet.SetValue(2, 1, 50);
fpSpread1.ActiveSheet.SetValue(3, 1, 75);
fpSpread1.ActiveSheet.SetValue(4, 1, 99);
fpSpread1.ActiveSheet.SetValue(1, 2, 5);
fpSpread1.ActiveSheet.SetValue(2, 2, 50);
fpSpread1.ActiveSheet.SetValue(3, 2, 75);
fpSpread1.ActiveSheet.SetValue(4, 2, 99);

//
// 旧形式による条件付き書式の設定
//

FarPoint.Win.Spread.NamedStyle styleBlue = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle stylePink = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle styleLime = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle styleYellow = new FarPoint.Win.Spread.NamedStyle();
FarPoint.Win.Spread.NamedStyle styleEmpty = new FarPoint.Win.Spread.NamedStyle();
styleBlue.BackColor = Color.Blue;
styleBlue.ForeColor = Color.White;
stylePink.BackColor = Color.Pink;
styleLime.BackColor = Color.Lime;
styleYellow.BackColor = Color.Yellow;
styleEmpty.BackColor = Color.Empty;
styleEmpty.ForeColor = Color.Empty;
fpSpread1.ActiveSheet.SetConditionalFormat(1, 1, styleEmpty, FarPoint.Win.Spread.ComparisonOperator.EqualTo, "0");
fpSpread1.ActiveSheet.SetConditionalFormat(1, 1, styleBlue, FarPoint.Win.Spread.ComparisonOperator.LessThan, "10");
fpSpread1.ActiveSheet.SetConditionalFormat(2, 1, stylePink, FarPoint.Win.Spread.ComparisonOperator.Between, "20", "50");
fpSpread1.ActiveSheet.SetConditionalFormat(3, 1, styleLime, FarPoint.Win.Spread.ComparisonOperator.Between, "50", "80");
fpSpread1.ActiveSheet.SetConditionalFormat(4, 1, styleYellow, FarPoint.Win.Spread.ComparisonOperator.GreaterThan, "80");

//
// 新形式による条件付き書式の設定
//

// EqualTo
FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule UnaryRule1 = new FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule(FarPoint.Win.Spread.UnaryComparisonOperator.EqualTo, 0, false);
UnaryRule1.BackColor = Color.Empty;

// LessThan
FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule UnaryRule2 = new FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule(FarPoint.Win.Spread.UnaryComparisonOperator.LessThan, 10, false);
UnaryRule2.BackColor = Color.Blue;
UnaryRule2.ForeColor = Color.White;

FarPoint.Win.Spread.ConditionalFormatting UnaryCF1 = new FarPoint.Win.Spread.ConditionalFormatting(new FarPoint.Win.Spread.Model.CellRange(1, 2, 1, 1));
UnaryCF1.Add(UnaryRule1);
UnaryCF1.Add(UnaryRule2);
fpSpread1.ActiveSheet.Models.ConditionalFormatting.Add(UnaryCF1);

// Between
FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule BetweenRule1 = new FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule(false, 20, false, 50, false);
BetweenRule1.BackColor = Color.Pink;
FarPoint.Win.Spread.ConditionalFormatting BetweenCF1 = new FarPoint.Win.Spread.ConditionalFormatting(new FarPoint.Win.Spread.Model.CellRange(2, 2, 1, 1));
BetweenCF1.Add(BetweenRule1);
fpSpread1.ActiveSheet.Models.ConditionalFormatting.Add(BetweenCF1);

// Between
FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule BetweenRule2 = new FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule(false, 50, false, 80, false);
BetweenRule2.BackColor = Color.Lime;
FarPoint.Win.Spread.ConditionalFormatting BetweenCF2 = new FarPoint.Win.Spread.ConditionalFormatting(new FarPoint.Win.Spread.Model.CellRange(3, 2, 1, 1));
BetweenCF2.Add(BetweenRule2);
fpSpread1.ActiveSheet.Models.ConditionalFormatting.Add(BetweenCF2);

// GreaterThan
FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule UnaryRule3 = new FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule(FarPoint.Win.Spread.UnaryComparisonOperator.GreaterThan, 80, false);
UnaryRule3.BackColor = Color.Yellow;
FarPoint.Win.Spread.ConditionalFormatting UnaryCF3 = new FarPoint.Win.Spread.ConditionalFormatting(new FarPoint.Win.Spread.Model.CellRange(4, 2, 1, 1));
UnaryCF3.Add(UnaryRule3);
fpSpread1.ActiveSheet.Models.ConditionalFormatting.Add(UnaryCF3);
}
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
' データの設定
FpSpread1.ActiveSheet.SetValue(1, 1, 5)
FpSpread1.ActiveSheet.SetValue(2, 1, 50)
FpSpread1.ActiveSheet.SetValue(3, 1, 75)
FpSpread1.ActiveSheet.SetValue(4, 1, 99)
FpSpread1.ActiveSheet.SetValue(1, 2, 5)
FpSpread1.ActiveSheet.SetValue(2, 2, 50)
FpSpread1.ActiveSheet.SetValue(3, 2, 75)
FpSpread1.ActiveSheet.SetValue(4, 2, 99)

'
' 旧形式による条件付き書式の設定
'

Dim styleBlue As New FarPoint.Win.Spread.NamedStyle
Dim stylePink As New FarPoint.Win.Spread.NamedStyle
Dim styleLime As New FarPoint.Win.Spread.NamedStyle
Dim styleYellow As New FarPoint.Win.Spread.NamedStyle
Dim styleEmpty As New FarPoint.Win.Spread.NamedStyle
styleBlue.BackColor = Color.Blue
styleBlue.ForeColor = Color.White
stylePink.BackColor = Color.Pink
styleLime.BackColor = Color.Lime
styleYellow.BackColor = Color.Yellow
styleEmpty.BackColor = Color.Empty
styleEmpty.ForeColor = Color.Empty
FpSpread1.ActiveSheet.SetConditionalFormat(1, 1, styleEmpty, FarPoint.Win.Spread.ComparisonOperator.EqualTo, "0")
FpSpread1.ActiveSheet.SetConditionalFormat(1, 1, styleBlue, FarPoint.Win.Spread.ComparisonOperator.LessThan, "10")
FpSpread1.ActiveSheet.SetConditionalFormat(2, 1, stylePink, FarPoint.Win.Spread.ComparisonOperator.Between, "20", "50")
FpSpread1.ActiveSheet.SetConditionalFormat(3, 1, styleLime, FarPoint.Win.Spread.ComparisonOperator.Between, "50", "80")
FpSpread1.ActiveSheet.SetConditionalFormat(4, 1, styleYellow, FarPoint.Win.Spread.ComparisonOperator.GreaterThan, "80")

'
' 新形式による条件付き書式の設定
'

' EqualTo
Dim UnaryRule1 As New FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule(FarPoint.Win.Spread.UnaryComparisonOperator.EqualTo, 0, False)
UnaryRule1.BackColor = Color.Empty

' LessThan
Dim UnaryRule2 As New FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule(FarPoint.Win.Spread.UnaryComparisonOperator.LessThan, 10, False)
UnaryRule2.BackColor = Color.Blue
UnaryRule2.ForeColor = Color.White

Dim UnaryCF1 As New FarPoint.Win.Spread.ConditionalFormatting(New FarPoint.Win.Spread.Model.CellRange(1, 2, 1, 1))
UnaryCF1.Add(UnaryRule1)
UnaryCF1.Add(UnaryRule2)
FpSpread1.ActiveSheet.Models.ConditionalFormatting.Add(UnaryCF1)

' Between
Dim BetweenRule1 As New FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule(False, 20 , False, 50, False)
BetweenRule1.BackColor = Color.Pink
Dim BetweenCF1 As New FarPoint.Win.Spread.ConditionalFormatting(New FarPoint.Win.Spread.Model.CellRange(2, 2, 1, 1))
BetweenCF1.Add(BetweenRule1)
FpSpread1.ActiveSheet.Models.ConditionalFormatting.Add(BetweenCF1)

' Between
Dim BetweenRule2 As New FarPoint.Win.Spread.BetweenValuesConditionalFormattingRule(False, 50 , False, 80, False)BetweenRule2.BackColor = Color.Lime
Dim BetweenCF2 As New FarPoint.Win.Spread.ConditionalFormatting(New FarPoint.Win.Spread.Model.CellRange(3, 2, 1, 1))
BetweenCF2.Add(BetweenRule2)
FpSpread1.ActiveSheet.Models.ConditionalFormatting.Add(BetweenCF2)

' GreaterThan
Dim UnaryRule3 As New FarPoint.Win.Spread.UnaryComparisonConditionalFormattingRule(FarPoint.Win.Spread.UnaryComparisonOperator.GreaterThan, 80, False)
UnaryRule3.BackColor = Color.Yellow
Dim UnaryCF3 As New FarPoint.Win.Spread.ConditionalFormatting(New FarPoint.Win.Spread.Model.CellRange(4, 2, 1, 1))
UnaryCF3.Add(UnaryRule3)
FpSpread1.ActiveSheet.Models.ConditionalFormatting.Add(UnaryCF3)
End Sub

 

 


© MESCIUS inc. All rights reserved.