PHP

PHPSpreadsheetライブラリを使ってExcelをJSON形式に変換する

PHP-アイキャッチ

こんにちは!ゆーたろうです。

前回、PHPExcelライブラリを使って、Excelを配列にするところを実践しました。

しかしその後、2020現在ではPHPSpreadsheetライブラリを使うことが推奨されていることが分かったため、さっそく後継ライブラリを使ってみました。

その際の備忘録も兼ねて、議事録としてExcelの内容をJSON形式にするところまでの流れを紹介していこうと思います。

Composerを使ってPHPSpreadsheetライブラリをインストールする

PHPExcelライブラリはZIPを解凍するだけで使うことができましたが、後継のこのPHPSpreadsheetライブラリはComposerでインストールしなければ使うことができませんでした。

なので、Composerで自分の環境に入れていきます。

方法1.SSHシェルコマンドを使って入れる

SSHなどのコマンドプロンプトが使える環境であれば、下記のコマンドを打つことで簡単に入れることができます。

composer require phpoffice/phpspreadsheet

方法2.PHPのexec()関数を使って入れる

私が使っているロリポップ ライトプランをはじめとして、SSHシェルコマンドが使えないサーバ環境もあるかと思います。

その場合は、下記のプログラムを実行することで、入れることも可能です。

方法1のやり方は他のサイトでもたくさん解説があるので、このブログでは、こちらの方法を使ったインストールで進めていきます。

Gitから、ソース一式を持ってくる

exec()を使ったインストール方法は、いわば手動で行う方法です。

なので、まずは元となるソースから用意していきます。

https://github.com/PHPOffice/PhpSpreadsheet

上記のURLから「clone or download」を選択して、ZIP形式でダウンロードします。

PHPSpreadsheetライブラリのダウンロード

解凍し、実行したい環境へアップロードする

PHPSpreadsheetライブラリ解凍後

ダウンロードさらたPHPSpreadsheet-masterを解凍するとこのようなファイル構成になっているかと思います。

これらすべてを実行環境にアップロードします。

私で言えばこのようになります。

ロリポップ-トップ階層
┣src
┣CHANGELOG.md
┣composer.json
┣CONTRIBUTING.md
┣LICSENSE
┗README.md

インストールを行うプログラムを書く

次に、composerを使ってパッケージをインストールするプログラムを書いていきます。

PHPバージョンの部分はそれぞれに合わせる必要がありますが、それ以外は特に環境に依存しないはずなので、下記のプログラムをそのままコピーペーストすればいいはずです。

<?php
 	putenv('HOME="/root"');
 
    /**
     * composerインストールコマンド設定
     */
    $command = "curl -sS https://getcomposer.org/installer | /usr/local/php/7.3/bin/php ";
    $output = "";
 
    //コマンドを実行
    exec("{$command} 2>&1", $output, $ret);
     
    //実行結果を出力
    echo "exec result:" . $ret . "<br>";
    echo "exec output:";
    print_r($output);
    echo "<br><br>";
     
     
     
    /**
     * pharバージョン確認コマンド設定
     */
    $output=""; 
    $command= "/usr/local/php/7.3/bin/php composer.phar -V";
     
    //コマンドを実行
    exec("{$command} 2>&1", $output, $ret);
     
    //実行結果を出力
    echo "exec result:" . $ret . "<br>";
    echo "exec output:";
    print_r($output);
    echo "<br><br>";
   
   
   
    /**
     * pharインストールコマンド設定
     */
    $output=""; 
    $command= "/usr/local/php/7.3/bin/php composer.phar install";
     
    //コマンドを実行
    exec("{$command} 2>&1", $output, $ret);
     
    //実行結果を出力
    echo "exec result:" . $ret . "<br>";
    echo "exec output:";
    print_r($output);
   
   
   
    /**
     * pharインストールコマンド設定
     */
    $output=""; 
    $command= "/usr/local/php/7.3/bin/php composer require phpoffice/phpspreadsheet";
     
    //コマンドを実行
    exec("{$command} 2>&1", $output, $ret);
     
    //実行結果を出力
    echo "exec result:" . $ret . "<br>";
    echo "exec output:";
    print_r($output);
     
?>

昔書いた記事が役に立ちました^^
知識は宝ですね。

ここでポイントになるのが、2行目付近にある「putenv(‘HOME=”/root”‘);」です。

これを入れないと「The HOME or COMPOSER_HOME environment variable must be set for composer to run correctly」というエラーが発生します。

参考:シェルスクリプトで Composer のインストールができない

そのため、putenvを使い、環境を設定する必要があります。

このプログラムを実行すると、下記図のようになります。

こうなれば、exec()とcomposerを使って、PHPSpreadsheetパッケージがインストール完了です。

execとcomposerを使ったPHPSpreadsheetライブラリのインストール

実行したパスを確認すると、確かにvendorフォルダが作成されていますね。
これでOKです。

PHPSpreadsheet用vendorフォルダ

ExcelをJSONに変換してみよう!【サンプルプログラム】

ここまで終われば、後はExcelをJSONにしていくだけです。

PHPSpreadsheetの使い方は他のサイトでもたくさん解説されているので、ここでは割愛します。

参考にさせていただいたサイトです。

<?php

/**
 * PHPSpreadsheetライブラリ読み込み
 */


require_once "vendor/autoload.php";

ini_set( 'display_errors', 1 );


use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader; // 拡張子xlsxのExcelファイル読み込み用
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter; // 拡張子xlsxのExcelファイル書き込み用
use PhpOffice\PhpSpreadsheet\Reader\Xls  as XlsReader;  // 拡張子xlsのExcelファイル読み込み用
use PhpOffice\PhpSpreadsheet\Writer\Xls  as XlsWriter;  // 拡張子xlsのExcelファイル書き込み用
use PhpOffice\PhpSpreadsheet\Spreadsheet;               // スプレッドシート用
use PhpOffice\PhpSpreadsheet\Style\Alignment;           // 出力位置指定用
use PhpOffice\PhpSpreadsheet\Style\Border;              // 罫線用
use PhpOffice\PhpSpreadsheet\Style\Color;               // 色指定用
use PhpOffice\PhpSpreadsheet\Style\Fill;                // 塗りつぶし種類



/**
 * 読み込むExcelファイルを設定する
 */
$input = 'Book2.xlsx';
//$input = 'read_test.xls';



/**
 * ファイル拡張子を判定して、適切な処理メソッドに切り替える
 */
$extension = pathinfo($input, PATHINFO_EXTENSION);

if ($extension === 'xls')
{
	echo "xlsファイルです<BR>";
	$reader = new XlsReader();
}
else if ($extension === 'xlsx')
{
	echo "xlsxファイルです<BR>";
	$reader = new XlsxReader();
}
else
{
	echo "不明な拡張子です:".$extension;
	exit();
}



/**
 * ファイルを読み込む
 */
$spreadsheet = $reader->load($input);

//結果表示用変数を定義
$result = array();



/**
 * 読み込んだファイルの全シート分処理する
 */
for($i = 0; $i < $spreadsheet->getSheetCount(); $i++)
{
	$sheet = $spreadsheet->getSheet($i);               // 現在のシートのオブジェクトを取得
	echo "◆".$sheet->getTitle()."<BR>";               // 現在のシート名を表示
	
	//行
	foreach( $sheet->getRowIterator() as $row )
	{
		//初期化
		$array = array();
		$idx = 0;
		
		//列
		foreach( $sheet->getColumnIterator() as $column )
		{
			//1セルずつブラウザに表示
			echo $column->getColumnIndex() ."=>". $sheet->getCell( $column->getColumnIndex().$row->getRowIndex() )->getCalculatedValue()."/";
			
			//JSONにまとめる用に、1セルずつ配列へ格納していく
			$array[$idx++] = [$column->getColumnIndex() => $sheet->getCell( $column->getColumnIndex().$row->getRowIndex() )->getCalculatedValue()];
		}
		
		//連想配列としてまとめていく
		//構造:シート名->1行目->各列ごと
		$result[$sheet->getTitle()][$row->getRowIndex()] = $array;
		echo "<BR>";
	}
	
	//次のシートへ進む
	echo "<HR>";
}



/**
 * まとめた連想配列をJSON形式に変換する
 */
$result = json_encode($result, JSON_UNESCAPED_UNICODE);
echo $result;

echo "<HR>";

//2シート目のB4を表示してみる
$result = json_decode($result, true);

echo $result["②シート目"]["4"] [ 1 ] ["B"];

exit();




※Excelは拡張子の種類がたくさんあるので、作成するインスタンスも異なるところに注意ですね。

PHPExcelを使ってExcelファイルを読み込んで編集や保存ダウンロードする

こちらのサイトが参考になりました。

実行結果

以下に、テストした時のサンプルを載せておきます。

エクセルの構成

PHPSpreadsheetに読み込ませるエクセル構成

実行結果(ブラウザに表示される値)

xlsxファイルです
◆1シート目
A=>test/B=>/C=>/
A=>テスト/B=>/C=>/
A=>A列/B=>B列/C=>C列/


◆②シート目
A=>あ/B=>/
A=>い/B=>/
A=>う/B=>/
A=>/B=>え/
A=>/B=>/
A=>/B=>/
A=>お/B=>/


{“1シート目”:{“1”:[{“A”:”test”},{“B”:null},{“C”:null}],”2″:[{“A”:”テスト”},{“B”:null},{“C”:null}],”3″:[{“A”:”A列”},{“B”:”B列”},{“C”:”C列”}]},”②シート目”:{“1”:[{“A”:”あ”},{“B”:null}],”2″:[{“A”:”い”},{“B”:null}],”3″:[{“A”:”う”},{“B”:null}],”4″:[{“A”:null},{“B”:”え”}],”5″:[{“A”:null},{“B”:null}],”6″:[{“A”:null},{“B”:null}],”7″:[{“A”:”お”},{“B”:null}]}}


JSON形式に整えてみる

JSONきれい ~JSON整形ツール~というサイトを使って整形しました。

{
	"1シート目": {
		"1": [
			{
				"A": "test"
			},
			{
				"B": null
			},
			{
				"C": null
			}
		],
		"2": [
			{
				"A": "テスト"
			},
			{
				"B": null
			},
			{
				"C": null
			}
		],
		"3": [
			{
				"A": "A列"
			},
			{
				"B": "B列"
			},
			{
				"C": "C列"
			}
		]
	},
	"②シート目": {
		"1": [
			{
				"A": "あ"
			},
			{
				"B": null
			}
		],
		"2": [
			{
				"A": "い"
			},
			{
				"B": null
			}
		],
		"3": [
			{
				"A": "う"
			},
			{
				"B": null
			}
		],
		"4": [
			{
				"A": null
			},
			{
				"B": "え"
			}
		],
		"5": [
			{
				"A": null
			},
			{
				"B": null
			}
		],
		"6": [
			{
				"A": null
			},
			{
				"B": null
			}
		],
		"7": [
			{
				"A": "お"
			},
			{
				"B": null
			}
		]
	}
}

いい感じですね!

シート名や列名(カラム名)、値がちゃんと「Key => Value」形式になっています。

変換されたjsonをまたPHP側で扱うときは、json_decodeをすることで連想配列に戻すことができます。

echo $result[“②シート目”][“4”] [ 1 ] [“B”];

とすれば、「②シート目」にあるB4の値を取ることができます。

余談:ハマったところ

シート名を取得する方法がネットで調べてもうまく見つけられなかったです。

getSheetNames()とかgetSheetByName()とかあって、それっぽかったですが、私がしたい目的には使えませんでした。

若干、PHPExcelのやり方とは違っていて、それと同じ関数を使うことができなかったので、ライブラリのソースから検索して見つけることにしました。

サクラエディタのGrep機能を使い「PhpSpreadsheet-master\src\PhpSpreadsheet」の中から「getSheet」を検索したところ、

「PhpSpreadsheet-master\src\PhpSpreadsheet\Spreadsheet.php(826,37) [SJIS]: $returnValue[] = $this->getSheet($i)->getTitle();」

というように、getTitle()でシート名を取得することができることが分かりました。

ネットで探してもうまく見つけられない情報は、ライブラリのソースを読んだり検索したりして見つけたほうが、意外と早く解決できたりしますね。

まとめ

いかがだったでしょうか。

PHPSpreadsheetの導入に時間がかかりましたが、実際の処理自体は結構簡単ですね。

これでエクセル処理も捗りそうです。