Excel と Excel Services を使ったデータベース連携

  1. SharePoint におけるさまざまな活用場面
  2. 例1 : InfoPath と Forms Services を使ったデータベース連携
  3. 例2 : Excel と Excel Services を使ったデータベース連携 
  4. こんにちは。 

    今回は Excel Services を使って、前回登録したデータベース上のデータを閲覧する機能を作成してみましょう。

    勉強会でも話が出たのですが、SharePoint 上でデータベースのデータ表示をおこなう方法は、実は、非常にさまざまです。まずは、その点を補足しておきます。

    • データビュー

      単に表を扱う場合には、SharePoint Designer のデータビューを使用して作成することができます。データビューを使用すると、データベースの内の SQL やストアドプロシージャを使用したデータの表示や、さらに作成したデータビュー上でのフィルターの設定なども可能など、細かなビューの設定が可能です。(注 : ただし、データビューのフィルターでは、データベースへの検索のフィルターではなく、取得したデータをフィルターして表示しているため注意してください)
      このデータビューは、SharePoint Designer を扱える 業務管理者 (現場の管理者) による 実装を想定した仕組みです。

    • ビジネスデータカタログ (BDC)

      エンタープライズビジネス向けの Microsoft Office SharePoint Server 2007 でのみ使用可能です。
      このシナリオでは、プロフェッショナル開発者により構築されたアプリケーション定義 (xml) を SharePoint に
      インストールして検索などの設定をおこなうことで、SharePoint を使用する現場の担当者(エンドユーザー)がこのデータを使用した Web パーツ挿入によるデータの表示や、Web パーツどうしの接続、look-up 列としての使用、検索・閲覧 などの使い方が可能になるというもので、開発者 /管 理者による設定と、エンドユーザーによるビジネス構築という統合シナリオ での利用を想定した機能です。(SharePoint 上におけるデータ統合的な観点でコラボレーションされます)
      ただし、BDC では更新系の処理はサポートされていない (閲覧のみ) ので注意してください。

    • SQL Server Report Services

      SharePoint では、SQL Server のレポートサービス (Report Services) と連携したデータの表示が可能です。
      レポートサービス用のアドイン (ダウンロード可能) を SharePoint にインストールすることで、SQL Server のレポートサービスと連携したレポートの管理や、レポート ビューア Web パーツを使用したデータの表示が可能になるため、SQL Server が持っているレポーティングのためのフレームワークを SharePoint 上でも有効に、そして完結的に活用できます。

    Excel Services の場合は、ピボット、グラフなどのインテリジェンスと組み合わせた エンドユーザーによる実装・展開 が可能であり、 データベース連携シナリオにおいても、SQL Server Analysis Services との連携 (キューブデータの使用) など、いくつかの統合シナリオがあります。
    これらの 1 つ 1 つのシナリオは、以下で簡潔に紹介されていますので、是非参考にしながら、ステップバイステップでつ設定してみてください。(決して長いドキュメントではありません。最初のほうは、セキュリティ構成の方法などについてです)

    http://www.microsoft.com/japan/technet/archive/prodtechnol/office/sharepoint/library/eea3ace8-0863-429a-b1e8-041254ed2fc4.mspx?mfr=true

    なお、Excel Services を使用する場合、Excel 上へのデータのインポートの際のデータの種類として 「テーブル」(クエリテーブル、外部データ範囲) は挿入できないので注意してください。ピボットテーブル (pivot table) などを使ったデータベース (SQL Server など) のデータの取り込みが必要です。

    挿入したグラフなどは「Webパーツ」としてトップページなどに取り込むことができ、一般的なシナリオは達成されるでしょう。

    さて、ここからは少し余談を記載しましょう。 

    Excel Services を使って取り込んだデータに高度なフィルターなどを使って処理したくなる場合もあるでしょう。
    この場合には、「開発」の必要が生じます。

    1 つの方法としては、Excel Services を使ったコンシューマWebパーツを作成し、プロバイダーWebパーツと接続 (Web パーツ接続) をおこなうという方法です。「接続」を使用したWeb パーツの実装方法については、以前 私のブログ でも簡単な例を紹介しましたが、Excel Services を使ったコンシューマ Web パーツの作成方法については以下で紹介されていますので、「接続」の概念を身につけてから以下を参照すると良いでしょう。

    http://msdn.microsoft.com/en-us/office/bb266408.aspx

    また、もう 1 つの回避策として、UDF (ユーザー定義関数) を使用した (Excel Services 上の) Excel シートを作成することで解決するという方法もあります。単純な表形式のデータなどの場合にはこの方法が使えるでしょう。
    ここでは、このサンプルをご紹介しましょう。

    UDF とは、構築経験のある方は百も承知と思いますが、関数を SharePoint Server 上に登録して、この関数を Excel Services 上の Excel シートから (さも Excel に組み込まれている関数のように) 呼び出すというものです。
    UDF では表形式のデータを返すことも可能です。

    1. まず、UDF を使用できるようにするため、SharePoint の SSP (共有サービスプロバイダ) の管理画面で、[信頼できる保存場所] の設定画面を表示します。
      Excel ブックをアップロードする場所 (サイト) に対して UDF の使用を許可しておきましょう。

    2. 今回作成する UDF では、ストアドプロシージャを作成して呼び出すようにしましょう。
      このため、まずは、以下のストアドプロシージャを作成しておきます。(テーブルは、前回作成したものをそのまま使用します)

      create procedure GetTestData
        @Name nvarchar(255)
      as
        select Id, Name, Cost from TestTbl where Name like @Name
      go

    3. UDF の関数を作成します。[クラスライブラリ] のプロジェクトを新規作成し、以下の関数を実装します。
      ここでのポイントは、返り値として object[,] の表 (table) の配列を渡しているという点です。(同様の手法を用いて、Excel 上に設定された 1 次元や、2 X 2 の配列を引数に取ることもできます)

      using Microsoft.Office.Excel.Server.Udf;

      . . . . .

      [UdfClass]
      public class TestDataUDF
      {
          [UdfMethod]
          public object[,] GetTestData(string name)
          {
              int maxrow = 5;
              object[,] data = new object[maxrow, 2];
              using (SqlConnection con = new SqlConnection(@”data source=.sqlexpress;initial catalog=TestDB;user id=Demo;password=P@ssw0rd”))
              {
                  con.Open();
                  SqlCommand cmd = new SqlCommand(@”GetTestData”, con);
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.Parameters.Add(“@Name”, SqlDbType.NVarChar, 255).Value = name;
                  using (SqlDataReader reader = cmd.ExecuteReader())
                  {
                      for (int i = 0; (i < maxrow) && reader.Read(); i++)
                      {
                          data[i, 0] = reader[“Name”];
                          data[i, 1] = reader[“Cost”];
                      }
                      reader.Close();
                  }
                  con.Close();
              }
              return data;
          }
      }

    4. ビルドをおこなって、アセンブリ (dll) を作成します

       

    5. SharePoint の SSP (共有サービスプロバイダ) の管理画面で、[ユーザー定義関数アセンブリ] の設定画面を表示し、上記で作成したアセンブリを登録します

    6. Excel ブックを作成しましょう。
    • 今回は下図のようにテーブルを挿入します。ただし、Excel の「テーブル」では上記の配列データ (UDF 関数が返してくる結果) が使用できないため、今回は、いったんテーブルを挿入したら、範囲に変換 ([デザイン] タブの [範囲に変換] をクリック) をおこなっておきます。
    • 下図の通り、UDF に渡すパラメータを入力するセルを今回は C2 としています。
      C2 のセルを選択し、リボンの [数式] タブ – [名前の定義] をクリックして「param1」という名前を付けておきましょう。
    • Excel のシートのデータを貼り付ける範囲の左上のセルに、上記のUDFを使用した式「=GetTestData(C2)」を入力します (この時点では、Excel Services を使用していないので「#Name」が表示されます)。
      つぎに、データを貼り付ける範囲すべてを選択して F2 を押し、続けて [Ctrl] + [Shift] + [Enter] を押すと、選択した範囲すべてに、式「{=GetTestData(B2)}」が設定されます。
      (これで、配列として式が設定されます。)

  5. 上記の Excel ブックを SharePoint のサーバーに発行 (Publish) します。
    発行の際には、[Excel Services のオプション] をクリックして、パラメータとして上記の「param1」を選択しておきましょう。
  6. これで完了です。

    登録された Excel シートは Web ブラウザで閲覧可能であり、param1 に入力された値に応じて関数が実行され、データが貼りつきます。

    このようにして登録された Excel シートでは、表部分、グラフ部分などの部品ごとに Web パーツとして貼り付けて、上記の param1 のフィルターの値と連携 (Web パーツ接続) させることも可能です。例えば、[テキストフィルター] Web パーツを使用して下図のように default.aspx に検索結果などを表示することが可能です。

     

     

Advertisements

One thought on “Excel と Excel Services を使ったデータベース連携

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s