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

投稿日: 2011/09/04 作成者: masuda

実験メモ。

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

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 でやると思うのですが、何故かプログラムコードに直すと、なかなか思い付かないようです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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行ぐらいだと差はつかないのかもしれません。

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

カテゴリー: 開発, VB パーマリンク

Excel で 書式をクリップボード経由にすると遅くなる への2件のフィードバック

  1. あひる のコメント:
    2017/05/23 1:44 pm

    「クリップボードを経由させない。 」においては、セル内の値も一緒にコピーされてしまいますね。

    なので、他のものとは、動きが変わりませんか？

    • masuda のコメント:
      2017/05/25 10:46 am

      随分前のことなので覚えていないのですが、「クリップボードを経由しないと早くなる」のが目的なので、動きが変わっても特に問題はないです。

コメントは停止中です。