Bladephp: Now with 7+ codeigniter, php, and laravel project 7+ php project From $18/Year From Rs. 1000/Year

PHPExcel import Excel to mysql in PHP

by
Category:php
php mysql phpexcel
PHPExcel import Excel to mysql in PHP

Today I will show you 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 btn-rounded"> 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 dubplicate 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;
    }
?>

0 Comments


Html code work in comment box


Related Post