エクセルからコピーしたデータをグリッドに貼り付ける、という処理で
大量データの張り付け（コピーした対象レコードが多い）と、
貼り付け処理にかなり時間がかかります。
オープンコントロールのソースを見るかぎり、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}
}
||このタイミングでイベントを消費しないと、
||正しくレコードが作成されない（１レコード目のみ作成）されるため
||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}
||レコードがない場合に貼り付けコマンドがエラーにならないように１レコード追加する
{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?
}
}
