お客様が developerWorks に初めてサインインすると、プロフィールが作成されます。プロフィールで選択した情報は公開されますが、いつでもその情報を編集できます。お客様の姓名（非表示設定にしていない限り）とディスプレイ・ネームは、投稿するコンテンツと一緒に表示されます。

送信されたすべての情報は安全です。

developerWorks に初めてサインインするとプロフィールが作成されますので、その際にディスプレイ・ネームを選択する必要があります。ディスプレイ・ネームは、お客様が developerWorks に投稿するコンテンツと一緒に表示されます。

ディスプレイ・ネームは、3文字から31文字の範囲で指定し、かつ developerWorks コミュニティーでユニークである必要があります。また、プライバシー上の理由でお客様の電子メール・アドレスは使用しないでください。

送信されたすべての情報は安全です。

My developerWorks：

通知：

サイン・アウト

言語を選択する：

高度なスプレッドシート・エクスポート機能と PHP を組み合わせてピボットテーブルを作成する

柔軟でエクスポート可能なスプレッドシートを XML テンプレートから作成する

Jack D. Herrington, Senior Software Engineer, Fortify Software, Inc.

Jack Herrington はサンフランシスコ湾岸地域に住む技術者、著作者、講演者です。彼の最新の活動や記事を http://jackherrington.com で知ることができます。

概要： Microsoft Excel の高度なエクスポート機能と PHP を使って、柔軟なピボットテーブルを XML フォーマットで作成しましょう。すると皆さんもユーザーも、ピボットテーブルのデータを瞬時にソートしたり分析したりすることができます。

タグを付ける!

日付： 2011年 4月 12日
レベル： 初級
この記事の原文： 英語
アクティビティー： 4717 ビュー
お気軽にご意見・ご感想をお寄せください：

私は 6 年前、「PHP を使って Excel データを読み書きする」という記事を書き、PHP を使って Microsoft Excel のスプレッドシートとしてデータをエクスポートする方法について説明しました。その記事が非常に好評だったので、このフォローアップ記事を書くことにしました。最初に書いた記事では、Excel の強力さを表面的に説明したにすぎません。この記事では Excel のエクスポート機能の可能性を示す一例として、より高度な機能の 1 つ、ピボットテーブルの使い方を説明します。

Excel の高度なエクスポート機能と PHP を使用してピボットテーブルを作成する

よく使われる頭文字語

CSV: Comma-Separated Value
W3C: World Wide Web Consortium
XML: Extensible Markup Language

ピボットテーブルというのは、どのフィールドを行や列の見出しに使用するか、各セルのデータとして何を表示するかをユーザーが動的に選択できるテーブルです。ユーザーは瞬時にフィールドを再配置することができるため、複雑な質問に対する答えを得たり、興味深いパターンでデータ・マイニングを行ったりすることができます。

図 1 は Excel の基本的なスプレッドシートを示しています。最初の行にはフィールド名が含まれ (Account、Genre、Images、Average Ran、Total Size)、それ以下の行にはデータが含まれています。この場合は画像保管サイト (Flickr など) からエクスポートされたデータを表示しています。各行には、指定されたアカウントの情報が含まれています (画像のジャンル、アップロードされた画像の数、それらの画像の平均ランキング、すべての画像の合計サイズ (バイト))。

図 1. Excel の基本的なテーブル
Excel の基本的なテーブルのスクリーン・キャプチャーです。画像ストレージ・サイトのアカウント情報が表示されています。

Excel でピボットテーブルを作成するためには、ピボットテーブルに使用するデータ列を選択し、次に「データ」メニューから「ピボットテーブルとピボットグラフレポート」を選択します。するとウィザードが表示され、どのデータをレポートに使用するのかを指定するように促されます。このウィザードへの入力を完了すると、新しいシートが Excel ファイルに追加されます。

図 2 は空のピボットテーブルを示しており、ウィンドウ上にピボットテーブルのツールバーが表示されています。このスプレッドシートには、レポートを生成するために各フィールド項目をドロップするための領域が含まれています。

図 2. ピボットテーブルを作成する
空のピボットテーブルのスクリーン・キャプチャーです。行フィールド、列フィールド、データ項目のドロップ先となる領域が表示されています。

図 3 はピボットテーブルのツールバーの詳細を示しています。最上部には、「ピボットテーブル」メニュー、そしてテーブルのネストの深さを制御するツールバー・ボタンなどがあります。ツールバーの一番下には利用可能なフィールド (Account、Genre、Images、Average Ran、Total Size) があります。

図 3. ピボットテーブルのツールバー
ピボットテーブルのツールバーのスクリーン・キャプチャーです。メニュー、ボタン、利用可能なフィールドが表示されています。

ピボットテーブルを調整するためには、フィールドを選択し、ページ上の適当なゾーンにドロップします。図 4 は、レポートの左側のデータ領域に最初に「Genre (ジャンル)」をドロップし、次に「Account (アカウント)」と「Total Size (合計サイズ)」をドロップした結果を示しています。

図 4. データが挿入されたピボットテーブル
データが挿入されたピボットテーブルのスクリーン・キャプチャーです。

これでレポートには、最初にジャンルでグループ分けされ、次にアカウントでグループ分けされたレコードが表示されるようになり、最後にアカウントごとのサイズとジャンルごとの合計サイズが表示されるようになりました。データの表示順序を調整するためには、単純にフィールドをドラッグ・アンド・ドロップすればよいのです。これはデータを詳しく調べる上での非常に強力なメカニズムであり、大規模なデータ・セットや複雑なデータ・セットを扱う際には採用の価値があるメカニズムです。

Excel スプレッドシートを XML フォーマットで保存する場合、データとピボットテーブルのパラメーターはすべて XML ファイルに保存されます。このファイルの一部を示したものがリスト 1 です。

リスト 1. Excel のエクスポートの例

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
 xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
 xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Jack Herrington</Author>
  <LastAuthor>Jack Herrington</LastAuthor>
  <Created>2011-03-26T21:15:20Z</Created>
  <LastSaved>2011-03-26T21:45:33Z</LastSaved>
  <Company>Myself</Company>
  <Version>12.0</Version>
 </DocumentProperties>
...
</Workbook>

サンプルの PHP アプリケーションでは Excel XML ファイルを作成します。このファイルには、オリジナルのデータ用のワークシートとピボットテーブル用の別のワークシートがあります。このピボットテーブル・タブには、Excel で作成した場合とまったく同じように、利用可能な動的機能がすべて含まれています。

作業を始めるためには、まずスプレッドシートに挿入するためのデータが必要です。

上に戻る

データを取得する

作業が楽になるように、データを XML ファイルに保存します (リスト 2)。

リスト 2. data.xml

<users>
  <user account="Megan" genre="Portraits" images="20"
     avgrank="4.0" size="72000" />
  <user account="Hannah" genre="Landscapes" images="31"
     avgrank="3.5" size="83000" />
  <user account="Vicky" genre="Floral" images="25"
     avgrank="4.2" size="42000" />
  <user account="Ian" genre="Portraits" images="40"
     avgrank="3.7" size="92000" />
  <user account="Michael" genre="Landscapes" images="23"
     avgrank="3.8" size="72000" />
  <user account="Daniel" genre="Landscapes" images="29"
     avgrank="4.4" size="85000" />
</users>

この XML のルートは users タグであり、このタグには各ユーザーに対するタグが含まれています。各 users タグには属性として、account、genre、images、avgrank、size が含まれています。これらの属性は各アカウントに関係するデータを保持しています。

データを読み取るために、単純な data.php ファイルには load_data という 1 つの関数が含まれています。この関数は XML を読み取り、ハッシュ・テーブルの配列としてデータを返します。リスト 3 にこの関数のコードを示します。

リスト 3. data.php

<?php
function load_data() {
  $xmlDoc = new DOMDocument();
  $xmlDoc->load("data.xml");
  $data = array();
  foreach ($xmlDoc->documentElement->childNodes AS $item)
  {
    if ( $item->nodeType == XML_ELEMENT_NODE ) {
    $data []= array( "account" => $item->getAttribute("account"),
      "genre" => $item->getAttribute("genre"),
      "images" => $item->getAttribute("images"),
      "avgrank" => $item->getAttribute("avgrank"),
      "size" => $item->getAttribute("size") );
    }
  }
  return $data;
}
?>

このコードはまず DOMDocument オブジェクトを作成し、このオブジェクトを使ってファイルから XML をロードします。次に foreach ループで各ユーザー・ノードに対して繰り返し処理を行い、それらのノードの属性をデータ配列に保存します。そしてこのデータ配列が呼び出し側に返されます。

データ読み取り関数の他に、print_file という別の関数を使用してファイルの内容全体を出力します。リスト 4 にこの関数を示します。

リスト 4. helpers.php

<?php
function print_file( $file ) {
  print file_get_contents( $file );
}
?>

print_file 関数が存在する理由は単純です。Excel の XML ファイルは非常に複雑であり、このファイルを作成するには、エクスポートした XML ファイルを出発点として使用し、そのファイルから大きなコード・ブロックを取得してシード・コードとして使用するのが最も容易な方法となるからです。ここでは、ファイルの先頭部分から最初のワークブックが定義されている部分までを取得し、それを body_header.txt に保存し、ファイルの最後の部分、つまり最後のワークブックが定義されている部分以降を body_footer.txt に保存します。

同様のファイルとして、data_header.txt と data_footer.txt, にスプレッドシートのデータ・タブのテーブルのヘッダーとフッターを保存します。

上に戻る

単純な Excel スプレッドシートを作成する

データ読み取り関数とファイル出力関数を用意できると、Excel スプレッドシートを作成することができます。リスト 5 に最初の Excel エクスポート・コードを示します。

リスト 5. helpers.php

<?php
require_once 'data.php';
require_once 'helpers.php';
require_once 'exporters.php';

$data = load_data();

print_file( 'body_header.txt' );
print_file( 'data_header.txt' );
?>
<Table ss:ExpandedColumnCount="5" 
       ss:ExpandedRowCount="<?php echo( count($data) + 1 ) ?>" x:FullColumns="1"
 x:FullRows="1">
 <Row>
  <Cell ss:StyleID="s21"><Data ss:Type="String">Account</Data></Cell>
  <Cell ss:StyleID="s21"><Data ss:Type="String">Genre</Data></Cell>
  <Cell ss:StyleID="s21"><Data ss:Type="String">Images</Data></Cell>
  <Cell ss:StyleID="s21"><Data ss:Type="String">Average Ranking</Data></Cell>
  <Cell ss:StyleID="s21"><Data ss:Type="String">Total Size</Data></Cell>
 </Row>
<?php foreach( $data as $row ) { ?>
<Row>
 <Cell><Data ss:Type="String"><?php echo( $row['account'] ) ?></Data></Cell>
 <Cell><Data ss:Type="String"><?php echo( $row['genre'] ) ?></Data></Cell>
 <Cell><Data ss:Type="Number"><?php echo( $row['images'] ) ?></Data></Cell>
 <Cell><Data ss:Type="Number"><?php echo( $row['avgrank'] ) ?></Data></Cell>
 <Cell><Data ss:Type="Number"><?php echo( $row['size'] ) ?></Data></Cell>
</Row>
<?php } ?>
</Table>
<?php
print_file( 'data_footer.txt' );
print_file( 'body_footer.txt' );
?>

このコードでは、まずデータを読み取り、本体のヘッダーとデータのヘッダーの両方を出力してから、XML コードによってテーブルを定義します。このテーブルでは、最初の行のヘッダーに続いて、foreach ループによって各データ項目に対して繰り返し処理を行い、それらのデータを使って新しい行を作成します。

このプログラムを実行すると、出力はファイルに保存されます。保存されたファイルは Excel によって開かれます。その結果は図 5 のようになります。

図 5. PHP からエクスポートされた基本的なテーブル
ピボットテーブルのスクリーン・キャプチャーです。データのフォーマットは元のファイルと同じです。

図 5 のデータは、元のファイルで表示されていたときと同じように適切なフォーマットで表示されており、最初の行はフィールドの見出しを表示し、それ以降の各行はデータを表示しています。

もちろん皆さんのフィールドおよびデータは、この例とは異なるはずなので、皆さんはこのコードをそのまま Excel のエクスポートに使用することにはなりません。ただしシートを作成するプロセスは同じです。つまり最初に Excel ファイルを作成し、次にヘッダー・セクションとフッター・セクションを取り出し、最後にデータを挿入する、というプロセスは変わりません。

上に戻る

ピボットテーブルを作成する

ピボットテーブルのスプレッドシートはもう少し複雑ですが、考え方はほぼ同じです。このスクリプトは最初にデータを読み取り、次に本体のヘッダーを出力します。続いてデータのページ、ピボットテーブルのページ、フッターのページを出力します。リスト 6 に、このプロセスのコードを示します。

リスト 6. build2.php

<?php
require_once 'data.php';
require_once 'helpers.php';
require_once 'exporters.php';

header( 'Content-type: application/excel' );
header( 'Content-Disposition: attachment; filename="pivot.xml"' );

$data = load_data();

print_file( 'body_header.txt' );
export_data( $data );
export_pivot( $data );
print_file( 'body_footer.txt' );
?>

最初にヘッダー関数を呼び出しているのも興味深い部分です。これらの呼び出しにより、このファイルに対して何をすべきかをブラウザーに指示しているからです。Content-type ヘッダーはブラウザーに対し、このスクリプトによって Excel スプレッドシートをエクスポートするように指示しています。Content-disposition ヘッダーはブラウザーに対し、このスプレッドシートをファイルとして保存するように指示し、ファイル名としてこの場合は pivot.xml を推奨しています。

作業の大部分はエクスポート関数によって行われます。エクスポート関数は exporters.php ファイルで定義されます (リスト 7)。

リスト 7. exporters.php

<?php
require_once 'helpers.php';

function export_data( $data ) {
  print_file( 'data_header.txt' );
?>
<Table ss:ExpandedColumnCount="5" 
       ss:ExpandedRowCount="<?php echo( count($data) + 1 ) ?>" x:FullColumns="1"
 x:FullRows="1">
...
</Table>
<?php
  print_file( 'data_footer.txt' );
}

function export_pivot( $data ) {
?>
<Worksheet ss:Name="Pivot 1">
<?php
  export_pivot_table( $data );
  export_pivot_worksheet_options( $data );
  export_pivot_pivottable( $data );
?>
</Worksheet>
<?php
  export_pivotcache( $data );
}

function export_pivot_table( $data ) {
?>
<Table ss:ExpandedColumnCount="2" 
       ss:ExpandedRowCount="<?php echo( count($data) + 5 ) ?>" x:FullColumns="1"
 x:FullRows="1">
 <Column ss:Width="96.0"/>
 <Column ss:AutoFitWidth="0" ss:Width="49.0"/>
 <Row ss:Index="3">
  <Cell ss:StyleID="s22">
     <Data ss:Type="String">Sum of Total Size</Data>
  </Cell>
  <Cell ss:StyleID="s24"/>
 </Row>
 <Row>
  <Cell ss:StyleID="s34">
     <Data ss:Type="String">Account</Data>
  </Cell>
  <Cell ss:StyleID="s24">
     <Data ss:Type="String">Total</Data>
  </Cell>
 </Row>
<?php
$total = 0;
foreach( $data as $row ) { 
$total += intval( $row['size'] );
?>
<Row>
  <Cell ss:StyleID="s22">
     <Data ss:Type="String"><?php echo( $row['account'] ) ?></Data>
  </Cell>
 <Cell ss:StyleID="s26">
     <Data ss:Type="Number"><?php echo( $row['size'] ) ?></Data>
  </Cell>
</Row>
<?php } ?>
 <Row>
  <Cell ss:StyleID="s31">
     <Data ss:Type="String">Grand Total</Data>
  </Cell>
  <Cell ss:StyleID="s33">
     <Data ss:Type="Number"><?php echo( $total ) ?></Data>
  </Cell>
 </Row>
</Table>
<?php
}

function export_pivot_pivottable( $data ) {
?>
<PivotTable xmlns="urn:schemas-microsoft-com:office:excel">
...
</PivotTable>
<?php
}

function export_pivot_worksheet_options( $data ) {
?>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
...
</WorksheetOptions>
<?php
}

function export_pivotcache( $data ) {
?>
<PivotCache xmlns="urn:schemas-microsoft-com:office:excel">
...
<?php foreach( $data as $row ) { ?>
  <row Col1="<?php echo( $row['account'] ) ?>" Col2="<?php echo( $row['genre'] ) ?>" 
	Col3="<?php echo( $row['images'] ) ?>" Col4="<?php echo( $row['avgrank'] ) ?>" 
	Col5="<?php echo( $row['size'] ) ?>" xmlns="#RowsetSchema"/>
<?php } ?>
 </data>
</PivotCache>
<?php
}
?>

これらの関数の内容の大部分はスペースを節約するために省略されています。実際に動作する完全なコードは、ダウンロード・ファイル excelpivotxml.zip として入手することができます (「ダウンロード」を参照)。最初の関数 export_data が実行する内容は、最初のスクリプトでデータをエクスポートした場合と同じです。ピボットテーブル・タブを作成するために、export_pivot 関数は最初にワークシートを作成し、次にデータのテーブル、ワークシートのオプション、そしてピボットテーブルの XML を追加します。ワークシートが完成した後、最終的に PivotCache が作成されます。

正直なところ、なぜこれほど大量の XML コードがピボットテーブルの定義に必要なのか、私には理解できません。単純なデータのテーブルに必要とされるよりも確実にコードの量は多くなっています。しかし良い点として、Excel でピボットテーブルを作成して XML として保存し、その XML コードを皆さん独自のコードのテンプレートとして使用することができます。

この PHP スクリプトを実行して結果をファイルに保存し、そのファイルを Excel で開くと、図 6 のようなものが表示されます。

図 6. エクスポートされたピボットテーブル
スプレッドシートのスクリーン・キャプチャーです。アカウントと (サイズの) 合計、そして総計が表示されています。

図 6 のピボットテーブルには、各アカウント、各アカウントのサイズの合計、そして最後にサイズの総計が表示されています。Excel のピボットテーブルが適切に作成されているため、ピボットテーブルのツールバーがウィンドウに表示されます。このツールバーを使用することで、ユーザーはフィールドを追加したりテーブルを調整したりすることができます。

上に戻る

まとめ

Excel で使用される XML フォーマットは少し複雑かもしれませんが、保存された XML ファイルは、エクスポート対象のスプレッドシートを作成するためのテンプレートとして使用することができます。この方法によって、ファイル生成プロセスが大幅に単純化されます。また、ユーザーが自分の表示したい形式でスプレッドシートを作成できるようになり、これらのスプレッドシートをエクスポートしてライブ・データをオンザフライで追加することもできるようになります。これは、サーバーの PHP と XML を利用して皆さんの顧客に新たな価値を提供する素晴らしい方法です。

上に戻る

ダウンロード

内容	ファイル名	サイズ	ダウンロード形式
Source code for article	excelpivotxml.zip		HTTP

ダウンロード形式について

参考文献

学ぶために

「PHP を使って Excel データを読み書きする: XML サポートを利用する」(Jack D. Herrington 著、developerWorks、更新版は 2010年8月公開、オリジナルは 2005年10月公開) を読んでください。PHP で XML をサポートする例として Excel と PHP との組み合わせを説明した元の記事であり、Microsoft Excel 2003 からエクスポートされる XML からデータを読み取る方法を説明しています。
ウィキペディアの「Microsoft Office XML formats」の項目を見てください。ここでは Excel のファイル・フォーマットを説明しており、同様の XML フォーマット、特に Open Office との比較を行っています。
PHP: Hypertext Preprocessor の Web サイトを訪れてください。ここは PHP に関して調べる上で最も参考になるサイトです。
W3C の Web サイトを訪れてください。この記事に関係する XML 標準を含め、さまざまな技術標準が公開されています。
Microsoft のサイトの「Excel でサポートしているファイル形式」を訪れ、詳細な情報を入手してください。
ウィキペディアの Microsoft Office XML formats の項目には、Microsoft Office アプリケーションでの XML のサポートに関するすべてが説明されています。Excel に関心のない人も、他に重要なフォーマットがないかどうか調べてみてください。
著者の Jack Herrington が developerWorks に寄稿した他の記事も読んでください (2005年3月から現在まで)。Ajax、JSON、PHP、XML その他の技術が解説されています。
developerWorks の XML ゾーンには、XML の領域でのスキルを磨くためのリソースが豊富に用意されています。
developerWorks の Open source ゾーンをご覧ください。オープンソース技術を使った開発や、IBM 製品でオープンソース技術を使用するためのハウ・ツー情報やツール、プロジェクトの更新情報など、豊富な情報が用意されています。また最も人気のある記事やチュートリアルもご覧ください。
My developerWorks で developerWorks のエクスペリエンスをパーソナライズしてください。
XML および関連技術において IBM 認定技術者になる方法については、IBM XML certification を参照してください。
developerWorks の XML ゾーンを XML の技術ライブラリーとして利用してください。広範な話題を網羅した技術記事やヒント、チュートリアル、技術標準、および IBM Redbooks などが用意されています。また、他にも XML に関するヒントを記した記事があります。
developerWorks の Technical events and webcasts で最新情報を入手してください。
developerWorks on Twitter で、今すぐ Twitter に参加して developerWorks のツイートをフォローしてください。
developerWorks podcasts でソフトウェア開発者のための興味深いインタビューや議論を聞いてください。
developerWorks On demand demos をご覧ください。初心者のための製品インストール方法やセットアップのデモから、上級開発者のための高度な機能に至るまで、多様な話題が解説されています。