Author: Haseeb Ahmad Basil
Viewers: 4,955
Last month viewers: 973
Categories: PHP Tutorials
Read this article to learn how to easily import an Excel file in XLSX format and insert the data in a MySQL database using the SimpleXLSX package.
Contents
What is XLSX
Parsing XLSX in PHP
Ways to Import XLSX in MySQL
Importing XLSX in MySQL via PHP
Download the SimpleXLSX Package
What is XLSX
XLSX is a file format used to create an Excel spreadsheet. It was first intruduced by Microsoft in Excel 2007. It is XML-based, which makes it easier to transfer data between applications.
A XLSX file stores data in worksheets in the form of cells. Cell are contained in rows and columns which can have multiple properties of the cell data like styles, formatting, alignment, etc..
XLSX is in reality a simple ZIP archive. It contains multiple XML files, which have all the information of the spreadsheet properties.
Parsing XLSX in PHP
There are multiple ways to parse XLSX with PHP. As it is was mentioned above, a XLSX file is a XML-based ZIP archive.
You just need to extract the ZIP file content files and parse them using any XML parser. But it is easier to use a ready-made XLSX parser as it might contain more functionality. There are few popular packages available to parse XLSX using PHP:
1. Spreadsheet_Excel_Reader
2. PHPExcel
3. SimpleXLSX
In this article I focus more on the SimpleXLSX package and how to use it to parse XLSX files and importing its data into a MySQL database.
Using the SimpleXLSX package you can get data from the spreadsheet in form of rows. You can use that information for your own purposes.
Ways to Import XLSX in MySQL
Since a XLSX file contains multiple files, it cannot be imported directly into MySQL. So the recommended way is to convert the XLSX file into a CSV and then import that to the DB.
There is also an online tool that can be used for that. You just upload the XLSX file and it creates the insert statements for it.
There is also plenty of desktop software like e.g. Excel2MySQL, Navicat, MySQL for Excel, Mr. Data Converter etc.. You can use the XLSX parsers in PHP to do this too. In the next section I will be going into more details about that approach.
Importing XLSX in MySQL via PHP
As I mentioned above, you can use XLSX parsers written in PHP to import XLSX file data into a MySQL database. You might wonder why go to all the trouble of using parsers when there are other easier ways of importing XLSX into a MySQL database.
Well there can be many uses for this approache including, importing given user provided XLSX files into a database automatically. This can be done using SimpleXLSX in a couple of ways.
The first method is to parse the XLSX file in PHP and then change it to CSV and import that in MySQL, it can be done using a code similar to the one below:
<?php
include 'simplexlsx.class.php';
$xlsx = new SimpleXLSX( 'countries_and_population.xlsx' );
$fp = fopen( 'file.csv', 'w');
foreach( $xlsx->rows() as $fields ) {
fputcsv( $fp, $fields);
}
fclose($fp);
Using the above code you can parse an XLSX file in to a CSV file and then you can easily import the CSV file into the MySQL database.
Now the other method to do this is to parse the XLSX file into an array and import it into the db using the mysqli or PDO extensions. You can do this by using a code simillar to the following:
<?php
include 'simplexlsx.class.php';
$xlsx = new SimpleXLSX( 'countries_and_population.xlsx' );
try {
$conn = new PDO( "mysql:host=localhost;dbname=mydb", "user", "pass");
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$stmt = $conn->prepare( "INSERT INTO countries_and_population (rank, country, population, date_of_estimate, powp) VALUES (?, ?, ?, ?, ?)");
$stmt->bindParam( 1, $rank);
$stmt->bindParam( 2, $country);
$stmt->bindParam( 3, $population);
$stmt->bindParam( 4, $date_of_estimate);
$stmt->bindParam( 5, $powp);
foreach ($xlsx->rows() as $fields)
{
$rank = $fields[0];
$country = $fields[1];
$population = $fields[2];
$date_of_estimate = $fields[3];
$powp = $fields[4];
$stmt->execute();
}
Download the SimpleXLSX Package
It is fairly easy to parse an XLSX file. The SimpleXLSX package provides an easy way to read and convert any XLSX file so it can be processed in any way your PHP application needs or even insert the data into a MySQL database for instance.
You can download the SimpleXLSX package in the ZIP format or install it using the composer tool with instructions presented in the download page.
Share this article with other colleague PHP developers that can benefit from this information. If you have questions post a comment below.
You need to be a registered user or login to post a comment
1,616,803 PHP developers registered to the PHP Classes site.
Be One of Us!
Login Immediately with your account on:
Comments:
4. Thx - Sergey Shuchkin (2019-08-23 21:55)
Thx... - 0 replies
Read the whole comment and replies
3. i need to try - ??? ??? (2018-01-02 09:27)
....... - 0 replies
Read the whole comment and replies
1. Primary Key & Unique Constraints? Performance & Benchmarks? - Umadhar M (2017-07-18 07:56)
Primary Key & Unique Constraints? Performance & Benchmarks?... - 1 reply
Read the whole comment and replies
2. The Class Does not works - Ak1524 (2017-07-18 07:55)
The Classes is not working with any .xlsx file... - 2 replies
Read the whole comment and replies