In this post, I am going to explain how to import excel file into mysql database using PHP. In this article post we will be learning to import excel .xls, .xlsx format file into database using PHPExcel package.

We always need to add data from admin panel like product, items, clients, emails and so on. In case, we have few records and need to add the new data manually, it is alright. But in case we have more data, it will take long time to store data on database. At that time we can use direct import excel file into mysql database.

So in this tutorial I am using PHPExcel package to import excel to database. So you can do it by the following step in this tutorial.

Step 1: Download Package

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

Click here to download PHPExcel

After downloading the file, extract it to your root folder.

Step 2: Create Database and Table

In this step, we will create the database and table in our local server.

To create the database use the below code:

CREATE DATABASE student;

To create the table use the below code:

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

Step 3: Create dbconnect.php file

In this step, we will create dbconnect.php file for database configuration. In this file we have to set database host, username and password. We will use this file for connecting to database.

So let’s create the file and put the below code.

<?php
$servername ="localhost";
$username ="root";
$password ="";
$dbname ="student";
//Create Connection
$con = mysqli_connect ($servername, $username, $password, $dbname);
//Check Connection
if (!$con) {
    die ( "Connection Failed: " . mysqli_connect_error($con) );
}

?>

Step 4: Create index.php file

In this step, we will create a index.php file in root directory. This file is created form html. By this way you just have to select the excel file and click the upload button.

So let’s copy below 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"> Excel File Upload </label> 
<input type="file" name="uploadFile" value=""> 
<p class="help-block"> Only .xls/.xlxs extension File format. </p>
<input type="submit" name="submit" value="Upload"> 
</form>

</body>
</html>

Step 5: Create excelUpload.php file

In this step, we will create excelUpload.php file which will manage to import data into database.

So let’s create excelUpload.php file and put the below code.

<?php

require('dbconnect.php');

if ( isset($_POST['submit'])) {
    if ( isset($_FILES['uploadFile']['name']) &amp;&amp; $_FILES['uploadFile']['name'] != "" ) {
        $allowedExtensions = array("xls","xlsx");
// Return the extension of the file
        $ext = pathinfo( $_FILES['uploadFile']['name'] , PATHINFO_EXTENSION );
        if ( in_array ($ext, $allowedExtensions)){
            $isUploaded = $_FILES['uploadFile']['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());
                }
                // An excel file may contains many sheets so you have to specify which one you need to read or work with.
                $sheet = $objPHPExcel->getSheet(0);
                // It returns the highest number of rows.
                $total_rows = $sheet->getHighestRow();
                // It returns the highest number of columns.
                $highest_column = $sheet->getHighestColumn();
                //Table used to display the contents of the file
                echo '<table class=" table responsive" cellpadding="5" cellspacing="0" border="1">';
                // Loop through each row of the worksheet in turn
                for ($row = 2 ; $row <= $total_rows; $row++) {
                    // Read a row of data into an array
                    $rowData = $sheet-> rangeToArray ('A' . $row . ':' . $highest_column . $row, NULL, TRUE, FALSE);
                    $sub_code = $rowData[0][0];
                    $sub_name = $rowData[0][1];
                    $sql = " INSERT INTO subject ( sub_code, sub_name ) VALUES ( '".$sub_code."', '".$sub_name."' ) " ;
                    $result = mysqli_query($con,$sql);
                }
                if ( $result) {
                    echo "<script type=\"text/javascript\"> 
                        alert(\"File is uploaded Successfully.\"); 
                        </script>";
                    for ($row = 1 ; $row <= $total_rows; $row++) {
                        // Read a row of data into an array
                        $rowData = $sheet-> rangeToArray ('A' . $row . ':' . $highest_column . $row, NULL, TRUE, FALSE);
                        $sub_code = $rowData[0][0];
                        $sub_name = $rowData[0][1];
                        echo '<tr>';
                        echo '<td>'.$sub_code.'</td>';
                        echo '<td>'.$sub_name.'</td>';
                        echo '</tr>';
                    }
                }else {
                    echo "ERROR: " . mysqli_error($con);
                }
                echo '</table>';
            } else {
                echo '<span class="msg">File not uploaded!</span>';
            }
        } else {
            echo '<span class="msg">This type of file not allowed!</span>';
        }
    }
    else { echo '<span class="msg">Select an excel file first!</span>';
    }
}
?>

CONCLUSION

In this article, we discussed how you could import Excel file using PHP and MySQL. This is a simple example you can add more complex logic and validations as per your requirements. If you wish to add to the discussion or would like to ask a question, leave a comment below.

Download Code From below

Download Code

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *