<?php
function ExcelToArray($file,$keycol=NULL,$col=[]){
    if(!isset($_FILES[$file])){
        return "Invalid File"; //no file
    }
    $path_parts = pathinfo($_FILES[$file]["name"]);
    $extension = $path_parts['extension'];
    if($extension != 'xls' &&  $extension != 'xlsx')return "Invalid Excel File"; //invalid file
    //move file to temp location
    do{
        $randnum = mt_rand(3000000,9000000000);
    }while(file_exists($randnum.".".$extension));
    
    //move the file
    if(move_uploaded_file($_FILES[$file]["tmp_name"],$randnum.".".$extension)){
       // $exceltype = strtolower(trim($extension)) == "xls"?"Excel5":"Excel2007";
       $exceltype = PHPExcel_IOFactory::identify($randnum.".".$extension);
        $objReader = PHPExcel_IOFactory::createReader($exceltype);
        //exit($exceltype);
        if ($exceltype === 'Excel5' || $exceltype === 'Excel2007') {
            $objReader->setReadDataOnly(true);
        }else{
            return "Invalid Excel File"; 
        }
        //$objReader->setReadDataOnly(true);
        
        $objPHPExcel = $objReader->load($randnum.".".$extension);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        
        $highestRow = $objWorksheet->getHighestRow(); // e.g. 10
        $highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
        
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
       
        //get the reg,ca and exam lookup col
        //rstudstudy=2&rstudfac=4&rstuddept=34&rstudprog=34&rstudlvl=1&semest=1&sestb=8&rcourse=1420&sprstupload_file=?&colregno=2&colca=4&colexam=5
        //"sestb","rstudstudy","rstudfac","rstuddept","rstudprog","rstudlvl","semest","rcourse"
       /*  $colregno = (int)$_POST['colregno'];
        $colca = (int)$_POST['colca'];
        $colexam = (int)$_POST['colexam']; */
        $highestColumnNumber = $highestColumnIndex + 1;
        if($highestColumnNumber < count($col) && count($col) > 0)return "Sheet does not contain the number of column specified"; //invalid data in excel or invalid column number specified
        //$ff = "";
        if(count($col) > 0){
            foreach($col as $indcol){
               // $ff .= $indcol.":";
                $indcol = is_numeric($indcol)?$indcol:PHPExcel_Cell::columnIndexFromString($indcol);
                //$ff .= $indcol." ";
                if($highestColumnNumber < (int)$indcol){
                    return "Invalid Column Specified in Sheet";
                }
            }
        }
        //$ss = PHPExcel_Cell::columnIndexFromString("F");
        //return $ss . "";
       /*  $colregnoIndex = $colregno - 1;
        $colcaIndex = $colca - 1;
        $colexamIndex = $colexam - 1; */
        
        //loop to convert the excel to array
        //using the regNo as key
        $DataArray = [];
        for ($row = 1; $row <= $highestRow; ++$row) {
            $datarr = [];
            if(count($col) > 0){ //if user specify coloum to load
             
              foreach($col as $indcol){
                $indcol = is_numeric($indcol)?$indcol:PHPExcel_Cell::columnIndexFromString($indcol);
                $datarr[] = $objWorksheet->getCellByColumnAndRow($indcol-1, $row)->getValue();
              }
            }else{
                for($s=0;$s<=$highestColumnIndex;$s++){
                    $datarr[] = $objWorksheet->getCellByColumnAndRow($s, $row)->getValue();  
                }
            }
            if(!is_null($keycol)){
                $keycol = is_numeric($keycol)?$keycol:PHPExcel_Cell::columnIndexFromString($keycol);
                if($keycol <= $highestColumnNumber){
                    $keycolval = $objWorksheet->getCellByColumnAndRow($keycol-1, $row)->getValue();
                if(trim($keycolval) != ""){
                    $DataArray[strtolower(trim($keycolval))] = $datarr;
                }else{
                    continue;
                }
                }else{
                    $DataArray[] = $datarr;
                }
                
            }else{
                $DataArray[] = $datarr;
            }
          /*   $regNo = $objWorksheet->getCellByColumnAndRow($colregnoIndex, $row)->getValue();
            if(trim($regNo) == "")continue;
            $ca = $objWorksheet->getCellByColumnAndRow($colcaIndex, $row)->getValue();
            $exam = $objWorksheet->getCellByColumnAndRow($colexamIndex, $row)->getValue();
            $DataArray[strtolower(trim($regNo))] = [(float)$ca,(float)$exam]; */
        }
        //get the regno,ca and exam column num
        
        unlink($randnum.".".$extension);
        return $DataArray;
    }else{
      return "Reading/Loading File Failed"; //cannot read/move file 
    }
}

?>