When developing a website, you sometimes have to manipulate data in Microsoft Excel format. For example, your website could offer users the option of importing an Excel file that you would parse to integrate its data it into your database, for example. This seemingly trivial task can be complicated. Fortunately, I will present you with a simple and powerful open source solution that will allow you to easily read Excel XLS and XLSX files in PHP.
Powerful And Simple Solution With SimpleXLSX
In this tutorial, I suggest you to parse your files in Microsoft Excel format using the open source solution SimpleXLSX. Solution is a very big word here since SimpleXLSX is a single PHP class without any dependency. To use it, you only have to import it into your PHP scripts.
SimpleXLSX does not need external dependencies because the class does the unzipping and parsing work internally for the Open XML format on which the XLSX files are based.
SimpleXLSX is under MIT license and can be downloaded freely from GitHub:
Parse and retrieve data from Excel XLSx files. MS Excel 2007 workbooks PHP reader. No addiditional extensions need…
For parsing XLS files, the PHP code will be the same but you will have to use the SimpleXLS dedicated class provided by the developer of SimpleXLSX.
Considering A Microsoft Excel File
Before starting to write our PHP code, we need to create a Microsoft Excel File in XLSX format. In this file, we write the TIOBE Programming Language Index for August 2019.
It gives the following content for our tiobe-languages-august-2019.xlsx file:
Parsing The Excel File In PHP
Now, we can write the PHP code for parsing this Excel file. Our goal will be to parse the file and then to display the data inside a HTML table. First step is to include the SimpleXLSX class into our parseXLSX.php script.
Then, we can parse the file by calling the parse static method of the SimpleXLSX class with the path to our Excel file in parameter. This call returns an object represeing the data parsed from the Excel file. The SimpleXLSX class offers a lot of possibilities since you can obtain a reference to a specific sheet or getting the value of a specific cell.
In our case, we just need to get the array representing the whole content line by line of our Excel file. For that, we call the rows() method of the object returned previously. Now, we have to iterate on the elements of this array. Just before our foreach loop, we print the table and tbody HTML tags. In the foreach loop, we display each line of the array inside a row in the HTML table. Each row having two columns since we had two columns in the original Excel file.
It gives us the following code for our parseXLSX.php script:
When executing this script, you should have the following display:
Adding Some Fancy With CSS
Our script works well but it is missing some fancy. To fixing that, we add some CSS to our script. Like that, we customize the rendering of the HTML table and its cells.
It gives us the following complete code for the parseXLSX.php script:
When executing this script, you should have the following fancy display:
That’s all for that tutorial.
Don’t hesitate to give a try to SimpleXLSX when you will need to parse Microsoft Excel Files in PHP in the the future.