Thread Rating:
  • 291 Vote(s) - 2.78 Average
  • 1
  • 2
  • 3
  • 4
  • 5
get-cell-at を少しでも早くするには?
03-06-2014, 02:27 PM, (This post was last modified: 03-06-2014, 02:29 PM by umemura.)
#2
RE: get-cell-at を少しでも早くするには?
エクセルからコピーしたデータをグリッドに貼り付ける、という処理で
大量データの張り付け(コピーした対象レコードが多い)と、
貼り付け処理にかなり時間がかかります。

オープンコントロールのソースを見るかぎり、get-celll-at で、
張り付けもとのデータをセルにセットしている処理が重そうだ、という見切りがついたため、
前段の質問となりました。

内部の get-cell-at ではなく、レコードの作成に変更したところ、
かなり高速化できました。

ただ、グリッド内の全レコードを、貼り付けデータから作り直すという要件にはいいのですが、
部分貼り付けのような機能には、このサンプルは対応できないので、注意が必要です。


Code:
{import * from CURL.DESKTOP.CLIPBOARD}


{define-class public open ExcelDataRecordGridPaste {inherits RecordGridPaste}
  {constructor public {default context:RecordGrid}
    {construct-super context}
  }

  {method public {do-command allow-prompt?:bool = true}:void

    {super.do-command allow-prompt? = allow-prompt?}

  }

  {method protected open {execute}:void
    {if-non-null rs = self.context.record-source then
        ||全選択時のみレコードの差し替え
        {if self.context.selection.record-count == self.context.records.size then
            ||改行を含んだセルや、空行のトリミングを行い、
            ||貼り付け先のレコードグリッドに、貼り付け対象と同数のレコードを作成する
            {self.adjust-records}
        }
        ||このタイミングでイベントを消費しないと、
        ||正しくレコードが作成されない(1レコード目のみ作成)されるため
        ||dispatch-events をコールしている
        {dispatch-events false}


        def ret-val = {popup-question "高速貼り付けを行う?"}
        def dt = {DateTime}
        {if ret-val == Dialog.yes then
            {with
                self.context.records.batch-events? = true,
                rs.batch-events? = true do
                {self.execute-rapid}
            }
         else
            {super.execute}
        }
        {popup-message "貼り付け所要時間:" & {dt.elapsed}}
    }
  }


  ||オープンコントロールから引用
  ||貼り付けを高速にするために、
  ||get-cell-at を利用した perform-paste ではなく
  ||レコードへのデータセットを行うよう改造
  ||ただし、全レコードの差し替えでしか利用できないことに注意
  {method {execute-rapid}:void


    {if not self.enabled? then {return}}

    || 1) Get the clipboard and ensure its validity

    let clipping:String = ""
    let clipboard-valid?:bool = false

    {try
        set (clipping, clipboard-valid?) =
            {{Clipboard.get-system-clipboard}.get-string}
    catch e:HostClipboardException do
        {return}
    }

    {if not clipboard-valid? then {return}}

    || 2) Setup

    let constant grid:RecordGrid = self.context
    let constant ui:RecordGridUI = grid.ui
    let constant selection:RecordGridSelection = grid.selection
    let constant selection-empty?:bool = selection.empty?
    let constant line-delimiter:char = '\n'
    let constant cell-delimiter:char = '\t'
    let constant cell-splitter:CharClass = {CharClass.from-any cell-delimiter}

    let column-index:int = -1
    let record-index:int = -1
    let leftmost-col-index:int = 0

    || 3) Locate our starting cell and leftmost boundary

    {if-non-null focus-cell = ui.grid-focus then
        {if-non-null column = focus-cell.column then
            set column-index = {grid.columns.find column}
            set leftmost-col-index = column-index
        }
        set record-index = focus-cell.record-index

    elseif selection.region-count >= 1 then
        let region:RecordGridRegion = {selection.regions.read-one}
        set leftmost-col-index = region.first-column
        set record-index = region.first-row
        set column-index = region.first-column

    elseif selection.record-count >= 1 then
        set record-index = max-int
        {for one-rec-index in selection.records do
            {if one-rec-index < record-index then
                set record-index = one-rec-index
            }
        }
        set leftmost-col-index = 0
        set column-index = 0

    elseif selection.column-count >= 1 then
        set leftmost-col-index = max-int
        {for one-col in selection.columns do
            let one-col-index:int = {grid.columns.find one-col}
            {if one-col-index >= 0 and
                one-col-index < leftmost-col-index
             then
                set leftmost-col-index = one-col-index
            }
        }
        set record-index = 0
        set column-index = leftmost-col-index

    }

    || ensure that we have a viable starting cell
    {if record-index < 0 or
        record-index >= grid.records.size or
        column-index < 0 or
        column-index >= grid.columns.size
    then
        {return}
    }

    || 4) Loop through a block of cells and locate all target cells
    || This must be done first in case there is a selection, as data
    || updates may cancel the selection.

    let constant clip-lines:{Array-of String} =
        {clipping.split split-chars = {CharClass.from-any line-delimiter}}
    let constant clip-lines-last-index:int = clip-lines.size - 1
    let constant target-records:{Array-of int} = {new {Array-of int}}
    let constant target-columns:{Array-of int} = {new {Array-of int}}
    let constant target-fragments:{Array-of String} = {new {Array-of String}}

    {for tag = lines, one-clip-line key line-index in clip-lines do

        let cell-fragments:{Array-of String} =
            {one-clip-line.split split-chars = cell-splitter}

        || Ensure that blank lines are actually "", not empty;
        || except in the case of the \n$ final "line", which would be a
        || copy artifact and shouldn't become a ghost line in the paste.
        {if cell-fragments.empty? and
            line-index != clip-lines-last-index
         then
            {cell-fragments.append ""}
        }

        {for tag = cells, one-cell-fragment in cell-fragments do

            || 4b) ensure we're within the selection, if any
            || If we had a selection, only paste into selected cells,
            || otherwise skip and discard this text fragment.
            {if not selection-empty? then
                {if not ({selection.contains-record?
                             record-index} or
                         {selection.contains-column?
                             grid.columns[column-index]} or
                         {selection.regions-contain-cell?
                             record-index, column-index}
                        )
                 then
                    {continue tag = cells}
                }
            }

            || 4c) push the indices for later paste
            {target-records.push record-index}
            {target-columns.push column-index}
            {target-fragments.push one-cell-fragment}

         next

            || 4d) move to the next cell
            {inc column-index}
            {if column-index > (grid.columns.size - 1) then
                {continue tag = lines}
            }
        }

    next
        || 4e) move to the next row
        {inc record-index}
        {if record-index > grid.records.size - 1 then
            {break tag = lines}
        }
        set column-index = leftmost-col-index
    }


    ||高速貼り付けを行うため get-cell-at ではなく
    ||レコードの作成を行う
    {with self.context.records.batch-events? = true do
        def rfs = self.context.records.source.fields


        {for i = 0 to target-records.size - 1 do

            def r = grid.records[target-records[i]]
            def col = grid.columns[target-columns[i]]

            def (rf:RecordField, found?:bool) =  {rfs.get-if-exists col.field-name}

            {if found? then
                set r[rf.name] = target-fragments[i]
            }
        }
    }


  }



  ||改行を含んだセルや、空行のトリミングを行い、
  ||貼り付け先のレコードグリッドに、貼り付け対象と同数のレコードを作成する
  {method {adjust-records}:void

    || 1) Get the clipboard and ensure its validity

    let clipping:String = ""
    let clipboard-valid?:bool = false

    {try
        set (clipping, clipboard-valid?) =
            {{Clipboard.get-system-clipboard}.get-string}
    catch e:HostClipboardException do
        {return}
    }

    {if not clipboard-valid? then {return}}

    def csv-reader =
        {CsvDataReader.from-stream
            {clipping.to-InputStream}
        }

    ||改行でスプリットして行数を計算する
    let rows:int =
        {clipping.split split-chars = '\n'}.size

    {if-non-null rs = self.context.record-source then
        {with rs.batch-events? = true do
            {rs.delete-all}
            {for i:int = 0 below rows do
                def new-r = {rs.new-record}
                {rs.append new-r}
            }
        }
        {rs.commit}
    }
    {self.context.select-all}
  }
}


||エクセルからのデータを貼り付けられることを想定したレコードグリッド
|| 貼り付けコマンド発行時に ExcelDataRecordGridPaste が作成、実行されます。
{define-class public ExcelDataPasteControlRecordGrid {inherits RecordGrid}

  {constructor public {default ...}
    {construct-super {splice ...}}
  }

  ||貼り付け時は、エクセルデータ貼り付け処理を行う
  {method public {create-command name:String}:#Command
    {switch name
    case "paste" do
        def  paste-command =
            {ExcelDataRecordGridPaste self}

        {return paste-command}
    else
        {return {super.create-command name}}
    }
  }
}



{def rf-ary = {{Array-of RecordField}}}
{for i:int = 1 to 100 do
    {rf-ary.append {RecordField {String i}, domain = String, nullable? = true}}
}

{def rs = {RecordSet {RecordFields {splice rf-ary}}}}

{def paste-grid =
    {ExcelDataPasteControlRecordGrid
        width = 5in, height =2in, record-source = rs}}

{value paste-grid}

{let str:String = ""}
{do
    {for i:int = 1 to 100 do
        {for j :int = 1 to 100 do
            set str = str & {String i * j} & "\t"
        }
        set str = str & "\n"
    }
}

{def ta = {TextArea value = str , width = 5in, height =2in}}

{value ta}

{CommandButton
    label = "貼り付け",
    {on Action do

        def clip = {Clipboard.get-system-clipboard}
        {clip.set-string ta.value}

        def default-paste-grid-editable? = paste-grid.editable?

        set paste-grid.editable? = true
        {dispatch-events false}

        ||レコードがない場合に貼り付けコマンドがエラーにならないように1レコード追加する
        {if paste-grid.records.size == 0 then
            {if-non-null rs-tmp = paste-grid.record-source then
                {rs-tmp.append {rs-tmp.new-record}}
            }
        }

        {paste-grid.select-all}
        {paste-grid.do-command "paste"}
        {paste-grid.select-nothing}

        set paste-grid.editable? = default-paste-grid-editable?

    }
}


Messages In This Thread
RE: get-cell-at を少しでも早くするには? - by umemura - 03-06-2014, 02:27 PM
Forum Jump:


Users browsing this thread:
1 Guest(s)

MyBB SQL Error

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1017 - Can't find file: 'mybb_threadviews' (errno: 2)
Query:
INSERT INTO mybb_threadviews (tid) VALUES('1045')