今回は PhpSpreadsheet の「ワークシート」の 「セル」へのアクセス を行・列指定で行う方法について説明したいと思います。
以下の記事では、エクセルの行・列指定（"A1","B2"など）によるアクセスについて説明しましたが、 今回は行・列指定指定の部分が、行と列指定に分けて行う方法です。
⇒PHP PhpSpreadsheet エクセルのワークシートのセルへのアクセスについて
- 行・列指定によるセル値の設定（setCellValueByColumnAndRow()）
- 行・列指定によるセル値の設定（日付・時刻設定）
- 行・列指定によるセル値の設定（先行ゼロ付きの数値設定）
- 行・列指定によるセル値の設定（配列データの設定）
■行・列指定によるセル値の設定
行・列指定によるセル値の設定は、ワークシートの setCellValueByColumnAndRow() メソッドを使用して行うことができます。
または、getCellByColumnAndRow() メソッドでセルオブジェクトを取得してから、セルオブジェクトの setValue() メソッドを呼び出すこともできます。
<?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->setCellValueByColumnAndRow(1, 1, 'ABCDEFG');
// [B2]セルに数値設定
$objSheet->setCellValueByColumnAndRow(2, 2, 123.56);
// [C3]セルにBoolean値設定
$objSheet->setCellValueByColumnAndRow(3, 3, TRUE);
// [A4]セルに書式設定
$objSheet->setCellValueByColumnAndRow(5, 1, '=IF(C3, CONCATENATE(A1, " ", B2), CONCATENATE(B2, " ", A1))');
// [B8]セルに getCellByColumnAndRow() でセルを取得し、setValue() で文字列設定
$objSheet->getCellByColumnAndRow(2, 8)->setValue('test 行列指定によるセルオブジェクト取得');
// [test3-3-1.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test3-3-1.xlsx');
exit();
?>
setCellValueByColumnAndRow() メソッドは setCellValue($pCoordinate , ...) メソッドの $pCoordinate(エクセル座標文字列:"A1"等) の部分を 列番号と行番号に置き換えたメソッドです。
（ライブラリの内部では行番号と列番号からエクセル座標文字列に変換して処理しています）
尚、行番号は「1」から始まり、列番号も「1」から始まります。(1 ⇒ "A", 2 ⇒ "B", 3 ⇒ "C", ... に対応)
PHPExcel では列番号は「0」から始まりましたが PhpSpreadsheet では「1」からになりました。
出力されたエクセルファイルを見てみると以下の様になります。
■行・列指定によるセル値の設定（日付・時刻設定）
日付および時刻の値は、エクセル内では（単純な浮動小数点値）のタイムスタンプとして保持され、数値フォーマットマスクを使用して、その値が表示されます。
よって、セルに日付を保存する場合は、正しい エクセルタイムスタンプ を計算し、数値形式マスクを設定する必要があります。
「PHP PhpSpreadsheet エクセルのワークシートのセルへのアクセスについて」 の記事の使用メソッドを行・列指定に変更します。
setCellValue() メソッドは setCellValueByColumnAndRow() メソッドを使用します。
また、getStyle() メソッドは getStyleByColumnAndRow() メソッドを使用します。
<?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->setCellValueByColumnAndRow(1, 1, $excelDateValue);
// [A1]セルの数値フォーマットに日付時刻の設定(yyyy-mm-dd)
$objSheet->getStyleByColumnAndRow(1, 1)->getNumberFormat()->setFormatCode(
NumberFormat::FORMAT_DATE_YYYYMMDD2
);
// [A2]セルにエクセルタイムスタンプ値設定
$objSheet->setCellValueByColumnAndRow(1, 2, $excelDateValue);
// [A2]セルの数値フォーマットに日付時刻の設定(yy/mm/dd;@)
$objSheet->getStyleByColumnAndRow(1, 2)->getNumberFormat()->setFormatCode(
NumberFormat::FORMAT_DATE_YYYYMMDDSLASH
);
// [test3-3-2.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test3-3-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 PhpSpreadsheet エクセルのワークシートのセルへのアクセスについて」 の記事の使用メソッドを行・列指定に変更します。
setCellValueExplicit() メソッドは setCellValueExplicitByColumnAndRow() メソッドを使用します。
また、getStyle() メソッドは getStyleByColumnAndRow() メソッドを使用します。
<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// シート設定
$objSheet = $objSpreadsheet->getActiveSheet();
// [A1]セルにゼロが先頭に有る数値文字列を、文字列として設定
$objSheet->setCellValueExplicitByColumnAndRow(
1, 1,
"09088881234",
DataType::TYPE_STRING
);
// [A2]セルに数値設定
$objSheet->setCellValueByColumnAndRow(1, 2, 9088881234);
// [A2]セルの数値フォーマットに "00000000000" の設定
$objSheet->getStyleByColumnAndRow(1, 2)->getNumberFormat()->setFormatCode(
'00000000000'
);
// [A3]セルに数値設定
$objSheet->setCellValueByColumnAndRow(1, 3, 9088881234);
// [A3]セルの数値フォーマットに "0000-000-0000" の設定
$objSheet->getStyleByColumnAndRow(1, 3)->getNumberFormat()->setFormatCode(
'0000-000-0000'
);
// [test3-3-3.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test3-3-3.xlsx');
exit();
?>
出力されたエクセルファイルを見てみると以下の様になります。
■行・列指定によるセル値の設定（配列データの設定）
ワークシートの fromArray メソッドに配列データを渡すことにより、1回の呼び出しでセル範囲に設定することができます。
fromArray メソッドに対応する行・列指定のメソッドが無いため、エクセル座標文字列を PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex() メソッドで作成します。
<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
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),
);
// 列と行が指定されたとする
$intColumn = 1;
$intRow = 2;
// [A2]セルの座標値計算
// 列の文字列変換:「0」⇒"A"
$strCoordinate = Coordinate::stringFromColumnIndex($intColumn);
// 行の文字列を付加
$strCoordinate = $strCoordinate.$intRow;
// [A2]セルにから配列データ設定
$objSheet->fromArray(
$arrData, // 配列データ
NULL, // 配列データの中でセルに設定しないNULL値の指定
$strCoordinate // 左上座標(デフォルト:"A1")
);
// [test3-3-4.xlsx]:Excel2007形式で保存する
$objWriter = new Xlsx($objSpreadsheet);
$objWriter->save('test3-3-4.xlsx');
exit();
?>
出力されたエクセルファイル[test3-3-4.xlsx]を見てみると以下の様になります。
コメント