[ エクセル操作フォームEx ] [ 複数シートとアクティブでないシートに対する操作 ]

Delphi Programming / Object Pascal

[掲載 2009年05月26日] [更新 2020年10月19日]　エクセル操作フォームEx

エクセル操作フォームEx 参考コード類複数シートとアクティブでないシートに対する操作
動作確認等	Windows 7 U64(SP1) + Delphi XE Pro + Excel 2010

Range オブジェクトで操作
複数のシートオブジェクトを生成して操作
複数のシートをジェネリックスの TList<T> で管理

Range オブジェクトで操作

このエクセル操作フォームEx では，一度に扱うブック，シートオブジェクトは 1 つだけです．
複数のシート間でデータのやり取りを行う場合，次のコードのように Range オブジェクトに対して操作することができます．

	図1 起動時のブック [Sheet1] にデータがあるボタンをクリックすると...
	図2 実行結果 [Sheet1] 内のセルに複写
	図3 実行結果同時に [Sheet2] のセルにも複写される

リスト1
別シートのセルに値をコピー

procedure TplExcelFormEx1.Button1Click(Sender: TObject);
var
  RangeA : Txlf2000Range;
  RangeB : Txlf2000Range;
begin
  xlfActiveSheetName := 'Sheet1';

  //コピー元のセルRangeオブジェクト
  RangeA := xlfExcelSheet.Range['B2', 'B5'];

  //同じシート内に貼付け
  RangeA.Copy(xlfExcelSheet.Range['D2','D5']);

  //Sheet2のRangeオブジェクトに対して操作する
  if xlfActiveSheetName<>'Sheet2' then begin
    RangeB := (xlfExcelBook.Worksheets['Sheet2'] as _WorkSheet).Range['B2','B5'];
    RangeA.Copy(RangeB);
  end;
end;

複数のシートオブジェクトを生成して操作

[Sheet1] のセルに対する計算結果を [Sheet2] と [Sheet3] のセルに書き込む例です．
単純なセルに対する操作であれば，Range オブジェクトが使用できますが，少し複雑な操作を行いたい場合は，いつでもシートオブジェクトを操作可能という意味では，複数のシートオブジェクトを生成して使用すると確実です．ただし，この程度の操作であれば，実際にはシートオブジェクトを生成するまでもありません．

	図4 ボタンをクリックすると...
	図5 [Sheet2] に計算結果を書き込む

リスト2
複数のシートオブジェクトを生成して，別シートのセルに計算結果を書き込む

unit ExcelTestForm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Buttons, StdCtrls, plExcelEx, ToolWin, ComCtrls, ExtCtrls, Excel2000;

type
  TplExcelFormEx1 = class(TplExcelFormEx)
    Button1: TButton;
    procedure FormShow(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure Button1Click(Sender: TObject);
  private
    { Private 宣言 }
    ExcelSheet1: TExcelWorkSheet;
    ExcelSheet2: TExcelWorkSheet;
    ExcelSheet3: TExcelWorkSheet;
  public
    { Public 宣言 }
  end;

var
  plExcelFormEx1: TplExcelFormEx1;

implementation

{$R *.dfm}

//=============================================================================
//  フォーム表示開始時の処理
//
//  シートは操作対象分のTExcelWorkSheetオブジェクトのインスタンスを作成する
//
//  Sheet1だけはエクセル操作フォームのオブジェクトを使用してもよいが，オブジ
//  ェクトの名前を合わせるために3つとも生成して使用している
//=============================================================================
procedure TplExcelFormEx1.FormShow(Sender: TObject);
begin
  //シートオブジェクトのCreate
  ExcelSheet1 := TExcelWorkSheet.Create(Self);
  ExcelSheet2 := TExcelWorkSheet.Create(Self);
  ExcelSheet3 := TExcelWorkSheet.Create(Self);

  //フォーム表示開始時に開くブック名
  xlfBookFullPath := 'テスト.xls';

  //テスト動作確認のためにグリッド線等を表示
  xlfDisplayHeadings     := True;
  xlfDisplayGridLines    := True;
  xlfDisplayWorkBookTabs := True;

  //元のOnShowを実行
  inherited;

  //各シートの接続処理
  ExcelSheet1.ConnectTo (xlfExcelBook.Worksheets['Sheet1'] as _workSheet);
  ExcelSheet2.ConnectTo (xlfExcelBook.Worksheets['Sheet2'] as _workSheet);
  ExcelSheet3.ConnectTo (xlfExcelBook.Worksheets['Sheet3'] as _workSheet);
end;

//=============================================================================
//  フォームが閉じる時の処理
//=============================================================================
procedure TplExcelFormEx1.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
begin
  xlfBookSaved := True;

  //各シートのDisconnect処理
  ExcelSheet1.Disconnect;
  ExcelSheet2.Disconnect;
  ExcelSheet3.Disconnect;

  ExcelSheet1.Free;
  ExcelSheet2.Free;
  ExcelSheet3.Free;

  //元のCloseQueryを実行
  inherited;
end;

//=============================================================================
//  シート(オブジェクト)に対する操作例
//
//  [Sheet1]のB2～C5に値がセットされていると仮定
//
//  [Sheet1]のセル値を2倍して[Sheet2]の同じセル位置に書込む
//  [Sheet1]のセル値を3倍して[Sheet3]の同じセル位置に書込む
//
//  iRowが行番号．iColが列番号
//=============================================================================
procedure TplExcelFormEx1.Button1Click(Sender: TObject);
var
  iRow : Integer;
  iCol : Integer;
  V    : OleVariant;
begin
  for iRow := 2 to 5 do begin
    for iCol := 2 to 3 do begin
      V := ExcelSheet1.Cells.Item[IRow,ICol].Value;

      ExcelSheet2.Cells.Item[iRow,iCol].Value := 2 * V;
      ExcelSheet3.Cells.Item[iRow,iCol].Value := 3 * V;
    end;
  end;
end;

end.

複数のシートをジェネリックスの TList<T> で管理

複数のシートを扱う場合，ジェネリックスの TList<T> でシートオブジェクトを管理した方が便利かも知れません．
以下のサンプルでは，起動時のブックにある全てのシートのオブジェクトを生成します．このようにしておくと，どのシートもいつでも操作可能となります．

参考リンク
[161_複数の同じコンポーネントを実行時に生成]

リスト3
複数のシートオブジェクトをジェネリックスの TList<T> で管理

unit ExcelTestForm;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Buttons, StdCtrls, plExcelEx, ToolWin, ComCtrls, ExtCtrls, Excel2000,
   Generics.Collections;

type
  TplExcelFormEx1 = class(TplExcelFormEx)
    Button1: TButton;
    procedure FormShow(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure Button1Click(Sender: TObject);
  private
    { Private 宣言 }
  public
    { Public 宣言 }
    FSheetList : TList<TExcelWorkSheet>;
    function IndexOfSheetName(ASheetName: string;
      ASheetList:  TList<TExcelWorkSheet>): Integer;
  end;

var
  plExcelFormEx1: TplExcelFormEx1;

implementation

{$R *.dfm}

//=============================================================================
//  フォーム表示開始時の処理
//
//  シートは操作対象分の TExcelWorkSheet オブジェクトのインスタンスを作成する
//  TList でシートオブジェクトを管理する例
//  TList の使用には uses に Generics.Collections が必要
//=============================================================================
procedure TplExcelFormEx1.FormShow(Sender: TObject);
var
  LSheet : TExcelWorksheet;
  LIndex : Integer;
begin
  // フォーム表示開始時に開くブック名
  xlfBookFullPath := 'テスト.xls';

  // テスト動作確認のためにグリッド線等を表示
  xlfDisplayHeadings     := True;
  xlfDisplayGridLines    := True;
  xlfDisplayWorkBookTabs := True;

  // 元の OnShow を実行
  inherited;

  // シートオブジェクトを格納するリストを作成
  FSheetList := TList<TExcelWorksheet>.Create;

  // 表示中のシート全てのシートオブジェクトを生成してリストに追加
  for LIndex := 1 to xlfExcelBook.Worksheets.Count do begin
    LSheet := TExcelWorksheet.Create(Self);
    LSheet.ConnectTo (xlfExcelBook.Worksheets[LIndex] as _workSheet);
    FSheetList.Add(LSheet);
  end;
end;

//=============================================================================
//  フォームが閉じる時の処理
//=============================================================================
procedure TplExcelFormEx1.FormCloseQuery(Sender: TObject;
  var CanClose: Boolean);
var
  LIndex : Integer;
begin
  xlfBookSaved := True;

  // 各シートの Disconnect と解放処理
  for LIndex := FSheetList.Count - 1 downto 0 do begin
    FSheetList[LIndex].Disconnect;
    FSheetList[LIndex].Free;
  end;
  FreeAndNil(FSheetList);

  // 元の CloseQuery を実行
  // ブックやエクセルのオブジェクトの Disconnect 等の処理
  inherited;
end;

//=============================================================================
//  シートオブジェクトに対する操作例
//
//  1 番目のシートの B2～C5 に値がセットされていると仮定
//
//  1 番目のシートのセル値を 2 倍して 2 番目のシートの同じセル位置に書込む
//  1 番目のシートのセル値を 3 倍して 3 番目のシートの同じセル位置に書込む
//
//  iRow が行番号．iCol が列番号
//  シート名でアクセスしたい場合は，ループ処理でシートのインデックスを検索する
//=============================================================================
procedure TplExcelFormEx1.Button1Click(Sender: TObject);
var
  iRow : Integer;
  iCol  : Integer;
  V     : OleVariant;
  LIdx : Integer;
begin
  for iRow := 2 to 5 do begin
    for iCol := 2 to 3 do begin
      V := FSheetList[0].Cells.Item[IRow,ICol].Value;

      FSheetList[1].Cells.Item[iRow,iCol].Value := 2 * V;
      FSheetList[2].Cells.Item[iRow,iCol].Value := 3 * V;
    end;
  end;

  // シート名が Sheet2 のシートをアクティブなシートにする
  LIdx := IndexOfSheetName('Sheet2', FSheetList);
  FSheetList[LIdx].Activate;
end;

//-----------------------------------------------------------------------------
//  シート名に該当するコンポーネントリストのインデックスを取得する関数
//-----------------------------------------------------------------------------
function TplExcelFormEx1.IndexOfSheetName(ASheetName: string;
  ASheetList: TList<TExcelWorkSheet>): Integer;
var
  Lindex : Integer;
begin
  Result := -1;
  for Lindex := 0 to ASheetList.Count - 1 do begin
    if ASheetList[Lindex].Name = ASheetName then begin
      Result := Lindex;
      Break;
    end;
  end;
end;

end.

シートを追加した時はリストにシートのオブジェクトを追加します．シートを削除する時は，シートを削除する前に，該当するシートオブジェクトのアイテムを削除しておきます．シートを削除すると，そのシートのオブジェクトにアクセスできなくなり，例外が発生します．この例外は捕捉できません．

エクセルには，シートを追加した時のイベントはありますが，シート削除のイベントはありません．シートのタブのメニューからシートを削除した場合は対応できません．そのため，エクセル操作フォームEx では，エンドユーザによるシートの削除を回避するために，デフォルトではシートのタブを非表示にしています．

図6
シートを追加 (挿入)

[Sheet1] タブのポップアップメニューで [挿入] を選択して操作

リスト4
シート追加 (挿入) のイベント処理の例

//-----------------------------------------------------------------------------
//  xlfExcelBook (TExcelWorkbook) の OnNewSheet イベント処理
//  ブックにシートを追加すると発生するイベント
//
//  イベントの宣言文と設定は以下
//  procedure OnNewSheetEvent(Sender: TObject; const Sh: IDispatch);
//  xlfExcelBook.OnNewSheet := OnNewSheetEvent;
//-----------------------------------------------------------------------------
procedure TplExcelFormEx1.OnNewSheetEvent(Sender: TObject; const Sh: IDispatch);
var
  LSheet : TExcelWorksheet;
  LName  : string;
begin
  // 追加されたシートオブジェクトをシートのリストに追加
  LSheet := TExcelWorksheet.Create(Self);
  LSheet.ConnectTo(sh as _workSheet);
  FSheetList.Add(LSheet);

  // 追加されたシートのシート名を表示
  LName := FSheetList[FSheetList.Count - 1].Name;
  MessageBox(Handle, PChar(LName), '追加シート名', MB_ICONINFORMATION);
end;

[備考]
Excel 2013 には，シート削除時のイベント Beforedelete が追加されています．