Migaro. 技術Tips

                       

ミガロ.　製品の技術情報
IBMiの活用に役立つ情報を掲載！


Delphi OLEのExcel操作術　第７回：その他・発展編

2022/11/22

DelphiアプリケーションでExcelを操作する便利な方法を
全７回に分けてご紹介してまいります。

今回は第１～６回とは少し異なる操作や特別コンテンツでお送りします。

　

※第１～６回でも使用した以下の変数は、今回も引き続き使用します。

uses節には「System.Win.ComObj」を追加しておいて下さい。
（Delphi XE以前では「ComObj」）

var // 「OLEのExcel操作術」各Tips共通で使用する変数宣言
  MsExcel: OleVariant;       // ExcelのOLE変数
  MsApplication: OleVariant; // アプリケーションのOLE変数
  WBook: OleVariant;         // ブックのOLE変数
  WSheet: OleVariant;        // シートのOLE変数

また、記事中の各コードは対象となるExcelアプリケーションなどの各変数が
設定されていることが前提です。
（プログラムではExcelファイルが開かれるコードを実行した状態になります）
手っ取り早く検証のみ行いたい場合は、上記変数定義ののち以下を記述します。

  // Excel起動
  MsExcel := CreateOleObject('Excel.Application');
  MsApplication := MsExcel.Application;
  MsApplication.Visible := True;

  // 新規ファイルを作成
  WBook  := MsApplication.WorkBooks.Add ;
  WSheet := WBook.ActiveSheet;

　

また、本文中で登場するExcel側で決められた定数は
constで宣言しておくとわかりやすくなります。

（※uses節に「Excel2000」を追加しておくと本記事で使用する名前の定数の大半が
　　含まれているため、const宣言不要になります。
　　またExcel2000ではなく「Office2000」または「OfficeXP」に含まれる定数もあります。）

　

セルの検索

本項ではセルの「検索」を行う方法についてご説明します。

検索する範囲に対してFindを実行すると、見つかった最初のセルをオブジェクトとして返します。
この例では、’株式会社ＸＸＸ’という文字列を検索しています。

変数FndCellはセルを指しますので、
Rowなどのセルのプロパティ値以外にもメソッドを使用できます。

var
  FndCell : Variant;
begin
  FndCell := WSheet.Cells.Find('株式会社ＸＸＸ');
  ShowMessage(IntToStr(FndCell.Row)+'行目に見つかりました');

また、変数を介さずにFindに続けてメソッドやプロパティを記述することも可能です。

  WSheet.Cells.Find('株式会社ＸＸＸ').Select;

また特定列のみ検索する場合、列番号を指定することで行えます。
次の例では2列目、つまりB列を検索します。

  WSheet.Columns[2].Find('株式会社ＸＸＸ').Select;

列番号でなく、アルファベットで列を指定したい場合には、
指定した範囲を含む列全体を取得できる EntireColumn を使用するとよいでしょう。

  // B1を含む列全体が対象
  WSheet.Range['B1'].EntireColumn.Find('株式会社ＸＸＸ').Select;

　

なおセルが見つからなかった場合に、
そのプロパティ値の取得やメソッドを実行すると、エラーとなります。

それを避けるには、Findでオブジェクトが取得できたかを判断する必要がありますので、
VarIsNull関数を使用します。
VarIsNull関数は、Variant型の変数値が Null かどうかがわかります。

  FndCell := WSheet.Cells.Find('株式会社ＸＸＸ');
  if VarIsNull(FndCell) = False then
    ShowMessage('見つかりません')
  else
    ShowMessage(IntToStr(FndCell.Row));   // 行番号を取得

　

ここまでご紹介した検索の方法は、複数のセルに一致する文字列がある場合でも
最初に見つかったセルしか返さないものでした。

次は複数ある場合を考慮して、すべてのセルを検索する方法をご紹介します。

Delphiでファイルのレコードを読み込むイメージで、
Findで先頭のセルを見つけ、ループでFindNextで次のセルへ移動する処理を行います。

FindNextの引数は直前に見つかったセルのオブジェクトになります。
またFindNextもFindと同様に見つけたセルのオブジェクトを返し、
見つからなければヌルを返しますので、ヌルでない間（while）は処理を繰り返します。

注意する点としましては、
FindNextは最初にFindで見つけたセルかどうかを判断できず、ループ処理内で使用すると
最後のセルが見つかってもまた最初に戻ってしまい、永久ループとなります。

それを避けるために、最初のセルのアドレスを変数に持たせ、
最初のセルに戻ってきた場合には、処理を抜けるようにする必要があります。

次の例では、’東京都’の文字列をExcel全体で検索し、
見つかればTMemoに’Col=列番号,Row=行番号’の形で追加しています。

var
  FndCell : Variant;
  CellAddr: Variant;
begin
  // 最初のセルを取得
  FndCell := WSheet.Cells.Find('東京都');
  // セルのアドレスを変数に保存
  if VarIsNull(FndCell) = False then
    CellAddr := FndCell.Address;

  while not (VarIsNull(FndCell)) do
  begin
    Memo1.Lines.Add('Col='+IntToStr(FndCell.Column) +',Row='+IntToStr(FndCell.Row));
    FndCell := WSheet.Cells.FindNext(FndCell);
    if  CellAddr = FndCell.Address  then
      Break;
  end;

　

Excel 一括取込関数の作成

第１～７回のロジックをフル活用すれば、
例えば以下のようなExcelの指定したブック・シートの値を
一括で配列に取り込むような関数・手続きが作成可能です。

{*******************************************************************************
 目的: Excel取込の共通処理
 引数: AFile  - ブックのパス
       ASheet - シート名
       AXF    - 列番号の開始位置（1始まり、0の場合は1から）
       AXT    - 列番号の終了位置（0の場合は最終列）
       AYF    - 行番号の開始位置（1始まり、0の場合は1から）
       AYT    - 行番号の終了位置（0の場合は最終行）
 戻値:
*******************************************************************************}
procedure TForm1.ExcelCapture(AFile, ASheet: String; AXF, AXT, AYF, AYT: Integer);
var
  ix, iy: Integer;           // for文用

  // 「OLEのExcel操作術」各Tips共通で使用する変数宣言
  MsExcel: OleVariant;       // ExcelのOLE変数
  MsApplication: OleVariant; // アプリケーションのOLE変数
  WBook: OleVariant;         // ブックのOLE変数
  WSheet: OleVariant;        // シートのOLE変数

//  // セル値を保管する変数（実際には当手続きの外で定義する）
//  sValue: array of array of String;
begin

  // エラーチェック
  if not(FileExists(AFile)) then
  begin
    ShowMessage('指定されたファイルが存在しません。');
    Abort;
  end;
  if (AXF < 0) or (AXT < 0) or (AYF < 0) or (AYT < 0) or // 0未満は不可
     ((AXT > 0) and (AXF > AXT)) or                      // From＞To 列
     ((AYT > 0) and (AYF > AYT)) then                    // From＞To 行
  begin
    ShowMessage('列番号または行番号の指定が正しくありません。');
    Abort;
  end;

  // Excel起動
  MsExcel := CreateOleObject('Excel.Application');
  MsApplication := MsExcel.Application;
  MsApplication.Visible := True;

  try
    // ファイルを開いてシート指定
    WBook  := MsApplication.WorkBooks.Open(AFile);
    WSheet := WBook.Sheets[ASheet]; // 注意 指定名のシートが無いとエラー
    WSheet.Select;

    // 最終行列の取得
    if (AXF = 0) then  AXF := 1;
    if (AXT = 0) then  AXT := WSheet.UsedRange.Columns.Count;
    if (AYF = 0) then  AYF := 1;
    if (AYT = 0) then  AYT := WSheet.UsedRange.Rows.Count;

    // 配列の要素設定
    SetLength(sValue,    AYT + 1);
    SetLength(sValue[0], AXT + 1);

    // セル値を順次取得して配列に保持
    for iy := AYF to AYT do
    begin
      SetLength(sValue[iy], AXT + 1);
      for ix := AXF to AXT do
      begin
        sValue[iy][ix] := WSheet.Cells[iy, ix].Value;
      end;
    end;

  finally
    // 保存の確認を行わない
    WBook.Saved := True;
    // Excel終了
    MsApplication.WorkBooks.Close;
    MsExcel.Quit;
  end;
end;

上記のソース内で「// 注意 指定名のシートが無いとエラー」と
記載した箇所についても、これまでのTipsを復習して頂ければ
エラー回避ロジックを記述できるかと思います。

=======================================
【免責事項】
本ページに掲載しているソースコードは情報提供の為のサンプルプログラムとなります。
お客様作成アプリケーション内で自由にご利用いただけます。
ただし、これらのソースコードやサンプルプログラムを使用したことによって生じた、
いかなる障害・損失に関しても一切の責を負いかねますので、ご了承下さい。
=======================================

　

Excel操作のベースとなるVBAについて

長らく連載していましたOLEのExcel操作術はこれが最終章となります。
最後にExcel操作のベースとなるVBAについてご説明します。

DelphiからのExcel操作はVBA（Visual Basic for Applications）が基本になります。
VBAをDelphiの文法に合わせて修正する必要がありますが、
何か操作を行いたいときには、ヘルプや書籍、Web上等で調べて利用することができます。

Delphiで修正する点としましては、次のようなものがあります。

  • VBAにはピリオド等文末を示す記号がありませんが、Delphiでは通常のように ; を付加します。
  • VBAでは代入時に = を使用しますが、Delphiでは通常のように := になります。
  • 引用文字列はVBAでは “と“ で囲まれていますが、Delphiでは通常の文字列と同様に ‘と‘ で囲みます。
  • VBAで使用している括弧 () は、基本的にはDelphiでは [] になります。
　（例）
    Worksheets("Sheet1").Select
  ⇒Worksheets['Sheet1'].Select;

    Worksheets("Sheet1").Range("A1").Font.Size = 14
  ⇒Worksheets['Sheet1'].Range['A1'].Font.Size := 14;

　

またExcelには「マクロの記録」という機能があり、
Excelに対する操作を自動でマクロとして作成しますので、その操作のVBAが簡単にわかります。
あとはそれをDelphiに合わせてコードを修正すれば、調べる手間なく利用できます。
「マクロの記録」の操作については、Excelのヘルプ等をご参照下さい。

なお、Delphiからマクロを登録して実行することも可能です。
この場合、Delphiの文法に合わせた修正を行う必要がありません。
次のようなマクロをDelphiで登録して実行し、最後に削除するコードを示します。
InsertLinesで追加する行番号とその行のコードを設定します。
（※xlsやxlsmなど、マクロが使用可能なファイル形式の場合のみ）

【実行したいマクロ：DateAdd関数で現在日付（DATE）の1年後の日付を取得し、MsgBoxで表示】
  Sub Macro1()
    MsgBox (DateAdd("yyyy", 1, Date))
  End Sub

【Delphi側】
  var
    CM : Variant;
    CNT: integer;
  begin

    // モジュールの作成
    CM := WBook.VBProject.VBComponents.Item(1).CodeModule;

    // マクロの記述
    CM.InsertLines(1,'Sub Macro1()');
    CM.InsertLines(2,'  MsgBox (DateAdd("yyyy", 1, Date))');
    CM.InsertLines(3,'End Sub');

    // マクロ実行
    MsApplication.Run('ThisWorkBook.Macro1');

    // マクロ削除
    CNT := WBook.VBProject.VBComponents.Item(1).CodeModule.CountOfLines;
    WBook.VBProject.VBComponents.Item(1).CodeModule.DeleteLines(1, CNT);

※『プログラミングによる Visual Basic プロジェクトへのアクセスは信頼性に欠けます』というエラーが表示された場合は、Microsoft社のこちらのページを参考にExcelのセキュリティ設定をご確認ください。

※『マクロ ‘～～～’ を実行できません。このブックでマクロが使用できないか、またはすべてのマクロが無効になっている可能性があります。』というエラーが表示された場合は、ブック名やマクロ名が誤っていないかご確認ください。また「ThisWorkBook.Macro1」部分を「ブック名.xlsm!Macro1」に変更してお試しください。

　

　

　

全７回にわたってよく使用される様々な機能についてご説明してきましたが、
いかがだったでしょうか。

お伝えできていないものもたくさんありますが、
今回ご紹介したマクロを合わせると、ほとんどの操作はDelphiならびにDelphi/400から実行できます。

これからも、Delhi/400をどうぞご活用下さい。

　

（本記事は、以下の資料をもとに最新情報を含めた再構成をしております。）

  • ミガロ.情報マガジン「MIGARO News!!」
    • Vol.140 2012年7月号より（セルの検索）
    • Vol.141 2012年8月号より（セルの次を検索）
    • Vol.142 2012年9月号より（OLEとVBAについて）
    • 新規書き下ろし（Excel 取込関数の作成）

Migaro.技術Tips一覧へ

 

株式会社ミガロ．

© 2024 MIGARO CO.,LTD.