Delphi Programming / Object Pascal
サイトのトップページへ リンクのページへ ロゴマーク
[掲載 2009年04月21日] [更新 2014年01月14日]　Delphi サンプルプログラム集
424_エクセル操作の処理速度比較
動作確認等 Windows 7 U64(SP1) + Delphi XE Pro + Excel 2010 + Excel Creator 3.0
424_ExcelSpeedTest.zip　[2,422 KB]　2014年01月14日版 (EXE 同梱)



  • 2014年01月14日
  • Excel Creator 3.0 がない環境でテストするためのユーティリティを添付
  • 変数名等，コードの整備



エクセル操作の処理速度比較


エクセルのブックに対して，大量のデータを読書きするテストプログラムです．以下の 4 つ方法でテストします．
  • [Servers] タブの TExcelApplication を使用したコード
  • CreateOLEObject で生成した Excel のオブジェクトを使用したコード
  • アドバンスソフトウェアの Excel Creator 3.0 を使用したコード
  • M & I さん作の TXBiff または TXWriteBiff を使用したコード

これらの方法によるエクセルの操作には一長一短があります．TExcelApplication と CreateOLEObject による方法は，大量のデータの読書きには向いていませんが，表示しているブックやシートに対する操作ができます．操作の結果をリアルタイムで確認できます．TExcelApplication ではコード補完機能が使えます．
本ページのサンプルは，単純にセルに値を代入しているだけですが，大量のデータは，バリアント配列を使用するとかなり高速化できます．バリアント配列の使い方は，下記のリンクの記事のサンプルを参考にしてください．

一方，Excel Creator 3.0 と TXBiff, TXWriteBiff では表示中のエクセルに対する操作はできませんが，ディスク上のエクセルファイルへの読書きは極めて高速です．データベース等のデータの読書きに向いているでしょう．Excel Creator 3.0 は書式や属性の読書きも可能で，多くの機能を持っていますが比較的高価です．導入するには，時間も経費に換算できる等の，開発への投資に対する考え方も必要なのかも知れません．



[備考 1]
Excel Creator 3.0 は，以前は単体で販売されていましたが，2014年01月12日現在，Excel Creator 2012 に含まれている製品で，xls 形式のブック専用となっています．Excel Creator 3.6 という製品も含まれていますが，こちらは Excel 2007 以降の xlsx 形式専用です．


[備考 2]
COM オブジェクトの操作にはオーバヘッドが伴います．一方，Excel Creator や TXBiff 等はファイルを直接操作します．そのため，高速な処理が可能です．






01_書込みテスト


同じ列に対して，連続して文字列を 5000 行書込みます．


図1
設計時画面
図2
実行結果例
図3
書込み用に用意したブック
  • TestBiff.xls は TWriteBiff のテスト用
  • それ以外は Test.xls に書き込む

リスト1
エクセルのブックへのデータ書込みテスト 
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Excel2000, OleServer, XlsCrt3, XWriteBiff;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    procedure Button4Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private 宣言 }
    TimeStart    : DWORD;
    TimeEnd      : DWORD;
    BookFullPath : String;
    RowCount     : Integer;
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

uses MMSystem, ComObj;

{$R *.DFM}

//=============================================================================
//  フォーム表示開始時の処理
//  測定に使用する各変数の値を設定
//=============================================================================
procedure TForm1.FormShow(Sender: TObject);
begin
  BookFullPath := ExtractFilePath(Application.ExeName) + 'Test.xls';
  RowCount     := 5000;
end;

//=============================================================================
//  Serverコンポーネント(TExcel...)
//=============================================================================
procedure TForm1.Button1Click(Sender: TObject);
var
  objExcelApp : OleVariant;
  i           : Integer;
begin
  Edit1.Clear;
  Edit1.Update;

  ExcelApplication1.Connect;
  //表示しない
  ExcelApplication1.Visible[0] := False;

  //ブックを開いてExcleWorkbook1に接続
  //Openメソッドは引数が多いのでOleVariant型で扱うものとする
  objExcelApp := ExcelApplication1.Application;
  objExcelApp.Workbooks.Open(FileName := BookFullPath);
  ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook as _workbook);

  //シートを ExcelWorksheetに接続
  ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets['Sheet1'] as _Worksheet);
  //警告を表示しない
  ExcelApplication1.DisplayAlerts[0] := False;

  TimeStart := TimeGetTime;
  Screen.Cursor := crHourGlass;
  for i := 1 to RowCount do begin
    ExcelWorksheet1. Cells.Item[i,1].value := 'TExcelApplication';
  end;
  Screen.Cursor := crDefault;
  TimeEnd := TimeGetTime;

  //順番にDisconnect.ExcelApplicationはQuitが必要
  ExcelWorkSheet1.Disconnect;
  ExcelWorkbook1.Close(True,EmptyParam, False, 0);
  ExcelWorkbook1.Disconnect;
  ExcelApplication1.Quit;
  ExcelApplication1.Disconnect;

  Edit1.Text := IntToStr(TimeEnd - TimeStart) + ' ms';
end;

//=============================================================================
//  CreateOLEObject
//=============================================================================
procedure TForm1.Button2Click(Sender: TObject);
var
  i            : Integer;
  objExcel     : OleVariant;
  objExcelApp  : OleVariant;
  objWorkBook  : OleVariant;
  objWorkSheet : OleVariant;
begin
  Edit2.Clear;
  Edit2.Update;

  //Excelをオープン
  objExcel            :=  CreateOleObject('Excel.Application');
  objExcelApp         :=  objExcel.Application;
  objExcelApp.Visible :=False;

  //XLSファイルを指定
  objExcelApp.WorkBooks.Open(BookFullPath);
  objWorkBook  := objExcelApp.ActiveWorkbook;
  objWorkSheet := objExcelApp.ActiveSheet;
  //変更の通知はしない
  objExcelApp.DisplayAlerts := False;
  //必ず保存するようにする
  objWorkBook.Saved := False;

  TimeStart := TimeGetTime;
  Screen.Cursor := crHourGlass;
  for i := 1 to RowCount do begin
    objWorkSheet.Cells[i,2].Value := 'CreateOLEObject';
  end;
  objWorkBook.Save;
  Screen.Cursor := crDefault;
  TimeEnd := TimeGetTime;

  //終了
  objWorkBook.Close;
  objWorkBook  := unAssigned;
  objWorkSheet := unAssigned;
  objExcelApp.Quit;
  objExcelApp := unAssigned;
  objExcel.Quit;

  Edit2.Text := IntToStr(TimeEnd - TimeStart) + ' ms';
end;

//=============================================================================
//  Excel Creator 3.0
//  Excel Creator 3.0は文字列をAnsiString型で扱わないと動作しないことに注意
//  xlsx形式のブックは扱えない
//=============================================================================
procedure TForm1.Button3Click(Sender: TObject);
var
  i         : Integer;
  hXlsID    : Longint;
  pFileName : AnsiString;
begin
  Edit3.Clear;
  Edit3.Update;

  pFileName := AnsiString(BookFullPath);
  if XlsOpenBook(PAnsiChar(pFileName), nil, hXlsID) then begin
    TimeStart := TimeGetTime;
    Screen.Cursor := crHourGlass;
    for i := 0 to RowCount - 1 do begin
      XlsPutStr(PAnsiChar('Excel Creator 3.0'),XlsCrt3.Pos(2, i, 2, i));
    end;
    Screen.Cursor := crDefault;
    TimeEnd := TimeGetTime;

    //Excelファイルを閉じる
    XlsCloseBook(True);

    Edit3.Text:=IntToStr(TimeEnd - TimeStart) + ' ms';
  end;
end;

//=============================================================================
//  TWritBiff
//  xlsx形式のブックは扱えない
//
//  これだけ保存先のファイル名が違うので注意
//  TWriteBiffでは，Excel2.1ワークシート形式となる
//  TExcelApplicationではこの形式は扱えない
//=============================================================================
procedure TForm1.Button4Click(Sender: TObject);
var
  i        : Integer;
  XWrite   : TWriteBiff;
  BiffFile : String;
begin
  Edit4.Clear;
  Edit4.Update;

  BiffFile := ExtractFilePath(Application.ExeName) + 'TestBiff.xls';

  XWrite := TWriteBiff.Create(BiffFile, 1, 1);
  try
    TimeStart := TimeGetTime;
    Screen.Cursor := crHourGlass;
    for i := 0 to RowCount - 1 do begin
      XWrite.WriteCell(i, 3, 0, 'TWriteBiff');
    end;
    Screen.Cursor := crDefault;
    TimeEnd := TimeGetTime;

    Edit4.Text := IntToStr(TimeEnd - TimeStart) + ' ms';
  finally
    XWrite.Free;
  end;
end;

end.



02_読込みテスト


こちらも 5000 回，同じセルからデータを文字列として読み出すテストです．


図4
設計時画面
図5
実行結果例
図6
読出し対象ブック
  • 前のテストで作成した Test.xls を添付

リスト2
エクセルのブックからのデータ読出しテスト 
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Excel2000, OleServer, XlsCrt3, XBiff;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit4: TEdit;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    procedure Button4Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private 宣言 }
    TimeStart    : DWORD;
    TimeEnd      : DWORD;
    BookFullPath : String;
    RowCount     : Integer;
    procedure OnXBiff1ReadCell(Sender: TObject; SheetNo, Row, Col,
      rgbAttr: Integer; Data: String; var Cancel: Boolean);
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

uses MMSystem, ComObj;

{$R *.DFM}

//=============================================================================
//  測定に使用する各変数の値を設定
//=============================================================================
procedure TForm1.FormShow(Sender: TObject);
begin
  BookFullPath := ExtractFilePath(Application.ExeName) + 'Test.xls';
  RowCount     := 5000;
end;

//=============================================================================
//    Serverコンポーネント(TExcel...)
//=============================================================================
procedure TForm1.Button1Click(Sender: TObject);
var
  objExcelApp : OleVariant;
  i           : Integer;
  StrText     : String;
begin
  Edit1.Clear;
  Edit1.Update;

  ExcelApplication1.Connect;
  //表示しない
  ExcelApplication1.Visible[0] := False;

  //ブックを開いてExcleWorkbook1に接続
  //Openメソッドは引数が多いのでOleVariant型で扱うものとする
  objExcelApp := ExcelApplication1.Application;
  objExcelApp.Workbooks.Open(FileName := BookFullPath);
  ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook as _workbook);

  //シートを ExcelWorksheetに接続
  ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets['Sheet1'] as _Worksheet);
  //警告を表示しない
  ExcelApplication1.DisplayAlerts[0] := False;

  TimeStart := TimeGetTime;
  Screen.Cursor := crHourGlass;
  for i := 1 to RowCount - 1 do begin
    StrText := ExcelWorksheet1.Cells.Item[2, 2].value;
  end;
  Screen.Cursor := crDefault;
  TimeEnd := TimeGetTime;

  //順番にDisconnect.ExcelApplicationはQuitが必要
  ExcelWorkSheet1.Disconnect;
  ExcelWorkbook1.Close(True, EmptyParam, False, 0);
  ExcelWorkbook1.Disconnect;
  ExcelApplication1.Quit;
  ExcelApplication1.Disconnect;

  Edit1.Text := IntToStr(TimeEnd - TimeStart) + ' ms';
end;

//=============================================================================
//  CreateOLEObject
//=============================================================================
procedure TForm1.Button2Click(Sender: TObject);
var
  i            : Integer;
  objExcel     : Variant;
  objExcelApp  : Variant;
  objWorkBook  : Variant;
  objWorkSheet : Variant;
  StrText      : String;
begin
  Edit2.Clear;
  Edit2.Update;

  //Excelをオープン
  objExcel    :=  CreateOleObject('Excel.Application');
  objExcelApp :=  objExcel.Application;

  objExcelApp.Visible :=False;
  //XLSファイルを指定
  objExcelApp.WorkBooks.Open(BookFullPath);
  objWorkBook  := objExcelApp.ActiveWorkbook;
  objWorkSheet := objExcelApp.ActiveSheet;
  //変更の通知はしない
  objExcelApp.DisplayAlerts := False;

  TimeStart     := TimeGetTime;
  Screen.Cursor := crHourGlass;
  for i := 0 to RowCount - 1 do begin
    StrText := objWorkSheet.Cells[2, 2].Value;
  end;
  objWorkBook.Save;
  Screen.Cursor := crDefault;
  TimeEnd := TimeGetTime;

  //終了
  objWorkBook.Close;
  objWorkBook  := unAssigned;
  objWorkSheet := unAssigned;
  objExcelApp.Quit;
  objExcelApp := unAssigned;
  objExcel.Quit;

  Edit2.Text := IntToStr(TimeEnd - TimeStart) + ' ms';
end;

//=============================================================================
//  Excel Creator 3.0
//  Excel Creator 3.0は文字列をAnsiString型で扱わないと動作しないことに注意
//  xlsx形式のブックは扱えない
//=============================================================================
procedure TForm1.Button3Click(Sender: TObject);
var
  hXlsID    : Longint;
  pFileName : AnsiString;
  StrChar   : array [0..MAX_PATH] of AnsiChar;
  i         : Integer;
begin
  Edit3.Clear;
  Edit3.Update;

  pFileName := AnsiString(BookFullPath);
  if XlsOpenBook(PAnsiChar(pFileName), nil, hXlsID) then begin
    TimeStart := TimeGetTime;
    Screen.Cursor := crHourGlass;
    for i := 0 to RowCount do begin
      XlsGetStr(StrChar, SizeOf(StrChar), XlsCrt3.Pos(2, 2, 2, 2));
    end;
    Screen.Cursor := crDefault;
    TimeEnd := TimeGetTime;

    //Excelファイルを閉じる
    XlsCloseBook(True);

    Edit3.Text := IntToStr(TimeEnd - TimeStart) + ' ms';
  end;
end;

//=============================================================================
//  TXBiff
//  TXBiffの場合は，全てのワークシートのデータを読み込む方式
//  データの処理はOnReadCell等で行う
//  ファイルの内容を読み出す方式なので非常に速い
//
//  TXBiffはコンポーネントパレットに登録して利用可能
//  xlsx形式のブックは扱えない
//=============================================================================
procedure TForm1.Button4Click(Sender: TObject);
var
  XBiff : TXBiff;
begin
  Edit4.Clear;
  Edit4.Update;

  XBiff := TXBiff.Create(Self);
  XBiff.OnReadCell := OnXBiff1ReadCell;
  try
    TimeStart := TimeGetTime;
    Screen.Cursor := crHourGlass;

    //データを読み込む
    XBiff.LoadFile(BookFullPath);

    Screen.Cursor := crDefault;
    TimeEnd:=TimeGetTime;

    Edit4.Text := IntToStr(TimeEnd - TimeStart) + ' ms';
  finally
    XBiff.Free;
  end;
end;

//=============================================================================
//  TXBiffのOnReadCellイベント
//  ここで読み出したデータの処理を行う
//
//  引数の値を利用して，特定のシートやセルの値だけの読出しが可能
//=============================================================================
procedure TForm1.OnXBiff1ReadCell(Sender: TObject; SheetNo, Row, Col,
  rgbAttr: Integer; Data: String; var Cancel: Boolean);
var
  StrText : String;
begin
  StrText := Data;
end;

end.



03_Excel Creator 3.0 がない環境での EXE の実行


上の 2 つのテストの内，Excel Creator 3.0 を使用する部分は，Excel Creator 3.0 がインストールされていないと実行できません．
添付の [DLL_RegistUnRegist] にあるプログラムは，Excel Creator 3.0 がインストールされていな環境でもテストを可能にするものです．起動して [登録] をクリックすると，XlsCrt2.DLL をレジストリに登録して，テストコードの実行を可能にします．[登録解除] で登録を解除します．

登録した XslCrt2.DLL にパスを通すのは面倒なので，各サンプルのフォルダにも XslCtr2.DLL を置いています．

レジストリへの登録には，管理者権限が必要です．そこで，添付のプログラムは，実行すると，管理者への昇格ダイアログ (ユーザアカウント制御ダイアログ) を表示するようにしています．



図7
DLL_RegistUnRegist.exe を実行
図8
起動時画面