Import Excel File Data In Database Using PHP

Today, We want to share with you upload excel file in php example.In this post we will show you Import Excel File Data In Database Using Php, hear for php excel import to mysql using phpexcel we will give you demo and example for implement.In this post, we will learn about How to Export MySQL Database data to Excel in PHP MySQLi with an example.

read excel file in php and insert into database

There are the Following The simple About import excel .xls, .xlsx format file into database using PHPExcel package. Full Information With Example and source code.

As I will cover this Post with live Working example to develop import excel to mysql using php download, so the import excel to mysql using php code is used for this example is following below.

I always required to add data from admin panel like product info, students info, website list, items, transaction, clients, emails, mobile info, and so on. In phase, i have few records and required to add the fresh lots of the data step by step verfiy or manually, it is alright. But in phase i have lots of the data, it will take long time to store or save data on MySQL or any other database. At that time i can use direct file import excel file into mysql database.

So in this article I am using very helpful PHPExcel package to upload excel file in php example. Therefor you can do it by the bellow step in this article.

STEP 1: DOWNLOAD PACKAGE

In the first step i have to download PHPExcel library. So first let’s download it from here:

Click here to download PHPExcel

And then you can successfully downloading the file, extract it to your root any dasktop folder.

STEP 2: CREATE DATABASE AND TABLE

In this phase, i will make the database and db table in our local server like as a localhost.

To make the database use the below full source code:

CREATE DATABASE student;

To make the table use the below code:

CREATE TABLE IF NOT EXISTS `subject` ( 
`product_id` INT(11) NOT NULL AUTO_INCREMENT , 
`product_code` VARCHAR(30) NOT NULL , 
`product_name` VARCHAR(30) NOT NULL , 
PRIMARY KEY (`product_id`) 
) ENGINE = InnoDB DEFAULT CHARSET = latin1; 

STEP 3: CREATE And CONNECT DATABASE FILE

In this phase, we will make dbconnect.php file for database configuration. In this file we have to set mysql database host such as a “localhost”, your username like as a root as well as password. i will use this file for connecting to database.

Therefor let’s make the file and put the below code.

<?php
$servername ="localhost";
$username ="root";
$password ="";
$dbname ="student";

$con = mysqli_connect ($servername, $username, $password, $dbname);

if (!$con) {
    die ( "Connection Failed: " . mysqli_connect_error($con) );
}
 
?>

STEP 4: MAKE A LANDING PAGE

In this phase, i will make a main index.php file in root directory. This file is created form html view. By this methods you just have to select or choose the excel sheet file as well as click to the upload button.

So let’s copy below all the source code and put it in the index.php file

<!DOCTYPE html>
<html>
<head>
  <title></title>
</head>
<body>
 
  <form enctype="multipart/form-data" method="POST" action="excelUpload.php" role="form"> 
<label for="upload">Any Product Excel File Upload Here</label> 
<input type="file" name="userUploadedFile" value=""> 
<p class="help-block"> Only .xls/.xlxs extension File format. </p>
<input type="submit" name="submit" value="Upload"> 
</form>
 
</body>
</html>

STEP 5: MAKE EXCEL UPLOAD PHP FILE

In this phase, i will make file which will processing to import data into mysql database.

So let’s make a simple PHP excelUpload.php file and copy and paste the below full source code.

<?php
 
require('dbconnect.php');
 
if ( isset($_POST['submit'])) {
    if ( isset($_FILES['userUploadedFile']['name']) && $_FILES['userUploadedFile']['name'] != "" ) {
        $allowedExtensions = array("xls","xlsx");
		// Return the extension of the file
        $ext = pathinfo( $_FILES['userUploadedFile']['name'] , PATHINFO_EXTENSION );
        if ( in_array ($ext, $allowedExtensions)){
            $isUploaded = $_FILES['userUploadedFile']['tmp_name'];
            if ($isUploaded) {
                include "Classes/PHPExcel/IOFactory.php";
 
                try {
                    $objPHPExcel = PHPExcel_IOFactory::load($isUploaded);
                } catch (Exception $e) {
                    die ( 'Error loading file "' . pathinfo($isUploaded, PATHINFO_BASENAME ) . '": ' . $e->getMessage());
                }
                
                $phpspreadsheet = $objPHPExcel->getSheet(0);

                $total_products = $phpspreadsheet->getHighestRow();

                $highest_column = $phpspreadsheet->getHighestColumn();

                echo '<table class=" table responsive" cellpadding="5" cellspacing="0" border="1">';

                for ($row = 2 ; $row <= $total_products; $row++) {
                    $productsDataAll = $phpspreadsheet-> rangeToArray ('A' . $row . ':' . $highest_column . $row, NULL, TRUE, FALSE);
                    $product_code = $productsDataAll[0][0];
                    $product_name = $productsDataAll[0][1];
                    $sql = " INSERT INTO subject ( product_code, product_name ) VALUES ( '".$product_code."', '".$product_name."' ) " ;
                    $result = mysqli_query($con,$sql);
                }
                if ( $result) {
                    echo "<script type=\"text/javascript\"> 
                        alert(\"Good Luck, Your File is uploaded Successfully.\"); 
                        </script>";
                    for ($row = 1 ; $row <= $total_products; $row++) {

                        $productsDataAll = $phpspreadsheet-> rangeToArray ('A' . $row . ':' . $highest_column . $row, NULL, TRUE, FALSE);
                        $product_code = $productsDataAll[0][0];
                        $product_name = $productsDataAll[0][1];
                        echo '<tr>';
                        echo '<td>'.$product_code.'</td>';
                        echo '<td>'.$product_name.'</td>';
                        echo '</tr>';
                    }
                }else {
                    echo "ERROR: " . mysqli_error($con);
                }
                echo '</table>';
            } else {
                echo '<span class="msg">Sorry, File not uploaded!</span>';
            }
        } else {
            echo '<span class="msg">This File type of file not allowed!</span>';
        }
    }
    else { echo '<span class="msg">Please Select an excel file first!</span>';
    }
}
?>

FINAL CONCLUSION

In this big Tutorials, i learn to how you could import Excel sheet file using PHP and MySQL Database. This is a sample demo with example you can extra add more advance level complex logic as well as validations as per your any other requirements. If you wish to add to the chatting with me or would like to ask a any types of the question, leave a comment sections below.