<?php
// Load library phpspreadsheet
require('./vendor/autoload.php');
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
// End load library phpspreadsheet
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
// Set document properties
$spreadsheet->getProperties()->setCreator('Aditya Yudhit Irfianto - UNU Purwokerto')
			->setLastModifiedBy('Aditya Yudhit - UNU Purwokerto')
			->setTitle('Office 2007 XLSX Test Document')
			->setSubject('Office 2007 XLSX Test Document')
			->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
			->setKeywords('office 2007 openxml php')
			->setCategory('Test result file');
// set size column
$spreadsheet->getActiveSheet(0)->getColumnDimension('A')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('B')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('C')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('D')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('E')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('F')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('G')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('H')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('I')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('J')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('K')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('L')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('M')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('N')
			->setAutoSize(true);
// Add some data
$spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1','Laporan Penjualan Per Struk')
            ->setCellValue('A2','Periode Tanggal '.@$awal.' Sampai '.@$akhir);
$spreadsheet->setActiveSheetIndex(0)->mergeCells('A1:M1');
$spreadsheet->setActiveSheetIndex(0)->mergeCells('A2:M2');
//pemberian nama kolom
$spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A4','No.')
            ->setCellValue('B4','Tanggal')
            ->setCellValue('C4','No. Faktur')
            ->setCellValue('D4','Kode Member')
			->setCellValue('E4','Nama Member')
			->setCellValue('F4','HPP')
			->setCellValue('G4','Total Belanja')
			->setCellValue('H4','Jumlah Piutang')
			->setCellValue('I4','Retur')
			->setCellValue('J4','Non Tunai')
			->setCellValue('K4','Tukar Poin')
			->setCellValue('L4','Tukan Koin')
            ->setCellValue('M4','Sisa Piutang')
            ->setCellValue('N4','Operator');
$spreadsheet->getActiveSheet(0)->getStyle('A4:M4')->getAlignment()->setHorizontal('center');
//tampilkan data di excel
$no=1;
$i=5;
foreach ($datajual as $hasil) {
	$retur=@array_sum($arretur[$hasil->faktur_jual]);
	$hpp=@array_sum($arrhpp[$hasil->faktur_jual]);
	$spreadsheet->setActiveSheetIndex(0)
				->setCellValue('A'.$i, $no.'.')
				->setCellValue('B'.$i, date('d-m-Y',strtotime($hasil->tanggal)))
				->setCellValue('C'.$i, $hasil->faktur_jual)
				->setCellValue('D'.$i, $hasil->kode_member)
                ->setCellValue('E'.$i, $hasil->member)
                ->setCellValue('F'.$i, $hpp)
				->setCellValue('G'.$i, $hasil->total)
				->setCellValue('H'.$i, $hasil->piutang)
				->setCellValue('I'.$i, $retur)
				->setCellValue('J'.$i, $hasil->non_tunai)
				->setCellValue('K'.$i, $hasil->poin)
				->setCellValue('L'.$i, $hasil->koin)
				->setCellValue('M'.$i, $hasil->sisa)
                ->setCellValue('N'.$i, $hasil->pegawai);
	$no++;
	$i++;
}
//untuk border
$x=$i-1;
$spreadsheet->setActiveSheetIndex(0)
			->setCellValue('B'.$i, 'Jumlah Total')
			->setCellValue('F'.$i, '=SUM(F5:F'.$x.')')
			->setCellValue('G'.$i, '=SUM(G5:G'.$x.')')
			->setCellValue('H'.$i, '=SUM(H5:H'.$x.')')
			->setCellValue('I'.$i, '=SUM(I5:I'.$x.')')
			->setCellValue('J'.$i, '=SUM(J5:J'.$x.')')
			->setCellValue('K'.$i, '=SUM(K5:K'.$x.')')
			->setCellValue('L'.$i, '=SUM(L5:L'.$x.')')
			->setCellValue('M'.$i, '=SUM(M5:M'.$x.')');
$spreadsheet->setActiveSheetIndex(0)->mergeCells('B'.$i.':E'.$i);
$border=array();
$border['borders']=array();
$border['borders']['outline']['borderStyle']=\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN;
$border['borders']['allBorders']['borderStyle']=\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN;
$spreadsheet->setActiveSheetIndex(0)->getStyle('A4:N'.$i)->applyFromArray($border);
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Sheet1');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Laporan Penjualan Per Struk - '.@$awal.'_'.$akhir.'.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;
?>