2020年04月14日 に更新

PhpSpreadsheetでExcelを操作する全28実例！

こんにちは❗フリーランス・コンサルタント＆エンジニアの 九保すこひ です。

さてさて、ウェブ開発の分野は特に変化のスピードが速い業界ですので、少し前まで使っていた技術がたった２、３年で古くなってしまうことも結構ザラにあったりします。

そのため普段から新しい情報は常にチェックするようにしていますが、それでも全てをカバーすることは難しいですよね。

そして、今回紹介する「PhpSpreadsheet」もそのひとつで、このパッケージは少し前まではPhpExcelという名前で公開されていました。しかし、どうやらPhpExcelは2017年に非推奨となり、現在はPhpSpreadsheetとしてリニューアルされています。（公式のGitHubによると2015年にリリースされて2017年に非推奨になっています。たった２年だったんですね・・・😅）

ということで、今回はPHPでExcelを作成や読み込みなどができるPhpSpreadsheetの使い方を紹介したいと思います。

最後に今回実際に使ったソースコードをダウンロードできます！
ぜひ皆さんのお役に立てると嬉しいです。

【追記：2020.4.14】
この記事は、2019年6月21日に投稿された内容に加筆修正されたものです。

パッケージをインストールする

以下のコマンドでパッケージをインストールしましょう。

composer require phpoffice/phpspreadsheet

※ 私の環境はLaravelですが、PhpSpreadsheetはPHP全般で利用できます。

PhpSpreadsheetでExcelファイルを作成する

基本的な使い方

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// ここで $sheet を通してセルなどを操作します。

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->save('/PATH/TO/YOUR/PROJECT/test.xlsx');

セルの入力

セルに文字や数字を入力するには、setCellValue()を使います。

$sheet->setCellValue('A1', 'テスト文字列');
$sheet->setCellValue('B1', 100);
$sheet->setCellValue('C1', true);
$sheet->setCellValue('D1', null);   // 空白

※ setCellValue()を使った場合、自動的に書式が決定されます。書式を明確に決定したい場合は、次のsetCellValueExplicit()を使ってください。

書式を指定してセルに入力する

例えば、「数字なんだけど文字列としてセルに入力したい」という場合です。
その場合、setCellValueExplicit()を使います。

$sheet->setCellValueExplicit(
    'E1',
    '012345',
    \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
);

データタイプは主に他にも以下のようなものがあります。

  • 文字列　・・・　DataType::TYPE_STRING
  • 数字　・・・　DataType::TYPE_NUMERIC
  • true / false　・・・　DataType::TYPE_BOOL
  • null　・・・　DataType::TYPE_NULL
  • 式　・・・　DataType::TYPE_FORMULA

改行を含むテキストを入力

例えば、以下のように複数行のテキストを入力する場合です。

1行目
2行目
3行目

この場合、次のようにしてください。

$text = "1行目\n2行目\n3行目";
$sheet->getStyle('A1')
    ->getAlignment()
    ->setWrapText(true);
$sheet->setCellValue('A1', $text);

郵便番号として入力

例えば、「元の郵便番号データは数字だけなんだけど、ハイフンをいれたい」という場合です。

$sheet->getStyle('F1')
    ->getNumberFormat()
    ->setFormatCode('000-0000');
$sheet->setCellValue('F1', 1310045);

※ 000-0000の部分がフォーマットになります。

日時として入力

セルに日時を入力したい場合は、以下のように表示フォーマットとDate::PHPToExcelを利用して実装します。

$sheet->getStyle('G1')
    ->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd h:mm:ss');
$datetime = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel('2019/01/01 01:02:03');
$sheet->setCellValue('G1', $datetime);

リンクを入力

例えば、クリックしたらブラウザでURLを開くリンクをつくる場合です。

$sheet->setCellValue('A1', 'リンク文字列');
$sheet->getCell('A1')
    ->getHyperlink()
    ->setUrl('http://example.com');

一気に入力

もちろん一個ずつセルにデータ入力してもいいですが、もしデータが大量にある場合は効率よく実行したいものです。そんな場合はfromArray()で一気にデータを追加するといいでしょう。

$data = [
    ['テスト1-1', 'テスト1-2', 'テスト1-3'],
    ['テスト2-1', 'テスト2-2', 'テスト2-3'],
    ['テスト3-1', 'テスト3-2', 'テスト3-3']
];
$exception_value = 'テスト2-2';   // この値のものは反映されない（全て反映するにはnullを指定）
$sheet->fromArray($data, $exception_value, 'A3');

※ この場合A3に「テスト1-1」が入力され、そこから右方向と下方向のセルにデータ入力されることになります。

式の入力

エクセルに用意されている様々な式を入力したい場合です。
次の例では、２つのセルの文字を結合して表示しています。

$sheet->setCellValue('A7', '=CONCATENATE(A1, " - ", B1)');

セルの結合

２つのセルを結合する場合です。
以下の例では、A9とC9を結合してひとつのセルにしています。

$sheet->mergeCells('A9:C9');
$sheet->setCellValue('A9', '結合されたセル');

改ページ

改ページを設定したい場合です。

$sheet->setBreak('A11', \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::BREAK_ROW);

ドロップダウンを作成する

選択肢を直接指定する場合

選択肢を直接指定する場合は次のようになります。

$sheet->setCellValue('A13', '選択肢1');
$validation = new DataValidation();
$validation->setFormula1('"選択肢1,選択肢2,選択肢3"');
$validation->setType('list');
$validation->setAllowBlank(true);
$validation->setShowDropDown(true);
$sheet->setDataValidation('A13', $validation);

※ setFomula1()内のダブルクォートがないとエラーが発生するので忘れないように気をつけてください。

選択肢を参照する場合

他のセルを参照して選択肢を指定する方法です。

$sheet->setCellValue('A15', '選択肢A');    //　ここが選択ボックス
$sheet->setCellValue('B16', '選択肢A');    // 参照する選択肢
$sheet->setCellValue('B17', '選択肢B');    // 参照する選択肢
$sheet->setCellValue('B18', '選択肢C');    // 参照する選択肢

$validation = new DataValidation();
$validation->setFormula1('$B$15:$B$18');
$validation->setType('list');
$validation->setAllowBlank(true);
$validation->setShowDropDown(true);
$sheet->setDataValidation('A15', $validation);

オートフィルタ

オートフィルタを設定したい場合です。

$sheet->setCellValue('A20', 'オートフィルタ');
$sheet->setCellValue('A21', '選択肢（１）');
$sheet->setCellValue('A22', '選択肢（２）');
$sheet->setCellValue('A23', '選択肢（３）');
$sheet->setAutoFilter('A20:A20');

枠線の表示、非表示

Excelではデフォルトでうっすらと枠線が表示されていますが、これを非表示にするには以下のようにします。

$sheet->setShowGridlines(false);

文字の色を指定する

例えば、文字の色を赤にしたい場合です。

$sheet->setCellValue('A25', '文字の色・赤');
$sheet->getStyle('A25')
    ->getFont()
    ->getColor()
    ->setARGB('FFFF0000');

※ 指定する色はARGBであることに注意してください。

背景色を指定する

例えば、セルの背景を赤にしたい場合です。

$sheet->setCellValue('A27', '背景色・赤');
$sheet->getStyle('A27')
    ->getFill()
    ->setFillType('solid')
    ->getStartColor()
    ->setARGB('FFFF0000');

枠線を指定する

セルに枠線をつけたい場合です。
太線と細線が指定できます

$sheet->setCellValue('A29', '枠線');

$borders = $sheet->getStyle('A29')->getBorders();
$borders
    ->getTop()
    ->setBorderStyle('thick');  // 太線
$borders
    ->getRight()
    ->setBorderStyle('thin');   // 細線
$borders
    ->getBottom()
    ->setBorderStyle('thick');  // 太線
$borders
    ->getLeft()
    ->setBorderStyle('thin');   // 細線

右寄せ、左寄せ、中央寄せ

$sheet->setCellValue('A31', '右寄せ');
$sheet->getStyle('A31')
    ->getAlignment()
    ->setHorizontal('right');
$sheet->setCellValue('B31', '左寄せ');
$sheet->getStyle('B31')
    ->getAlignment()
    ->setHorizontal('left');
$sheet->setCellValue('C31', '中央寄せ');
$sheet->getStyle('C31')
    ->getAlignment()
    ->setHorizontal('center');

文字を太字にする

$sheet->setCellValue('A33', '太字');

$sheet->getStyle('A33')
    ->getFont()
    ->setBold(true);

文字を斜体にする

$sheet->setCellValue('A35', 'ななめ文字');

$sheet->getStyle('A35')
    ->getFont()
    ->setItalic(true);

文字のサイズを指定する

$sheet->setCellValue('A37', '文字サイズを指定');

$sheet->getStyle('A37')
    ->getFont()
    ->setSize(8);

文字に下線をつける

$sheet->setCellValue('A39', '下線あり');

$sheet->getStyle('A39')
    ->getFont()
    ->setUnderline(true);

PhpSpreadsheetでExcelファイルを読み込む

基本的な使い方

以下の例では、Excelファイルを読み込んで全てのデータを配列に格納しています。

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$spreadsheet = $reader->load('/PATH/TO/YOUR/PROJECT/test2.xlsx');
$sheet = $spreadsheet->getActiveSheet();

$data = [];

foreach ($sheet->getRowIterator() as $i => $row) {

    $row_data = [];
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(FALSE);

    foreach ($cellIterator as $j => $cell) {

        $cell_id = $i .'-'. $j;
        $row_data[$cell_id] = $cell->getValue();

    }

    $data[$i] = $row_data;

}

print_r($data);

値の取得

現在のワークシートからセルのデータを取得したい場合の例になります。

通常の取得

echo $sheet->getCell('A1')->getValue();

式を計算した後のデータ取得

例えば、セルの中身が、=CONCATENATE(A1, " - ", B1) だった場合、結合した後の文字列を取得することになります。

echo $sheet->getCell('A7')->getCalculatedValue();

表示フォーマットで取得

例えば、日付などの表示フォーマットを指定していた場合、そのフォーマットに従ってデータを取得することができます。

echo $sheet->getCell('G1')->getFormattedValue();

配列で一気にデータ取得

範囲を指定して配列としてデータ取得する場合です。

$data = $sheet->rangeToArray('A3:C5');
print_r($data);

ソースコードをダウンロードする

今回実際に作成したソースコードを以下からダウンロードすることができます。

※ ただし、保存するパス、読み込むExcelファイルはご自身で設定する必要があります。（そのままだとおそらくエラーが表示されます）

PhpSpreadsheetでExcelを操作する

おわりに

今回はじめてPhpSpreadsheetを使ったわけですけど、私の記憶が正しければ前バージョンのPhpExcelよりコードがとても書きやすくなっているイメージです。直感的にコードの理解もしやすいんじゃないでしょうか。

また、ためしにMicrosoft Excel以外にもフリーソフトウェアのLibre OfficeのCalc、それからGoogle Spreadsheetでも表示してみましたがこれも問題もなく表示されました。

こっちの方の技術も進化しているということですね。

ということで、皆さんのウェブサイトにもExcel作成機能をつけてみてはいかがでしょうか。

ではでは〜！

開発のご依頼お待ちしております 😊✨
開発のご依頼はこちらから: お問い合わせ
どうぞよろしくお願いいたします！
このエントリーをはてなブックマークに追加       follow us in feedly  

開発効率を上げるための機材・まとめ

合わせて読みたい