目次
こういうことってないですか？
webでの管理システムを設計するときに「CSVでインポートやエクスポート機能を付けてよ」って言われる事があります。インポートに関しては一括登録や一括削除や更新など割とリスクが大きいのですが、エクスポートはあったりします。
CSVなので本来はカンマ区切りで並べてtext/csvとかで吐き出せば簡単なのですが、下記のような不具合が発生する可能性があります。
- データの文字列にカンマが入ってるので、ズレる
- 上記を防ぐためにダブルクォートで文字列を囲うも文字列にダブルクォートが入っている
- 電話番号ハイフンの場合「09012345678」がExcelで開くと「9012345678」と数値になる
ただこれはオペレーション上の問題であり、正直システムとしてはどうしようもない部分なのも事実です。
となると･･･
「最初からExcelファイルで処理すれば良いのでは？」
って事でPHPを使ってExcelファイルを操作する方法をまとめました。
なお、ここでいうExcelファイルですが、Excel97-2003形式の「.xls」ではなくExcel2007以降の「.xlsx」形式のみ対応となります。古いファイルは一度.xlsx形式に変換してください。
インストール方法
みんな大好きComposerを使います。ちなみにインストールするライブラリは「PhpSpreadsheet」というものを使います。
「レンタルサーバーだとサーバーからインストールできないよー」って場合はローカルPCにComposerをインストールしてそこからインストールしたvendorフォルダをアップしましょう！
|
1 |
composer require phpoffice/phpspreadsheet |
これにより、PhpSpreadsheetがプロジェクトに追加されます。
PHPでライブラリの呼び出し方
これも一般的なComposerライブラリを読み込んで使うライブラリだけ読み込む形です|
1 2 3 4 5 6 7 8 |
<?php require 'vendor/autoload.php'; // Composerでライブラリを読み込む use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; // 新しいスプレッドシートオブジェクトを作成 $spreadsheet = new Spreadsheet(); |
インストールがしっかりとできていれば、スムーズに行きます。
Excelファイルのエクスポート方法
それではExcelファイルのエクスポートを行います。基本的にはCSVファイルをエクスポートする感じで、連想配列に値を放り込んでそれをライブラリを使って出力します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
<?php ini_set('display_errors', 1); require '../vendor/autoload.php'; // Composerでライブラリを読み込む use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\Style\Fill; $data = array( ['id', '氏名','電話番号',"フリーテキスト"], ); $mysqli = new mysqli('localhost', '*****', '', 'test'); if ($mysqli->connect_error) { echo $mysqli->connect_error; exit(); } else { // 完成済みのSELECT文を実行する $sql = "SELECT id, 氏名, created_at FROM 日本語データテーブル "; if ($result = $mysqli->query($sql)) { // 連想配列を取得 while ($row = $result->fetch_assoc()) { $data[] = array( $row["id"], $row['氏名'], "09012345678", "赤,青,緑", ); } // 結果セットを閉じる $result->close(); } } // 新しいスプレッドシートオブジェクトを作成 $spreadsheet = new Spreadsheet(); // アクティブなシートを取得 $sheet = $spreadsheet->getActiveSheet(); $sheet->fromArray($data, null, 'A1'); // ヘッダー行に灰色の背景色を設定 $sheet->getStyle('A1:D1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFDDDDDD'); // セルの横幅をテキストに合わせて自動調整 $sheet->getColumnDimension('B')->setAutoSize(true); $sheet->getColumnDimension('C')->setAutoSize(true); $sheet->getColumnDimension('D')->setAutoSize(true); // カーソルをA1セルに設定 $sheet->setSelectedCell('A1'); // サーバーに保存するファイル名 $filePath = 'example.xlsx'; // Excelファイルにデータを書き出し $writer = new Xlsx($spreadsheet); $writer->save('example.xlsx'); |
細かく説明していきます。
今回はMySQLのデータをExcelで出力してヘッダーのセルの色を灰色にするという感じです。
MySQL周りについては説明は割愛します。（SELECTで全件取得しているだけです）
|
1 2 3 |
use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\Style\Fill; |
上の方でライブラリの読み込みを行いましたが、今回はセルの装飾を行うので1行追加してます。
|
1 2 3 |
$data = array( ['id', '氏名','電話番号',"フリーテキスト"], ); |
出力するデータを連想配列$dataに入れてます。
1行名はヘッダー行にしたいのでここに列のタイトルを入れています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
// 完成済みのSELECT文を実行する $sql = "SELECT id, 氏名, created_at FROM 日本語データテーブル "; if ($result = $mysqli->query($sql)) { // 連想配列を取得 while ($row = $result->fetch_assoc()) { $data[] = array( $row["id"], $row['氏名'], "09012345678", "赤,青,緑", ); } // 結果セットを閉じる $result->close(); } |
ここはMySQLからデータを入れて連想配列に流し込んでます。
「電話番号がハイフンなしで入れてるよ」「文字列の中にカンマがあるよ」というのをわかりやすくするため固定で入れてます。
|
1 2 3 4 5 6 7 |
// 新しいスプレッドシートオブジェクトを作成 $spreadsheet = new Spreadsheet(); // アクティブなシートを取得 $sheet = $spreadsheet->getActiveSheet(); $sheet->fromArray($data, null, 'A1'); |
fromArray()はセルA1から連想配列を展開するよってことです。
これで自動的にセルの中に文字列を入れてくれます。
|
1 2 |
// ヘッダー行に灰色の背景色を設定 $sheet->getStyle('A1:D1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFDDDDDD'); |
チェインしてますが、ヘッダー行になるA1からD1までの背景色をFFDDDDDD（灰色）にしますよというのを1行で記述してます
|
1 2 3 4 5 6 7 |
// セルの横幅をテキストに合わせて自動調整 $sheet->getColumnDimension('B')->setAutoSize(true); $sheet->getColumnDimension('C')->setAutoSize(true); $sheet->getColumnDimension('D')->setAutoSize(true); // カーソルをA1セルに設定 $sheet->setSelectedCell('A1'); |
セルの横幅は初期値なので、これを最後にすることで、横幅を自動調整してくれます。
ただ、内容によっては見栄えが悪くなる可能性があるのでそこは要調整です。
最後にカーソルの位置をA1に指定します。
|
1 2 3 4 5 6 |
// サーバーに保存するファイル名 $filePath = 'example.xlsx'; // Excelファイルにデータを書き出し $writer = new Xlsx($spreadsheet); $writer->save($filePath); |
Excelファイルに書き出しです。
これはサーバーに保存してますが、ダウンロードさせたい場合はreadFileで吐き出せばダウンロードできます。
これだけで簡単にExcelファイルで出力できます！
電話番号もちゃんと文字列として処理してくれるので、先頭の0抜けがありません！
今後はExcel出力でいいのではと言う気がします。
ただ出力したCSVファイルを別のシステムでインポートするとかだとCSVはマズいので、CSV出力しましょう。
ちなみに･･･
PhpSpreadsheetでもCSV出力が可能です。先ほどのソースを少し改造してみて
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?php ～ 先ほどの処理～ $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); // データをセルに書き込む $sheet->fromArray($data, null, 'A1'); // CSVファイルにデータを書き出し $writer = new Csv($spreadsheet); $writer->setDelimiter(','); $writer->setEnclosure('"'); $writer->setLineEnding("\r\n"); $writer->setUseBOM(true); $csvFilePath = 'example.csv'; $writer->save($csvFilePath); |
これでできますね！
カンマ区切りでエンクロージャはダブルクオートで改行ありとか細かなところまで設定できて最高です！
Excelファイルのインポート方法
今度は逆にExcelファイルを読み込んでテーブルにインサートする方法をまとめます。HTMLからPOSTでExcelファイルをサーバーに保存するなどは今回は実装しません。
元々サーバーにあるExcelファイルをインポートしてみます
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
require 'vendor/autoload.php'; // Composerでライブラリを読み込む use PhpOffice\PhpSpreadsheet\IOFactory; // Excelファイルを読み込む $spreadsheet = IOFactory::load('example.xlsx'); // 保存しているExcelファイルのパスを指定 // Excelファイルからデータを取得 $worksheet = $spreadsheet->getActiveSheet(); $data = $worksheet->toArray(); // データベースに接続 $mysqli = new mysqli('localhost', '*****', '******', 'test'); // データベース接続情報を設定 if ($mysqli->connect_error) { die('データベース接続エラー: ' . $mysqli->connect_error); } // ヘッダー行を取得 $header = array_shift($data); // データをデータベースにINSERT foreach ($data as $row) { $rowData = array_combine($header, $row); // ヘッダーとデータを組み合わせて連想配列を作成 $id = $mysqli->real_escape_string($rowData['id']); $name = $mysqli->real_escape_string($rowData['氏名']); // INSERT文を実行 $sql = "INSERT INTO 日本語データテーブル (id, 氏名) VALUES ('$id', '$name')"; if ($mysqli->query($sql) !== TRUE) { echo 'INSERTエラー: ' . $mysqli->error; } } // データベース接続を閉じる $mysqli->close(); echo 'データベースへのデータINSERTが完了しました。'; |
CSVよりもシンプルにインポートすることができます。
|
1 2 3 4 5 6 |
// Excelファイルを読み込む $spreadsheet = IOFactory::load('example.xlsx'); // あなたのExcelファイルのパスを指定 // Excelファイルからデータを取得 $worksheet = $spreadsheet->getActiveSheet(); $data = $worksheet->toArray(); |
実質的にこれだけでできるのは楽すぎますね。
バリデーションチェックだけはしっかりと行いましょう！
これを組み合わせると？？？
インポートとエクスポートを組み合わせると、例えば何かの書類のテンプレートをExcelで作成し、そこにセルを指定して文字を入力し、PDFで出力することも可能かなと思います。次回はその形でExcelで作ったPDFファイルの作成を紹介できればと思います。
