忍者ブログ

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

VB.NETのTIPS(小技集)を中心に、Javascript、PHP その他のプログラミングについて少し役に立つ情報を発信します。いわゆる個人的な忘備録ですが、みなさんのお役に立てれば幸いです。

PHP PHPExcel エクセルのワークシートのセルへのアクセスについて


今回は 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() メソッドを呼び出すと、 PHPExcelPHP の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]を見てみると以下の様になります。













PR

コメント

コメントを書く