From Excel to MySQL. Small PHP function (fixed)

Introduction

Hello $ habrauser !

It happens that you need to import an Excel file into MySQL, but there is no ready-made solution anywhere. So I, when a friend asked me to look for an easy way to import, first decided to google to look for a solution. Alas, the php excel to mysql query did not give anything concrete, or the methods described were not quite convenient. Then I decided to find a library for working with Excel in PHP, and I got PHPExcel. But again, I was disappointed; the phpexcel to mysql query did not give anything worthwhile (I am a lazy user and do not go beyond the 1st page). In the end, I decided to create my own bike script, which I want to share with you.

Start

So, I found the library, downloaded and began to understand. To begin with, it was necessary to connect the library and create a connection to the database, which is not at all difficult:

require_once "PHPExcel.php"; $connection = new mysqli("localhost", "user", "pass", "base"); $connection->set_charset("utf8"); 

Next you need to open the Excel file for reading:

 $PHPExcel_file = PHPExcel_IOFactory::load("./file.xlsx"); 

After opening the file, we need to go through all the sheets in it and add each to the MySQL database (you can also add 1 specific one, but more on that later):

 foreach ($PHPExcel_file->getWorksheetIterator() as $worksheet) { // ... } 

And now the most interesting ...

Brute force and add

We will proceed from the fact that we do not have a table (or we have it, but with other data) and need to create it. To do this, we need to get the names for the columns (as requested by a friend, the names can be in the 1st row of the table):

 //      MySQL $columns_str = ""; //     Excel $columns_count = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn()); //    Excel        for ($column = 0; $column < $columns_count; $column++) { $columns_str .= ($columns_name_on1line ? "column" . $column : $worksheet->getCellByColumnAndRow($column, 1)->getCalculatedValue()) . ","; } //  ,     $columns_str = substr($columns_str, 0, -1); 

Next, delete the table from the database, if it existed, and create a new one:

 $connection->query("DROP TABLE IF EXISTS exceltable"); $connection->query("CREATE TABLE exceltable (" . str_replace(",", " TEXT NOT NULL,", $columns_str) . " TEXT NOT NULL)"); 

As can be seen from the code, the values ​​will be of type TEXT . Now we are going to actually search the cells and add them to the database. Of course, such an algorithm is not difficult to find in the vast Stack Overflow , however, it was noticed that an error occurred when trying to read the merged cells (more precisely, the mismatch of the number of columns and values ​​in the query). This is what I decided to take into account:

 //     Excel $rows_count = $worksheet->getHighestRow(); //    Excel for ($row = 1; $row <= $rows_count; $row++) { //         Excel $value_str = ""; //    Excel for ($column = 0; $column < $columns_count; $column++) { //       Excel $merged_value = ""; //   Excel $cell = $worksheet->getCellByColumnAndRow($column, $row); //      Excel foreach ($worksheet->getMergeCells() as $mergedCells) { //    - , if ($cell->isInRange($mergedCells)) { //      ,       //   $merged_value = $worksheet->getCell(explode(":", $mergedCells)[0])->getCalculatedValue(); break; } } // ,    :  ,    ,    //   $value_str .= "'" . (strlen($merged_value) == 0 ? $cell->getCalculatedValue() : $merged_value) . "',"; } //  ,     $value_str = substr($value_str, 0, -1); //     MySQL $connection->query("INSERT INTO exceltable (" . $columns_str . ") VALUES (" . $value_str . ")"); } 

It's all about the function!

Of course, this script would be much more convenient if everything were combined into a function. Therefore, the final result is:

Conclusion

Well, I hope this article will help you. Well, or if you want to invent your bike, but only write a script with a small motor , this article will help you get started.
')

PS

This is my first, and I think not the last article. Therefore, I am waiting for your advice and amendments, as is customary here, in the comments.

Update

I see, nevertheless, I managed to create a small discussion, but not everyone understands why it was done this way. I will try to explain.

First of all: an older person had to work with this and it would be difficult for him to explain how to save the file to CSV without losing the data (and this cannot be ruled out, besides, they have their own format on the XLS file that comes from above) and, Moreover, how to import it via phpMyAdmin (which, by the way, since version 3.4.5 does not support XLS / XLSX , I advise you to see why) or the like. So it does not fit.

Secondly: all this should be located on the hosting, and the installation of modules both on the server and for local programs is not suitable (besides, there is Linux, and not Windows, as some have thought).

Thirdly: this business is carried out every six months, but from idleness, I decided to write such a function, which is able to generalize the import (all of a sudden who needs it).

Now for the good: rewrote this function in the class, corrected something and added the ability to export from MySQL to Excel. You can pick up from here .

Sorry for not answering in the comments, I decided that the article itself would be relevant.