Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Import excel sheet into database using PHP

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 4
    • 0
    • 3.38k
    Comment on it

    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.

    Import excel sheet into database using PHP

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: