<?php
require "../../config/koneksi.php";
require "../../vendor/autoload.php";
require "../../config/function.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$style_col = [
    'font' => ['bold' => true], // Set font nya jadi bold
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, // Set text jadi ditengah secara horizontal (center)
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
    ]
];
$style_row = [
    'alignment' => [
        'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER // Set text jadi di tengah secara vertical (middle)
    ],
    'borders' => [
        'top' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border top dengan garis tipis
        'right' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN],  // Set border right dengan garis tipis
        'bottom' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN], // Set border bottom dengan garis tipis
        'left' => ['borderStyle'  => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN] // Set border left dengan garis tipis
    ]
];

$sheet->setCellValue('A1', "   IMPORT DATA SISWA"); 
$sheet->mergeCells('A1:N1'); 
$sheet->getStyle('A1')->getFont()->setBold(true); 
$sheet->getStyle('A1')->getFont()->setSize(14); 
$sheet->mergeCells('A2:N2'); 
$sheet->setCellValue('A2', "   UNTUK JENJANG SD / SMP KOLOM JURUSAN KOSONGKAN SAJA");
$sheet->getStyle('A2')->getFont()->setBold(true);  
$sheet->mergeCells('A3:N3'); 
$sheet->setCellValue('A3', "   DILARANG MERUBAH KODE SERVER, UNTUK TINGKAT GUNAKAN ANGKA MISAL: 7 , 8, 9, 10, 11, 12 DAN UNTUK PHOTO BOLEH DIISI BOLEH TIDAK"); 



$sheet->setCellValue('A5', "NO"); 
$sheet->setCellValue('B5', "NIS"); 
$sheet->setCellValue('C5', "NISN"); 
$sheet->setCellValue('D5', "NAMA LENGKAP");
$sheet->setCellValue('E5', "TINGKAT");
$sheet->setCellValue('F5', "KELAS"); 
$sheet->setCellValue('G5', "JURUSAN"); 
$sheet->setCellValue('H5', "RUANG");
$sheet->setCellValue('I5', "SESI");  
$sheet->setCellValue('J5', "SERVER"); 
$sheet->setCellValue('K5', "AGAMA"); 
$sheet->setCellValue('L5', "KELAMIN"); 
$sheet->setCellValue('M5', "NO WA"); 
$sheet->setCellValue('N5', "PHOTO"); 

$sheet->getStyle('A5')->applyFromArray($style_col);
$sheet->getStyle('B5')->applyFromArray($style_col);
$sheet->getStyle('C5')->applyFromArray($style_col);
$sheet->getStyle('D5')->applyFromArray($style_col);
$sheet->getStyle('E5')->applyFromArray($style_col);
$sheet->getStyle('F5')->applyFromArray($style_col);
$sheet->getStyle('G5')->applyFromArray($style_col);
$sheet->getStyle('H5')->applyFromArray($style_col);
$sheet->getStyle('I5')->applyFromArray($style_col);
$sheet->getStyle('J5')->applyFromArray($style_col);
$sheet->getStyle('K5')->applyFromArray($style_col);
$sheet->getStyle('L5')->applyFromArray($style_col);
$sheet->getStyle('M5')->applyFromArray($style_col);
$sheet->getStyle('N5')->applyFromArray($style_col);

$sheet->getRowDimension('1')->setRowHeight(20);
$sheet->getRowDimension('2')->setRowHeight(20);
$sheet->getRowDimension('3')->setRowHeight(20);



$no = 1; 
$row=6;  
$sql = mysqli_query($koneksi, "SELECT * FROM aplikasi");
while($data = mysqli_fetch_array($sql)){ 
    $sheet->setCellValue('A' . $row, $no);
    $sheet->setCellValueExplicit('B' . $row, '2022107001',\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
    $sheet->setCellValueExplicit('C' . $row, '0087599901', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
    $sheet->setCellValue('D' . $row, 'AGUS MULYANA');
    $sheet->setCellValue('E' . $row, '9');
    $sheet->setCellValue('F' . $row, 'X-A');
	$sheet->setCellValue('G' . $row, '');
	$sheet->setCellValue('H' . $row, 'R1');
	$sheet->setCellValue('I' . $row, '1');
	$sheet->setCellValue('J' . $row, $data['id_server']);
	$sheet->setCellValue('K' . $row, 'Islam');
	$sheet->setCellValue('L' . $row, 'Laki-laki');
	$sheet->setCellValueExplicit('M' . $row, '081380899002', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
    $sheet->setCellValue('N' . $row, '');
   
    $sheet->getStyle('A' . $row)->applyFromArray($style_row);
    $sheet->getStyle('B' . $row)->applyFromArray($style_row);
    $sheet->getStyle('C' . $row)->applyFromArray($style_row);
    $sheet->getStyle('D' . $row)->applyFromArray($style_row);
    $sheet->getStyle('E' . $row)->applyFromArray($style_row);
    $sheet->getStyle('F' . $row)->applyFromArray($style_row);
	$sheet->getStyle('G' . $row)->applyFromArray($style_row);
    $sheet->getStyle('H' . $row)->applyFromArray($style_row);
    $sheet->getStyle('I' . $row)->applyFromArray($style_row);
    $sheet->getStyle('J' . $row)->applyFromArray($style_row);
    $sheet->getStyle('K' . $row)->applyFromArray($style_row);
    $sheet->getStyle('L' . $row)->applyFromArray($style_row);
	 $sheet->getStyle('M' . $row)->applyFromArray($style_row);
	 $sheet->getStyle('N' . $row)->applyFromArray($style_row);

    $sheet->getStyle('A' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); // Set text center untuk kolom No
    $sheet->getStyle('B' . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT); // Set text left untuk kolom NIS

    $sheet->getRowDimension($row)->setRowHeight(20); 

    $no++; 
    $row++; 
}

// Set width kolom
$sheet->getColumnDimension('A')->setWidth(5); 
$sheet->getColumnDimension('B')->setWidth(15); 
$sheet->getColumnDimension('C')->setWidth(15); 
$sheet->getColumnDimension('D')->setWidth(30); 
$sheet->getColumnDimension('E')->setWidth(10); 
$sheet->getColumnDimension('F')->setWidth(10);
$sheet->getColumnDimension('G')->setWidth(15); 
$sheet->getColumnDimension('H')->setWidth(8); 
$sheet->getColumnDimension('I')->setWidth(8); 
$sheet->getColumnDimension('J')->setWidth(10); 
$sheet->getColumnDimension('K')->setWidth(10); 
$sheet->getColumnDimension('L')->setWidth(15); 
$sheet->getColumnDimension('M')->setWidth(13); 
$sheet->getColumnDimension('N')->setWidth(15); 

$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);


$sheet->setTitle("Data Siswa");

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="Impor Data Siswa.xlsx"'); 
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');

?>
