合計行の表示

[umemura]

グリッドに合計行を表示と思ったら、どんな方法がありますか？
合計行用のレコードを追加して、レコード変更イベントで合計計算をする、というのはわかりそうなのですが、
行位置を固定したいのでソートされた場合のハンドリングや、
そのレコードのみ入力不可にするためのカスタムセルの実装など、
いろいろ意識するポイントがあるかと思います。

実装上のテクニックなどあれば教えてください。

[umemura]

最上行に表示するのであれば、ソートや行列の固定を利用（オーバーライドも必要？）すれば、何とか実装できそうな気がしていますが、
最下行として表示する方法についてはうまく思いつきません。
（最下行のレコードを対象に行列の固定をすると、スクロールできなくなってしまうため）


エクセルなどでも、合計行は、通常、下の方に表示することが多いと思いますが
Curl を利用した業務画面では、どのようなレイアウト実装をすることが多いのでしょうか。

Code:

{value
    def rfs =
        {RecordFields
            {RecordField "sum?", modifiable? = false, domain = bool, default-value = false},
            {RecordField "title", domain = String, caption = "項目"},
            {RecordField "1", domain = String, caption = "１月"},
            {RecordField "2", domain = String, caption = "２月"},
            {RecordField "3", domain = String, caption = "３月"},
            {RecordField "4", domain = String, caption = "４月"},
            {RecordField "5", domain = String, caption = "５月"},
            {RecordField "6", domain = String, caption = "６月"},
            {RecordField "7", domain = String, caption = "７月"},
            {RecordField "8", domain = String, caption = "８月"},
            {RecordField "9", domain = String, caption = "９月"},
            {RecordField "10", domain = String, caption = "10月"},
            {RecordField "11", domain = String, caption = "11月"},
            {RecordField "12", domain = String, caption = "12月"}
        }
    let rs:RecordSet = {RecordSet rfs }
    def rg = {RecordGrid width = 5in}
    set rg.record-source = rs
    {for i:int = 0 to 50 do
        def r1 = {rs.new-record}
        {rs.append r1}
    }
    ||合計計算
    {rs.add-event-handler
        {on e:RecordModified do
            {if-non-null rf = e.field then
                {if rf.name != "sum?" then
                let sum:int = 0
                {for r in rs do
                    {if r["sum?"] != true then
                        def s = {String r[rf.name] }
                        set sum = sum + {s.to-int}
                    }
                }
                {if-non-null sum-r =
                    {rs.select-one
                        filter =
                            {RecordFilter
                                {proc {r:Record}:bool
                                    {if r["sum?"] == true then
                                        {return true}
                                    }
                                    {return false}
                                 }
                            }
                    } then
                    set sum-r[rf.name] = {String sum}
                }
            }
            }
        }
    }
    ||合計行を最上行として表示
    def r = {rs.new-record}
    set r["sum?"] = true
    {rs.append r}
    {rs.commit}
    set rg.frozen-row-count =  1
    set rg.sort = "sum?"
    ||合計行を入力不可にする
    def sum-r-idx:int = 0
    {for col:int = 0 below rg.columns.size do
        {if-non-null cell = {rg.ui.get-cell-at-index sum-r-idx, col} then
            set cell.editable? = false
        }
    }
    rg
}

[umemura]

合計行専用のグリッドを別に作成し、入力用グリッドの下に表示するようにしてみました。
行列の固定時に同期する機能は未実装です。
依然としてスクロールバーを同期させる方法が怪しいですが。

Code:

{import * from COM.CURL.SONNTAG.LIB}
{define-proc public {get-grid-hscroll grid:RecordGrid}:#Scrollbar
    let hscroll:#Scrollbar = null
    {walk-graphics
        grid,
        ensure-ui-generated? = true,
        {proc {g:Graphic}:void
            {type-switch g
             case sb:Scrollbar do
                {if sb.direction == Orientation.horizontal then
                    set hscroll = sb
                }
            }
        }
    }
    {return hscroll}
}
{def rfs =
    {RecordFields
        {RecordField "1", domain = String},
        {RecordField "2", domain = String},
        {RecordField "3", domain = String},
        {RecordField "4", domain = String},
        {RecordField "5", domain = String},
        {RecordField "6", domain = String},
        {RecordField "7", domain = String},
        {RecordField "8", domain = String},
        {RecordField "9", domain = String},
        {RecordField "10", domain = String},
        {RecordField "11", domain = String},
        {RecordField "12", domain = String}
    }
}
{let rs:RecordSet = {RecordSet rfs }}
{let sum-rs:RecordSet = {RecordSet rfs}}
{def rg =
    {RecordGrid
        record-source = rs,
        width = {make-elastic},
        display-navigation-panel? = false
    }
}
{def sum-rg =
    {RecordGrid
        editable? = false,
        record-source = sum-rs,
        width = {make-elastic},
        display-navigation-panel? = false,
        display-column-headers? = false,
        height = 30pt
    }
}
{def v =
    {View
        width = 5in, height = 5in,
        {Frame
            width = {make-elastic},
            height = {make-elastic},
            hstretch? = true,
            vstretch? = true,
            {VBox rg, sum-rg}
        }
    }
}

{do
    {v.show}
    set (rg.ui-object asa RecordGridUI ).margin = 0pt
    set (sum-rg.ui-object asa RecordGridUI ).margin = 0pt
    {for i:int = 0 to 50 do
        def r = {rs.new-record}
        {rs.append r}
    }
    {rs.commit}
    def sum-r = {sum-rs.new-record}
    {sum-rs.append sum-r}
    {sum-rs.commit}
    ||合計計算
    {rs.add-event-handler
        {on e:RecordModified do
            {if-non-null rf = e.field then
                let sum:int = 0
                {for r in rs do
                    def s = {String r[rf.name] }
                    set sum = sum + {s.to-int}
                }
                {if-non-null sum-r = {sum-rs.select-one} then
                    set sum-r[rf.name] = {String sum}
                }
            }
        }
    }
    ||スクロールバーの同期設定
    {if-non-null
        hscrl1 = {get-grid-hscroll rg},
        hscrl2 = {get-grid-hscroll sum-rg}
     then
        set hscrl1.visible? = false
        set hscrl1.parent.height = 0pt
        {hscrl1.add-event-handler
            {on e:Adjustment at s:Scrollbar do
                {hscrl2.set-scroll-value {s.get-scroll-value}}
            }
        }
        {hscrl2.add-event-handler
            {on e:Adjustment at s:Scrollbar do
                {hscrl1.set-scroll-value {s.get-scroll-value}}
            }
        }
    }
}

[dyoshida]

Curl External Library のWorksheet のサンプルを参考にして、Worksheet にRecordGridを
埋め込んで、その下のセルに列の合計を表示する方法を試してみました。

※マウス操作でのRecordGridの列幅の変更や列の入れ替えまで対応すると、大変そうなので、
　RecordGridのオプションで無効にしています。

※外側のWorksheetの縦スクロールバーは必要なさそうですが、縦のみ非表示にするのは
　手間がかかりそうなので未対応です

Code:

{import * from COM.CURL.EXT.WORKSHEET}

{value
    def rf =
        {RecordFields
            {RecordField "title", domain = String, caption = "項目"},
            {RecordField "jan", domain = int, caption = "１月"},
            {RecordField "feb", domain = int, caption = "２月"},
            {RecordField "mar", domain = int, caption = "３月"},
            {RecordField "apr", domain = int, caption = "４月"},
            {RecordField "may", domain = int, caption = "５月"},
            {RecordField "jun", domain = int, caption = "６月"},
            {RecordField "jul", domain = int, caption = "７月"},
            {RecordField "aug", domain = int, caption = "８月"},
            {RecordField "sep", domain = int, caption = "９月"},
            {RecordField "oct", domain = int, caption = "10月"},
            {RecordField "nov", domain = int, caption = "11月"},
            {RecordField "dec", domain = int, caption = "12月"}
        }

    def rs =
        {RecordSet
            rf,
            {RecordData
                title = "項目1",
                jan = 10, feb = 20, mar = 30, apr = 40, may = 50, jun = 60,
                jul = 70, aug = 80, sep = 90, oct = 100, nov = 110, dec = 120
            },
            {RecordData
                title = "項目2",
                jan = 11, feb = 21, mar = 31, apr = 41, may = 51, jun = 61,
                jul = 71, aug = 81, sep = 91, oct = 101, nov = 111, dec = 121
            },
            {RecordData
                title = "項目3",
                jan = 12, feb = 22, mar = 32, apr = 42, may = 52, jun = 62,
                jul = 72, aug = 82, sep = 92, oct = 102, nov = 112, dec = 122
            },
            {RecordData
                title = "項目4",
                jan = 13, feb = 23, mar = 33, apr = 43, may = 53, jun = 63,
                jul = 73, aug = 83, sep = 93, oct = 103, nov = 113, dec = 123
            },
            {RecordData
                title = "項目5",
                jan = 14, feb = 24, mar = 34, apr = 44, may = 54, jun = 64,
                jul = 74, aug = 84, sep = 94, oct = 104, nov = 114, dec = 124
            }
        }

    def rg =
        {EmbeddedRecordGrid
            record-source = rs,
            takes-focus? = true,
            region-selection-enabled? = true,
            automatic-columns? = true,
            column-movable? = false,            || 列のドラッグ移動可否(不可に設定)
            column-resizable? = false           || 列のドラッグサイズ変更可否(不可に設定)
        }

    def rds = {RecordSetDataSource rs}
    def jan-ref = {rds.get-ref "jan"}
    def feb-ref = {rds.get-ref "feb"}
    def mar-ref = {rds.get-ref "mar"}
    def apr-ref = {rds.get-ref "apr"}
    def may-ref = {rds.get-ref "may"}
    def jun-ref = {rds.get-ref "jun"}
    def jul-ref = {rds.get-ref "jul"}
    def aug-ref = {rds.get-ref "aug"}
    def sep-ref = {rds.get-ref "sep"}
    def oct-ref = {rds.get-ref "oct"}
    def nov-ref = {rds.get-ref "nov"}
    def dec-ref = {rds.get-ref "dec"}

    || 列合計を求めるプロシージャ
    def sum-proc =
      {proc {dest:DataRef, ...:DataRef}:void
            let x:int = 0
            {for v in ... do
                {if v.composite? then
                    {for y in v do
                        set x = x + (y.value asa int)
                    }
                 else
                    set x = x + (v.value asa int)
                }
            }
            set dest.value = {String x}
        }

    ||　各月の合計セルのFormulaSpecを生成するプロシージャ
    def make-sum-cell-formula-spec =
        {proc {month-ref:DataRef}:FormulaSpec
            {return
                {formula-cell
                    sum-proc,
                    domain = DataSource.string-domain,
                    halign = "right",
                    background="white",
                    month-ref
                }
            }
        }

    || ワークシート
    {Worksheet
        2, rs.fields.size + 1,
        || TODO:スクロールバーのサイズを0.45ｃｍでハードコードしている
        {widths 1cm, 1cm, 1cm, 1cm, 1cm, 1cm, 1cm, 1cm, 1cm, 1cm, 1cm, 1cm, 1cm, 0.45cm},

        default-column-width = 1cm,
        font-size = rg.font-size,
        font-family = rg.font-family,
        grid-line-color = "gray", ||rg.grid-line-color,

        || RecordGrid本体
        row = 0, col = 0,
        row-height = 3cm, colspan = rs.fields.size + 1,
        rg,

        || 合計行
        row = 1, col = 0,
        {value-cell
            {bold 合計},
            background="white",
            valign = "center",
            font-size = rg.font-size,
            font-family = rg.font-family
        },
        row = 1, col = 1,
        {make-sum-cell-formula-spec  jan-ref},
        row = 1, col = 2,
        {make-sum-cell-formula-spec feb-ref},
        row = 1, col = 3,
        {make-sum-cell-formula-spec mar-ref},
        row = 1, col = 4,
        {make-sum-cell-formula-spec apr-ref},
        row = 1, col = 5,
        {make-sum-cell-formula-spec may-ref},
        row = 1, col = 6,
        {make-sum-cell-formula-spec jun-ref},
        row = 1, col = 7,
        {make-sum-cell-formula-spec jul-ref},
        row = 1, col = 8,
        {make-sum-cell-formula-spec aug-ref},
        row = 1, col = 9,
        {make-sum-cell-formula-spec sep-ref},
        row = 1, col = 10,
        {make-sum-cell-formula-spec oct-ref},
        row = 1, col = 11,
        {make-sum-cell-formula-spec nov-ref},
        row = 1, col = 12,
        {make-sum-cell-formula-spec dec-ref}
    }
}