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 🙂