Although rare, but there might come a case when you might have to ask someone to upload an excel file and use it’s data for storage purposes. Most of the times you’ll get a request for exporting data into an excel file and we’ll get there in the next post. But for now, let’s just say that we have a client that wants us to have his employees upload some data which he can save it in a database for further processing.

For this there are many PHP libraries available, but I”m are going to use the most common i.e. PHPExcel. It’s easy to use, has a ton of examples, has a lot of support and Q&A on stackoverflow. What I’m telling is no different than what’s already out there, but it should be a part of a developers blog. So here it is:

So, the client says that he has an excel spreadsheet having employees info which he would like in a MySQL database. The info he receives is provided to him daily in the lot of 100s and he has to upload them into the system. You can easily create a file upload system for him and then when the file is uploaded all you have to do is read it and save its content.

So we have a MySQL database `workforce` and in it we have to create an `employees` table with the given columns. We’ll run the following query for that:

No our table is ready. For importing the data we’ll need the latest PHPExcel Library. The latest stable release is PHPExcel 1.8.0, which we can download at:
PHPExcel by MarkBaker. It’s also available on github at PHPExcel on github, where you can also find it’s DOCUMENTATION.

In addition to this I’m also using a MySQLi library for db management, taken directly from opencart, as it’s light and reliable. You can download it along with the source files for this code.

Now, once your file is uploaded, we can manage it in our php code. The first thing to do is to include the required PHPExcel and MySQLi DB libraries.

Next thing is to read the the excel cell contents. For this we’ll load the uploaded file for our library, if your uploaded file is not in the same directory as that of this php file, remember to use the absolute path for loading the excel file like (/var/file/html/upload/file.xlsx). We’ll use the “getWorksheetIterator” function of the PHPExcel class to iterate through the cells and get the data in a desired array.

No we’ll just unset the first row as that’s a header row which we won;t need to insert, at least in our example.

Now, all we have to do is insert the data into our MySQL database. As we are using an OpenCart MySQLi library, all we have to do is make a connection and write a loop with the insert query.

That’s it. If there’s an error the DB library will print it out. You also don’t have to escape the data as you can do so by using the $db->escape method.

If you have any question, feedback, requests or suggestions, feel free to say hello in the comment box below. You can download the entire code and required files from HERE.