How Can Php Import Excel To Mysql Using An Php Xlsx, Import Excel File Into Mysql Database Using Php

Database backup and restore is the most important part in maintaining software. Taking periodical automatic backup is must for any project.

Đang xem: How can php import excel to mysql using an php xlsx

In case of unknown security flaw which may collapse your system. In such unfortunate situation, it will be the holy grail to save you if you have a backup.

It is not only enough to take a backup by exporting your database. Also, we need to experiment with importing backup to ensure the restore.

Backup files can be in any format. You may see many of the database clients supports SQL, Excel or CSV format files to import external data.

We are going to create our own PHP database client only for importing Excel data into a database.

Restoring excel backup into a database via programming will save our time. Let us study how to implement a quick restore by importing bulk data from excel files. If you are looking for export example, you may visit my earlier export data in CSV format.

What is inside?

About this excel import example

This example allows the user to choose the import template via HTML form input. A sample Excel template is added with this hướng dẫnable resource.

With valid file, the HTML form submit will call PHP to process Excel parsing.

In PHP, the PHPSpreadSheet library is used for reading the Excel file data and convert it into an array.

By iterating the array data, the code will execute database insert row by row.

Xem thêm: Mẫu Thể Thức Văn Bản Của Đảng Năm 2017, Quy Định Số 66

File Structure

See the Excel import example’s file structure shown below. The vendorfolder has the PHPSpreadsheet library and ites dependacies.

I have given sample import template with this files to experiment the import operation.

Theschema.sqlhas the script to create the target database. You can find the SQL script in the next section.

Database script

Import the following SQL script and create database environment. Also, configure your database details in DataSource.php to make this example working.

—- Database: `import_excel`—- ———————————————————— Table structure for table `tbl_info`–CREATE TABLE `tbl_info` ( `id` int(11) NOT NULL, `name` varchar(50) NOT NULL, `description` varchar(50) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);—- Indexes for table `tbl_info`–ALTER TABLE `tbl_info` ADD PRIMARY KEY (`id`);—- AUTO_INCREMENT for table `tbl_info`–ALTER TABLE `tbl_info` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;;

Creating UI to upload import template

This HTML form with the file upload option is used to choose the excel source. On submitting this form, the excel file will be sent to the PHP to parse the data source. This file upload option will only allow the excel files to choose by using the accept attribute.

This code also contains the response HTML for displaying the message returned from PHP. This message is shown based on the type of response sent from PHP after excel import.

Import Excel File into MySQL Database using PHP

select($sqlSelect);if (! empty($result)) {{?>

Parse excel data using PHPSpreadsheet

In this PHP code, I specified the array of allowed file type for the file-type validation in PHP. I validated the uploaded file type along with the array allowed type.

If match found, the code will execute further logic. Otherwise, it will return the error message to the user.

With successful validation, excel file parsing read and convert the data into an array. Each array index has an array of row-data. After reading the non-empty row data, I run the database insert and show the response.

Xem thêm: 73 mẫu văn bản áp dụng trong xét xử và thi hành án hình sự

getConnection();require_once (“./vendor/autoload.php”);if (isset($_POST<"import">)) { $allowedFileType = < "application/vnd.ms-excel", "text/xls", "text/xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" >; if (in_array($_FILES<"file"><"type">, $allowedFileType)) { $targetPath = “uploads/” . $_FILES<"file"><"name">; move_uploaded_file($_FILES<"file"><"tmp_name">, $targetPath); $Reader = new PhpOfficePhpSpreadsheetReaderXlsx(); $spreadSheet = $Reader->load($targetPath); $excelSheet = $spreadSheet->getActiveSheet(); $spreadSheetAry = $excelSheet->toArray(); $sheetCount = count($spreadSheetAry); for ($i = 0; $i insert($query, $paramType, $paramArray); // $query = “insert into tbl_info(name,description) values(“” . $name . “”,”” . $description . “”)”; // $result = mysqli_query($conn, $query); if (! empty($insertId)) { $type = “success”; $message = “Excel Data Imported into the Database”; } else { $type = “error”; $message = “Problem in Importing Excel Data”; } } } } else { $type = “error”; $message = “Invalid File Type. Upload Excel File.”; }}?>

Output: Excel import and data listing

This screenshot shows the output by displaying the list of imported rows from the database.