<?php
set_time_limit(20000);
ini_set('memory_limit','-1');
require_once './PHPExcel.php';
require_once './PHPExcel/IOFactory.php';
require_once './PHPExcel/Reader/Excel5.php';
$dsn = "mysql:host=localhost;dbname=lab2_crud;";
$user = "fred";
$password = "bRKRAqCbVEQLn9e7";
try{
    $dbh = new PDO($dsn,$user,$password);
    $dbh->query('set names utf8;');
}catch(PDOException $e){
    echo "Failed to connection".$e->getMessage();
}


$filePath = "STOCK.xlsx";
$PHPReader = new PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead($filePath)){
    $PHPReader = new PHPExcel_Reader_Excel5();
    if(!$PHPReader->canRead($filePath)){
        echo 'no Excel';
        return ;
    }
}
$PHPExcel = $PHPReader->load($filePath);
$sheetCount = $PHPExcel->getSheetCount();
    for($i = 0; $i< $sheetCount; $i ++){
        $currentSheet = $PHPExcel->getSheet($i);
        $highestColumn = $currentSheet->getHighestColumn();
        $highestRow = $currentSheet->getHighestRow();

        $stmt = $dbh->prepare("insert into products(Name,IID, Type) values (:Name,:IID,:Type) ");
        $stmt->bindParam(":Name", $Name, PDO::PARAM_STR);
        $stmt->bindParam(":IID", $IID, PDO::PARAM_STR);
        $stmt->bindParam(":Type", $IID, PDO::PARAM_STR);
        for ($j = 2; $j <= $highestRow; $j++) {
            $IID = $PHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();
            $Name = $PHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();
            $Type = $i;
            if ($IID != "" && $Name != "") {
                echo "$IID:$Name : $Type";
                echo "<br>";
                $stmt->execute();
            }
        }
    }

?>