合計行の表示

[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}
    }
}