Geekly Articles each Day

PHPのExcelでの処理とレポート

プロジェクトの開発時に、レポート統計を生成する必要があることは珍しくありません。 プロジェクトがDelphi 、 C＃または、たとえばC ++およびWindowsで開発されている場合、問題はありません。 COMオブジェクトを使用するだけです。 ただし、 PHPでExcel形式のレポートを生成する必要がある場合、状況は異なります。 そして、この作成がUNIXライクなシステムで機能するようにします。 しかし、幸いなことに、それほど悪くはありません。 そして、これには十分なライブラリがあります。 PHPExcelを選択しました 。 私はこのライブラリで数年働いており、満足しています。 クロスプラットフォームなので、移植性に問題はありません。


PHPExcelを使用すると、Excelにデータをインポートおよびエクスポートできます。 さまざまなデザインスタイルをレポートに適用します。 一般的に、すべてが上にあります。 数式を使用することもできます。 すべての作業（読み取りおよび書き込み）がutf-8エンコードである必要があることを考慮する必要があるだけです。


ライブラリのインストール

動作するには、PHPバージョン5.2.0以降が必要です。 次の拡張機能も必要です：php_zip、php_xml、php_gd2。 ここからライブラリをダウンロードできます。


PHPExcelライブラリを使用すると、次の形式でデータを書き込むことができます。


PHPからExcelにデータをインポートする


九九を形成する例を考えてみましょう。


//      excel require_once('PHPExcel.php'); //        excel require_once('PHPExcel/Writer/Excel5.php'); //    PHPExcel $xls = new PHPExcel(); //     $xls->setActiveSheetIndex(0); //    $sheet = $xls->getActiveSheet(); //   $sheet->setTitle(' '); //     A1 $sheet->setCellValue("A1", ' '); $sheet->getStyle('A1')->getFill()->setFillType( PHPExcel_Style_Fill::FILL_SOLID); $sheet->getStyle('A1')->getFill()->getStartColor()->setRGB('EEEEEE'); //   $sheet->mergeCells('A1:H1'); //   $sheet->getStyle('A1')->getAlignment()->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER); for ($i = 2; $i < 10; $i++) { for ($j = 2; $j < 10; $j++) { //    $sheet->setCellValueByColumnAndRow( $i - 2, $j, $i . "x" .$j . "=" . ($i*$j)); //   $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()-> setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); } }

次に、* .xlsファイルを取得する必要があります。 ここでは、2つの方法で移動できます。 あなたがオンラインストアを持っていると仮定し、クライアントが価格表をダウンロードしたい場合、この結論に頼る方が良いでしょう：


 //  HTTP- header ( "Expires: Mon, 1 Apr 1974 05:00:00 GMT" ); header ( "Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT" ); header ( "Cache-Control: no-cache, must-revalidate" ); header ( "Pragma: no-cache" ); header ( "Content-type: application/vnd.ms-excel" ); header ( "Content-Disposition: attachment; filename=matrix.xls" ); //    $objWriter = new PHPExcel_Writer_Excel5($xls); $objWriter->save('php://output');


ここで、生成されたデータはすぐにブラウザに「吐き出され」ます。 ただし、すぐに「捨てる」のではなくファイルを保存する必要がある場合は、 HTTPヘッダーを表示する必要はなく、「php：//出力」の代わりにファイルのパスを指定する必要があります。 ファイルを作成するディレクトリには書き込み権限が必要であることを忘れないでください。 これはUNIXライクシステムに適用されます。


3つの便利な指示の例を見てみましょう。



また、レポートの操作に必要な次の方法にも注意してください。



ご覧のとおり、上記のメソッドはペアになっています。 したがって、座標の文字列または数値表現を使用してセルを操作できます。 もちろん、これは作業の追加の利点です。


ExcelでPHPを使用したレポート作成


非常に多くの場合、レポートの一部のデータを強調表示する必要があります。 フォントを選択するか、一部のセルなどに背景を塗りつぶした境界線を適用します これにより、最も重要な情報に集中することができます（真実は気を散らす可能性があります）。 これらの目的のために、PHPExcelライブラリには、Excelのセルに適用できる一連のスタイルがあります。 もちろん、このライブラリにはわずかな「マイナス」があります。スタイルを複数のセルに同時に適用することはできませんが、個々のセルにのみ適用できます。 しかし、これはWebアプリケーションの開発時に不快感を引き起こすことはありません。


セルのスタイルを設定するには、次の3つの方法があります。



注ぐ

fillパラメーターの値は、次のオプションパラメーターを持つ配列です。


塗りつぶしスタイル
FILL_NONEなし
FILL_SOLID固い
FILL_GRADIENT_LINEAR線形
FILL_GRADIENT_PATH道
FILL_PATTERN_DARKDOWN暗闇
FILL_PATTERN_DARKGRAYダークグレー
FILL_PATTERN_DARKGRIDダークグリッド
FILL_PATTERN_DARKHORIZONTAL暗い水平
FILL_PATTERN_DARKTRELLISdarkTrellis
FILL_PATTERN_DARKUP暗黒
FILL_PATTERN_DARKVERTICAL暗い垂直
FILL_PATTERN_GRAY0625gray0625
FILL_PATTERN_GRAY125グレー125
FILL_PATTERN_LIGHTDOWNライトダウン
FILL_PATTERN_LIGHTGRAYlightGray
FILL_PATTERN_LIGHTGRIDlightGrid
FILL_PATTERN_LIGHTHORIZONTAL光の水平
FILL_PATTERN_LIGHTTRELLISlightTrellis
FILL_PATTERN_LIGHTUPライトアップ
FILL_PATTERN_LIGHTVERTICALlightVertical
FILL_PATTERN_MEDIUMGRAYmediumGray
塗りつぶしの設定を指定する例：
 array( 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, 'rotation' => 0, 'startcolor' => array( 'rgb' => '000000' ), 'endcolor' => array( 'argb' => 'FFFFFFFF' ), 'color' => array( 'rgb' => '000000' ) );

または、次の方法を使用できます。

$ PHPExcel_Style-> getFill（）-> setFillType（PHPExcel_Style_Fill :: FILL_GRADIENT_LINEAR）;

$ PHPExcel_Style-> getFill（）-> setRotation（0）;

$ PHPExcel_Style-> getFill（）-> getStartColor（）-> applyFromArray（array（ 'rgb' => 'C2FABD'））;

$ PHPExcel_Style-> getFill（）-> getEndColor（）-> applyFromArray（array（ 'argb' => 'FFFFFFFF'））。


画像挿入

めったにありませんが、レポートに画像を挿入すると便利です。 ロゴ、図などを使用できます。 動作するには、次のメソッドが必要です。


画像挿入アルゴリズムを示すコードを以下に示します：


 ... $sheet->getColumnDimension('B')->setWidth(40); $imagePath = dirname ( __FILE__ ) . '/excel.png'; if (file_exists($imagePath)) { $logo = new PHPExcel_Worksheet_Drawing(); $logo->setPath($imagePath); $logo->setCoordinates("B2"); $logo->setOffsetX(0); $logo->setOffsetY(0); $sheet->getRowDimension(2)->setRowHeight(190); $logo->setWorksheet($sheet); } ...

以下は、画像が挿入されたレポートです。




フォント

次のオプションパラメータを含む配列は、 fontパラメータの値として指定されます。


下線スタイル
UNDERLINE_NONEいや
UNDERLINE_DOUBLE二重下線
UNDERLINE_SINGLE一重下線
フォントの設定を指定する例：
 array( 'name' => 'Arial', 'size' => 12, 'bold' => true, 'italic' => false, 'underline' => PHPExcel_Style_Font::UNDERLINE_DOUBLE, 'strike' => false, 'superScript' => false, 'subScript' => false, 'color' => array( 'rgb' => '808080' ) );

または、次の方法を使用します。

$ PHPExcel_Style-> getFont（）-> setName（ 'Arial'）;

$ PHPExcel_Style-> getFont（）-> setBold（true）;

$ PHPExcel_Style-> getFont（）-> setItalic（false）;

$ PHPExcel_Style-> getFont（）-> setSuperScript（false）;

$ PHPExcel_Style-> getFont（）-> setSubScript（false）;

$ PHPExcel_Style-> getFont（）-> setUnderline（PHPExcel_Style_Font :: UNDERLINE_DOUBLE）;

$ PHPExcel_Style-> getFont（）-> setStrikethrough（false）;

$ PHPExcel_Style-> getFont（）-> getColor（）-> applyFromArray（array（ 'rgb' => '808080'））;

$ PHPExcel_Style-> getFont（）-> setSize（12）。


フレーム

配列は、次のオプションパラメータを含むbordersパラメータの値として指定されます。


線のスタイル
BORDER_NONEいや
BORDER_DASHDOTドットが点在
BORDER_DASHDOTDOT2つのドットが点在
BORDER_DASHED点線
BORDER_DOTTEDポイント
BORDER_DOUBLEダブル
BORDER_HAIRヘアライン
BORDER_MEDIUM平均的
BORDER_MEDIUMDASHDOTドットが点在
BORDER_MEDIUMDASHDOTDOT2つの点を持つ太い点線
BORDER_MEDIUMDASHED太い点線
BORDER_SLANTDASHDOT点線点線
BORDER_THICK厚くなった
BORDER_THIN薄い
フレームの設定を指定する例：
 array( 'bottom' => array( 'style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'color' => array( ' rgb' => '808080' ) ), 'top' => array( 'style' => PHPExcel_Style_Border::BORDER_DASHDOT, 'color' => array( 'rgb' => '808080' ) ) );

次の方法を使用することもできます。

$ PHPExcel_Style-> getBorders（）-> getLeft（）-> applyFromArray（array（ 'style' => PHPExcel_Style_Border :: BORDER_DASHDOT、 'color' => array（ 'rgb' => '808080'）））;

$ PHPExcel_Style-> getBorders（）-> getRight（）-> applyFromArray（array（ 'style' => PHPExcel_Style_Border :: BORDER_DASHDOT、 'color' => array（ 'rgb' => '808080'）））;

$ PHPExcel_Style-> getBorders（）-> getTop（）-> applyFromArray（array（ 'style' => PHPExcel_Style_Border :: BORDER_DASHDOT、 'color' => array（ 'rgb' => '808080'）））;

$ PHPExcel_Style-> getBorders（）-> getBottom（）-> applyFromArray（array（ 'style' => PHPExcel_Style_Border :: BORDER_DASHDOT、 'color' => array（ 'rgb' => '808080'）））;

$ PHPExcel_Style-> getBorders（）-> getDiagonal（）-> applyFromArray（array（ 'style' => PHPExcel_Style_Border :: BORDER_DASHDOT、 'color' => array（ 'rgb' => '808080'）））;

$ PHPExcel_Style-> getBorders（）-> setDiagonalDirection（array（ 'style' => PHPExcel_Style_Border :: BORDER_DASHDOT、 'color' => array（ 'rgb' => '808080'）））


アライメント

アラインメントパラメータ値は、4つのオプションパラメータを入力として受け入れる配列です。


水平方向の配置
HORIZONTAL_GENERALメイン
HORIZONTAL_LEFT左
HORIZONTAL_RIGHT右へ
HORIZONTAL_CENTER中央に
HORIZONTAL_CENTER_CONTINUOUS選択中心
HORIZONTAL_JUSTIFY幅で
垂直方向の配置
VERTICAL_BOTTOM下端に沿って
VERTICAL_TOP上端
VERTICAL_CENTER中央に
VERTICAL_JUSTIFY高さで
整列スタイル設定の例：
 array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER, 'rotation' => 0, 'wrap' => true, 'shrinkToFit' => false, 'indent' => 5 )

または、次の方法を使用します。

$ PHPExcel_Style-> getAlignment（）-> setHorizo​​ntal（PHPExcel_Style_Alignment :: HORIZONTAL_CENTER）;

$ PHPExcel_Style-> getAlignment（）-> setVertical（PHPExcel_Style_Alignment :: VERTICAL_JUSTIFY）;

$ PHPExcel_Style-> getAlignment（）-> setTextRotation（10）;

$ PHPExcel_Style-> getAlignment（）-> setWrapText（true）;

$ PHPExcel_Style-> getAlignment（）-> setShrinkToFit（false）;

$ PHPExcel_Style-> getAlignment（）-> setIndent（5）。


データ表示形式

numberformatパラメーターは、1つのパラメーター（ code-セルデータ形式）のみを含む配列です。


可能な形式のリスト
FORMAT_GENERAL全般
FORMAT_TEXT@
FORMAT_NUMBER0
FORMAT_NUMBER_000.00
FORMAT_NUMBER_COMMA_SEPARATED1＃、## 0.00
FORMAT_NUMBER_COMMA_SEPARATED2＃、## 0.00_-
FORMAT_PERCENTAGE0％
FORMAT_PERCENTAGE_000.00％
FORMAT_DATE_YYYYMMDD2yyyy-mm-dd
FORMAT_DATE_YYYYMMDDyy-mm-dd
FORMAT_DATE_DDMMYYYYdd / mm / yy
FORMAT_DATE_DMYSLASHd / m / y
FORMAT_DATE_DMYMINUSdmy
FORMAT_DATE_DMMINUSdm
FORMAT_DATE_MYMINUS私の
FORMAT_DATE_XLSX14mm-dd-yy
FORMAT_DATE_XLSX15d-mmm-yy
FORMAT_DATE_XLSX16d-mmm
FORMAT_DATE_XLSX17mmm-yy
FORMAT_DATE_XLSX22m / d / yy h：mm
FORMAT_DATE_DATETIMEd / m / yh：mm
FORMAT_DATE_TIME1h：mm AM / PM
FORMAT_DATE_TIME2h：mm：ss AM / PM
FORMAT_DATE_TIME3h：mm
FORMAT_DATE_TIME4h：mm：ss
FORMAT_DATE_TIME5mm：ss
FORMAT_DATE_TIME6h：mm：ss
FORMAT_DATE_TIME7i：sS
FORMAT_DATE_TIME8h：mm：ss
FORMAT_DATE_YYYYMMDDSLASHyy / mm / dd; @
FORMAT_CURRENCY_USD_SIMPLE"$"＃、## 0.00 _-; @
FORMAT_CURRENCY_USD$＃、## 0_-
FORMAT_CURRENCY_EUR_SIMPLE[$ EUR]＃、## 0.00_-
セルデータ形式の設定例：
 array( 'code' => PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE );

または、次の方法を使用できます。

$ PHPExcel_Style-> getNumberFormat（）-> setFormatCode（PHPExcel_Style_NumberFormat :: FORMAT_CURRENCY_EUR_SIMPLE）;


セル保護

保護パラメーターの値は、2つのオプションパラメーターを含む配列です。


セル保護のパラメーターの設定例：
 array( 'locked' => true, 'hidden' => false );

または、次の方法を使用します。


$ PHPExcel_Style-> getProtection（）-> setLocked（true）;

$ PHPExcel_Style-> getProtection（）-> setHidden（false）;


これで、スタイル設定と各スタイルに存在するパラメーターがわかりました。 ここで、表のセルにスタイルを適用しますが、これは3つの方法で行います。 最初の方法は、設定の配列を作成することです。設定の配列は、 PHPExcel_StyleクラスのapplyFromArrayメソッドにパラメーターとして渡します。


 $style = array( 'font' => array( 'name' => 'Arial', ), 'fill' => array( 'type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array ( 'rgb' => 'C2FABD' ) ), 'alignment' => array ( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER ) );

次に、作成したスタイルをExcelセルに適用します。

 $sheet->getStyleByColumnAndRow($i - 2, $j)->applyFromArray($style);

ここで、同じスタイルを適用しますが、異なる手法を使用します。


 //  $sheet->getStyleByColumnAndRow($i - 2, $j)->getAlignment()->setHorizontal( PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //   $sheet->getStyleByColumnAndRow($i - 2, $j)->getFont()->setName('Arial'); //   $sheet->getStyleByColumnAndRow($i - 2, $j)->getFill()-> setFillType(PHPExcel_Style_Fill::FILL_SOLID); $sheet->getStyleByColumnAndRow($i - 2, $j)->getFill()-> getStartColor()->applyFromArray(array('rgb' => 'C2FABD'));

取得したものは次のとおりです。



特定のセルのスタイルデータを取得するには、PHPExcel_Styleクラスのインスタンスを返す次のメソッドのいずれかを使用する必要があります。



次に、スタイルを複製してセルをスタイル設定する3番目の方法を検討します。 使用例を以下に示します（特定のスタイルがセル「B2」に適用され、セル「F2：F10」の範囲に対してそれを複製することを想定しています）：

 $sheet->duplicateStyle($sheet->getStyle('B2'), 'F2:F10');

コメントを追加

セルにコメントを追加する機会を誰かが取る機会は少ないと思いますが、これは純粋に私の個人的な意見ですが、そのような機会があります。 以下の例からわかるように、セルへのコメントの追加は非常に簡単です。


 ... //   $fBold = array('name' => 'Tahoma', 'size' => 10, 'bold' => true); $fNormal = array('name' => 'Tahoma', 'size' => 10); $richText = $sheet->getComment('B2')->getText(); $richText->createTextRun("Lorem ipsum ")->getFont()-> applyFromArray($fNormal); $richText->createTextRun("dolor sit")->getFont()-> applyFromArray($fBold); $richText->createTextRun(" amet consectetuer")->getFont()-> applyFromArray($fNormal); //    $sheet->getComment('B2')->setWidth('250'); //    $sheet->getComment('B2')->setHeight('25'); ...

createTextRun（）メソッドを再度呼び出すと、新しいコメントが既存のコメントに追加されますが、置き換えられないことに注意してください。 このメソッドは、 PHPExcel_RichText_Runクラスのオブジェクトを返すことに注意してください。このクラスには、フォントパラメータを設定および受信するメソッドがあります。


以下にコメントを示します。




リンク挿入

以下の例からわかるように、セルにリンクを挿入しても問題は発生しません。


 ... //   - $sheet->getCell('A2')->getHyperlink()->setUrl('http://www.phpexcel.net'); //       Sheet2 $sheet->getCell('A2')->getHyperlink()->setUrl("sheet://'Sheet2'!D5"); ...

リンクの形式でも使用できます。たとえば、メールアドレス：mailto：example@mail.com。



Excelからのデータの読み取り

レポートを生成し、それらにスタイルを適用することは確かに素晴らしいです。 しかし、PHPExcelライブラリの可能性はこれで終わりではありません。 さて、彼女が他に何ができるか見てみましょう。 また、* .xls / * .xlsxファイルからデータを読み取ることもできます。

PHPExcelライブラリを使用すると、次の形式を読み取ることができます。


作業には、2つのクラスのオブジェクトが必要です。


デモンストレーションのために、車に関する情報を含むテーブルからデータを出力します。



ファイルの読み取りの例を以下に示します。


 require_once ('PHPExcel/IOFactory.php'); //   $xls = PHPExcel_IOFactory::load('xls.xls'); //     $xls->setActiveSheetIndex(0); //    $sheet = $xls->getActiveSheet();

最初のオプション

 ... echo "<table>"; //        $rowIterator = $sheet->getRowIterator(); foreach ($rowIterator as $row) { //          $cellIterator = $row->getCellIterator(); echo "<tr>"; foreach ($cellIterator as $cell) { echo "<td>" . $cell->getCalculatedValue() . "</td>"; } echo "</tr>"; } echo "</table>";

2番目のオプション

 ... echo "<table>"; for ($i = 1; $i <= $sheet->getHighestRow(); $i++) { echo "<tr>"; $nColumn = PHPExcel_Cell::columnIndexFromString( $sheet->getHighestColumn()); for ($j = 0; $j < $nColumn; $j++) { $value = $sheet->getCellByColumnAndRow($j, $i)->getValue(); echo "<td>$value</td>"; } echo "</tr>"; } echo "</table>";


最初のオプションでは、反復子を使用してセルからデータを読み取ります。 2番目の方法では、インデックスのアドレス指定を使用して、シートのセルにアクセスしてデータを取得します。 行と列の数に関するデータを取得するには、 PHPExcel_Worksheetクラスの次のメソッドを使用します。


また、 PHPExcel_Cellクラスの一部であるcolumnIndexFromStringメソッドの助けがなければ実行できません。 このメソッドを使用すると、シンボル表現によって列のインデックスを決定できます。

デモ機能を使用して、Excel形式のファイル形式でレポートを生成および読み取ることができます。 また、スタイルを操作するためのほぼすべての可能な方法が示されました。

Source: https://habr.com/ru/post/J245233/

More articles:


All Articles