DelphiでExcelブック内のシート一覧を取得し表示する(dbGo経由)

前のブログで、ExcelのTypeライブラリーを使ってシート一覧を取得しましたが
ついでといっては、なんですが、dbGo(Ado)を使って、シート一覧を取得してみます。

TAdoQueryを使ってSQL文で、テーブル一覧を取得できないか、ちょっと調べたましたが
無理そうだったので、ここでは、

TADOConnection.OpenSchema (
 　　　　　　　 const Schema: TSchemaInfo;  
　　　　　　　　const Restrictions: OleVariant;
　　　　　　　  const SchemaID: OleVariant;
                 DataSet: TADODataSet);

メソッドを使ってテーブルを取得します。

方法は、簡単で、

OpenSchema関数のパラメータ

　　　　　Schemaに　TSchemaInfo.siTables
　　　　　DataSetに　スキーマ取得結果の書き込み先のレコードセットを指定します。

　　　　　 
　　　　　また、今回は、RestrictionsとSchemaIDは使用しませんのでEmptyParamを指定します。


さて、やってみます。

フォームにTADOConnectionを配置し、ConnectionStringの

　　　ProviderにMicrosoft.ACE.OLEDB.12.0
　　　Data SourceにExcelのワークブックのパス
　　　Extended PropertiesにExcel 12.0(Excel2010の場合)

を指定します。

（ConnectionStringについては、http://connectionstrings.com/ が参考になります。)


次に結果格納先としての TADODataSetコンポーネントを配置し、Connectionプロパティに
上記の TADOConnectionコンポーネントを指定します。

あとは、通常の操作で、DataSource,DbGridを配置し、それぞれ接続します。

あとはボタンなどを配置しそのイベントハンドラに

  ADOConnection1.Connected := true;
  ADOConnection1.OpenSchema(siTables, EmptyParam, EmptyParam,ADODataSet1);
 
のようなコードを書きます。

で実行すれば、

のように結果が得られます（右側)

 
 
なお、torry's Delphiのページにもうちょっと詳しいサンプルがあります。http://www.swissdelphicenter.ch/torry/showcode.php?id=1433

また、AdoでのExcelのSchema,については、MSのHELP
http://support.microsoft.com/kb/257819/ja


が参考になります。

DelphiでExcelブック内のシート一覧を取得し表示する

先日、DelphiでExcelのWorkSheetを列挙しながらSheetを編集する処理を作成したとき、
思いもよらずはまったので、自分メモとして保存。

DelphiからExcelを操作する方法としては、

1. Excelのタイプライブラリーをインポート
2. dbGoを使用する
3. サードパーティのコンポーネントを使用する
4. ・・・

などの方法があげらるが、

今回は、1.タイプタイプライブラリーをインポートしてExcelを操作しシート名を一覧表示する
処理をつかった。

以下、ソース

view plainprint?

1. unit Unit1;  
2.   
3. interface  
4.   
5. uses  
6.   Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,  
7.   Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls;  
8.   
9. type  
10.   TForm1 = class(TForm)  
11.     Button1: TButton;  
12.     ListBox1: TListBox;  
13.     procedure Button1Click(Sender: TObject);  
14.   private  
15.     { Private 宣言 }  
16.   public  
17.     { Public 宣言 }  
18.   end;  
19.   
20. var  
21.   Form1: TForm1;  
22.   
23. implementation  
24.   
25. {$R *.dfm}  
26.   
27. uses Excel_TLB,System.Win.ComObj;  
28. const  
29.   LCID = LOCALE_SYSTEM_DEFAULT;  
30.   
31.   
32. procedure TForm1.Button1Click(Sender: TObject);  
33. Var  
34.   ExcelApp : Excel_TLB.ExcelApplication;  
35.   ExcelBook : Excel_TLB.ExcelWorkbook;  
36.   ExcelSheet : Excel_TLB.ExcelWorksheet;  
37.   BookPath : String;  
38.   i : integer;  
39. begin  
40.   ListBox1.Clear;  
41.   ExcelApp := CreateComObject(CLASS_ExcelApplication) as ExcelApplication;  
42.   ExcelApp.DisplayAlerts[LCID] := false;  
43.   
44.   BookPath := IncludeTrailingPathDelimiter(ExtractFileDir(Application.ExeName)) + 'Test.xlsx';  
45.   
46.   ExcelBook := ExcelApp.Workbooks.Add(BookPath, LCID);  
47.   
48.   (* このように書きたいが 
49.   　 'GetEnumerator' のメンバが含まれていないかアクセスできないため） 
50.       通常では使用不可　　　 
51.   for  ExcelSheet in  ExcelBook.Worksheets do 
52.   begin 
53.      ListBox1.Items.Add(ExcelSheet.Name); 
54.   end; 
55.   *)  
56.   
57.   
58.   // Excelのコレクションは1基数なので1からカウントを始める。  
59.   for i := 1 to ExcelBook.Worksheets.Count do  
60.   begin  
61.     ExcelSheet := ExcelBook.Worksheets.Item[i] As  Excel_TLB.ExcelWorksheet;  
62.     ListBox1.Items.Add(ExcelSheet.Name);  
63.   end;  
64.   
65.   ExcelSheet := nil;  
66.   ExcelBook.Close(false,BookPath,false,LCID);  
67.   ExcelBook := nil;  
68.   
69.   if Assigned(ExcelApp) then  
70.   begin  
71.     ExcelApp.Quit;  
72.     ExcelApp := nil;  
73.   end;  
74.   
75.   
76. end;  
77.   
78. end.   

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls;

type
  TForm1 = class(TForm)
    Button1: TButton;
    ListBox1: TListBox;
    procedure Button1Click(Sender: TObject);
  private
    { Private 宣言 }
  public
    { Public 宣言 }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses Excel_TLB,System.Win.ComObj;
const
  LCID = LOCALE_SYSTEM_DEFAULT;


procedure TForm1.Button1Click(Sender: TObject);
Var
  ExcelApp : Excel_TLB.ExcelApplication;
  ExcelBook : Excel_TLB.ExcelWorkbook;
  ExcelSheet : Excel_TLB.ExcelWorksheet;
  BookPath : String;
  i : integer;
begin
  ListBox1.Clear;
  ExcelApp := CreateComObject(CLASS_ExcelApplication) as ExcelApplication;
  ExcelApp.DisplayAlerts[LCID] := false;

  BookPath := IncludeTrailingPathDelimiter(ExtractFileDir(Application.ExeName)) + 'Test.xlsx';

  ExcelBook := ExcelApp.Workbooks.Add(BookPath, LCID);

  (* このように書きたいが
  　 'GetEnumerator' のメンバが含まれていないかアクセスできないため）
      通常では使用不可　　　
  for  ExcelSheet in  ExcelBook.Worksheets do
  begin
     ListBox1.Items.Add(ExcelSheet.Name);
  end;
  *)


  // Excelのコレクションは1基数なので1からカウントを始める。
  for i := 1 to ExcelBook.Worksheets.Count do
  begin
    ExcelSheet := ExcelBook.Worksheets.Item[i] As  Excel_TLB.ExcelWorksheet;
    ListBox1.Items.Add(ExcelSheet.Name);
  end;

  ExcelSheet := nil;
  ExcelBook.Close(false,BookPath,false,LCID);
  ExcelBook := nil;

  if Assigned(ExcelApp) then
  begin
    ExcelApp.Quit;
    ExcelApp := nil;
  end;


end;

end. 

for　～　in doの構文が使えると、基数のこと意識しなくても良いが、コンパイルすると

 E2431 for-in ステートメントはコレクション型 'Sheets' で動作できません（'Sheets' に 'GetEnumerator' のメンバが含まれていないかアクセスできないため）

のメッセージが、出てEXEが作れないため、従来のfor文で列挙している。

  Excelのコレクションが1基数なので、for 文は、1からシート数まででにしていのが
 ポイントです。（ポイントというもののものではありませんが・・・）

 まあ、Excelに限らず、Win32版のVisual Basic(VB6,VB5)とか、VBAのコレクションInterface
 は基本1基数なのですが・・・

 下図のようなブックに対して

 上のような処理を実行すると

 のような結果がえられます。


以下は、余談ですが、

自分 、Excelのコレクションが1基数だということをすっかり忘れていて、結果、午前中つぶしちゃいました。