<?php
	/*Database Connection*/
	include("dbconnection.php");
	
	/*include PhpSpreadsheet library*/
	require 'PhpSpreadsheet/vendor/autoload.php';

	use PhpOffice\PhpSpreadsheet\Spreadsheet;
	use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
	use PhpOffice\PhpSpreadsheet\Writer\Xls;	
	use PhpOffice\PhpSpreadsheet\Style\Alignment;
	use PhpOffice\PhpSpreadsheet\Style\Fill;
	use PhpOffice\PhpSpreadsheet\Style\Color;
	use PhpOffice\PhpSpreadsheet\Style\Border;
	use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
	use PhpOffice\PhpSpreadsheet\Style\Protection;
	use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
	use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
	use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
	use PhpOffice\PhpSpreadsheet\Worksheet\Row;
	use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
	use PhpOffice\PhpSpreadsheet\Cell\Cell;
	use PhpOffice\PhpSpreadsheet\Cell\DataValidation;	
	use PhpOffice\PhpSpreadsheet\IOFactory;	
	
	$spreadsheet = new Spreadsheet();
	
	/*Page Setup
	Page Orientation(ORIENTATION_LANDSCAPE/ORIENTATION_PORTRAIT), 
	Paper Size(PAPERSIZE_A3,PAPERSIZE_A4,PAPERSIZE_A5,PAPERSIZE_LETTER,PAPERSIZE_LEGAL etc)*/
	$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(PageSetup::ORIENTATION_PORTRAIT);
	$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
	
	/*Set Page Margins for a Worksheet*/
	$spreadsheet->getActiveSheet()->getPageMargins()->setTop(0.75);
	$spreadsheet->getActiveSheet()->getPageMargins()->setRight(0.70);
	$spreadsheet->getActiveSheet()->getPageMargins()->setLeft(0.70);
	$spreadsheet->getActiveSheet()->getPageMargins()->setBottom(0.75);	
	
	/*Center a page horizontally/vertically*/
	$spreadsheet->getActiveSheet()->getPageSetup()->setHorizontalCentered(false);
	$spreadsheet->getActiveSheet()->getPageSetup()->setVerticalCentered(true);

	/*Show/hide gridlines(true/false)*/
	$spreadsheet->getActiveSheet()->setShowGridlines(true);
	
	$spreadsheet->getActiveSheet()->getPageSetup()->setFitToWidth(1);
	$spreadsheet->getActiveSheet()->getPageSetup()->setFitToHeight(0);	
	
	//Activate work sheet
    $spreadsheet->createSheet(0);
    $spreadsheet->setActiveSheetIndex(0);
	$spreadsheet->getActiveSheet(0);
	
	//work sheet name
    $spreadsheet->getActiveSheet()->setTitle('Excel Export Data');
	
	/*Default Font Set*/
	$spreadsheet->getDefaultStyle()->getFont()->setName('Calibri');
	
	/*Default Font Size Set*/
	$spreadsheet->getDefaultStyle()->getFont()->setSize(11);
	
	/*Border color*/
    $styleThinBlackBorderOutline = array('borders' => array('outline'=> array('borderStyle' => Border::BORDER_THIN, 'color' => array('argb' => '5a5a5a'))));
	$spreadsheet->getActiveSheet()->SetCellValue('A2', 'Excel Export Data');
	$spreadsheet->getActiveSheet()->getStyle('A2')->getFont();
	
	/*Font Size for Cells*/
	$spreadsheet -> getActiveSheet()->getStyle('A2') -> applyFromArray(array('font' => array('size' => '14', 'bold' => true)), 'A2');
	
	/*Text Alignment Horizontal(HORIZONTAL_LEFT,HORIZONTAL_CENTER,HORIZONTAL_RIGHT)*/
	$spreadsheet -> getActiveSheet()->getStyle('A2') -> getAlignment()->setHorizontal(Alignment::VERTICAL_CENTER);
	
	/*Text Alignment Vertical(VERTICAL_TOP,VERTICAL_CENTER,VERTICAL_BOTTOM)*/
	$spreadsheet -> getActiveSheet() -> getStyle('A2')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	
	/*merge Cell*/
	$spreadsheet -> getActiveSheet() -> mergeCells('A2:F2');
	
	/*Fill Color Change function for Cells*/
	cellColor('A1:F3', 'd9e1ec');
	cellColor('A4:F4', '9ab1d1');
	
	/*Value Set for Cells*/
    $spreadsheet -> getActiveSheet()										
				->SetCellValue('A4', 'SL')						
				->SetCellValue('B4', 'Item Name')
				->SetCellValue('C4', 'Item Code')							
				->SetCellValue('D4', 'Date')							
				->SetCellValue('E4', 'Price')							
				->SetCellValue('F4', 'Quantity')
				->SetCellValue('J4', 'ItemNo');//This cell is hide for item id's
	
	/*Cell Visible True/False*/
	//This line is used for hide item id's
	$spreadsheet->getActiveSheet()->getColumnDimension('J')->setVisible(false);
	
	/*Font Size for Cells*/
	$spreadsheet -> getActiveSheet()->getStyle('A4') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'A4');	
	$spreadsheet -> getActiveSheet()->getStyle('B4') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'B4');
	$spreadsheet -> getActiveSheet()->getStyle('C4') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'C4');
	$spreadsheet -> getActiveSheet()->getStyle('D4') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'D4');
	$spreadsheet -> getActiveSheet()->getStyle('E4') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'E4');
	$spreadsheet -> getActiveSheet()->getStyle('F4') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'F4');
	$spreadsheet -> getActiveSheet()->getStyle('J4') -> applyFromArray(array('font' => array('size' => '12', 'bold' => true)), 'F4');
	
	/*Text Alignment Horizontal(HORIZONTAL_LEFT,HORIZONTAL_CENTER,HORIZONTAL_RIGHT)*/
	$spreadsheet -> getActiveSheet()->getStyle('A4') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
	$spreadsheet -> getActiveSheet()->getStyle('B4') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
	$spreadsheet -> getActiveSheet()->getStyle('C4') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
	$spreadsheet -> getActiveSheet()->getStyle('D4') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
	$spreadsheet -> getActiveSheet()->getStyle('E4') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
	$spreadsheet -> getActiveSheet()->getStyle('F4') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
	$spreadsheet -> getActiveSheet()->getStyle('J4') -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
	
	/*Text Alignment Vertical(VERTICAL_TOP,VERTICAL_CENTER,VERTICAL_BOTTOM)*/
	$spreadsheet -> getActiveSheet() -> getStyle('A4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('B4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('C4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('D4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('E4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('F4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	$spreadsheet -> getActiveSheet() -> getStyle('J4')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	
	/*Width for Cells*/
	$spreadsheet -> getActiveSheet() -> getColumnDimension('A') -> setWidth(5);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('B') -> setWidth(35);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('C') -> setWidth(20);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('D') -> setWidth(20);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('E') -> setWidth(20);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('F') -> setWidth(20);
	$spreadsheet -> getActiveSheet() -> getColumnDimension('J') -> setWidth(20);
	
	/*Wrap text*/
	$spreadsheet->getActiveSheet()->getStyle('A4')->getAlignment()->setWrapText(true);
	
	/*border color set for cells*/
	$spreadsheet -> getActiveSheet() -> getStyle('A4:A4') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('B4:B4') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('C4:C4') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('D4:D4') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('E4:E4') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('F4:F4') -> applyFromArray($styleThinBlackBorderOutline);
	$spreadsheet -> getActiveSheet() -> getStyle('J4:J4') -> applyFromArray($styleThinBlackBorderOutline);
	
	/*sql*/
	$sql = "SELECT id, ItemName, ItemCode,`Date`, Price, Quantity 
	FROM excelexport WHERE EItype = 'excel' ORDER BY id DESC;";
	$result = $pdo->query($sql);

	$i=1; 
	$j=5;
	while ($aRow = $result->fetch(PDO::FETCH_ASSOC)){
	
		/*Value Set for Cells*/
		$spreadsheet->getActiveSheet()
					->SetCellValue('A'.$j, $i)							
					->SetCellValue('B'.$j, $aRow['ItemName'])	
					->SetCellValue('C'.$j, $aRow['ItemCode'])																
					->SetCellValue('D'.$j, $aRow['Date'])																
					->SetCellValue('E'.$j, $aRow['Price'])															
					->SetCellValue('F'.$j, $aRow['Quantity'])
					->SetCellValue('J'.$j, $aRow['id']);//This cell is hide for item id's
				
		/*border color set for cells*/	
		$spreadsheet -> getActiveSheet() -> getStyle('A' . $j . ':A' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('B' . $j . ':B' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('C' . $j . ':C' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('D' . $j . ':D' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('E' . $j . ':E' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('F' . $j . ':F' . $j) -> applyFromArray($styleThinBlackBorderOutline);
		$spreadsheet -> getActiveSheet() -> getStyle('J' . $j . ':J' . $j) -> applyFromArray($styleThinBlackBorderOutline);
	
		/*Text Alignment Horizontal(HORIZONTAL_LEFT,HORIZONTAL_CENTER,HORIZONTAL_RIGHT)*/
		$spreadsheet -> getActiveSheet()->getStyle('A' . $j . ':A' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
		$spreadsheet -> getActiveSheet()->getStyle('B' . $j . ':B' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
		$spreadsheet -> getActiveSheet()->getStyle('C' . $j . ':C' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
		$spreadsheet -> getActiveSheet()->getStyle('D' . $j . ':D' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
		$spreadsheet -> getActiveSheet()->getStyle('E' . $j . ':E' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
		$spreadsheet -> getActiveSheet()->getStyle('F' . $j . ':F' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
		$spreadsheet -> getActiveSheet()->getStyle('J' . $j . ':J' . $j) -> getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
		
		/*Text Alignment Vertical(VERTICAL_TOP,VERTICAL_CENTER,VERTICAL_BOTTOM)*/
		$spreadsheet -> getActiveSheet() -> getStyle('A' . $j . ':A' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('B' . $j . ':B' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('C' . $j . ':C' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('D' . $j . ':D' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('E' . $j . ':E' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('F' . $j . ':F' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
		$spreadsheet -> getActiveSheet() -> getStyle('J' . $j . ':J' . $j)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
	
		/* Data Validation for Column E */
		$objValidation = $spreadsheet->getActiveSheet()->getCell('E' . $j)->getDataValidation();
		$objValidation->setType(DataValidation::TYPE_WHOLE);
		$objValidation->setErrorStyle(DataValidation::STYLE_STOP);
		$objValidation->setAllowBlank(true);
		$objValidation->setShowInputMessage(true);
		$objValidation->setShowErrorMessage(true);
		$objValidation->setErrorTitle('Input error');
		$objValidation->setError('Only Number is permitted!');			
		
		/* Data Validation for Column F */
		$objValidation = $spreadsheet->getActiveSheet()->getCell('F' . $j)->getDataValidation();
		$objValidation->setType(DataValidation::TYPE_WHOLE);
		$objValidation->setErrorStyle(DataValidation::STYLE_STOP);
		$objValidation->setAllowBlank(true);
		$objValidation->setShowInputMessage(true);
		$objValidation->setShowErrorMessage(true);
		$objValidation->setErrorTitle('Input error');
		$objValidation->setError('Only Number is permitted!');
	
		/*DateTime format for Cell D*/
		$spreadsheet->getActiveSheet()->getStyle('D'.$j)->getNumberFormat()->setFormatCode('yyyy-mm-dd'); //Date Format
		//$spreadsheet->getActiveSheet()->getStyle('D'.$j)->getNumberFormat()->setFormatCode('yyyy-mm-dd hh:mm:ss'); //DateTime Format	
	
		/*Number format for Cell E*/
		$spreadsheet->getActiveSheet()->getStyle('E'.$j)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
		$spreadsheet->getActiveSheet()->getStyle('E'.$j)->getNumberFormat()->setFormatCode('#,##0'); 
		// $spreadsheet->getActiveSheet()->getStyle('E'.$j)->getNumberFormat()->setFormatCode('#,##0.00'); 
		
		/*Number format for Cell F*/
		$spreadsheet->getActiveSheet()->getStyle('F'.$j)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
		$spreadsheet->getActiveSheet()->getStyle('F'.$j)->getNumberFormat()->setFormatCode('#,##0'); 
		
		/*Protected the Cell Range*/			
		$spreadsheet->getActiveSheet()->protectCells('J4:J'.$j, 'PHP'); 
		
		/*Unprotected the Cell Range*/
		$spreadsheet->getActiveSheet()->getStyle('A'.$j.':F'.$j)->getProtection()	
			->setLocked(Protection::PROTECTION_UNPROTECTED); 		
		
		if ($j % 2 == 0) {
			cellColor('A'.$j.':F'.$j, 'f4f8fb');
		}		
	
		$i++; $j++;
	}
	
	/*DateTime format for Cell D*/
	$spreadsheet->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode('yyyy-mm-dd'); //Date Format
	//$spreadsheet->getActiveSheet()->getStyle('D'.$j)->getNumberFormat()->setFormatCode('yyyy-mm-dd hh:mm:ss'); //DateTime Format	
	
	/*Number format for Cell E*/
	$spreadsheet->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode('#,##0'); 
	//$spreadsheet->getActiveSheet()->getStyle('E'.$j)->getNumberFormat()->setFormatCode('#,##0.00'); 
	
	/*Number format for Cell F*/
	$spreadsheet->getActiveSheet()->getStyle('F')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('F')->getNumberFormat()->setFormatCode('#,##0');
	
	/*Protected the Cell Range*/
	$spreadsheet->getActiveSheet()->protectCells('A1:F4', 'PHP'); 	
	
	/*Unprotected the Cell Range*/
	$spreadsheet->getActiveSheet()->getStyle('A:F')->getProtection()
		->setLocked(Protection::PROTECTION_UNPROTECTED);
		
	/*Protection the Worksheet*/
	$spreadsheet->getActiveSheet()->getProtection()->setSheet(true);		
	
	if (function_exists('date_default_timezone_set')) {
			date_default_timezone_set('UTC');
	} else {
			putenv("AU=UTC");
	}
	
	$exportTime = date("Y-m-d-His", time());
	
	$writer = new Xlsx($spreadsheet);
	$file = 'excel-export-data-'.$exportTime. '.xlsx'; //Save file name
	$writer->save('excel-export/' . $file);
	header('Location:excel-export/' . $file); //File open location
	
	function cellColor($cells, $color) {
		global $spreadsheet;
		$spreadsheet->getActiveSheet()->getStyle($cells)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($color);
	}
	
?>