<?php

spl_autoload_register(function ($mod) {
    require('../../../../../asset/mod/' . $mod . '.php');
});

// Load file autoload.php
require '../../../../../asset/bootstrap/asset/phpspreadsheet/autoload.php';


date_default_timezone_set('Asia/Jakarta');
$tanggal = mktime(date("m"), date("d"), date("Y"));
$tglsekarang = date("d-m-Y", $tanggal);
$tahunnow = date("Y", $tanggal);

if (isset($_GET['tahun'])) {
    $tahun = $_GET['tahun'];
} else {
    $tahun = $tahunnow;
};
$tahun1 = substr($tahun, 0, 4);
$tahun2 = substr($tahun, 5, 4);

$tahun_tabel = substr($tahun, 2, 2) . substr($tahun, 7, 2);

$tb_siswa = "tb_siswa" . $tahun_tabel;
$tb_transaksi = "db_transaksi" . $tahun_tabel;
$tb_detailakun = "detail_akun" . $tahun_tabel;
$tb_setor = "setor_kjks" .  $tahun_tabel;
$tb_transaksisim = "sim_transaksi" . $tahun_tabel;



if (isset($_GET['jenjang'])) {
    $jenjang_lap = $_GET['jenjang'];
} else {
    $jenjang_lap = "Semua";
};
if (isset($_GET['paralel'])) {
    $paralel_lap = $_GET['paralel'];
} else {
    $paralel_lap = "Semua";
};
if (isset($_GET['jenis'])) {
    $jenis_lap = $_GET['jenis'];
} else {
    $jenis_lap = "Semua";
};

$dt_jenis = new namaBayar();
$showJenis = $dt_jenis->shownamaBayar($tahun, $jenis_lap, $jenjang_lap);
$count_nb = $showJenis->rowCount();
$datajenis = $showJenis->fetch(PDO::FETCH_OBJ);
if ($count_nb > 0) {
    $namaBayar = $datajenis->nama;
    $bulanan = $datajenis->bulanan;
    $filterJenis = "and jenis='$datajenis->kode'";
} else {
    $namaBayar = "Semua";
    $bulanan = "Tidak";
    $filterJenis = "";
};



if ($bulanan == "Ya" or $bulanan == "ya") {
    $tahap1 = "Juli";
    $tahap2 = "Agt";
    $tahap3 = "Sept";
    $tahap4 = "Okt";
    $tahap5 = "Nop";
    $tahap6 = "Des";
    $tahap7 = "Jan";
    $tahap8 = "Feb";
    $tahap9 = "Mar";
    $tahap10 = "Apr";
    $tahap11 = "Mei";
    $tahap12 = "Jun";
} else {
    $tahap1 = "Thp-1";
    $tahap2 = "Thp-2";
    $tahap3 = "Thp-3";
    $tahap4 = "Thp-4";
    $tahap5 = "Thp-5";
    $tahap6 = "Thp-6";
    $tahap7 = "Thp-7";
    $tahap8 = "Thp-8";
    $tahap9 = "Thp-9";
    $tahap10 = "Thp-10";
    $tahap11 = "Thp-11";
    $tahap12 = ">=12";
};


//Query Tanggungan
$classTanggungan = new listTanggungan();
$val_sumtang = $classTanggungan->sumTanggunganByFilter($tb_siswa, $jenjang_lap, $paralel_lap, $jenis_lap);

if ($val_sumtang == "" || $val_sumtang  == 0) {
    $sumtanggungan = number_format(0, 2, ",", ".");
    $v_sumtanggungan = 0;
} else {
    $sumtanggungan = number_format($val_sumtang, 2, ",", ".");
    $v_sumtanggungan = $val_sumtang;
};
//Terbayar & Kurang
$classTrans = new transaksi();
$Terbayar = $classTrans->sumTerbayar($tb_transaksi, $jenjang_lap, $paralel_lap, $jenis_lap);
$sumterbayar = @(number_format($Terbayar, 2, ".", "."));
$kurang = $v_sumtanggungan - $Terbayar;
$sumkurang = @(number_format(($kurang), 2, ".", "."));


//Indo to Egg
function ubahformatTgl($tanggal)
{
    $pisah = explode('-', $tanggal);
    $urutan = array($pisah[2], $pisah[1], $pisah[0]);
    $satukan = implode('-', $urutan);
    return $satukan;
}
// Cara penggunaan function ubahTgl
$clssekolah = new sekolah;

// Include librari PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet1 = $spreadsheet->setActiveSheetIndex(0);

$spreadsheet->getActiveSheet()->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_PORTRAIT);
$spreadsheet->getActiveSheet()->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);
$spreadsheet->getActiveSheet()->getPageMargins()->setTop(1);
$spreadsheet->getActiveSheet()->getPageMargins()->setLeft(1);
$spreadsheet->getActiveSheet()->getPageMargins()->setRight(1);
$spreadsheet->getActiveSheet()->getPageMargins()->setBottom(1);
// Buat sebuah variabel untuk menampung pengaturan style dari header tabel
$style_judul = [
    'font' => ['bold' => true], // Set font nya jadi bold
    'font-family' => ['Arial'], // 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)
    ],

];
$style_col = [
    'font' => ['bold' => true], // Set font nya jadi bold
    'font-family' => ['Arial'], // 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
    ]
];

// Buat sebuah variabel untuk menampung pengaturan style dari isi tabel
$style_row = [
    'font-family' => ['Arial'], // Set font nya jadi bold
    '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
    ]
];


$sheet1->getStyle('A1')->getFont()->setBold(true);
$sheet1->getStyle('A1')->getFont()->setSize(15);
$sheet1->getStyle('A1:F1000')->getFont()->setName('Arial');

//for ($i = 'A'; $i !=  $sheet1->getHighestColumn(); $i++) {
//    $sheet1->getColumnDimension($i)->setAutoSize(TRUE);
//}
$sheet1->getColumnDimension('A')->setWidth(5);
$sheet1->getColumnDimension('B')->setWidth(25);
$sheet1->getColumnDimension('C')->setWidth(10);
$sheet1->getColumnDimension('D')->setWidth(10);
$sheet1->getColumnDimension('E')->setWidth(15);
$sheet1->getColumnDimension('F')->setWidth(15);
$sheet1->getColumnDimension('G')->setWidth(15);
$sheet1->getColumnDimension('H')->setWidth(15);
$sheet1->getColumnDimension('I')->setWidth(15);
$sheet1->getColumnDimension('J')->setWidth(15);
$sheet1->getColumnDimension('K')->setWidth(15);
$sheet1->getColumnDimension('L')->setWidth(15);
$sheet1->getColumnDimension('M')->setWidth(15);
$sheet1->getColumnDimension('N')->setWidth(15);
$sheet1->getColumnDimension('O')->setWidth(15);
$sheet1->getColumnDimension('P')->setWidth(15);
$sheet1->getColumnDimension('Q')->setWidth(15);
$sheet1->getColumnDimension('R')->setWidth(15);
$sheet1->getColumnDimension('S')->setWidth(15);
$sheet1->getColumnDimension('T')->setWidth(15);


$sheet1->setCellValue('A1', 'LAPORAN REKAPITULASI [MODEL-3]');
$sheet1->setCellValue('A2', $clssekolah->datasekolah('sekolah'));
$sheet1->mergeCells('A1:T1');
$sheet1->mergeCells('A2:T2');

$sheet1->setCellValue('A4', 'Kelas');
$sheet1->setCellValue('A5', 'Jenis Pembayaran');

$sheet1->setCellValue('C4', ': ' . $jenjang_lap . "-" . $paralel_lap);
$sheet1->setCellValue('C5', ': ' . $jenis_lap . " - " . $namaBayar);


// Buat header tabel nya pada baris ke 3
$sheet1->setCellValue('A7', 'No');
$sheet1->setCellValue('B7', 'Nama');
$sheet1->setCellValue('C7', 'NIS');
$sheet1->setCellValue('D7', 'Kelas');
$sheet1->setCellValue('E7', 'Tanggungan');
$sheet1->setCellValue('F7', $tahap1);
$sheet1->setCellValue('G7', $tahap2);
$sheet1->setCellValue('H7', $tahap3);
$sheet1->setCellValue('I7', $tahap4);
$sheet1->setCellValue('J7', $tahap5);
$sheet1->setCellValue('K7', $tahap6);
$sheet1->setCellValue('L7', $tahap7);
$sheet1->setCellValue('M7', $tahap8);
$sheet1->setCellValue('N7', $tahap9);
$sheet1->setCellValue('O7', $tahap10);
$sheet1->setCellValue('P7', $tahap11);
$sheet1->setCellValue('Q7', $tahap12);
$sheet1->setCellValue('R7', 'Jumlah');
$sheet1->setCellValue('S7', 'Kurang');

// Apply style header yang telah kita buat tadi ke masing-masing kolom header
$sheet1->getStyle('A1:T1')->applyFromArray($style_judul);
$sheet1->getStyle('A2:T2')->applyFromArray($style_judul);

$spreadsheet->getActiveSheet(1)->getStyle('A7:T7')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('d5dbdb');
$sheet1->getStyle('A7:T7')->applyFromArray($style_row);
// Set height baris ke 1, 2 dan 3
$sheet1->getRowDimension('1')->setRowHeight(20);


// Buat query untuk menampilkan semua data siswa

$classLaporan = new laporan();
$showList = $classLaporan->showRekap($tb_siswa, $jenjang_lap, $paralel_lap, 'jenjang asc, paralel asc, nama asc');
$count_siswa = $showList->rowCount();
$no = 0; // Untuk penomoran tabel, di awal set dengan 1
$row = 7;
$row_end = $row + $count_siswa;
$row_jumlah = $row + $count_siswa + 1;
while ($data = $showList->fetch(PDO::FETCH_OBJ)) {

    $nis = $data->nis;
    $id = $data->id;
    $nama = $data->nama;
    $jenjang = $data->jenjang;
    $paralel = $data->paralel;
    $kelas = $jenjang . '-' . $paralel;

    //Query Tanggungan
    $classTanggungan = new listTanggungan();
    $val_tang = $classTanggungan->tanggunganByKode($tb_siswa, 'id', $id, $jenis_lap);
    if ($val_tang == "" || $val_tang  == 0) {
        $tanggungan = number_format(0, 0, ",", ".");
        $v_tanggungan = 0;
    } else {
        $tanggungan = number_format($val_tang, 0, ",", ".");
        $v_tanggungan = $val_tang;
    };
    //Terbayar & Kurang
    $classTrans = new transaksi();
    $Terbayar = $classTrans->infoterbayarIf($tb_transaksi, $nis, $jenis_lap);
    //$terbayar = @(number_format($Terbayar, 0, ".", "."));
    $kurang = $v_tanggungan - $Terbayar;
    //$kurang = @(number_format(($kurang), 0, ".", "."));



    $no++;
    $row++;
    $sheet1->setCellValue('A' . $row, $no);
    $sheet1->setCellValue('B' . $row, $nama);
    $sheet1->setCellValue('C' . $row, $nis);
    $sheet1->setCellValue('D' . $row, $kelas);
    $sheet1->setCellValue('E' . $row, $v_tanggungan);
    $sheet1->setCellValue('F' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '1', $nis,  $filterJenis));
    $sheet1->setCellValue('G' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '2', $nis,  $filterJenis));
    $sheet1->setCellValue('H' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '3', $nis,  $filterJenis));
    $sheet1->setCellValue('I' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '4', $nis,  $filterJenis));
    $sheet1->setCellValue('J' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '5', $nis,  $filterJenis));
    $sheet1->setCellValue('K' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '6', $nis,  $filterJenis));
    $sheet1->setCellValue('L' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '7', $nis,  $filterJenis));
    $sheet1->setCellValue('M' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '8', $nis,  $filterJenis));
    $sheet1->setCellValue('N' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '9', $nis,  $filterJenis));
    $sheet1->setCellValue('O' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '10', $nis,  $filterJenis));
    $sheet1->setCellValue('P' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '11', $nis,  $filterJenis));
    $sheet1->setCellValue('Q' . $row, $classLaporan->sumTahapXlsx($tb_transaksi, '>=12', $nis,  $filterJenis));
    $sheet1->setCellValue('R' . $row, $Terbayar);
    $sheet1->setCellValue('S' . $row, $kurang);

    $sheet1->getStyle('A' . $row . ':A' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet1->getStyle('B' . $row . ':B' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
    $sheet1->getStyle('C' . $row . ':C' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet1->getStyle('D' . $row . ':D' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $sheet1->getStyle('E' . $row . ':E' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('F' . $row . ':F' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('G' . $row . ':G' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('H' . $row . ':H' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('I' . $row . ':I' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('J' . $row . ':J' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('K' . $row . ':K' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('L' . $row . ':L' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('M' . $row . ':M' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('N' . $row . ':N' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('O' . $row . ':O' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('P' . $row . ':P' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('Q' . $row . ':Q' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('R' . $row . ':R' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
    $sheet1->getStyle('S' . $row . ':S' . $row_end)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);


    $sheet1->getStyle('A1:S' . $row_end)->getFont()->setName('Arial');
    $sheet1->getStyle('A8:S' . $row_end)->getFont()->setSize(10);

    $sheet1->getStyle('A' . $row . ':A' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('B' . $row . ':B' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('C' . $row . ':C' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('D' . $row . ':D' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('E' . $row . ':E' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('F' . $row . ':F' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('G' . $row . ':G' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('H' . $row . ':H' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('I' . $row . ':I' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('J' . $row . ':J' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('K' . $row . ':K' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('L' . $row . ':L' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('M' . $row . ':M' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('N' . $row . ':N' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('O' . $row . ':O' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('P' . $row . ':P' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('Q' . $row . ':Q' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('R' . $row . ':R' . $row_end)->applyFromArray($style_row);
    $sheet1->getStyle('S' . $row . ':S' . $row_end)->applyFromArray($style_row);
}


$sheet1->setCellValue('A' . $row_jumlah, 'JUMLAH');
$sheet1->mergeCells('A' . $row_jumlah . ':D' . $row_jumlah);

$sheet1->setCellValue('E' . $row_jumlah, "=SUM(E8:E" . $row_end . ")");
$sheet1->setCellValue('F' . $row_jumlah, "=SUM(F8:F" . $row_end . ")");
$sheet1->setCellValue('G' . $row_jumlah, "=SUM(G8:G" . $row_end . ")");
$sheet1->setCellValue('H' . $row_jumlah, "=SUM(H8:H" . $row_end . ")");
$sheet1->setCellValue('I' . $row_jumlah, "=SUM(I8:I" . $row_end . ")");
$sheet1->setCellValue('J' . $row_jumlah, "=SUM(J8:J" . $row_end . ")");
$sheet1->setCellValue('K' . $row_jumlah, "=SUM(K8:K" . $row_end . ")");
$sheet1->setCellValue('L' . $row_jumlah, "=SUM(L8:L" . $row_end . ")");
$sheet1->setCellValue('M' . $row_jumlah, "=SUM(M8:M" . $row_end . ")");
$sheet1->setCellValue('N' . $row_jumlah, "=SUM(N8:N" . $row_end . ")");
$sheet1->setCellValue('O' . $row_jumlah, "=SUM(O8:O" . $row_end . ")");
$sheet1->setCellValue('P' . $row_jumlah, "=SUM(P8:P" . $row_end . ")");
$sheet1->setCellValue('Q' . $row_jumlah, "=SUM(Q8:Q" . $row_end . ")");
$sheet1->setCellValue('R' . $row_jumlah, "=SUM(R8:R" . $row_end . ")");
$sheet1->setCellValue('S' . $row_jumlah, "=SUM(S8:S" . $row_end . ")");

$sheet1->getStyle('A' . $row . ':A' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('B' . $row . ':B' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('C' . $row . ':C' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('D' . $row . ':D' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('E' . $row . ':E' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('F' . $row . ':F' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('G' . $row . ':G' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('H' . $row . ':H' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('I' . $row . ':I' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('J' . $row . ':J' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('K' . $row . ':K' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('L' . $row . ':L' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('M' . $row . ':M' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('N' . $row . ':N' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('O' . $row . ':O' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('P' . $row . ':P' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('Q' . $row . ':Q' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('R' . $row . ':R' . $row_jumlah)->applyFromArray($style_row);
$sheet1->getStyle('S' . $row . ':S' . $row_jumlah)->applyFromArray($style_row);

$sheet1->getStyle('E' . $row . ':E' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('F' . $row . ':F' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('G' . $row . ':G' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('H' . $row . ':H' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('I' . $row . ':I' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('J' . $row . ':J' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('K' . $row . ':K' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('L' . $row . ':L' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('M' . $row . ':M' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('N' . $row . ':N' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('O' . $row . ':O' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('P' . $row . ':P' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('Q' . $row . ':Q' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('R' . $row . ':R' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$sheet1->getStyle('S' . $row . ':S' . $row_jumlah)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

$sheet1->getStyle('A' . $row_jumlah . ':S' . $row_jumlah)->getFont()->setName('Arial');
$sheet1->getStyle('A' . $row_jumlah . ':S' . $row_jumlah)->getFont()->setSize(10);

$spreadsheet->getActiveSheet()->setTitle("Laporan");
// Proses file excel
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="Laporan Rekapitulasi.xlsx"'); // Set nama file excel nya
header('Cache-Control: max-age=0');

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