VB.NET-TIPS などプログラミングについて

今回は PhpSpreadsheet で行う「ワークシート」の 「セル」への漢字（全角文字）の設定 について説明したいと思います。

• ワークシートの「セル」への漢字（全角文字）の設定の簡単なプログラム（エラー発生）
• エラー発生したソースの文字コードの確認
• ワークシートのデフォルトスタイルを使用したフォント設定

■ワークシートの「セル」への漢字（全角文字）の設定の簡単なプログラム（エラー発生）

ワークシートの setCellValue() メソッドで全角文字列を指定すれば問題無いであろうとも思って、以下の様なソースを実行しました。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// ワークシートオブジェクト
$objSheet = $objSpreadsheet->getActiveSheet();
// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'あいうえお漢字全角ＡＢＣＤＥ');

// [test5-1.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test5-1.xlsx');
exit();
?>

しかし、実際のエクセルファイル[test5-1.xlsx]を開くと以下の様な表示になります。 全角文字列がうまく反映していない様です。

■エラー発生したソースの文字コードの確認

上記のソースの文字コードを確認したら Shift-JIS コードになっていました。 これを UTF-8 に変更しプログラムを実行後、エクセルファイル（「test5-2.xlsx」としました）を開いてみると正しく文字が設定されました。

■ワークシートのデフォルトスタイルを使用したフォント設定

ワークシートの全てのセルに同じスタイルを設定したい場合に、デフォルトスタイルを設定します。

Spreadsheetオブジェクト のデフォルトスタイルを取得し（getDefaultStyle()メソッド）、 さらにそのスタイルからフォントオブジェクト取得します（getFont()メソッド）。
フォントオブジェクトの setName() メソッドでフォント名を設定し、 setSize() メソッドでフォントサイズを設定します。

セルの縦位置の設定には、デフォルトスタイルからアライメントオブジェクトを取得し setVertical() メソッドでセルの縦方向の設定をします。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// ワークシートオブジェクト
$objSheet = $objSpreadsheet->getActiveSheet();

// デフォルトスタイルオブジェクト取得
$objDefaultStyle = $objSpreadsheet->getDefaultStyle();
// フォントオブジェクト取得
$objFont = $objDefaultStyle->getFont();
// フォント名設定
$objFont->setName('ＭＳ Ｐゴシック');
// フォントサイズ設定
$objFont->setSize(12);
// アライメントオブジェクトでセルの縦方向の設定
$objDefaultStyle->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'あいうえお漢字全角ＡＢＣＤＥ');
// [A2]セルに[A1]と同じ文字列設定
$objSheet->setCellValue('A2', 'あいうえお漢字全角ＡＢＣＤＥ');
// [A3]セルに数値設定
$objSheet->setCellValue('A3', '123456789');
// [test5-3.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test5-3.xlsx');
exit();
?>

出力されたエクセルファイルを見てみると以下の様になります。

これは余談ですが、上記のソースでオブジェクトを変数で受けて処理していますが、以下の様に一気に書いてしまっても問題はありません。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// ワークシートオブジェクト
$objSheet = $objSpreadsheet->getActiveSheet();

// フォント名設定
$objSpreadsheet->getDefaultStyle()->getFont()->setName('ＭＳ Ｐゴシック');
// フォントサイズ設定
$objSpreadsheet->getDefaultStyle()->getFont()->setSize(12);
// アライメントオブジェクトでセルの縦方向の設定
$objSpreadsheet->getDefaultStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'あいうえお漢字全角ＡＢＣＤＥ');
// [A2]セルに[A1]と同じ文字列設定
$objSheet->setCellValue('A2', 'あいうえお漢字全角ＡＢＣＤＥ');
// [A3]セルに数値設定
$objSheet->setCellValue('A3', '123456789');
// [test5-3.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test5-3.xlsx');
exit();
?>

今回は PhpSpreadsheet で行う「ワークシート」の 「セル」へのアクセス について説明したいと思います。

• 座標によるセル値の設定
• 座標によるセル値の設定（日付・時刻設定）
• 座標によるセル値の設定（先行ゼロ付きの数値設定）
• 座標によるセル値の設定（配列データの設定）

■座標によるセル値の設定

座標によるセル値の設定は、ワークシートの setCellValue() メソッドを使用して行うことができます。
または、セルオブジェクトを取得してから、セルオブジェクトの setValue() メソッドを呼び出すこともできます。

以下のページと内容は同じですので、参照して下さい。
⇒PHP PhpSpreadsheet エクセルファイル出力の方法について

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();
// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'Spreadsheet');
// [A2]セルに数値設定
$objSheet->setCellValue('A2', 123.56);
// [A3]セルにBOOLEAN設定
$objSheet->setCellValue('A3', TRUE);
// [A4]セルに書式設定
$objSheet->setCellValue('A4', '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))');
// [A8]セルに getCell() でセルを取得し、setValue() で文字列設定
$objSheet->getCell('A8')->setValue('test data');

// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test.xlsx');
exit();
?>

エクセルは以下の7個のデータ型をサポートしています。

• string：文字列
• number：数値
• boolean：ブール値
• null　：ヌル
• formula：数式
• error　：エラー
• Inline(or rich-text)string：インライン、リッチテキスト

基本的には、ワークシートの setCellValue() メソッドまたは、セルの setValue() メソッドを呼び出すと、 Spreadsheet は PHP のnull、ブール値、浮動小数点数、または整数に対応する適切なデータ型を使用します。

メソッドに渡される文字列データは最も適切なデータ型にキャストされます。 数値文字列は数値にキャストされ ”=” で始まる文字列は数式に変換されます。 数値ではない文字列、または ”=” で始まっていない文字列は、文字列値として扱われます。

これらの「変換」はセルの「値バインダー」によって処理され、カスタムの「値バインダー」を記述して、これらの「変換」の動作を変更できます。

尚 PhpSpreadsheet は各種の形式の文字列を、適切な数値や datetimestamp値 に変換し、セルに設定します。
日付の様に見える値を含む文字列は、Excelのシリアル化されたdatetimestamp値に変換され、対応するマスクが適用されます。
以下のソースに例を示します。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();
// [A1]セルに数値文字列設定
$objSheet->setCellValue('A1', '1234.56');
// [A2]セルに数式文字列設定
$objSheet->setCellValue('A2', '=A1*100');
// [A3]セルに日付文字列設定
$objSheet->setCellValue('A3', '2020/05/10');
// [A4]セルに文字列設定
$objSheet->setCellValue('A4', 'ABCD123');
// [test3-1.xlsx]:XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test3-1.xlsx');
exit();
?>

■座標によるセル値の設定（日付・時刻設定）

日付および時刻の値は、エクセル内では（単純な浮動小数点値）のタイムスタンプとして保持され、数値フォーマットマスクを使用して、その値が表示されます。
よって、セルに日付を保存する場合は、正しい エクセルタイムスタンプ を計算し、数値形式マスクを設定する必要があります。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();
// システム時刻取得
$dateTimeNow = time();
// エクセルタイムスタンプ値に変換
$excelDateValue = Date::PHPToExcel($dateTimeNow);
// [A1]セルにエクセルタイムスタンプ値設定
$objSheet->setCellValue('A1', $excelDateValue);
// [A1]セルの数値フォーマットに日付時刻の設定(yyyy-mm-dd)
$objSheet->getStyle('A1')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD2);
// [A2]セルにエクセルタイムスタンプ値設定
$objSheet->setCellValue('A2', $excelDateValue);
// [A2]セルの数値フォーマットに日付時刻の設定(yy/mm/dd;@)
$objSheet->getStyle('A2')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);

// [test3-2.xlsx]:XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test3-2.xlsx');
exit();
?>

出力されたエクセルファイルを見てみると以下の様になります。

PhpOffice\PhpSpreadsheet\Style\NumberFormat は以下の様に定数が宣言されています。

    const FORMAT_GENERAL = 'General';

    const FORMAT_TEXT = '@';

    const FORMAT_NUMBER = '0';
    const FORMAT_NUMBER_00 = '0.00';
    const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
    const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';

    const FORMAT_PERCENTAGE = '0%';
    const FORMAT_PERCENTAGE_00 = '0.00%';

    const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd';
    const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd';
    const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy';
    const FORMAT_DATE_DMYSLASH = 'd/m/yy';
    const FORMAT_DATE_DMYMINUS = 'd-m-yy';
    const FORMAT_DATE_DMMINUS = 'd-m';
    const FORMAT_DATE_MYMINUS = 'm-yy';
    const FORMAT_DATE_XLSX14 = 'mm-dd-yy';
    const FORMAT_DATE_XLSX15 = 'd-mmm-yy';
    const FORMAT_DATE_XLSX16 = 'd-mmm';
    const FORMAT_DATE_XLSX17 = 'mmm-yy';
    const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm';
    const FORMAT_DATE_DATETIME = 'd/m/yy h:mm';
    const FORMAT_DATE_TIME1 = 'h:mm AM/PM';
    const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM';
    const FORMAT_DATE_TIME3 = 'h:mm';
    const FORMAT_DATE_TIME4 = 'h:mm:ss';
    const FORMAT_DATE_TIME5 = 'mm:ss';
    const FORMAT_DATE_TIME6 = 'h:mm:ss';
    const FORMAT_DATE_TIME7 = 'i:s.S';
    const FORMAT_DATE_TIME8 = 'h:mm:ss;@';
    const FORMAT_DATE_YYYYMMDDSLASH = 'yy/mm/dd;@';

    const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-';
    const FORMAT_CURRENCY_USD = '$#,##0_-';
    const FORMAT_CURRENCY_EUR_SIMPLE = '#,##0.00_-"€"';
    const FORMAT_CURRENCY_EUR = '#,##0_-"€"';
    const FORMAT_ACCOUNTING_USD = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)';
    const FORMAT_ACCOUNTING_EUR = '_("€"* #,##0.00_);_("€"* \(#,##0.00\);_("€"* "-"??_);_(@_)';

■座標によるセル値の設定（先行ゼロ付きの数値設定）

数値には先行ゼロがないため、先行ゼロのある数値（電話番号など）を設定しようとすると、値が数値にキャストされるため、これらは通常失われます。 そのため「09088881234」は 「9088881234」と表示されます。

PhpSpreadsheet にこの動作をさせない様な方法は2つあります。
1つは、数値に変換されないように、データ型を文字列として明示的に設定する方法です。
2つ目は、数値書式マスクを使用して、先行ゼロ付きの値を表示する方法です。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();

// [A1]セルにゼロが先頭に有る数値文字列を、文字列として設定
$objSheet->setCellValueExplicit('A1', "09088881234", DataType::TYPE_STRING);

// [A2]セルに数値設定
$objSheet->setCellValue('A2', 9088881234);
// [A2]セルの数値フォーマットに "00000000000" の設定
$objSheet->getStyle('A2')->getNumberFormat()->setFormatCode('00000000000');

// [A3]セルに数値設定
$objSheet->setCellValue('A3', 9088881234);
// [A3]セルの数値フォーマットに "0000-000-0000" の設定
$objSheet->getStyle('A3')->getNumberFormat()->setFormatCode('0000-000-0000');

// [test3-3.xlsx]:XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test3-3.xlsx');
exit();
?>

出力されたエクセルファイルを見てみると以下の様になります。

■座標によるセル値の設定（配列データの設定）

ワークシートの fromArray メソッドに配列データを渡すことにより、1回の呼び出しでセル範囲に設定することができます。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();

// 配列データ
$arrData = array(
    array(NULL, 2018, 2019, 2020),
    array('Q1',   10,   20,   25),
    array('Q2',   26,   33,   26),
    array('Q3',   32,   41,   59),
    array('Q4',   20,   12,    0),
);
// [A2]セルにから配列データ設定
$objSheet->fromArray(
    $arrData,  // 配列データ
    NULL,      // 配列データの中でセルに設定しないNULL値の指定
    'A2'       // 左上座標(デフォルト:"A1")
);

// [test3-4.xlsx]:XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test3-4.xlsx');
exit();
?>

出力されたエクセルファイル[test3-4.xlsx]を見てみると以下の様になります。

fromArray メソッドに2次元配列を渡すと、一連の行と列として扱われますが、1次元配列は単一の行として扱われます。
先ずは、横方向の1次元配列の設定を行い、その後で縦方向の設定を行います。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();

// 横方向の配列データ
$arrX = array('Value-1', 'Value-2', 'Value-3', 'Value-4');
$objSheet->fromArray(
    $arrX,      // 配列データ
    NULL,       // 配列データの中でセルに設定しないNULL値の指定
    'A1'        // 左上座標(デフォルト:"A1")
);

// 縦方向の配列データ
$arrY = array_chunk($arrX, 1);
$objSheet->fromArray(
    $arrY,      // 配列データ
    NULL,       // 配列データの中でセルに設定しないNULL値の指定
    'A3'        // 左上座標(デフォルト:"A3")
);

// [test3-5.xlsx]:XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test3-5.xlsx');
exit();
?>

出力されたエクセルファイル[test3-5.xlsx]を見てみると以下の様になります。

今回は Spreadsheet で行う 「ワークシートの扱い方」 について説明したいと思います。

• Spreadsheet オブジェクトの生成と ワークシート の取得
• 新規 ワークシート の追加処理（createSheet と addSheet）
• ワークシート のコピー処理（clone と addSheet、addExternalSheet ）
• ワークシート の削除処理（removeSheetByIndex）

■Spreadsheet オブジェクトの生成と ワークシート の取得

Spreadsheet オブジェクトの生成を行うと1個の ワークシート ができているので、その ワークシート・オブジェクト を取得し、 そのオブジェクトの各種メソッドでセルなどに処理を行います。

getActiveSheet メソッドは現在のアクティブなワークシート返しますが Spreadsheet オブジェクトを生成直後は1個しかないシートがアクティブとして返されます。

以下のページと内容はほぼ同じですので、参照して下さい。
⇒PHP PhpSpreadsheet エクセルファイル出力の方法について

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();
// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'Spreadsheet');
// [A2]セルに数値設定
$objSheet->setCellValue('A2', 123.56);
// [A3]セルにBOOLEAN設定
$objSheet->setCellValue('A3', TRUE);
// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test.xlsx');
exit();
?>

getSheet メソッドは引数にワークシートの Index 指定を指定します。
ワークシートの Index ではなく名前で指定するには getSheetByName メソッドを使用します。

// シート設定:ワークシートの Index 指定
$objSheet = $objBook->getSheet(0);

// シート設定:ワークシートの名前指定
$objSheet = $objBook->getSheetByName('Worksheet');

■新規 ワークシート の追加処理（createSheet と addSheet）

新規の ワークシート をブック(Spreadsheetオブジェクト)に追加する場合には createSheet と addSheet のメソッドを使います。
createSheet メソッドは引数の無い場合はシートの最後に追加されます。引数はシートの Index で指定された場合はその位置に挿入されます。
addSheet メソッドは指定されたシートオブジェクトを Index で指定された場合はその位置に挿入されます。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();

// シート追加
$objSpreadsheet->createSheet();
// シート追加:シート位置 Index 指定（Index = 0 :ブックの先頭）
$objSpreadsheet->createSheet(0);

// 新規ワークシートを生成
$objSheetMyData = new Worksheet($objSpreadsheet, 'MyData');
// [MyData]ワークシートをブックの先頭に追加
$objSpreadsheet->addSheet($objSheetMyData, 0);

// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test2.xlsx');
exit();
?>

出力されたエクセルファイルを見てみると以下の様になります。

このワークブックには必ず4個のワークシートが生成されています。
[Worksheet] は最初のPHPExcelオブジェクト作成後は必ず存在します。その後順番に以下の様になります。
(1) [Worksheet 1]はシートの最後に追加されます。
(2) [Worksheet 2]は先頭のシート（[Worksheet]）の前に追加されます。
(3) [MyData]は先頭のシート（[Worksheet 2]）の前に追加されます。

■ワークシート のコピー処理（clone と addSheet、addExternalSheet ）

ワークブックの中のコピーしたいワークシートのクローンを生成し、そのオブジェクトをワークブックに追加することでコピー処理となります。 ワークブックへの追加は addSheet メソッドを使用します。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();
// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'ABCDEFG');
// [A2]セルに数値設定
$objSheet->setCellValue('A2', 123.56);

// 0 番目のワークシート・オブジェクトのクローンを作成
$objCloneSheet = clone $objSpreadsheet->getSheet(0);
// クローンワークシートのタイトル設定
$objCloneSheet->setTitle('Copy Worksheet 1');
// ワークブックへの追加
$objSpreadsheet->addSheet($objCloneSheet);

// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test3.xlsx');
exit();
?>

出力されたエクセルファイルを見てみると以下の様になります。

他のワークブックからのコピーを行う場合は、そのワークブックの該当するワークシートのクローンを生成し addExternalSheet メソッドで行います。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// [test3.xlsx] ファイルをロードしPhpSpreadsheetオブジェクト作成
$objSpreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('./test3.xlsx');

// 別のPhpSpreadsheetオブジェクト作成
$objSpreadsheetNew = new Spreadsheet();
// シート設定
$objSheetNew = $objSpreadsheetNew->getActiveSheet();
// [A1]セルに文字列設定
$objSheetNew->setCellValue('A1', 'abcdefghijklmn');

// [test3.xlsx]:[Copy Worksheet 1] シートのクローンを作成
$objCloneSheet = clone $objSpreadsheet->getSheetByName('Copy Worksheet 1');
// 別のワークブックへの追加
$objSpreadsheetNew->addExternalSheet($objCloneSheet);

// 追加後の別のブックでのシート取得
$objSheet = $objSpreadsheetNew->getSheetByName('Copy Worksheet 1');
// クローンワークシートのタイトル設定
$objSheet->setTitle('New Worksheet 1');

// 別のブックでのXLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheetNew);
// ファイル書込み
$objWriter->save('test3-1.xlsx');
exit();
?>

出力されたエクセルファイル[test3-1.xlsx]を見てみると以下の様になります。

■ワークシート の削除処理（removeSheetByIndex）

ワークブックからワークシートの削除を行うには、削除対象のワークシートの Index 番号を取得し removeSheetByIndex メソッドで行います。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// [test3-1.xlsx] ファイルをロードしPhpSpreadsheetオブジェクト作成
$objSpreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('./test3-1.xlsx');

// シート名 "Worksheet" シート設定
$objSheet = $objSpreadsheet->getSheetByName('Worksheet');
// シートオブジェクトから Index 取得
$intIndex = $objSpreadsheet->getIndex($objSheet);
// Index によるシートオブジェクト削除
$objSpreadsheet->removeSheetByIndex($intIndex);

// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test3-2.xlsx');
exit();
?>

出力されたエクセルファイル[test3-2.xlsx]を見てみると以下の様になります。[test3-1.xlsx] の先頭のシートが削除されているのがわかります。

今回は PhpSpreadsheet で行う 「エクセルファイル出力」 について説明したいと思います。

• 簡単なエクセルファイルの登録処理（ローカルフォルダ）
• 簡単なエクセルファイルのダウンロード処理
• ローカルのエクセルファイルを読込み、データ追加後の登録処理

■簡単なエクセルファイルの登録処理（ローカルフォルダ）

簡単なエクセルファイルの登録処理として、エクセルの[A1]～[A4]セルに文字列、数値、Bool値、計算式を設定し、エクセルファイルをローカルフォルダに書込みます。 手順としては以下の様になります。

• PhpSpreadsheet ライブラリの読込準備(autoload.php)
• PhpSpreadsheet オブジェクトの生成
• エクセルのシートを取得し、セルへの値設定
• エクセルのXLSX形式を指定し登録

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();

// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'Spreadsheet');

// [A2]セルに数値設定
$objSheet->setCellValue('A2', 123.56);

// [A3]セルにBOOLEAN設定
$objSheet->setCellValue('A3', TRUE);

// [A4]セルに書式設定
$objSheet->setCellValue(
    'A4', 
    '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);

// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test1-1.xlsx');
exit();
?>

new Spreadsheet(); でSpreadsheetオブジェクトの作成の結果として新規のワークブックが生成されます。
このワークブックには必ず1個のワークシートが生成されています。
ワークシートは1個しかないので、アクティブなワークシートはその1個のワークシートが該当します。
よって getActiveSheet() メソッドでシートオブジェクトを取得します。 （getSheet(pIndex) メソッドでも同じことが出来ます。引数 pIndex は「0」からのワークシートのIndexです。）

後は、ワークシートオブジェクトの setCellValue() メソッドで文字列、数値、Boolean値、書式の設定を行います。

最後にエクセルファイルを保存するのですが Xlsx クラスオブジェクトを生成します。 Xlsx オブジェクトのメソッド save で保存します。保存結果は以下の様になります。

ワークシートオブジェクトの getCell() メソッドでセルオブジェクトを取得し、 セルオブジェクトの setValue() メソッドを使って以下の様にすることもできます。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();

// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'Spreadsheet');

// [A2]セルに数値設定
$objSheet->getCell('A2')->setValue('123.56');

// [A3]セルにBOOLEAN設定
$objSheet->getCell('A3')->setValue(TRUE);

// [A4]セルに書式設定
$objSheet->getCell('A4')->setValue('=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))');

// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test1-2.xlsx');
exit();
?>

■簡単なエクセルファイルのダウンロード処理

上の例ではローカルのフォルダーにエクセルファイルを保存しましたが、PHPのプログラムから直接ブラウザに対して送信する方法があります。 手順としては以下の様になります。

• Spreadsheet でシートデータを作成する
• 出力するドキュメントタイプをHTTPヘッダーでブラウザに送信する
• Xlsx オブジェクトのメソッド save で“php://output”に出力する

上記のソースを以下の様に変更します。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();

// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'Spreadsheet');
// [A2]セルに数値設定
$objSheet->setCellValue('A2', 123.56);
// [A3]セルにBOOLEAN設定
$objSheet->setCellValue('A3', TRUE);
// [A4]セルに書式設定
$objSheet->setCellValue('A4',  '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))');

// ブラウザへの指定
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test1-3.xlsx"');
header('Cache-Control: max-age=0');
// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('php://output'); 
?>

この処理は注意する点があるのですが、エクセル出力を行う前までに、ブラウザに対して表示出力を行うとダウンロード処理がうまくいきません。 PHP開始タグ <?php のタグの前に空白を入れず、PHP終了タグ ?> の後に最大で1つの改行を入れます。

■ローカルのエクセルファイルを読込み、データ追加後の登録処理

これまでは新規にエクセルファイルを作成する処理でしたが、サーバ側のローカルフォルダに存在するエクセルファイルを読込み、 その後データを追加する処理を説明します。

new Spreadsheet(); でPhpSpreadsheetオブジェクトの作成している部分を \PhpOffice\PhpSpreadsheet\IOFactory クラスの静的メソッドの load を使ってエクセルファイルを読込み PhpSpreadsheetオブジェクト を生成します。 その後、あるセルへの変更値を設定します。
尚、ブラウザに対してファイル名にシステム日付と時刻を付加して返しています。

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// [test1-1.xlsx] ファイルをロードしPhpSpreadsheetオブジェクト作成
$objSpreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('./test1-1.xlsx');
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();
// [B1]セルに文字列設定
$objSheet->setCellValue('B1', 'abcdefghijk');

// ブラウザへの指定
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test'.date("Y-m-d_His").'.xlsx"');
header('Cache-Control: max-age=0');
// XLSX形式で出力
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('php://output'); 
?>

FireFox でのダウンロードの表示は以下の通りです。

この方法は、テンプレートファイルを作成しておきクライアント側からの要求により対応するデータを設定し、 クライアントのブラウザに出力するといったことに使えると思います。

前回は以下の記事で composer のインストールまでを説明しましたが、今回はその続きで PhpSpreadsheet 本体のインストールについて説明したいと思います。

⇒PHP PhpSpreadsheet のインストールについて（XAMPPでのPhpSpreadsheetのインストールその１：composer）

• Composer によるバージョン指定
• Composer による PhpSpreadsheet のインストール
• PhpSpreadsheet による簡単な例

■Composer によるバージョン指定

私のPCのPHPのバージョンは「5.6.28」なので、バージョンの指定を Composer で行います。
コマンドプロンプトを開いて以下のコマンドを実行します。

composer config platform.php 5.6.28

なおこのコマンドを実行する前にディレクトリを PhpSpreadsheet をインストールしたい場所に変更します。
（私のPCではXAMPPなので「C:\xampp\htdocs」です。）

このコマンドの結果「composer.json」が作成されるはずです。

{
    "require": {
    },
    "config": {
        "platform": {
            "php": "5.6.28"
        }
    }
}

■Composer による PhpSpreadsheet のインストール

上記のコマンドに引き続いて PhpSpreadsheet のインストールをするために以下のコマンドを実行します。

composer require phpoffice/phpspreadsheet

実際の実行結果は以下の様になります。

C:\xampp\htdocs>composer require phpoffice/phpspreadsheet
Using version ^1.8 for phpoffice/phpspreadsheet
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 4 installs, 0 updates, 0 removals
  - Installing markbaker/matrix (1.2.0): Loading from cache
  - Installing markbaker/complex (1.4.8): Loading from cache
  - Installing psr/simple-cache (1.0.1): Loading from cache
  - Installing phpoffice/phpspreadsheet (1.8.2): Loading from cache
phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing jpgraph/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers)
Writing lock file
Generating autoload files

このコマンドの結果「composer.json」は以下の様になります。

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.8"
    },
    "config": {
        "platform": {
            "php": "5.6.28"
        }
    }
}

これで PhpSpreadsheet が利用できる様になりましたので、簡単な例を以下に示します。

■PhpSpreadsheet による簡単な例

それでは PhpSpreadsheet による簡単な例を示します。

以前の PHPExcel の場合とよく似ていますが、 最初に PhpSpreadsheet ライブラリの読み込みを行うため、 上記で PhpSpreadsheet のインストールしたディレクトリの中の「/vendor/autoload.php」をインクルードします。
尚、ソースが存在するディレクトリは PhpSpreadsheet のインストールしたディレクトリより1個階層が下なので「require '../vendor/autoload.php';」となります。

更に「use」キーワードでライブラリのクラス名のエイリアスを指定します。

あとのエクセルの処理は PHPExcel の以下の記事と似た感じで記述できます。
⇒PHP PHPExcel エクセルのワークシートのセルへのアクセスについて

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();

// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'Spreadsheetで作成しました。');

// [A2]セルに数値設定
$objSheet->setCellValue('A2', 123.56);

// [A3]セルにBOOLEAN設定
$objSheet->setCellValue('A3', TRUE);

// [A4]セルに書式設定
$objSheet->setCellValue(
    'A4', 
    '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
);

// XLSX形式オブジェクト生成
$objWriter = new Xlsx($objSpreadsheet);
// ファイル書込み
$objWriter->save('test1-1.xlsx');
exit();
?>

出力されたエクセルファイルを見てみると以下の様になります。 （取敢えずエクセルファイルができた様です）