Codeigniter 4 – PhpOffice/PhpSpreadsheet

this example controller code to write excel template and download as new file.

install using composer

composer require phpoffice/phpspreadsheet

the controller code

<?php

namespace App\Controllers\Admin;

use App\Controllers\BaseController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\Models\Admin\KopModel;

class Statistic extends BaseController
{

	public function __construct()
	{
		$this->session = session();
		$this->km = new KopModel();
	}
	public function index()
	{

		$param = []; // ubah jika menggunakan parameter query

		$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
		$spreadsheet = $reader->load('uploads/statistik_tpl.xlsx');

		// method 3
		$_sh = 4;
		for ($i = 0; $i < $_sh; $i++) {
			$query 	= view('statistic/query_' . $i, $param);
			$q 	= $this->km->query($query);
			$data	= $q->getResultArray();
			$_sheet = $spreadsheet->getSheet($i);
			for ($j = 0; $j < count($data); $j++) {
				$row = $data[$j];
				$c = 0;
				foreach ($row as $a => $val) {
					$_sheet->setCellValueByColumnAndRow(2 + $c++, 6 + $j, $val);
				}
			}
		}

		$writer = new Xlsx($spreadsheet);
		$filename = 'statistic_' . date('Y-m-d_H-i-s');
		header("Content-type: application/vnd.ms-excel");
		header("Content-Disposition: attachment;filename=" . $filename . '.xlsx');
		header("Cache-Control: max-age=0");
		$writer->save('php://output');
	}
}