Excel形式の集計データを取り込んで表示する(Laravel編)

• サイト運営
• sql
• データベース
• PHP
• Laravel

• 
• ツイート

売上管理をExcelでされている会社はまだまだ多く、システム化しているとしても、データ入力はExcelで、集計や解析だけシステムを利用しているケースも多いと思います。

こうなるとExcelデータをDBに取り込む必要が出てきますが、Excelの列をそのままテーブルのカラムにしたくない場合があります。例えばExcelの表がこうだった場合、

顧客ID|顧客名|商品1|商品2|商品3|商品4
100001|顧客01|10000|20000|40000|50000
100002|顧客02|20000|60000|80000|20000
100003|顧客03|10000|60000|80000|60000
100004|顧客04|20000|20000|40000|70000

今後の拡張、集計や解析することを考えると、こう入れたくなります。

顧客ID|商品ID|売上
100001|P00001|10000
100001|P00002|20000
100001|P00003|40000
100001|P00004|50000
100002|P00001|20000
100002|P00002|60000
100002|P00003|80000
100002|P00004|20000
100003|P00001|10000
100003|P00002|60000
100003|P00003|80000
100003|P00004|60000
100004|P00001|20000
100004|P00002|20000
100004|P00003|40000
100004|P00004|70000

では創造してみます。

public function import(Request $request) {
	//商品IDカラム番号（ここじゃないどっかで定義すべき）
	$ps_indices = [
		'P00001' => 2,
		'P00002' => 3,
		'P00003' => 4,
		'P00004' => 5,
		];
	
	if ($file = $request->file('input_file')) {
		$reader = \Excel::load($file);
		$rows = $reader->getActiveSheet()->toArray();
		foreach ($rows as $key => $row) {
			if ($key == 0) continue;
			$sales = [];
			foreach ($ps_indices as $product_id => $index) {
				$sales[$product_id] = $row[$index];
			}
			foreach ($sales as $product_id => $sales) {
				$product_sale = new ProductSale();
				$product_sale->customer_id = $row[0]; //顧客IDのカラム番号は0（ここじゃないどっかで定義すべき）
				$product_sale->product_id = $product_id;
				$product_sale->sales = $sales;
				$product_sale->save();
			}	
		}
	}	
}

次に表示です。テーブルのまま表示するわけにはいかないので、元のExcelと同じ列で表示する方法を考えます。顧客IDでgroup by、商品IDごとにcountが必要となります。

public static function getAggregateData($request) {
	//SUM関数のとこは商品IDマスターから生成すべき
	return self::selectRaw('
		SUM(CASE product_id WHEN 'P00001' THEN sales END) as P00001,
		SUM(CASE product_id WHEN 'P00002' THEN sales END) as P00002,
		SUM(CASE product_id WHEN 'P00003' THEN sales END) as P00003,
		SUM(CASE product_id WHEN 'P00004' THEN sales END) as P00004,
		customers.customer_id,
		customers.name')
		->leftJoin('customers', 'product_sales.customer_id', '=', 'customers.customer_id')
		->groupBy('product_sales.customer_id')
		->orderBy('product_sales.customer_id')->get();
}

受け取ったコレクションを以下のように回せば、元のExcelの形式で出力できます。

//カラム行は割愛
//※商品IDはマスターから引っ張るべき
foreach ($product_sales as $product_sale) {
	$row = [
		$product_sale->customer_id,
		$product_sale->name,
		$product_sale->P00001,
		$product_sale->P00002,
		$product_sale->P00003,
		$product_sale->P00004,
	];
	echo join('|', $row).'<br>';
}

出力結果は以下のようになります。

100001|顧客01|10000|20000|40000|50000
100002|顧客02|20000|60000|80000|20000
100003|顧客03|10000|60000|80000|60000
100004|顧客04|20000|20000|40000|70000

以上、創造なので試していませんが、今回のExcelのように手動で作成されたデータを取り込む場合、スキーマやSQL文がパッと思い浮かばない時があると思いますので、参考にしてみてください。