<?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('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);
$spreadsheet->getActiveSheet(0)->getColumnDimension('O')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('P')
			->setAutoSize(true);
$spreadsheet->getActiveSheet(0)->getColumnDimension('Q')
			->setAutoSize(true);
// Add some data
$spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1','Laporan Stok Opname')
            ->setCellValue('A2','Periode Tanggal '.@$tanggal);
$spreadsheet->setActiveSheetIndex(0)->mergeCells('A1:Q1');
$spreadsheet->setActiveSheetIndex(0)->mergeCells('A2:Q2');
//pemberian nama kolom
$spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A4','No.')
            ->setCellValue('B4','Tanggal')
            ->setCellValue('C4','Kode Barang')
            ->setCellValue('D4','Barcode Barang')
			->setCellValue('E4','Nama Barang')
			->setCellValue('F4','Stok Sistem')
            ->setCellValue('G4','Stok Fisik')
			->setCellValue('H4','Harga Beli Satuan')
            ->setCellValue('I4','Total Harga Beli Sistem')
            ->setCellValue('J4','Total Harga Beli Fisik')
			->setCellValue('K4','Selisih Harga Beli')
			->setCellValue('L4','Harga Jual Satuan')
			->setCellValue('M4','Total Harga Jual Sistem')
			->setCellValue('N4','Total Harga Jual Fisik')
			->setCellValue('O4','Selisih Harga Jual')
			->setCellValue('P4','Selisih Margin');
$spreadsheet->getActiveSheet(0)->getStyle('A4:Q4')->getAlignment()->setHorizontal('center');
//tampilkan data di excel
$no=1;
$i=5;
foreach ($databarang as $hasil) {
	$totalbelisistem=$hasil->harga_beli*$hasil->stok;
    $totalbelinyata=$hasil->harga_beli*$hasil->stok_nyata;
    $selisihbeli=$totalbelinyata-$totalbelisistem;

	$totaljualsistem=$hasil->harga_jual*$hasil->stok;
    $totaljualnyata=$hasil->harga_jual*$hasil->stok_nyata;
    $selisihjual=$totaljualnyata-$totaljualsistem;
	$margin=$selisihjual-$selisihbeli;
	$spreadsheet->setActiveSheetIndex(0)
				->setCellValue('A'.$i, $no.'.')
				->setCellValue('B'.$i, date('d-m-Y',strtotime($hasil->tanggal)))
				->setCellValue('C'.$i, $hasil->kode_barang)
				->setCellValue('D'.$i, $hasil->barcode)
                ->setCellValue('E'.$i, $hasil->barang)
				->setCellValue('F'.$i, $hasil->stok)
				->setCellValue('G'.$i, $hasil->stok_nyata)
                ->setCellValue('H'.$i, $hasil->harga_beli)
                ->setCellValue('I'.$i, '=H'.$i.'*F'.$i)
                ->setCellValue('J'.$i, '=H'.$i.'*G'.$i)
				->setCellValue('K'.$i, '=J'.$i.'-I'.$i)
				->setCellValue('L'.$i, $hasil->harga_jual)
				->setCellValue('M'.$i, '=L'.$i.'*F'.$i)
				->setCellValue('N'.$i, '=L'.$i.'*G'.$i)
				->setCellValue('O'.$i, '=N'.$i.'-M'.$i)
				->setCellValue('P'.$i, '=O'.$i.'-K'.$i);
	$no++;
	$i++;
}
$spreadsheet->setActiveSheetIndex(0)->getStyle('C')->getNumberFormat()->setFormatCode('#');
$spreadsheet->setActiveSheetIndex(0)->getStyle('D')->getNumberFormat()->setFormatCode('#');
//untuk border
$x=$i-1;
$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:Q'.$x)->applyFromArray($border);
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('Sheet 1');

// 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 Stok Opname - '.@$tanggal.'.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;
?>