SheetViewクラスのSetConditionalFormattingメソッドを使用します。
実行例：
「Quantity」列にデータバー、最後の列にカラースケールを設定しました。
FpSpread1.DataSource = new StockList(); //非連結列を追加します int lastCol = FpSpread1.Sheets[0].ColumnCount; FpSpread1.Sheets[0].AddColumns(lastCol, 1); FpSpread1.Sheets[0].Columns[lastCol].Label = "合計"; //「Quantity * UnitPrice」を計算します FpSpread1.Sheets[0].Columns[lastCol].Formula = "G1*I1"; //データバーを設定します DatabarConditionalFormattingRule dataBar = new DatabarConditionalFormattingRule() { //範囲内の最小値／最大値を基準にバーの長さを決めます Maximum = new ConditionalFormattingValue(ConditionalFormattingValueType.Max), Minimum = new ConditionalFormattingValue(ConditionalFormattingValueType.Min) }; FpSpread1.Sheets[0].SetConditionalFormatting(-1, 6, dataBar); //カラースケールを設定します TwoColorScaleConditionalFormattingRule colorScale = new TwoColorScaleConditionalFormattingRule() { //指定した最小値（0）／最大値（300,000）を基準にカラースケールを適用します MaxValue = new ConditionalFormattingColorValue(System.Drawing.Color.Orange, 300000, ConditionalFormattingValueType.Number), MinValue = new ConditionalFormattingColorValue(System.Drawing.Color.AliceBlue, 0, ConditionalFormattingValueType.Number) }; FpSpread1.Sheets[0].SetConditionalFormatting(-1, 11, colorScale); //コントロールのサイズを自動調整します FpSpread1.ClientAutoSize = true;
FpSpread1.DataSource = New StockList() '非連結列を追加します Dim lastCol As Integer = FpSpread1.Sheets(0).ColumnCount FpSpread1.Sheets(0).AddColumns(lastCol, 1) FpSpread1.Sheets(0).Columns(lastCol).Label = "合計" '「Quantity * UnitPrice」を計算します FpSpread1.Sheets(0).Columns(lastCol).Formula = "G1*I1" 'データバーを設定します '範囲内の最小値／最大値を基準にバーの長さを決めます Dim dataBar As New DatabarConditionalFormattingRule() With { .Maximum = New ConditionalFormattingValue(ConditionalFormattingValueType.Max), .Minimum = New ConditionalFormattingValue(ConditionalFormattingValueType.Min) } FpSpread1.Sheets(0).SetConditionalFormatting(-1, 6, dataBar) 'カラースケールを設定します '指定した最小値（0）／最大値（300,000）を基準にカラースケールを適用します Dim colorScale As New TwoColorScaleConditionalFormattingRule() With { .MaxValue = New ConditionalFormattingColorValue(System.Drawing.Color.Orange, 300000, ConditionalFormattingValueType.Number), .MinValue = New ConditionalFormattingColorValue(System.Drawing.Color.AliceBlue, 0, ConditionalFormattingValueType.Number) } FpSpread1.Sheets(0).SetConditionalFormatting(-1, 11, colorScale) 'コントロールのサイズを自動調整します FpSpread1.ClientAutoSize = True
この例では、データソースとして以下のクラスを使用しています。
[Serializable] public class Stock { public string ProductCode { get; set; } public string ProductName { get; set; } public DateTime StockDate { get; set; } public string NoteNo { get; set; } public DateTime ExpireDate { get; set; } public string LotNo { get; set; } public int Quantity { get; set; } public string Term { get; set; } public double UnitPrice { get; set; } public string Section { get; set; } public string StoreID { get; set; } } [Serializable] public class StockList : List<Stock> { public StockList() { Add(new Stock { ProductCode = "0000001", ProductName = "スイートアーモンド", StockDate = DateTime.Today.AddDays(-9), NoteNo = "D040000004", ExpireDate = DateTime.Today.AddMonths(1), LotNo = "2011092001", Quantity = 200, Term = "本", UnitPrice = 90, Section = "仕入", StoreID = "MYG1001" }); Add(new Stock { ProductCode = "0000001", ProductName = "スイートアーモンド", StockDate = DateTime.Today.AddDays(-9), NoteNo = "D040000004", ExpireDate = DateTime.Today.AddMonths(1), LotNo = "2011092003", Quantity = 400, Term = "本", UnitPrice = 90, Section = "仕入", StoreID = "MYG1001" }); Add(new Stock { ProductCode = "0000001", ProductName = "スイートアーモンド", StockDate = DateTime.Today.AddDays(-6), NoteNo = "D040000001", ExpireDate = DateTime.Today.AddMonths(1), LotNo = "0000001", Quantity = 490, Term = "本", UnitPrice = 90, Section = "製造", StoreID = "MYG1001" }); Add(new Stock { ProductCode = "0000002", ProductName = "グレープシード", StockDate = DateTime.Today.AddDays(-4), NoteNo = "D040000007", ExpireDate = DateTime.Today.AddMonths(2), LotNo = "20111119001", Quantity = 10, Term = "本", UnitPrice = 120, Section = "仕入", StoreID = "MYG1001" }); Add(new Stock { ProductCode = "0000002", ProductName = "グレープシード", StockDate = DateTime.Today.AddDays(-5), NoteNo = "M040000002", ExpireDate = DateTime.Today.AddMonths(2), LotNo = "123", Quantity = 4, Term = "本", UnitPrice = 100, Section = "製造", StoreID = "MYG1001" }); Add(new Stock { ProductCode = "0000003", ProductName = "オリーブ", StockDate = DateTime.Today.AddDays(-9), NoteNo = "D040000006", ExpireDate = DateTime.Today.AddMonths(3), LotNo = "20111119010", Quantity = 9, Term = "本", UnitPrice = 2000, Section = "仕入", StoreID = "MYG1002" }); Add(new Stock { ProductCode = "0000005", ProductName = "馬油", StockDate = DateTime.Today.AddDays(-7), NoteNo = "D040000009", ExpireDate = DateTime.Today.AddYears(1), LotNo = "20111214002", Quantity = 84, Term = "Ｋｇ", UnitPrice = 3200, Section = "仕入", StoreID = "MYG1001" }); Add(new Stock { ProductCode = "0000006", ProductName = "ビーワックス", StockDate = DateTime.Today.AddDays(-2), NoteNo = "D040000011", ExpireDate = DateTime.Today.AddYears(1), LotNo = "20111217004", Quantity = 150, Term = "Ｋｇ", UnitPrice = 1500, Section = "製造", StoreID = "MYG1002" }); Add(new Stock { ProductCode = "0000010", ProductName = "えごま", StockDate = DateTime.Today.AddDays(-3), NoteNo = "D040000008", ExpireDate = DateTime.Today.AddMonths(1), LotNo = "20111120002", Quantity = 52, Term = "本", UnitPrice = 120, Section = "仕入", StoreID = "MYG1002" }); } }
<Serializable> Public Class Stock Public Property ProductCode() As String Public Property ProductName() As String Public Property StockDate() As DateTime Public Property NoteNo() As String Public Property ExpireDate() As DateTime Public Property LotNo() As String Public Property Quantity() As Integer Public Property Term() As String Public Property UnitPrice() As Double Public Property Section() As String Public Property StoreID() As String End Class <Serializable> Public Class StockList Inherits List(Of Stock) Public Sub New() Add(New Stock() With { .ProductCode = "0000001", .ProductName = "スイートアーモンド", .StockDate = DateTime.Today.AddDays(-9), .NoteNo = "D040000004", .ExpireDate = DateTime.Today.AddMonths(1), .LotNo = "2011092001", .Quantity = 200, .Term = "本", .UnitPrice = 90, .Section = "仕入", .StoreID = "MYG1001" }) Add(New Stock() With { .ProductCode = "0000001", .ProductName = "スイートアーモンド", .StockDate = DateTime.Today.AddDays(-9), .NoteNo = "D040000004", .ExpireDate = DateTime.Today.AddMonths(1), .LotNo = "2011092003", .Quantity = 400, .Term = "本", .UnitPrice = 90, .Section = "仕入", .StoreID = "MYG1001" }) Add(New Stock() With { .ProductCode = "0000001", .ProductName = "スイートアーモンド", .StockDate = DateTime.Today.AddDays(-6), .NoteNo = "D040000001", .ExpireDate = DateTime.Today.AddMonths(1), .LotNo = "0000001", .Quantity = 490, .Term = "本", .UnitPrice = 90, .Section = "製造", .StoreID = "MYG1001" }) Add(New Stock() With { .ProductCode = "0000002", .ProductName = "グレープシード", .StockDate = DateTime.Today.AddDays(-4), .NoteNo = "D040000007", .ExpireDate = DateTime.Today.AddMonths(2), .LotNo = "20111119001", .Quantity = 10, .Term = "本", .UnitPrice = 120, .Section = "仕入", .StoreID = "MYG1001" }) Add(New Stock() With { .ProductCode = "0000002", .ProductName = "グレープシード", .StockDate = DateTime.Today.AddDays(-5), .NoteNo = "M040000002", .ExpireDate = DateTime.Today.AddMonths(2), .LotNo = "123", .Quantity = 4, .Term = "本", .UnitPrice = 100, .Section = "製造", .StoreID = "MYG1001" }) Add(New Stock() With { .ProductCode = "0000003", .ProductName = "オリーブ", .StockDate = DateTime.Today.AddDays(-9), .NoteNo = "D040000006", .ExpireDate = DateTime.Today.AddMonths(3), .LotNo = "20111119010", .Quantity = 9, .Term = "本", .UnitPrice = 2000, .Section = "仕入", .StoreID = "MYG1002" }) Add(New Stock() With { .ProductCode = "0000005", .ProductName = "馬油", .StockDate = DateTime.Today.AddDays(-7), .NoteNo = "D040000009", .ExpireDate = DateTime.Today.AddYears(1), .LotNo = "20111214002", .Quantity = 84, .Term = "Ｋｇ", .UnitPrice = 3200, .Section = "仕入", .StoreID = "MYG1002" }) Add(New Stock() With { .ProductCode = "0000006", .ProductName = "ビーワックス", .StockDate = DateTime.Today.AddDays(-2), .NoteNo = "D040000011", .ExpireDate = DateTime.Today.AddYears(1), .LotNo = "20111217004", .Quantity = 150, .Term = "Ｋｇ", .UnitPrice = 1500, .Section = "製造", .StoreID = "MYG1002" }) Add(New Stock() With { .ProductCode = "0000010", .ProductName = "えごま", .StockDate = DateTime.Today.AddDays(-3), .NoteNo = "D040000008", .ExpireDate = DateTime.Today.AddMonths(1), .LotNo = "20111120002", .Quantity = 52, .Term = "本", .UnitPrice = 120, .Section = "仕入", .StoreID = "MYG1002" }) End Sub End Class