スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

CakePHP用ExcelReviser拡張ヘルパー:連続帳票出力

CakePHPでExcelといえばPHPExcelなのかもしれませんが、正直あいつ遅い。使いものにならない。1000行の一覧帳票出力するのに10分とかあり得ない。ということで、私はExcelReviser派です。PHPExcelより少なくとも50倍高速です。

しかし、ExcelReviserにも弱点があります。「シート内でセルの値のコピーができないこと」「範囲指定して書式をコピーできないこと」。このために、ひとつの帳票をひとつのシートの中で連続的に出力する(縦に並べる。それが一覧表だろうが単票の連続だろうが同じ)ことが非常に面倒。

普通はどうするかというと、まずひとつの帳票を定義して、そのセル1個1個について、次の帳票の同じ場所に絶対位置指定で書式をコピーして、値をこれまた絶対位置指定で書き込んで・・・の繰り返し。何が面倒かって、n個目の帳票でのそれぞれのセル位置を都度都度、カウンタか何か利用して計算しなくてはならないこと。それに、値がない単なる飾りセルもすべてひとつずつ書式コピーしなくてはならないこと。このへんは実際にやってみると、本当に気が狂いそうになります。特に1枚で100行☓50列くらいある単票を連続して出力しようとすると泣けます。だって5000個のセルの手動コピーですよ。

なので、このあたりカバーしたCakePHP用ヘルパー作成しました。
その名も「ExcelReviserExtensionHelper」。

【機能】
雛形の書式をすべて、指定した帳票数の位置へ自動的にコピーします。
帳票の範囲内で値がない場所は自動的にコピー。
値がある場所は、値・相対位置・セル結合の指定を持つ配列を作成して突っ込んでやると、その通り生成します。

【設置】
(0) あらかじめ本家を入手してサーバに配置しておく。
(1) 25行目あたりのrequire_onceで本家のパスを指定。
(2) /app/helpers/以下にexcel_reviser_extension.phpとして保存。
(3) コントローラの$helpersにExcelReviserExtensionを追加。

【利用】
(1) 値を書くセルごとに以下のような書式で配列を作成。

$vars[] = array('type'=>'number', 'row'=>1, 'col'=>2, 'var'=>12345);
$vars[] = array('type'=>'string', 'row'=>5, 'col'=>11, 'var'=>'ほげ', 'rowspan'=>2, 'colspan'=>1);

type: stging/number
row: 列座標(開始位置からの相対位置)
col: 行座標(〃)
var: 値
rowspan: 行(縦)方向のセル結合の指定
colspan: 列(横)方向のセル結合の指定

(2) 上記配列を作ったら、以下のメソッドで書き込み。

$excelReviserExtension->addVars($sheet_cnt, $data_count, $vars, 5, 0, 2, 14);

$sheet_count: 書式の元となるシート番号(0スタート)
$data_count: 何枚目の帳票か(普通はループ値を入れます)
$vars: 上記配列
5,0: 繰り返しが始まる列・行の座標(つまりヘッダに相当する部分を除きます)
2,14: 繰り返し範囲(列数・行数)

(3) 本家で出力します。

$excelReviserExtension->reviser->reviseFile('/path/to/template.xls', 'HOGE.xls');


(4) その他本家のメソッド等は以下のように使用可能です。

$excelReviserExtension->reviser->HOGEHOGE()


以下がソースです。

/**
* ExcelReviserExtensionHelper
*
* Copyright (C) 2012 @muuri_cat All rights reserved.
*
* This program is free software; you can redistribute it and/or modify it under
* the terms of the GNU General Public License as published by the Free Software
* Foundation; either version 2 of the License, or (at your option) any later
* version.
*
* This program is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
* FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
* details.
*
* You should have received a copy of the GNU General Public License along with
* this program; if not, write to the Free Software Foundation, Inc., 59 Temple
* Place, Suite 330, Boston, MA 02111-1307 USA
*
*/

class ExcelReviserExtensionHelper extends AppHelper {

function __construct() {
require_once(realpath(APP) . DS . 'vendors' . DS . 'excel_reviser' . DS . 'reviser.php');
$this->reviser = NEW Excel_Reviser;
$this->reviser->setInternalCharset('utf8');
}

function addVars($sheet_count, $data_count, $vars, $top_row, $top_col, $height, $width) {

// Row num check: please modify as you like.
if (($data_count * $height) + $height > 65535) {
echo('Count of vars too large.');
return false;
}

$row_start = $top_row + ($data_count * $height);
$row_end = ($row_start + $height) - 1;
$col_start = $top_col;
$col_end = $top_col + $width - 1;
$yetstr = null;

foreach ($vars as $k => $v) {

$row = intval($v['row']);
$col = intval($v['col']);
$vrow = $row_start + $row;
$vcol = $col;
$yetstr .= $vrow . ':' . $vcol . ',';

if ($v['type'] == 'string' || $v['var'] ==='') {
$this->reviser->addString($sheet_count, $vrow, $vcol, $v['var'], ($top_row + $row), ($top_col + $col), 0);
} elseif ($v['type'] == 'number') {
$this->reviser->addNumber($sheet_count, $vrow, $vcol, $v['var'], ($top_row + $row), ($top_col + $col), 0);
} else {
echo('Invalid var type passed.');
return false;
}

if (array_key_exists('rowspan', $v) && array_key_exists('colspan', $v)) {
$row_span_end = $vrow + intval($v['rowspan']) - 1;
$col_span_end = $vcol + intval($v['colspan']) - 1;
$this->reviser->setCellMerge($sheet_count, $vrow, $row_span_end, $vcol, $col_span_end);
}
}

if ($data_count > 0) {
for ($y=$row_start; $y<=$row_end; $y++) {
for ($x=$col_start; $x<=$col_end; $x++) {
if (strpos($yetstr, $y . ':' . $x) === false) {
$this->reviser->addBlank($sheet_count, $y, $x, ($y - ($data_count * $height)), $x, 0);
}
}
}
}
}

実際にExcelReviserやったことがある方なら分かると思いますが、コード量激減しますよ、これ。
スポンサーサイト

CakePHPでExcel_Reviserを使ってみる

仕事でPHPでExcel扱う必要があったわけですよ。事前にちょろちょろ調べていて、PHPExcelなんてよさげじゃない?ってんで少しずつテストコードとか書いて手順とか確認しつつ本番(=発注)を迎えました。で、いざ使ってみたら、確かに使えるけど、動くけど、クソ遅い。なんか重いとかじゃない。例えば5列×500行程度の、ごくごく普通のシート1枚に値を入れて出力というオペレーションで、ボタン押下から出力まで5分。5秒じゃないよ。300秒ですよ。Webアプリで5分待ちって無理無理。絶対納品できない。件数を増やして2500件程度(これが今回の標準データ数)にしたら15分くらい。とういかタイムアウト。バカ?ねえ、あんたホントに2000年代のコード?大丈夫?というので、真っ青になって、泣きながら探しました。最近泣きながらなにかしてばっかりだな。

で、見つけたのがExcel_Reviser。2008年頃に残念ながら開発が止まってしまったようだけど。しかもオフィシャルで公開停止してるのね。だけどGPLだからね。再配布先から入手したけど恐れることはなし。いろいろドキュメントを見てみると.xlsの出力をする分には十分な機能。x86_64だと動かんという説もあるけど、某所にてパッチも発見したので、まあ、大丈夫かと(この64bitの件で異様に怒ってる人見たけどなんなんだろうね)。で、実際にコード組んで(これがPHPExcelと比較にならないくらい簡潔で嬉しい)試してみる。

同じような条件で2500件出力で約5秒。普通こうでしょう。ねえ。とりあえず空白セルの書式設定とか、テンプレートの結合セルが解除されちゃう件とか扱いが大変なところを補うようにCakePHPのヘルパーとしてヘボな実装が完了したので今度公開します。

Pagination

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。