今回は PHPExcel で行う「ワークシート」の 「セル」へのアクセス について説明したいと思います。
■座標によるセル値の設定
座標によるセル値の設定は、ワークシートの setCellValue() メソッドを使用して行うことができます。
または、セルオブジェクトを取得してから、セルオブジェクトの setValue() メソッドを呼び出すこともできます。
以下のページと内容は同じですので、参照して下さい。
⇒PHP エクセルファイル出力の方法について（PHPExcel）
<?php
// ライブラリ読込
require_once './PHPExcel-1.8/Classes/PHPExcel.php';
// PHPExcelオブジェクト作成
$objBook = new PHPExcel();
// シート設定
$objSheet = $objBook->getActiveSheet();
// [A1]セルに文字列設定
$objSheet->setCellValue('A1', 'ABCDEFG');
// [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');
// Excel2007形式で保存する
$objWriter = PHPExcel_IOFactory::createWriter($objBook, "Excel2007");
$objWriter->save('test.xlsx');
exit();
?>
エクセルは以下の7個のデータ型をサポートしています。
- string：文字列
- number：数値
- boolean：ブール値
- null ：ヌル
- formula：数式
- error ：エラー
- Inline(or rich-text)string：インライン、リッチテキスト
基本的には、ワークシートの setCellValue() メソッドまたは、セルの setValue() メソッドを呼び出すと、 PHPExcel は PHP のnull、ブール値、浮動小数点数、または整数に対応する適切なデータ型を使用します。
メソッドに渡される文字列データは最も適切なデータ型にキャストされます。 数値文字列は数値にキャストされ ”=” で始まる文字列は数式に変換されます。 数値ではない文字列、または ”=” で始まっていない文字列は、文字列値として扱われます。
これらの「変換」はセルの「値バインダー」によって処理され、カスタムの「値バインダー」を記述して、これらの「変換」の動作を変更できます。
尚 PHPExcel は各種の形式の文字列を、適切な数値や datetimestamp値 に変換し、セルに設定します。
”3/4”などの分数形式の文字列を数値（この場合は 0.75）に変換する分数フォーマットマスク、 ”5％”などの文字列は 0.05 の値に変換され、パーセント形式のマスクが適用されます。
日付の様に見える値を含む文字列は、Excelのシリアル化されたdatetimestamp値に変換され、対応するマスクが適用されます。
以下のソースに例を示します。（分数形式は小数の数値になるとありましたが、実際には”3/4”は3月4日の指定になりました）
<?php
// ライブラリ読込
require_once './PHPExcel-1.8/Classes/PHPExcel.php';
// PHPExcelオブジェクト作成
$objBook = new PHPExcel();
// シート設定
$objSheet = $objBook->getActiveSheet();
// [A1]セルに数値文字列設定
$objSheet->setCellValue('A1', '1234.56');
// [A2]セルに数式文字列設定
$objSheet->setCellValue('A2', '=A1*100');
// [A3]セルに分数文字列設定
$objSheet->setCellValue('A3', '3/4');
$objSheet->setCellValue('B3', '=A3*100');
// [A4]セルにパーセント文字列設定
$objSheet->setCellValue('A4', '5%');
$objSheet->setCellValue('B4', '=A4*100');
// [A5]セルに日付文字列設定
$objSheet->setCellValue('A5', '2020/05/10');
// [A6]セルに文字列設定
$objSheet->setCellValue('A6', 'ABCD123');
// [test3-1.xlsx]:Excel2007形式で保存する
$objWriter = PHPExcel_IOFactory::createWriter($objBook, "Excel2007");
$objWriter->save('test3-1.xlsx');
exit();
?>
■座標によるセル値の設定（日付・時刻設定）
日付および時刻の値は、エクセル内では（単純な浮動小数点値）のタイムスタンプとして保持され、数値フォーマットマスクを使用して、その値が表示されます。
よって、セルに日付を保存する場合は、正しい エクセルタイムスタンプ を計算し、数値形式マスクを設定する必要があります。
<?php
// ライブラリ読込
require_once './PHPExcel-1.8/Classes/PHPExcel.php';
// PHPExcelオブジェクト作成
$objBook = new PHPExcel();
// シート設定
$objSheet = $objBook->getActiveSheet();
// システム時刻取得
$dateTimeNow = time();
// エクセルタイムスタンプ値に変換
$excelDateValue = PHPExcel_Shared_Date::PHPToExcel($dateTimeNow);
// [A1]セルにエクセルタイムスタンプ値設定
$objSheet->setCellValue('A1', $excelDateValue);
// [A1]セルの数値フォーマットに日付時刻の設定(yyyy-mm-dd)
$objSheet->getStyle('A1')->getNumberFormat()->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
);
// [A2]セルにエクセルタイムスタンプ値設定
$objSheet->setCellValue('A2', $excelDateValue);
// [A2]セルの数値フォーマットに日付時刻の設定(yy/mm/dd;@)
$objSheet->getStyle('A2')->getNumberFormat()->setFormatCode(
PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH
);
// [test3-2.xlsx]:Excel2007形式で保存する
$objWriter = PHPExcel_IOFactory::createWriter($objBook, "Excel2007");
$objWriter->save('test3-2.xlsx');
exit();
?>
出力されたエクセルファイルを見てみると以下の様になります。
PHPExcel_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/y'; const FORMAT_DATE_DMYMINUS = 'd-m-y'; const FORMAT_DATE_DMMINUS = 'd-m'; const FORMAT_DATE_MYMINUS = 'm-y'; 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/y 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 = '[$EUR ]#,##0.00_-';
■座標によるセル値の設定（先行ゼロ付きの数値設定）
数値には先行ゼロがないため、先行ゼロのある数値（電話番号など）を設定しようとすると、値が数値にキャストされるため、これらは通常失われます。 そのため「09088881234」は 「9088881234」と表示されます。
PHPExcel にこの動作をさせない様な方法は2つあります。
1つは、数値に変換されないように、データ型を文字列として明示的に設定する方法です。
2つ目は、数値書式マスクを使用して、先行ゼロ付きの値を表示する方法です。
<?php
// ライブラリ読込
require_once './PHPExcel-1.8/Classes/PHPExcel.php';
// PHPExcelオブジェクト作成
$objBook = new PHPExcel();
// シート設定
$objSheet = $objBook->getActiveSheet();
// [A1]セルにゼロが先頭に有る数値文字列を、文字列として設定
$objSheet->setCellValueExplicit('A1', "09088881234", PHPExcel_Cell_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]:Excel2007形式で保存する
$objWriter = PHPExcel_IOFactory::createWriter($objBook, "Excel2007");
$objWriter->save('test3-3.xlsx');
exit();
?>
出力されたエクセルファイルを見てみると以下の様になります。
■座標によるセル値の設定（配列データの設定）
ワークシートの fromArray メソッドに配列データを渡すことにより、1回の呼び出しでセル範囲に設定することができます。
<?php
// ライブラリ読込
require_once './PHPExcel-1.8/Classes/PHPExcel.php';
// PHPExcelオブジェクト作成
$objBook = new PHPExcel();
// シート設定
$objSheet = $objBook->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]:Excel2007形式で保存する
$objWriter = PHPExcel_IOFactory::createWriter($objBook, "Excel2007");
$objWriter->save('test3-4.xlsx');
exit();
?>
出力されたエクセルファイル[test3-4.xlsx]を見てみると以下の様になります。
fromArray メソッドに2次元配列を渡すと、一連の行と列として扱われますが、1次元配列は単一の行として扱われます。
先ずは、横方向の1次元配列の設定を行い、その後で縦方向の設定を行います。
<?php
// ライブラリ読込
require_once './PHPExcel-1.8/Classes/PHPExcel.php';
// PHPExcelオブジェクト作成
$objBook = new PHPExcel();
// シート設定
$objSheet = $objBook->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]:Excel2007形式で保存する
$objWriter = PHPExcel_IOFactory::createWriter($objBook, "Excel2007");
$objWriter->save('test3-5.xlsx');
exit();
?>
出力されたエクセルファイル[test3-5.xlsx]を見てみると以下の様になります。
コメント