<?php
/** Error reporting */
error_reporting(E_ALL);
 
date_default_timezone_set('Europe/London');
 
/** PHPExcel_IOFactory */
require_once '../Classes/PHPExcel/IOFactory.php';
 
echo date('H:i:s') . " Load from Excel5 template\n";
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load("templates/templateLaporan.xls");
 
echo date('H:i:s') . " Add new data to the template\n";
$data = array(array('kode_try'	=> 'UBN1',
					'opr'		=> 0001,
					'km_pool'	=> 2,
					'km_opr'	=> 29,
					'jml_pnp'	=> 28,
					'awal_spu'	=> 27,
					'awal_spp'	=> 26,
					'akhir_spu'	=> 20,
					'akhir_spp'	=> 21,
				   ),
			array('kode_try'	=> 'UBN2',
					'opr'		=> 0002,
					'km_pool'	=> 21,
					'km_opr'	=> 291,
					'jml_pnp'	=> 281,
					'awal_spu'	=> 271,
					'awal_spp'	=> 261,
					'akhir_spu'	=> 201,
					'akhir_spp'	=> 211,
				   )
			 );
			 
$objPHPExcel->getActiveSheet()->setCellValue('D3', PHPExcel_Shared_Date::PHPToExcel(time()));
 
$baseRow = 7;
foreach($data as $r => $dataRow) {
	$row = $baseRow + $r;
	$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);
 
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1);
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $dataRow['kode_try']);
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $dataRow['opr']);
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $dataRow['km_pool']);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$row, $dataRow['km_opr']);
	$objPHPExcel->getActiveSheet()->setCellValue('F'.$row, $dataRow['jml_pnp']);
	$objPHPExcel->getActiveSheet()->setCellValue('G'.$row, $dataRow['awal_spu']);
	$objPHPExcel->getActiveSheet()->setCellValue('H'.$row, $dataRow['awal_spp']);
	$objPHPExcel->getActiveSheet()->setCellValue('I'.$row, $dataRow['akhir_spu']);
	$objPHPExcel->getActiveSheet()->setCellValue('J'.$row, $dataRow['akhir_spp']);
	$objPHPExcel->getActiveSheet()->setCellValue('K'.$row, '=G'.$row.'-I'.$row);
	$objPHPExcel->getActiveSheet()->setCellValue('L'.$row, '=H'.$row.'-J'.$row);
}
$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);
 
echo date('H:i:s') . " Write to Excel5 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));
 
// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";
 
// Echo done
echo date('H:i:s') . " Done writing file.\r\n";
echo "<script type='text/javascript'>
					location.href='laporan.xls';
				</script>
			";

$data = array(array('kode_try'	=> 'UBN1',
					'opr'		=> 0001,
					'km_pool'	=> 2,
					'km_opr'	=> 29,
					'jml_pnp'	=> 28,
					'awal_spu'	=> 27,
					'awal_spp'	=> 26,
					'akhir_spu'	=> 20,
					'akhir_spp'	=> 21,
				   ),
			array('kode_try'	=> 'UBN2',
					'opr'		=> 0002,
					'km_pool'	=> 21,
					'km_opr'	=> 291,
					'jml_pnp'	=> 281,
					'awal_spu'	=> 271,
					'awal_spp'	=> 261,
					'akhir_spu'	=> 201,
					'akhir_spp'	=> 211,
				   )
			 );