PhpSpreadsheet Excelを読み込んで、ごにょごにょしたい基本型

基本型

下記をもとにクラスを作ってやれば良さげ。

<?php
require 'vendor/autoload.php';

// 使うファイルをuseで設定していく
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;

if ( !defined('DS') ) {
  define('DS', DIRECTORY_SEPARATOR);
}

// 各PATHや対象にしたいシート名
$use_file_path    = __DIR__.DS.'hello world.xlsx';
$use_excel_sheet  = 'Worksheet';
$output_file_path = __DIR__.DS.'add_img.xlsx';

// Excelファイルを読み込み、シートを指定する
$reader = new XlsxReader();
$excel = $reader->load($use_file_path);
$sheet = $excel->getSheetByName($use_excel_sheet);

// ▼ごにょごにょしたい処理を追加
～ 略 ～
// ▲ごにょごにょしたい処理を追加

// サーバに出力
$writer = new XlsxWriter($excel);
$writer->save($output_file_path);

// // サーバに出力じゃなくダウンロードさせたいとき
// $dl_file_name = 'dl_hello_world.xlsx';
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;');
// header('Content-Disposition: attachment; filename="'.$dl_file_name.'"');
// header('Cache-Control: max-age=0');
// $writer = new XlsxWriter($excel);
// $writer->save('php://output');
// exit;

ごにょごにょしたい処理

上記の「～ 略 ～」ってトコに下記内容を記載

セルに値を追加

$sheet->setCellValue('A2', 'ほげほげ');

結合したセルに値を追加

一つ目のセルを指定すれば大丈夫っぽい

▼「C2」と「C3」を結合している場合

$sheet->setCellValue('C2', 'ほげほげ');

▼「C2」と「D2」を結合している場合

$sheet->setCellValue('C2', 'ほげほげ');

数式が絡むとき

PHPでごにょごにょしたからと言って数式が消えるわけではない。

例：Excelファイルの「C1」に「=A1*B1」という書式があった場合
→A1、B1にそれぞれ値をセットすると「C1」に「=A1*B1」の計算結果が入る

画像の追加

$img_path = '〇〇〇/〇〇〇.jpg';
(new Drawing())
  ->setPath($img_path)           // 画像のパス
  ->setCoordinates('A1')         // セルの指定（※大きい画像はセルをはみ出す）
  ->setWidth(200)                // 画像の幅（px）
  ->setHeight(100)               // 画像の高さ（px）
  ->setResizeProportional(false) // false:アスペクト比を維持
  ->setWorksheet($sheet);        // 対象のシート

▼別途、最初にuseが必要

use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

まとめて流し込む

$start_cell = 'A3';
$add_data = array(
  array(1, 2, 3),        // A3, B3, C3に格納される値
  array('hoge1'),        // A4に格納される値
  array('foo1', 'foo2'), // A5, B5に格納される値
);
$excel->getActiveSheet()->fromArray($add_data, NULL, $start_cell, true);

※第二引数：特定の値があったときに書き込みを拒否？

シートの複製

$ori_excel_sheet  = 'Worksheet';
$new_excel_sheet  = 'ほげ';

// シートのコピーをとり、シート名を設定
$cloned_excel_sheet = clone $excel->getSheetByName($ori_excel_sheet);
$cloned_excel_sheet->setTitle($new_excel_sheet);

// コピーしたシートを追加
$excel->addSheet($cloned_excel_sheet);

// 新しいシートを指定して、値を追加
$sheet = $excel->getSheetByName($new_excel_sheet);
$sheet->setCellValue('A2', 'ももたろう');

※「$excel->addSheet()」のトコで「addSheet()」を「addExternalSheet()」にすれば別のExcelファイルにコピーしたシートを複製できるっぽい（未調査）

書式指定

▼「文字列：001」を「数値：1」として入力

$sheet->setCellValueExplicit('A2', '001', DataType::TYPE_NUMERIC);

▼別途必要

use PhpOffice\PhpSpreadsheet\Cell\DataType;

▼指定できる内容と指定方法
・文字：TYPE_STRING
・数値：TYPE_NUMERIC
・true / false：TYPE_BOOL
・null：TYPE_NULL
・式：TYPE_FORMULA

「true / false：TYPE_BOOL」について

「0（数値 or 文字）」なら「FALSE」が入力される。
「0（数値 or 文字）」じゃないなら「TRUE」が入力される。
※nullや空白のときは、未調査。

「式：TYPE_FORMULA」について

「=」は必要。

・「A2+A3」としたとき→「=2+A3」がExcelに入力される
・「=A2+A3」としたとき→「=A2+A3」がExcelに入力される
※何か別の指定 or 設定の問題？

改行を許可

$cell = 'A1';
$val = "ほげ\nもげ\n";
$sheet->getStyle($cell)->getAlignment()->setWrapText(true);
$sheet->setCellValue($cell, $val);

範囲指定

最初（左上）のセルと最後（右下）のセルの「:」でつなぐ

$cell = 'A1:C3';

日時

$pValue = '2020-07-22 09:22:00';
$cell = 'A1';

// 日付の書式
$date_format = 'yyyy-mm-dd hh:mm:ss';

$conv_value = Date::PHPToExcel($pValue);
$sheet->getStyle($cell)->getNumberFormat()->setFormatCode($date_format);
$sheet->setCellValue($cell, $conv_value);

▼別途必要

use PhpOffice\PhpSpreadsheet\Shared\Date;

郵便番号、カンマ区切りの数字

$pValue = '1234567';
$cell = 'A1';

// 郵便番号：「$format = '000-0000';」（「$pValue=1234567」は「123-4567」になる）
// カンマあり：「$format = '#,##0';」
$format = '000-0000';

$sheet->getStyle($cell)->getNumberFormat()->setFormatCode($format);
$sheet->setCellValueExplicit($cell, $pValue, DataType::TYPE_NUMERIC);

▼別途必要

use PhpOffice\PhpSpreadsheet\Cell\DataType;

値の取得

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

値が式の場合

たとえば・・・「セル：C1」に「=A1+B2」が入っているとき。

▼計算式である「=A1+B2」を取得する

$cell = 'C1';
$val = $sheet->getCell($cell)->getValue();

▼計算式である「=A1+B2」の計算結果を取得する

$cell = 'C1';
$val = $sheet->getCell($cell)->getCalculatedValue();

getCalculatedValue()

計算式を取得することがないなら常に「getCalculatedValue()」を使うのも良さげな気がする

行・列の追加・削除

行・列の追加

できるのは、指定した行（列）の「前」に追加。「後」に追加はできないっぽい。

・insertNewColumnBefore()
・insertNewRowBefore()

▼「列：B」の前に「3列」追加

$sheet->insertNewColumnBefore('B', 3);

▼「行：２」の前に「４行」追加

$sheet->insertNewRowBefore(2, 4);

行・列の削除

・removeRow()
・removeColumn()

コピーした行・列の追加

無い。

元データをコピーして、空行を追加して、コピーした元データをペーストする感じ。

セルの追加

無いっぽい（未調査）