Hello friends, This tutorial will help you to insert excel sheet data into the MySQL database using PHP I have also included the snapshots which will help you to understand more clearly.

Import Students: You can import students by clicking on the “Browse” button.

This view file can be created with bootstrap css. I am just pasting the form below:

<section class="content">
 <div class="row">
<div class="col-xs-12">
<!-- <div class="container"> -->
    <div class="box">
      <div class="panel panel-default">
        <div class="panel-heading"><strong>Upload Excel Sheet of Students</strong>
            <span class="pull-right">
               <a href="#">Download CSV Format</a>
            </span>
            
        </div>
        <div class="panel-body">
          <h4>Select files from your computer</h4>
          <form accept-charset="utf-8" method="post" enctype="multipart/form-data" id="StudentImportForm" action="/students/import">
<div style="display:none;">
<input type="hidden" value="POST" name="_method">
</div>
<div class="input form-group file">
<label for="StudentUpload">Upload</label>
<input type="file" id="StudentUpload" style="height:100%;" name="data[Student][upload]"></div>
<div class="submit box-footer">
<input type="submit" value="Submit" class="btn btn-primary"></div>          
        </form></div>
      </div>
     </div> 
    <!-- </div> --> 
    <!-- /container -->  
</div>
</div>
</section>

Now your form is ready to use, now lets select the excel sheet of students in the following format:

Leave the 2nd row and start entering the data from the first column “First Name”, second column “Last Name”, third column “Gender”, fourth column “Date of Birth” in Y-m-d format and the fifth column email.

Note: In Excel sheet please format the cells for Date of Birth as text as shown below in the screen shot

Lets begin and create file index.php and write the following lines of code:

<?php
ini_set("display_errors",1);
require_once 'excel_reader.php';
require_once 'db.php';

$data = new Spreadsheet_Excel_Reader("students.xls");

echo "Total Sheets in this xls file: ".count($data->sheets)."<br /><br />";

$html="<table border='1'>";
for($i=0;$i<count($data->sheets);$i++) // Loop to get all sheets in a file.
{	
	//echo "<pre>";
	//print_r($data->sheets);
	if(count($data->sheets[$i]['cells'])>0) // checking sheet not empty
	{
		echo "Sheet $i:<br /><br />Total rows in sheet $i  ".count($data->sheets[$i]['cells'])."<br />";
		for($j=3;$j<=count($data->sheets[$i]['cells']);$j++) // loop used to get each row of the sheet
		{ 
			$html.="<tr>";
			for($k=1;$k<=count($data->sheets[$i]['cells'][$j]);$k++) // This loop is created to get data in a table format.
			{
				$html.="<td>";
				$html.=$data->sheets[$i]['cells'][$j][$k];
				$html.="</td>";
			}
			$data->sheets[$i]['cells'][$j][1];
			$first_name = mysqli_real_escape_string($connection,$data->sheets[$i]['cells'][$j][1]);
            $last_name = mysqli_real_escape_string($connection,$data->sheets[$i]['cells'][$j][2]);
            $gender = mysqli_real_escape_string($connection,$data->sheets[$i]['cells'][$j][3]);
            $dob = mysqli_real_escape_string($connection,$data->sheets[$i]['cells'][$j][4]);
			$email = mysqli_real_escape_string($connection,$data->sheets[$i]['cells'][$j][5]);
			
			$query = "insert into users(id,first_name,last_name,gender,dob,email) values(NULL,'".$first_name."','".$last_name."','".$gender."','".date('Y-m-d',strtotime($dob))."','".$email."')";
			mysqli_query($connection,$query);
			$html.="</tr>";
		}
	}
	
}

$html.="</table>";
echo $html;
echo "<br />Data Inserted in database";
?>

Next Create configuration file db.php

<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'amuk');
define('DB_PASSWORD', 'yourpassword');
define('DB_DATABASE', 'databasename');
$connection = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>

Now, create file excel_reader.php. I have attached this code, you can download it, the file name is excel_reader.php. This is library for reading excel sheet file. Click on the link below to copy the code:

excel_reader.php

After clicking on submit button you will be able to upload the excel file and if all the records are inserted successfully into the database then it will be displayed on the data grid view format as shown below:

Hope this blog will help you to upload excel sheet into the database. You can display the data from the database and display in the html page.

Thanks for reading the blog.