php - 将Excel上载到MySql数据库时排除行

我有一个网站，可以将Excel的内容上载到MYSQL数据库。问题是我想忽略的行之间有空隙。

这是一个例子。



这是我的PHP代码：

<?php
session_start();
include("excel/Classes/PHPExcel/IOFactory.php");

$connect = mysqli_connect("","","", "");

$ficheiro = $_FILES['fileToUpload'];

$objPHPExcel = PHPExcel_IOFactory::load($ficheiro['tmp_name']);

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet){

    $highestRow = $worksheet->getHighestRow();
    for($row=34; $row<=306; $row++) //I have this for but I want to exclude those lines

    {
        $worksheet->getCellByColumnAndRow(2,$row)->getValue();
        $nif = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0,$row)->getValue());
        $MarcaExploracao = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1,$row)->getValue());
        $NumeroChip = mysqli_real_escape_string($connect, preg_replace('/[^0-9]/','',$worksheet->getCellByColumnAndRow(2,$row)->getValue()));
        $Number = ltrim($NumeroChip,'0');
        $MarcaAuricular = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(3,$row)->getValue());
        $Data_Nascimento = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(4,$row)->getValue());
        $ano = substr($Data_Nascimento,0,6);
        $anoValido = preg_replace('/[^0-9]/', '', $ano);
        $idade = date('Y')-$anoValido;
        //$unixDate = ($Data_Nascimento - 25569) * 86400;
        //$Data_Nascimento = gmdate("Y-m-d", $unixDate);
        $sql = "INSERT INTO animal(NIF,MarcaExploracao,numerochip,MarcaAuricular,Data_Nascimento,id_utilizador,ano,idade) VALUES(".$nif.", '".$MarcaExploracao."', '".$Number."', '".$MarcaAuricular."', '".$Data_Nascimento."',{$_SESSION['id_utilizador']},'".$anoValido."','".$idade."')";
        mysqli_query($connect,$sql);


    }
}
?>
    <script>
    alert("Excel adicionado");
    self.location="InserirOvelhas.php";
    </script>

最佳答案

因此，为必须在循环中设置的一个（或多个）列添加一个测试，并使用continue;使脚本跳至for循环中的下一个索引

<?php
session_start();
include("excel/Classes/PHPExcel/IOFactory.php");

$connect = mysqli_connect("","","", "");

$ficheiro = $_FILES['fileToUpload'];

$objPHPExcel = PHPExcel_IOFactory::load($ficheiro['tmp_name']);

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet){

    $highestRow = $worksheet->getHighestRow();
    for($row=34; $row<=332; $row++) {
        //I have this for but I want to exclude those lines
        // looks like a line that should not exist
        //$worksheet->getCellByColumnAndRow(2,$row)->getValue();

        // ignore rows
        if ( $row > 306 && $row < 312) {
            continue;
        }

        if ( $worksheet->getCellByColumnAndRow(0,$row)->getValue() == '' ) {
            continue;   // go to next for
        }


        $nif = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0,$row)->getValue());
        $MarcaExploracao = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1,$row)->getValue());
        $NumeroChip = mysqli_real_escape_string($connect, preg_replace('/[^0-9]/','',$worksheet->getCellByColumnAndRow(2,$row)->getValue()));
        $Number = ltrim($NumeroChip,'0');
        $MarcaAuricular = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(3,$row)->getValue());
        $Data_Nascimento = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(4,$row)->getValue());
        $ano = substr($Data_Nascimento,0,6);
        $anoValido = preg_replace('/[^0-9]/', '', $ano);
        $idade = date('Y')-$anoValido;
        //$unixDate = ($Data_Nascimento - 25569) * 86400;
        //$Data_Nascimento = gmdate("Y-m-d", $unixDate);
        $sql = "INSERT INTO animal(NIF,MarcaExploracao,numerochip,MarcaAuricular,Data_Nascimento,id_utilizador,ano,idade) VALUES(".$nif.", '".$MarcaExploracao."', '".$Number."', '".$MarcaAuricular."', '".$Data_Nascimento."',{$_SESSION['id_utilizador']},'".$anoValido."','".$idade."')";
        mysqli_query($connect,$sql);


    }
}
?>