How Can PHP Import Excel to MySQL using a PHP XLSX Reader and Excel XLSX Converter

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.

An XLSX file stores data in worksheets in the form of cells. Cells 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 was mentioned above, an 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 the form of rows. You can use that information for your own purposes.

Ways to Import XLSX in MySQL

Since an 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 approach 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 into 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 similar 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 fellow PHP developers that can benefit from this information. If you have questions, please post a comment below.