今回は 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]を見てみると以下の様になります。
PR
コメント