[ エクセル操作フォームEx ] [ 連続してデータがある領域 ]

Delphi Programming / Object Pascal

[掲載 2018年03月13日] [更新 2018年03月14日]　エクセル操作フォームEx

エクセル操作フォームEx 参考コード類連続してデータがある領域
動作確認等	Windows 7 U64(SP1) + Delphi XE Pro + Excel 2010

ActiveCell と連続してデータがあるセル範囲の取得
連続してデータがあるセル範囲と Name プロパティの利用

ActiveCell と連続してデータがあるセル範囲の取得

ActiveCell (Range オブジェクト) には CurrentRegion というプロパティがあります．これを使用すると，アクティブセルだけではなく，表のデータ等，連続してデータが入力されているセルの範囲を取得することができます．
CurrentRegion プロパティの取得コードを実行すると， Row と Column プロパティの値fは，連続したデータの最初のセルとなります．したがって，連続したデータがあるセルの最終行や最終の列等を知ることができます．
最終行や最終列を知ることができるわけですから，例えば，最終行の次の行を合計行として，合計値を追加表示したりする用途にも利用できるでしょう．

このプロパティの値もまた Range オブジェクトですから，データが入力されているセル全体の色を変更したり，罫線を描画する等の操作にも利用できます．
通常，表にはタイトル行等がありますから，操作内容によっては考慮する必要があるかも知れません．

	図1 このような表があったとするいずれかのセルをアクティブにする
	図2 連続してデータがある領域を取得ここでは確認のために Select メソッドで領域全体を選択している Row と Column プロパティで最初のデータがあるセルを取得できる
	図3 データがないセルでの CurrentRegion プロパティの値単なるアクティブセルの取得となる

リスト1
アクティブセルと連続したデータがあるセル範囲の取得

//=============================================================================
//  連続してテータがある領域の最初のセルの行と列領域の取得
//  CurrentRegionを使用
//=============================================================================
procedure TplExcelFormEx1.Button3Click(Sender: TObject);
var
  LRange : Txlf2000Range;
  LRow   : Integer;
  LCol   : Integer;
  LRows  : Integer;
  LCols  : Integer;
  LText  : string;
begin
  LRange :=  xlfExcelApp.ActiveCell.CurrentRegion;

  //連続してデータがあるセル範囲を選択する
  LRange.Select;

  //行番号と列番号
  LRow := LRange.Row;
  LCol := LRange.Column;

  //領域の行数と列数
  LRows := LRange.Rows.Count;
  LCols := LRange.Columns.Count;

  LText := 'データの最初の行 : ' + IntToStr(LRow) + sLineBreak
         + 'データの最初の列 : ' + IntToStr(LCol) + sLineBreak
         + '連続した行数 : ' + IntToStr(LRows) + sLineBreak
         + '連続した列数 : ' + IntToStr(LCols);

  MessageBox(Handle, PChar(LText), '情報', MB_ICONINFORMATION);
end;

以下のコードは実際に合計値を表示する 1 つの例です．
上のテストと同じ値を使用して，最後の行の下に各々の列の合計値を表示します．実行前に，表内のどこかのセルをマウスクリック等でアクティブにしておきます．
Range オブジェクトの型を OleVariant 型にすることによって，AutoFill メソッドで設定する引数を省略しています．

図4
実行結果

リスト2
連続したデータ領域の範囲を利用して合計を計算して表示する例

procedure TplExcelFormEx1.Button4Click(Sender: TObject);

  //---------------------------------------------------------------------------
  //  セルの番地をR1C1形式からA1形式に変換
  //---------------------------------------------------------------------------
  function ToA1Value(ARow, ACol: Integer): string;
  begin
    Result := Chr(ACol + Ord('A') - 1) + IntToStr(ARow);
  end;
  //---------------------------------------------------------------------------

var
  LRange    : Txlf2000Range;
  LRow      : Integer;
  LCol      : Integer;
  LRows     : Integer;
  LCols     : Integer;
  LSumCell  : OleVariant;
  LFuncText : string;
begin
  LRange :=  xlfExcelApp.ActiveCell.CurrentRegion;

  //行番号と列番号
  LRow := LRange.Row;
  LCol := LRange.Column;

  //領域の行数と列数
  LRows := LRange.Rows.Count;
  LCols := LRange.Columns.Count;

  //関数の定義文のテキストを作成
  LFuncText := '=SUM(' + ToA1Value(LRow, LCol) + ':'
                       + ToA1Value(LRow + LRows - 1, LCol) + ')';

  //関数を実行するセルを指定して関数式を代入
  LSumCell := xlfExcelSheet.Cells.Item[LRow + LRows, LCol];
  LSumCell.Value := LFuncText;

  //関数の定義を他のセルにコピー
  LSumCell.AutoFill(xlfExcelSheet.Range[LSumCell, LSumCell.Offset[0, LCols - 1]]);

  //領域の背景色
  LRange.Interior.Color := clLime;
end;

連続してデータがあるセル範囲と Name プロパティの利用

Range オブジェクトには Name プロパティがあり，セルまたは前項のサンプルのような範囲に名前を付けることができます．Name プロパティを設定したセルまたは範囲に，この名前でアクセスすることができ，便利なことがあります．

以下のサンプルはこの Name プロパティを利用する例です．最初に取得した，連続したデータ領域に名前を付け，その名前の Range オブジェクトのセルの背景色を変更します．[Button1] クリックのコードで，連続したデータの領域を指定する際，具体的なセルの番地を指定していますが，このセルにもあらかじめ名前を付けておいてアクセスすることも考えられます．

	図5 EXE 実行開始時の画面
	図6 [Button1] クリックデータ領域のすぐ下の行に合計値をセットデータ領域の範囲のセルの背景色を変更
	図7 [Button2] クリック設定しておいた名前の範囲のセルを操作する例特定セルの値の変更とセルの背景色を変更

下図は操作対象の「表」の前に数行の行を挿入して [Button2] のコードを実行した結果です．このように，セルに対する操作を「表」に対して相対的に行えば表が移動しても変化しません．

図8
表の位置を移動して [Button2] クリック

リスト3
連続したデータがあるセル範囲と Name プロパティの利用

//=============================================================================
//  データが連続した領域の取得と操作の例
//=============================================================================
procedure TplExcelFormEx1.Button1Click(Sender: TObject);

  //---------------------------------------------------------------------------
  //  セルの番地をR1C1形式からA1形式に変換
  //---------------------------------------------------------------------------
  function ToA1Value(ARow, ACol: Integer): string;
  begin
    Result := Chr(ACol + Ord('A') - 1) + IntToStr(ARow);
  end;
  //---------------------------------------------------------------------------

var
  LCell     : Txlf2000Range;
  LRange    : Txlf2000Range;
  LRow      : Integer;
  LCol      : Integer;
  LRows     : Integer;
  LCols     : Integer;
  LSumCell  : OleVariant;
  LFuncText : string;
begin
  //範囲(領域-Rangeオブジェクト)に付ける名前
  //FtblNameはstring型のグローバル変数
  FtblName := 'TestTbl';

  LRange := NameToRange(FtblName);
  if LRange = nil then begin
    //指定したセルが含まれるデータ領域を取得
    LCell  := xlfExcelSheet.Range['B4', 'B4'];
    LRange := LCell.CurrentRegion;

    //領域に名前を付ける
    LRange.Name := FtblName;
  end;

  //行番号と列番号
  LRow := LRange.Row;
  LCol := LRange.Column;

  //領域の行数と列数
  LRows := LRange.Rows.Count;
  LCols := LRange.Columns.Count;

  //関数の定義文のテキストを作成
  LFuncText := '=SUM(' + ToA1Value(LRow, LCol) + ':'
                       + ToA1Value(LRow + LRows - 1, LCol) + ')';

  //関数を実行するセルを指定して関数式を代入
  LSumCell := xlfExcelSheet.Cells.Item[LRow + LRows, LCol];
  LSumCell.Value := LFuncText;

  //関数の定義を他のセルにコピー
  LSumCell.AutoFill(xlfExcelSheet.Range[LSumCell, LSumCell.Offset[0, LCols - 1]]);

  //領域の背景色
  LRange.Interior.Color := $00C2F194;
end;

//=============================================================================
//  名前でRangeオブジェクトを取得して操作
//=============================================================================
procedure TplExcelFormEx1.Button2Click(Sender: TObject);
var
  LRange : Txlf2000Range;
begin
  LRange := NameToRange(FtblName);
  if LRange <> nil then begin
    LRange.Select;

    //セルの値を変えてみる
    xlfExcelSheet.Cells.Item[LRange.Row, LRange.Column].Value := 10000;

    //領域の背景色
    LRange.Interior.Color := $00E7E7E7;
  end;
end;

//-----------------------------------------------------------------------------
//  名前からRangeオブジェクトを取得する関数
//-----------------------------------------------------------------------------
function TplExcelFormEx1.NameToRange(AName: string): Txlf2000Range;
begin
  try
    Result := xlfExcelApp.Names.Item(AName, EmptyParam, EmptyParam).RefersToRange;
  except
    Result := nil;
  end;
end;