- Tel:0912084206
Excel is a spreadsheet used to store data in tabular form. In the web application, the Excel file format is used mainly for 2 purposes, import and export data. The import feature helps to parse data from Excel files and insert data in the database. On the other hand, the Export feature used to download data from the database and save it as a file.
Generally, you need to insert data one by one manually to add records to the database. But, when there are a large number of records that need to be inserted in the database, it’s very time-consuming to insert data manually. The import feature is a useful option to insert bulk data into the database at once dynamically. In this tutorial, we will show you how to import data from Excel file and insert into the database using PHP.
In this example script, we will import members’ data from an Excel file in the database using PHP and MySQL.
To store the member’s data, a table is required in the database. The following SQL creates a members
table with some basic fields in the MySQL database.
CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `phone` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, `created` datetime NOT NULL, `modified` datetime NOT NULL, `status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Based on the database table structure, the Excel file should have these fields – First Name, Last Name, Email, Phone, and Status. To import the data from Excel file, the data format should be similar to the following screen.
We will use the PhpSpreadsheet library to read Excel files and parse data. The PhpSpreadsheet is a PHP library that helps to parse data from spreadsheet file formats (.xls, .xlsx, etc).
Use composer to install PhpSpreadsheet in the script folder.
composer require phpoffice/phpspreadsheet
Alternatively, you can use our source code to install PhpSpreadsheet without composer.
Note that: All the required files including the PhpSpreadsheet library are included in our source code, you do not require to install it separately.
Before getting started, look at the file structure of the Excel data import in PHP script.
import_excel_data_with_php/ ├── dbConnect.php ├── index.php ├── importData.php ├── vendor/ ├── assets/ └── css/ ├── bootstrap.min.css └── style.css
The dbConfig.php
is used to connect and select the database. Specify the database host ($dbHost
), username ($dbUsername
), password ($dbPassword
), and name ($dbName
) as per your MySQL database credentials.
<?php // Database configuration $dbHost = "localhost"; $dbUsername = "root"; $dbPassword = "root"; $dbName = "codexworld_db"; // Create database connection $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); // Check connection if ($db->connect_error) { die("Connection failed: " . $db->connect_error); } ?>
Initially, the existing member’s data is listed with the Excel file import option.
If the form is already submitted,
<?php // Load the database configuration file include_once 'dbConfig.php'; // Get status message if(!empty($_GET['status'])){ switch($_GET['status']){ case 'succ': $statusType = 'alert-success'; $statusMsg = 'Member data has been imported successfully.'; break; case 'err': $statusType = 'alert-danger'; $statusMsg = 'Something went wrong, please try again.'; break; case 'invalid_file': $statusType = 'alert-danger'; $statusMsg = 'Please upload a valid Excel file.'; break; default: $statusType = ''; $statusMsg = ''; } } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Import Excel File Data with PHP</title> <!-- Bootstrap library --> <link rel="stylesheet" href="assets/css/bootstrap.min.css"> <!-- Stylesheet file --> <link rel="stylesheet" href="assets/css/style.css"> <!-- Show/hide Excel file upload form --> <script> function formToggle(ID){ var element = document.getElementById(ID); if(element.style.display === "none"){ element.style.display = "block"; }else{ element.style.display = "none"; } } </script> </head> <body> <!-- Display status message --> <?php if(!empty($statusMsg)){ ?> <div class="col-xs-12 p-3"> <div class="alert <?php echo $statusType; ?>"><?php echo $statusMsg; ?></div> </div> <?php } ?> <div class="row p-3"> <!-- Import link --> <div class="col-md-12 head"> <div class="float-end"> <a href="javascript:void(0);" class="btn btn-success" onclick="formToggle('importFrm');"><i class="plus"></i> Import Excel</a> </div> </div> <!-- Excel file upload form --> <div class="col-md-12" id="importFrm" style="display: none;"> <form class="row g-3" action="importData.php" method="post" enctype="multipart/form-data"> <div class="col-auto"> <label for="fileInput" class="visually-hidden">File</label> <input type="file" class="form-control" name="file" id="fileInput" /> </div> <div class="col-auto"> <input type="submit" class="btn btn-primary mb-3" name="importSubmit" value="Import"> </div> </form> </div> <!-- Data list table --> <table class="table table-striped table-bordered"> <thead class="table-dark"> <tr> <th>#</th> <th>First Name</th> <th>Last Name</th> <th>Email</th> <th>Phone</th> <th>Status</th> <th>Created</th> </tr> </thead> <tbody> <?php // Get member rows $result = $db->query("SELECT * FROM members ORDER BY id DESC"); if($result->num_rows > 0){ $i=0; while($row = $result->fetch_assoc()){ $i++; ?> <tr> <td><?php echo $i; ?></td> <td><?php echo $row['first_name']; ?></td> <td><?php echo $row['last_name']; ?></td> <td><?php echo $row['email']; ?></td> <td><?php echo $row['phone']; ?></td> <td><?php echo $row['status']; ?></td> <td><?php echo $row['created']; ?></td> </tr> <?php } }else{ ?> <tr><td colspan="7">No member(s) found...</td></tr> <?php } ?> </tbody> </table> </div> </body> </html>
The importData.php
file handles the file upload and Excel data import operations using PHP and MySQL.
PhpSpreadsheet\Reader
.Xlsx()
class of the PhpSpreadsheet library.load()
method of Xlsx class.getActiveSheet()
method.toArray()
method.<?php // Load the database configuration file include_once 'dbConfig.php'; // Include PhpSpreadsheet library autoloader require_once 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; if(isset($_POST['importSubmit'])){ // Allowed mime types $excelMimes = array('text/xls', 'text/xlsx', 'application/excel', 'application/vnd.msexcel', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // Validate whether selected file is a Excel file if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $excelMimes)){ // If the file is uploaded if(is_uploaded_file($_FILES['file']['tmp_name'])){ $reader = new Xlsx(); $spreadsheet = $reader->load($_FILES['file']['tmp_name']); $worksheet = $spreadsheet->getActiveSheet(); $worksheet_arr = $worksheet->toArray(); // Remove header row unset($worksheet_arr[0]); foreach($worksheet_arr as $row){ $first_name = $row[0]; $last_name = $row[1]; $email = $row[2]; $phone = $row[3]; $status = $row[4]; // Check whether member already exists in the database with the same email $prevQuery = "SELECT id FROM members WHERE email = '".$email."'"; $prevResult = $db->query($prevQuery); if($prevResult->num_rows > 0){ // Update member data in the database $db->query("UPDATE members SET first_name = '".$first_name."', last_name = '".$last_name."', email = '".$email."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'"); }else{ // Insert member data in the database $db->query("INSERT INTO members (first_name, last_name, email, phone, status, created, modified) VALUES ('".$first_name."', '".$last_name."', '".$email."', '".$phone."', '".$status."', NOW(), NOW())"); } } $qstring = '?status=succ'; }else{ $qstring = '?status=err'; } }else{ $qstring = '?status=invalid_file'; } } // Redirect to the listing page header("Location: index.php".$qstring); ?>
Bước 1: Cài đặt thư viện PHPExcel
Sử dụng composer để cài đặt thư viện PHPExcel: download PHPExcel library, unzip and move it to your root folder.
composer require phpexcel/phpexcel
Bước 2: Kết nối đến database MySQL
Tạo file db_connect.php
để kết nối đến database MySQL:
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "matkhau";
$dbName = "smartinstall";
// Create database connection
//$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
$mysqli = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName);
$mysqli->set_charset('utf8');
// Check connection
if (mysqli_connect_errno()) {
echo 'Kết nối thất bại: '.mysqli_connect_error();
exit;
}
?>
Bước 3: Xử lý file Excel và import dữ liệu vào MySQL
Tạo file index.php
để xử lý file Excel và import dữ liệu vào MySQL:
<?php
require('db_connect.php');
require('Classes/PHPexcel.php');
if(isset($_POST['btnGui'])){
$file = $_FILES['file']['tmp_name'];
// in ra đường dẫn file
//echo $file;
$objReader = PHPExcel_IOFactory::createReaderForFile($file);
$objReader->setLoadSheetsOnly('guestinfo');
$objPHPExcel = $objReader->load($file);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
// In ra mảng dữ liệu lấy được từ sheet trên
//print_r($sheetData);
//Đếm cột cao nhất
//$getHighestColumn = $objPHPExcel->setActiveSheetIndex()->getHighestColumn();
// Đếm dòng cao nhất
$highestRow = $objPHPExcel->setActiveSheetIndex()->getHighestRow();
// in ra số dòng cao nhất (
//echo $highestRow;
// Load ra tên sheet của file
//$loadedAllSheetNames = $objReader->listWorksheetNames($file);
// Lặp qua từng dòng dữ liệu trong sheet
for ($row = 2; $row <= $highestRow; $row++) {
// Lấy dữ liệu từ từng cột
$guestId = $sheetData[$row]['B'];
$roomid = $sheetData[$row]['C'];
$guestName = $sheetData[$row]['D'];
// Tạo câu lệnh SQL để insert dữ liệu vào database
$sql = "INSERT INTO guestinfo (guestId, roomid, guestName) VALUES ('$guestId', '$roomid', '$guestName')";
// Thực thi câu lệnh SQL
$mysqli->query($sql);
}
echo 'Thêm dữ liệu thành công!';
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Import data khach hang </title>
<link rel="stylesheet" href="">
</head>
<body>
<form method="POST" enctype="multipart/form-data">
<input type="file" name="file">
<button type="submit" name="btnGui">Import</button>
</form>
</body>
</html>
Bước 4: Chạy script
Chạy file index.php
để import dữ liệu từ file Excel vào database MySQL.
Lưu ý:
your_file_name.xlsx
thành tên file Excel của bạn.db_connect.php
.Những tin mới hơn
Những tin cũ hơn