Clever Geek Handbook

Excel XMLへの簡単なエクスポート

電子文書管理システムを開発する際、一般的な形式でデータをエクスポートする機能を実装する必要がありました。 特に、Microsoft Excel形式。 エクスポート要件は非常に簡単でした-最小限の書式設定でデータをエクスポートします。 セルの結合、フォント付きのゲームなどはありません。 XLSXおよびExcel XMLエクスポート形式。







この場合、 Excel XMLについて説明します 。



そのため、表形式のデータで動作する傷があるシステムでは、データをエクスポートする必要が生じます。 エクスポートの目標は異なります。



目標に基づいて、データをエクスポートするときは、ファイルを開くアプリケーションがその裁量でフォーマットを適用しようとしないように、適切な列にデータ型を保存または指定する必要があるという合理的な結論に達しました。 つまり、日付は日付、数字は数字、文字列は文字列でなければなりません。



挑戦する



簡単に策定された技術要件：



エクスポートメカニズムを自律クラスとして設計することは明らかな要件であり、その実現により、将来クラスを他の開発者と共有し、新しいプロジェクトで使用できるようになります。



セルおよび行の値を書き込むためのクラスに関数のセットを実装することは基本的な要件であり、指定されたタイプのセル値を書き込む関数を作成し、完成した行をファイルに書き込むことができます。



無制限の量のデータを処理する機能-もちろん、エクスポートクラス自体は記録されたボリュームに応答できませんが、ディスクにデータを書き込み、次のデータ用にRAMを解放する機能を提供する必要があります。



説明されている要件に加えて、サービス機能を追加する必要がありました。





実装



まず、クラスを作成するときに、最終的なファイル名を確認し、列と行の数を要求します。 ファイルには有効な名前が必要です。また、ファイルを保存するフォルダーが存在している必要があります。 すべてがいつも通りです。

Excel XML形式を使用すると、ファイルに作成したユーザーに関する情報をファイルに保存できるため、ヘッダーを作成するときに、組織の名前、ユーザーに関する情報、およびファイルが作成された日付を書き留めます。 



public function writeDocumentProperties($organization = null, $user = null) { fwrite($this->file, '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'); if (!is_null($user)) { fwrite($this->file, '<Author>'.$user->description.'</Author>'); fwrite($this->file, '<LastAuthor>'.$user->description.'</LastAuthor>'); } $dt = new Datetime(); $dt_string = $dt->format('Ymd\TH:i:s\Z'); fwrite($this->file, '<Created>'.$dt_string.'</Created>'); fwrite($this->file, '<LastSaved>'.$dt_string.'</LastSaved>'); if (!is_null($organization)) fwrite($this->file, '<Company>'.$organization->name.'</Company>'); fwrite($this->file, '<Version>12.00</Version>'); fwrite($this->file, '</DocumentProperties>'); }


確かに、この機能では、ワークフローシステムのエンティティ（組織（組織）とユーザー（ユーザー））が使用されます。 これらのエンティティを、たとえば文字列値に置き換えることは問題ではありません。



タイトルで最も興味深いのは、スタイル情報です。 Excel XML形式では非常に便利に実装されるため、行、日付/時刻、およびハイパーリンクのスタイルを持つテーブルを作成するだけです。 



 public function writeStyles() { fwrite($this->file, '<Styles>'); //default style fwrite($this->file, '<Style ss:ID="Default" ss:Name="Normal"><Font ss:Color="#000000"/></Style>'); //Datetime style fwrite($this->file, '<Style ss:ID="DateTime"><NumberFormat ss:Format="General Date"/></Style>'); fwrite($this->file, '<Style ss:ID="Date"><NumberFormat ss:Format="Short Date"/></Style>'); fwrite($this->file, '<Style ss:ID="Time"><NumberFormat ss:Format="h:mm:ss"/></Style>'); //Hyperlink style fwrite($this->file, '<Style ss:ID="Hyperlink" ss:Name="Hyperlink"><Font ss:Color="#0000FF" ss:Underline="Single"/></Style>'); //Bold fwrite($this->file, '<Style ss:ID="Bold"><Font ss:Bold="1"/></Style>'); fwrite($this->file, '</Styles>'); }




準備作業が終了したら、データの書き込みに進むことができます。 ワークシートを開くことは、2、3のタグにすぎません。その瞬間、列と行の数に関する情報が使用されます。 



 public function openWorksheet() { fwrite($this->file, '<Worksheet ss:Name="Export">'); fwrite($this->file, strtr('<Table ss:ExpandedColumnCount="{col_count}" ss:ExpandedRowCount="{row_count}" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">', array('{col_count}'=>$this->colCount, '{row_count}'=>$this->rowCount))); }


しかし、ここにシリーズの記録があります-プロセスはより興味深いです。 クラスは迅速に機能し、無制限の量のデータを処理する必要があります。これは、レコードが10万または100万にもなる可能性があるためです。 速度が必要な場合-メモリを使用し、データの量に制限がない場合-ディスクを使用します。 要件を調整するために、resetRowおよびflushRow関数を実装しました。

最初の行は現在の行をクリアした後、再びデータで満たすことができ、2番目の行は現在の行をディスク上の開いているファイルに書き込みます。 これらの併用により、速度と使用されるメモリ量のバランスを維持できます。 



 public function resetRow() { $this->currentRow = array(); } public function flushRow() { fwrite($this->file, implode('', $this->currentRow)); unset($this->currentRow); }


各セルは、データ型に対応する関数、つまりappendCellxxx（xxxはデータ型）によって書き込まれます。 有効なデータタイプは、Num、String、Real、DateTime、Date、Time、Linkです。 数値を書き込むための関数の例： 



 public function appendCellNum($value) { $this->currentRow[] = '<Cell><Data ss:Type="Number">'.$value.'</Data></Cell>'; }


すべてのデータを記録した後、ワークシートとブックを閉じるために残ります。



申込み



記述されたクラスの使用は、 CArrayDataProviderプロバイダーを使用したデータのエクスポートに基づいています。 ただし、エクスポートされたデータのボリュームが非常に大きくなる可能性がある場合、特別なCDataProviderIteratorイテレータが使用され、返されたデータを100レコードずつ繰り返します（異なるレコード数を指定できます）。 



 public function exportExcelXML($organization, $user, &$filename) { $this->_provider = new CArrayDataProvider(/*query*/); Yii::import('ext.AlxdExportExcelXML.AlxdExportExcelXML'); $export = new AlxdExportExcelXML($filename, count($this->_attributes), $this->_provider->getTotalItemCount() + 1); $export->openWriter(); $export->openWorkbook(); $export->writeDocumentProperties($organization, $user); $export->writeStyles(); $export->openWorksheet(); //title row $export->resetRow(); $export->openRow(true); foreach ($this->_attributes as $code => $format) $export->appendCellString($this->_objectref->getAttributeLabel($code)); $export->closeRow(); $export->flushRow(); //data rows $rows = new CDataProviderIterator($this->_provider, 100); foreach ($rows as $row) { $export->resetRow(); $export->openRow(); foreach ($this->_attributes as $code => $format) { switch ($format->type) { case 'Num': $export->appendCellNum($row[$code]); /*other types*/ default: $export->appendCellString(''); } } $export->closeRow(); $export->flushRow(); } //close all $export->closeWorksheet(); $export->closeWorkbook(); $export->closeWriter(); //zip file $export->zip(); $filename = $export->getZipFullFileName(); }


私の場合、各行はディスクに書き込まれますが、これは現時点ではまったく問題ありませんが、将来変更が必要になる場合があります。 たとえば、すべての行を保存するのではなく、一度に10行または100行ごとに保存するのが賢明です。 その後、エクスポート速度が向上します。



スピード



ところで、私自身の経験から、エクスポートなどのバッチ操作で大量のデータが存在する可能性を想定することの重要性を確信しました。

最初に、 CActiveDataProviderを使用してデータをエクスポートしようとしましたが、エクスポートには約240秒の1000レコードが必要でした！ CArrayDataProviderを使用するようにクエリを変更すると、1000レコードのエクスポート時間が0.5秒に短縮されました。

特にこの出版物では、輸出のパフォーマンスを測定しました。

クローズされたインシデントに関する情報を表す9つの属性を持つエクスポートされた1626レコード（ ITSMを参照）。



エクスポートされたテーブルの初期ビュー


（申し訳ありませんが、写真は公開後に消えます）



結果


（申し訳ありませんが、写真は公開後に消えます）



エクスポートのパフォーマンス


最終ファイルのボリューム： 1 312 269

圧縮ファイルサイズ： 141 762

経過時間：約0.5秒



興味のある人は誰でも、私のAlxdExportExcelXMLクラスのソースコードを無料で入手できます。 writeDocumentProperties関数を修正して、組織およびユーザーワークフローエンティティを削除するか、対応するプロパティで同様のエンティティを使用することを忘れないでください。


More articles:


All Articles