PHPExcel import Excel to mysql in PHP

0
php

here is an example of how you can Import Excel to MySQL using PHP. Import Excel to MySQL database is a very important method. Sometimes you have a very big list of items in an excel file and at sometimes you need to import all that list into your database so you can see that list on your website. You may also like PHPExcel import excel to mysql in Codeigniter and How to Convert Excel Sheet into PHP Array using PHPExcel.

Download PHPExcel library.

Steps For PHPExcel import Excel to mysql in PHP

Step 1. Install PHPExcel Library

Unzip or extract the downloaded PHPExcel library files and copy Class directory inside files any folder and call library file.

require_once 'phpexcel/PHPExcel/IOFactory.php';

Step 2. Create HTML Form

Here we create the HTML form which contains the one file input element and one button.

<form method="post" action="" enctype="multipart/form-data" class="form-horizontal">
    <div class="row">
        <div class="col-sm-12">
            <div class="row">
                <label class="col-sm-3 label-on-left" style="margin-top: -16px;">Upload Excel</label>          
                <div class="col-md-6">
                    <input name="result_file"  required=""  type="file">
                </div>
            </div>
        </div>
    </div>
    
    <div class="row" >
        <div class="col-sm-3" style="width: 31%;margin-top: 15px;"> 
            <div class="pull-right hidden-print">
                <button type="submit" name="upload_excel" class="btn btn-primary"> Upload Excel</button>
            </div>
        </div>
    </div>   
</form>

Step 3. Press Upload Excel

In this step you are press upload Excel button after save file one specific folder and read file. PHPExcel_IOFactory read excel file and create array after store one by one record in mysql with check duplicate record.

<?php
    require_once 'phpexcel/PHPExcel/IOFactory.php';
    if(isset($_POST['upload_excel']))
    {
        $file_info = $_FILES["result_file"]["name"];
        $file_directory = "uploads/excel_mail/";
        $new_file_name = date("dmY").".". $file_info["extension"];
        move_uploaded_file($_FILES["result_file"]["tmp_name"], $file_directory . $new_file_name);
        $file_type	= PHPExcel_IOFactory::identify($file_directory . $new_file_name);
        $objReader	= PHPExcel_IOFactory::createReader($file_type);
        $objPHPExcel = $objReader->load($file_directory . $new_file_name);
        $sheet_data	= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

        foreach ($sheet_data as $row)
        {
            if(!empty($row['C']))
            {
                $checkemail = mysqli_query($conn,'SELECT * FROM `wo_emaillist` WHERE email = "'.$row['C'].'" ');
                if(mysqli_num_rows($checkemail) == '0')
                {
                    mysqli_query($conn,'INSERT INTO `wo_emaillist` (firstname,gender,email) VALUES ("'.$row['A'].'","'.$row['B'].'","'.$row['C'].'") ');
                }
            }
        }
        $updatemsg = "File Successfully Imported!";
        $updatemsgtype = 1;
    }
?>