Excel で 書式をクリップボード経由にすると遅くなる

実験メモ。

とあるプロジェクトで、Excel の書式コピーのパフォーマンスが良くない。ので調査。
とあるソースコードでは、

1. Excel のテンプレートファイルを開いている。
2. 書式を揃えるために、2行目から1000行までをコピーしている。
3. データベースを検索して、セルにちまちま書き込む。

ってことをやっていました。で「遅いのは何処か？」ってな感じなのですが、普通は3を疑うところなのですが、いえいえ実際は2でした、というオチです。
Excel で帳票を作って「いまいち遅いんだよなぁ」って方は、試してみてください。.NET から操作する場合は、データベースアクセスよりも、Excel への操作のほうがネックになることが多いのです。

■いちいち、copy and paste しているのが駄目

最初のコードは、こんな感じです。実は Range のところ、もっと駄目なのですが（column毎にループしていたり）サンプル作るのが面倒なので、ちょっと高速化。

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
	Dim xapp As New Microsoft.Office.Interop.Excel.Application
	Dim path As String = System.IO.Directory.GetCurrentDirectory() + "\" + "SampleTemplate.xlsx"
	xapp.Workbooks.Open(path)
	Dim sh As Worksheet = xapp.Workbooks(1).Sheets(1)

	' 先頭行はタイトル、2行目からコピーする
	Dim start As Date = Date.Now
	For r = 3 To 1000
		Dim src As Range = sh.Range(sh.Cells(2, 1), sh.Cells(2, 6))
		src.Copy()
		Dim dest As Range = sh.Range(sh.Cells(r, 1), sh.Cells(r, 6))
		dest.PasteSpecial()
	Next
	TextBox1.Text = (Date.Now - start).ToString()

	path = System.IO.Directory.GetCurrentDirectory() + "\" + "range1.xlsx"
	System.IO.File.Delete(path)
	xapp.Workbooks(1).SaveAs(path)
	xapp.Quit()
	MessageBox.Show("クリップボード経由版 保存しました")
End Sub

これを実行すると、2分強かかります。ええッ!!! 単にコピーしているだけなのに...と思うでしょう。

■copy を 1回だけにする

書式として使っている2行目は不変なわけですから、何度も copy する必要はありません。
なので、copy するところを1回にして、後は何度も paste すれば ok。

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
	Dim xapp As New Microsoft.Office.Interop.Excel.Application
	Dim path As String = System.IO.Directory.GetCurrentDirectory() + "\" + "SampleTemplate.xlsx"
	xapp.Workbooks.Open(path)
	Dim sh As Worksheet = xapp.Workbooks(1).Sheets(1)

	' 先頭行はタイトル、2行目からコピーする
	Dim start As Date = Date.Now
	Dim src As Range = sh.Range(sh.Cells(2, 1), sh.Cells(2, 6))
	src.Copy()
	For r = 3 To 1000
		Dim dest As Range = sh.Range(sh.Cells(r, 1), sh.Cells(r, 6))
		dest.PasteSpecial()
	Next
	TextBox2.Text = (Date.Now - start).ToString()

	path = System.IO.Directory.GetCurrentDirectory() + "\" + "range2.xlsx"
	System.IO.File.Delete(path)
	xapp.Workbooks(1).SaveAs(path)
	xapp.Quit()
	MessageBox.Show("Range経由版 保存しました")
End Sub

これを実行すると、13秒になります。ええ、10倍早くなりましたね。

■pasteする時に range で範囲を指定する

実は、コピー先は3行から1000行の範囲なので、Range で指定して一気にペーストができます。
これはよく Excel でやると思うのですが、何故かプログラムコードに直すと、なかなか思い付かないようです。

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
	Dim xapp As New Microsoft.Office.Interop.Excel.Application
	Dim path As String = System.IO.Directory.GetCurrentDirectory() + "\" + "SampleTemplate.xlsx"
	xapp.Workbooks.Open(path)
	Dim sh As Worksheet = xapp.Workbooks(1).Sheets(1)

	' 先頭行はタイトル、2行目からコピーする
	Dim start As Date = Date.Now
	Dim src As Range = sh.Range(sh.Cells(2, 1), sh.Cells(2, 6))
	src.Copy()
	Dim dest As Range = sh.Range(sh.Cells(3, 1), sh.Cells(1000, 6))
	dest.PasteSpecial()
	TextBox3.Text = (Date.Now - start).ToString()

	path = System.IO.Directory.GetCurrentDirectory() + "\" + "range3.xlsx"
	System.IO.File.Delete(path)
	xapp.Workbooks(1).SaveAs(path)
	xapp.Quit()
	MessageBox.Show("Range経由版 保存しました")
End Sub

これを実行すると、0.04秒になります。ええ、1000倍ぐらい早くなります...つーか、一瞬で終わります。

■クリップボードを経由させない。

これまでは copy, paste で、クリップボードを使っていたわけですが、実は copy の引数にはコピー先の range を入れることができます。

Office TANAKA - Excel VBA講座：セルの操作[セルのコピー]
http://officetanaka.net/excel/vba/cell/cell09.htm

こうすると、クリップボードを経由しないので早くなるんですね（多分）。

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
	Dim xapp As New Microsoft.Office.Interop.Excel.Application
	Dim path As String = System.IO.Directory.GetCurrentDirectory() + "\" + "SampleTemplate.xlsx"
	xapp.Workbooks.Open(path)
	Dim sh As Worksheet = xapp.Workbooks(1).Sheets(1)

	' 先頭行はタイトル、2行目からコピーする
	Dim start As Date = Date.Now
	Dim src As Range = sh.Range(sh.Cells(2, 1), sh.Cells(2, 6))
	Dim dest As Range = sh.Range(sh.Cells(3, 1), sh.Cells(1000, 6))
	' クリップボードを媒介しない
	src.Copy(dest)
	TextBox4.Text = (Date.Now - start).ToString()

	path = System.IO.Directory.GetCurrentDirectory() + "\" + "range4.xlsx"
	System.IO.File.Delete(path)
	xapp.Workbooks(1).SaveAs(path)
	xapp.Quit()
	MessageBox.Show("Range直接版 保存しました")
End Sub

これを実行すると、0.03秒になる。非常に高速化、と言いますか既に誤差の範囲ですね。
まぁ、1000行ぐらいだと差はつかないのかもしれません。

実行結果の画像などはこちら。