Home:ALL Converter>Unable to upload excel file in php to mysql database

Unable to upload excel file in php to mysql database

Ask Time:2018-11-13T23:13:16         Author:Giant Gibbon

Json Formatter

I have been trying to build a section in php file from where i can upload a excel files to the database.I did all the coding part but its not working.Data was not uploaded. So please help me.

Here is my code

This is the html part from where i will be uploading the excel file using enctype="multipart/form-data":

<div class="panel panel-primary">

    <div class="panel-body">
    <form method="POST" action="uploads.php?y='.$y.'&m='.$m.'" style="margin-left: 100px;" enctype="multipart/form-data">
    <label for="fileToUpload">Select file to upload:
    <br/> <small style="color: red;">*Please attach only .xls & .xlsx files. 
   </small>
    </label>
<input class="form-control" type="file" name="fileToUpload" id="fileToUpload" style="width:300px; align: center;">
<br/>
<input class="btn btn-sm btn-info" type="submit" value="Upload File" name="submit">
    </form>
</div>

</div>

PHP file uploads.php :

Data of the excel file is then sent to the php file to upload in the database, But this isn't working.

  require_once('vendor/php-excel-reader/excel_reader2.php');
    require_once('vendor/SpreadsheetReader.php');

    $y=$_GET['y'];
    $m=$_GET['m'];
    $year=$y.'_'.$m;

   if (isset($_POST["submit"])){

$allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

if(in_array($_FILES["file"]["type"],$allowedFileType))
{

    $targetPath = 'uploads/'.$_FILES['file']['name'];
    move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);

    $Reader = new SpreadsheetReader($targetPath);

    $sheetCount = count($Reader->sheets());
    for($i=0;$i<$sheetCount;$i++)
    {
        $Reader->ChangeSheet($i);

        foreach ($Reader as $Row) {

            $date = "";
            if(isset($Row[0])) {
                $date = mysqli_real_escape_string($con,$Row[0]);
            }

            $janmin = "";
            if(isset($Row[1])) {
                $janmin = mysqli_real_escape_string($con,$Row[1]);
            }

            $janmax = "";
            if(isset($Row[2])) {
                $janmax = mysqli_real_escape_string($con,$Row[2]);
            }               

            $febmin = "";
            if(isset($Row[3])) {
                $febmin = mysqli_real_escape_string($con,$Row[3]);
            }

            $febmax = "";
            if(isset($Row[4])) {
                $febmax = mysqli_real_escape_string($con,$Row[4]);
            }

            $marmin = "";
            if(isset($Row[5])) {
                $marmin = mysqli_real_escape_string($con,$Row[5]);
            }

            $marmax = "";
            if(isset($Row[6])) {
                $marmax = mysqli_real_escape_string($con,$Row[6]);
            }

            $aprmin = "";
            if(isset($Row[7])) {
                $aprmin = mysqli_real_escape_string($con,$Row[7]);
            }

            $aprmax = "";
            if(isset($Row[8])) {
                $aprmax = mysqli_real_escape_string($con,$Row[8]);
            }

            $maymin = "";
            if(isset($Row[9])) {
                $maymin = mysqli_real_escape_string($con,$Row[9]);
            }

            $maymax = "";
            if(isset($Row[10])) {
                $maymax = mysqli_real_escape_string($con,$Row[10]);
            }

            $junemin = "";
            if(isset($Row[11])) {
                $junemin = mysqli_real_escape_string($con,$Row[11]);
            }

            $junemax = "";
            if(isset($Row[12])) {
                $junemax = mysqli_real_escape_string($con,$Row[12]);
            }

            $julymin = "";
            if(isset($Row[13])) {
                $julymin = mysqli_real_escape_string($con,$Row[13]);
            }

            $julymax = "";
            if(isset($Row[14])) {
                $julymax = mysqli_real_escape_string($con,$Row[14]);
            }

            $augmin = "";
            if(isset($Row[15])) {
                $augmin = mysqli_real_escape_string($con,$Row[15]);
            }

            $augmax = "";
            if(isset($Row[16])) {
                $augmax = mysqli_real_escape_string($con,$Row[16]);
            }

            $sepmin = "";
            if(isset($Row[17])) {
                $sepmin = mysqli_real_escape_string($con,$Row[17]);
            }

            $sepmax = "";
            if(isset($Row[18])) {
                $sepmax = mysqli_real_escape_string($con,$Row[18]);
            }

            $octmin = "";
            if(isset($Row[19])) {
                $octmin = mysqli_real_escape_string($con,$Row[19]);
            }

            $octmax = "";
            if(isset($Row[20])) {
                $octmax = mysqli_real_escape_string($con,$Row[20]);
            }

            $novmin = "";
            if(isset($Row[21])) {
                $novmin = mysqli_real_escape_string($con,$Row[21]);
            }

            $novmax = "";
            if(isset($Row[22])) {
                $novmax = mysqli_real_escape_string($con,$Row[22]);
            }

            $decmin = "";
            if(isset($Row[23])) {
                $decmin = mysqli_real_escape_string($con,$Row[23]);
            }

            $decmax = "";
            if(isset($Row[24])) {
                $decmax = mysqli_real_escape_string($con,$Row[24]);
            }

            if (!empty($date)) {
                $query = "INSERT INTO `$year` (janmin,janmax,febmin,febmax,marmin,marmax,aprilmin,aprilmax,maymin,maymax,junemin,junemax,julymin,julymax,augmin,augmax,sepmin,sepmax,octmin,octmax,novmin,novmax,decmin,decmax,user_id,date) 
 VALUES ('$janmin','$janmax','$febmin','$febmax','$marmin','$marmax','$aprmin','$aprmax','$maymin','$maymax','$junemin','$junemax','$julymin','$julymax','$augmin','$augmax','$sepmin','$sepmax','$octmin','$octmax','$novmin','$novmax','$decmin','$decmax','$user_id','$date')";
                $result = mysqli_query($con, $query);

                if (! empty($result)) {
                    $type = "success";
                    $message = "Excel Data Imported into the Database";
                    echo $type.'<br />'.$message;

                } else {
                    $type = "error";
                    $message = "Problem in Importing Excel Data";
                    echo $type.'<br />'.$message;
                }
            }



        }


    }
}
}

Author:Giant Gibbon,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/53284028/unable-to-upload-excel-file-in-php-to-mysql-database
yy