To import excel file data into web application (in MySQL database) is one of simplest task.
Requirements:
– PHP version 5.2.0 or higher
Steps:
- Read Excel file data in PHP using “PHPExcel” library
- Insert the fetch data into MySQL database
We have excel file , example_file.xlsx
First we will create table in MySQL database to store excel file data. as per the our excel file the database query to create a table structure is as follows,
|
1 2 3 4 5 6 7 |
CREATE TABLE IF NOT EXISTS `users` ( `id` int(10) NOT NULL AUTO_INCREMENT, `firstname` varchar(255) NOT NULL, `lastname` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; |
Fetch excel file data and insert into database as per below –
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
<?php require 'Classes/PHPExcel/IOFactory.php'; // Mysql database $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "databasename"; $inputfilename = 'example_file.xlsx'; $exceldata = array(); // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } // Read your Excel workbook try { $inputfiletype = PHPExcel_IOFactory::identify($inputfilename); $objReader = PHPExcel_IOFactory::createReader($inputfiletype); $objPHPExcel = $objReader->load($inputfilename); } catch(Exception $e) { die('Error loading file "'.pathinfo($inputfilename,PATHINFO_BASENAME).'": '.$e->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn(); // Loop through each row of the worksheet in turn for ($row = 1; $row <= $highestRow; $row++) { // Read a row of data into an array $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); // Insert row data array into your database of choice here $sql = "INSERT INTO users (firstname, lastname, email) VALUES ('".$rowData[0][0]."', '".$rowData[0][1]."', '".$rowData[0][2]."')"; if (mysqli_query($conn, $sql)) { $exceldata[] = $rowData[0]; } else { echo "Error: " . $sql . "<br>" . mysqli_error($conn); } } // Print excel data echo "<table>"; foreach ($exceldata as $index => $excelraw) { echo "<tr>"; foreach ($excelraw as $excelcolumn) { echo "<td>".$excelcolumn."</td>"; } echo "</tr>"; } echo "</table>"; mysqli_close($conn); ?> |
Happy Coding 🙂
This site is really interesting. I have bookmarked it.
Do you allow guest posting on your blog ? I can write high quality articles for you.
Let me know.
Hello Elbert,
If your article is belongs to PHP world then you are welcome.
Thanks,
Sagar
Nice one. This tutorial was really simple helpful for create a new blog on my business site.
Thanks.,
Excellent Article.. Explanation and program explanation very clearly so easy to understand.. I recommend this article to my friends for read..