<?php
/*
Install: composer require phpoffice/phpspreadsheet:dev-develop

Github: https://github.com/PHPOffice/PhpSpreadsheet/
Document: https://phpspreadsheet.readthedocs.io/
*/

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;


// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
// Set document properties
$spreadsheet->getProperties()->setCreator('PhpOffice')
        ->setLastModifiedBy('PhpOffice')
        ->setTitle('Office 2007 XLSX Test Document')
        ->setSubject('Office 2007 XLSX Test Document')
        ->setDescription('PhpOffice')
        ->setKeywords('PhpOffice')
        ->setCategory('PhpOffice');

// Add some data
$spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('A1', 'Hello');
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('SBI-20000');

$spreadsheet->createSheet();

// Add some data
$spreadsheet->setActiveSheetIndex(1)
        ->setCellValue('A1', '');

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('SBI>20000');

$spreadsheet->createSheet();

// Add some data
$spreadsheet->setActiveSheetIndex(2)
        ->setCellValue('A1', '');

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('NON SBI-20000');

$spreadsheet->createSheet();

$spreadsheet->setActiveSheetIndex(3)
        ->setCellValue('A1', '');

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('NON SBI>20000');


$spreadsheet->createSheet();

$spreadsheet->setActiveSheetIndex(4)
        ->setCellValue('A1', '');

// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle('all data');



if (isset($_POST['submit'])) {
  
    $file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      
    if(isset($_FILES['file']['name']) && in_array($_FILES['file']['type'], $file_mimes)) {
      
        $arr_file = explode('.', $_FILES['file']['name']);
        $extension = end($arr_file);
		$file_type="Xlsx";

        if('csv' == $extension) {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
        } else {
            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        }
      if('xls' == $extension) {
           $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
		}
  

  
        $spreadsheet1 = $reader->load($_FILES['file']['tmp_name']);

$worksheet = $spreadsheet1->getActiveSheet(2);
        $sheetData = $spreadsheet1->getActiveSheet()->toArray();




 $cn0=3; 
 $cn1=3; 
 $cn2=3; 
 $cn3=3;
 $cn4=3;
 
 $cn=3;

$n=0;
$i=0;
$dn = $sheetData[$i][0];
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A1', $dn);
$n++;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A1', $dn);

$n++;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A1', $dn);
$n++;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A1', $dn);


$dn='Employee Code';
$dsn='Employee Name';
$dtyp='Bank Name';
$dloc='Account No';
$dbr='IFSC Code';
$np='Net Pay';



$n=0;
              


$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn0, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn0, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn0, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn0, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn0, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn0, $np);

$n++;
$pp='$cn'.$n;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn1, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn1, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn1, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn1, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn1, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn1, $np);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('g'.$cn1, $pp);
$n++;
$pp='$cn'.$n;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn2, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn2, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn2, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn2, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn2, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn2, $np);
$n++;
$pp='$cn'.$n;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn3, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn3, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn3, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn3, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn3, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn3, $np);
 
$n++;
$pp='$cn'.$n;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn, $np);
 


        if (!empty($sheetData)) {
            for ($i=3; $i<count($sheetData); $i++) { //skipping first row
                $dn = $sheetData[$i][0];
                $dsn = $sheetData[$i][1];
				$dtyp=$sheetData[$i][2];
				$dloc= $sheetData[$i][3];
				$dbr=$sheetData[$i][4];
				$np=$sheetData[$i][5];


$mm=0;

$dp=substr($dbr,0,4);

switch($dp) {
  case 'SBIN':
     $mm=1;
 	break;
   
}				
				
  $n=1;	
  if ( ($mm==1) and ($np<=20000)) { 
			$n=0;
		$cn0++;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn0, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn0, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn0, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn0, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn0, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn0, $np);



  }
  if ( ($mm==1) and ($np>20000)) { 
		$n=1;
		$cn1++;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn1, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn1, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn1, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn1, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn1, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn1, $np);
  }
 if ( ($mm==0) and ($np<=20000)) { 
		$n=2;
		$cn2++;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn2, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn2, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn2, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn2, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn2, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn2, $np);
  }
 if ( ($mm==0) and ($np>20000)) { 
		$n=3;
 		$cn3++;
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('A'.$cn3, $dn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('B'.$cn3, $dsn);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('C'.$cn3, $dtyp);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('D'.$cn3, $dloc);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('E'.$cn3, $dbr);
$spreadsheet->setActiveSheetIndex($n)
        ->setCellValue('F'.$cn3, $np);
}

 



  
$nn=4;
$spreadsheet->setActiveSheetIndex($nn)
        ->setCellValue('A'.$cn, $dn);
$spreadsheet->setActiveSheetIndex($nn)
        ->setCellValue('B'.$cn, $dsn);
$spreadsheet->setActiveSheetIndex($nn)
        ->setCellValue('C'.$cn, $dtyp);
$spreadsheet->setActiveSheetIndex($nn)
        ->setCellValue('D'.$cn, $dloc);
$spreadsheet->setActiveSheetIndex($nn)
        ->setCellValue('E'.$cn, $dbr);
$spreadsheet->setActiveSheetIndex($nn)
        ->setCellValue('F'.$cn, $np);
 


$cn++;

             }
			 

$cn0++;			 
$first_i = 4;
$last_i = $cn0 - 1;
$sumrange = 'f' . $first_i . ':f' . $last_i;

$spreadsheet->setActiveSheetIndex(0)
        ->setCellValue('F' . $cn0, '=SUM(' . $sumrange . ')');

$cn1++;			 
$first_i = 4;
$last_i = $cn1 - 1;
$sumrange = 'f' . $first_i . ':f' . $last_i;

$spreadsheet->setActiveSheetIndex(1)
        ->setCellValue('F' . $cn1, '=SUM(' . $sumrange . ')');

$cn2++;			 
$first_i = 4;
$last_i = $cn2 - 1;
$sumrange = 'f' . $first_i . ':f' . $last_i;

$spreadsheet->setActiveSheetIndex(2)
        ->setCellValue('F' . $cn2, '=SUM(' . $sumrange . ')');
 
$cn3++;			 
$first_i = 4;
$last_i = $cn3 - 1;
$sumrange = 'f' . $first_i . ':f' . $last_i;

$spreadsheet->setActiveSheetIndex(3)
        ->setCellValue('F' . $cn3, '=SUM(' . $sumrange . ')');

 
$cn++;			 
$first_i = 4;
$last_i = $cn - 1;
$sumrange = 'f' . $first_i . ':f' . $last_i;

$spreadsheet->setActiveSheetIndex(4)
        ->setCellValue('F' . $cn, '=SUM(' . $sumrange . ')');




// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet1->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="Bank Statement.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;

}
	}
}
?>



 <form method="post" enctype="multipart/form-data">
    <input type="file" name="file" />
    <p><button type="submit" name="submit">Submit</button></p>
</form>