От автора: в этом уроке мы рассмотрим экспорт из Excel в MySQL на PHP. В одном из уроков нашего сайта, мы с Вами изучали библиотеку PHPExcel, которая используется для работы с таблицами Microsoft Excel, используя язык PHP. При этом на примере создания прайс-листа для сайта, мы научились переносить данные из СУБД MySql в данные таблицы. Но очень часто при разработке сайтов, необходимо выполнить обратную задачу – выгрузить данные из таблиц Microsoft Excel в базу данных СУБД MySql. Поэтому в данном уроке мы займемся решением данной задачи.
Установка библиотеки PHPExcel
Для начала хотел бы обратить Ваше внимание на то, что данный урок – это своего рода продолжение урока Генерация прайс-листа в формате Excel при помощи PHP. Phpexcel, который публиковался ранее.
При этом если Вы не знакомы с библиотекой PHPExcel и с основами работы с ней, то настоятельно рекомендую просмотреть вышеуказанный урок, так как сегодня мы не будем тратить время на изучение основ. Для данного урока мы будем использовать тестовый сайт, который написан с использованием объектно-ориентированного подхода и шаблона проектирования MVC. Сейчас я не буду приводить его код, так как он будет доступен Вам в дополнительных материалах к уроку. В функционале данного сайта описан механизм загрузки на сервер файлов формата Excel и соответственно, данный механизм нам необходимо доработать, а именно, после успешной загрузки файла на сервер, необходимо прочитать содержимое данного файла и перенести данные из необходимых столбцов таблицы Excel, в базу данных MySql.
Теперь давайте установим библиотеку PHPExcel. Для этого мы воспользуемся инструментом Composer, (для тех кто не знаком с данным инструментов, рекомендую посмотреть урок Введение в Composer, поэтому открываем командную строку, переходим в каталог с тестовым сайтом и выполняем следующую команду:
|
1 |
composer require phpoffice/phpexcel |
После установки библиотеки, в корневом каталоге тестового сайта, была создана папка vendor. В данной папке расположен файл autoload.php, который необходимо обязательно подключить в файле index.php (файл autoload.php содержит инструкции по подключению необходимых классов библиотеки).
|
1 |
require_once "vendor/autoload.php"; |
Подготовка к работе
Для данного урока нам потребуется база данных MySql, в которую мы будем осуществлять выгрузку данных. Структура таблицы main (в данную таблицу мы будем выгружать данные), тестовой базы данных:
При этом имена полей таблицы main, совпадают с заголовками столбцов документа Excel. Так как формировать SQl запрос по вставке данных, мы будем динамически и при этом, из таблицы Excel мы будем выгружать данные только из определенных столбцов, имена которых представлены в конфигурационном файле.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
class Config { public $cells = array( 'D'=>'order', 'F'=>'date port', 'B'=>'customer', 'G'=>'country', 'J'=>'products', 'K'=>'grade', 'U'=>'date', 'M'=>'size', 'P'=>'length', 'Q'=>'quantity', 'U'=>'date', 'V'=>'gruzo', ); } |
То есть, как Вы видите в свойстве $cells класса Config, содержится массив с именами столбцов, таблицы Excel, данные которых подлежат выгрузке в базу данных. При этом в качестве ключей используются имена столбцов, а в качестве значений — заголовки каждого столбца и соответственно заголовки столбцов одноименны с полями таблицы main, базы данных. Таким образом, добавляя или удаляя ячейки из данного массива, мы указываем какие данные необходимо загрузить из таблицы Excel.
Перенос данных из Excel в MySql
В тестовом сайте, за загрузку файла на сервер отвечает метод export(), класса Controller, при этом на данном этапе код данного метода, следующий:
|
1 2 3 4 5 6 |
public function export() { if(!empty($_FILES['xls']['tmp_name'])) { $file = $this->uploadFile($_FILES); } } |
Обратите внимание, что после успешной загрузки файла на сервер – возвращается имя загруженного файла, для дальнейшей работы. Поэтому вызовем на исполнение метод xlsToMysql($file), который выполнит перенос данных из таблицы Excel в базу данных MySql:
|
1 2 3 4 5 6 7 8 9 |
public function export() { if(!empty($_FILES['xls']['tmp_name'])) { $file = $this->uploadFile($_FILES); if($this->xlsToMysql($file)) { header('Location:index.php'); } } } |
Код метода xlsToMysql($file), для начала, разберем по порядку, а затем я приведу его в полном виде. То есть дальнейшие пояснения относятся к коду будущего метода. Первым делом получаем объект модели и объект класса PHPExcel:
|
1 2 |
$this->model = $this->getModel(); $this->xls = $this->getPhpExcel($file); |
Метод getModel() – не представляет интереса так как возвращает объект модели тестового сайта. Метод getPhpExcel($file), возвращает объект класса PHPExcel, для файла, имя которого содержится в переменной $file – то есть это имя — только что загруженного файла в систему. Другими словами мы загружаем файл с таблицей Excel, для дальнейших манипуляций, при этом возвращается объект, который мы будем использовать. Код метода getPhpExcel($file):
|
1 2 3 |
public function getPhpExcel($file) { return PHPExcel_IOFactory::load($file); } |
Далее продолжаем описывать код метода xlsToMysql($file) и устанавливаем активный лист таблицы Excel с индексом 0 (напомню, что индексация листов Excel начинается с нуля) и получаем объект активного листа с данными:
|
1 2 |
$this->xls->setActiveSheetIndex(0); $sheet = $this->xls->getActiveSheet(); |
Далее используя метод getRowIterator(), объекта активного листа – рекурсивно обходим все строки таблицы Excel и формируем объект с полученными данными:
|
1 |
$rowIterator = $sheet->getRowIterator(); |
Используя цикл foreach() мы можем пройтись по объекту $rowIterator и получить доступ к каждой строке таблицы в отдельности:
|
1 2 3 4 5 6 7 8 9 10 11 |
foreach($rowIterator as $row) { if($row->getRowIndex() != 1) { $cellIterator = $row->getCellIterator(); foreach($cellIterator as $cell) { $cellPath = $cell->getColumn(); if(isset($this->config->cells[$cellPath])) { $arr[$row->getRowIndex()][$this->config->cells[$cellPath]] = $cell->getCalculatedValue(); } } } } |
При этом на каждой итерации цикла в переменную $row, попадает объект одной строки таблицы Excel. Для определения индекса каждой строки, используем метод getRowIndex(). Далее, если на текущей итерации индекс строки не равен 1, так как в первой строке располагается шапка таблицы, значит, рекурсивно обходим все ячейки текущей строки, используя метод getCellIterator(). А затем, аналогично проходимся в цикле foreach(), по полученному объекту, получая, тем самым, доступ к каждой ячейке в отдельности. Для получения имени столбца, для текущей ячейки – используем метод getColumn(). При этом, если имя ячейки, присутствует в массиве свойства cells, объекта Config, значит, получаем данные текущей ячейки, используя метод getCalculatedValue().
Хотел бы обратить Ваше внимание, на следующее – если в некой ячейке таблицы содержится дата, то для преобразования данной даты в формат языка PHP, необходимо использовать статический метод ExcelToPHP($cell->getCalculatedValue()) класса PHPExcel_Shared_Date, который вернет дату из ячейки таблицы Excel, в формате timestamp. Полный код метода xlsToMysql():
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
protected function xlsToMysql ($file) { $this->model = $this->getModel(); $this->xls = $this->getPhpExcel($file); $this->xls->setActiveSheetIndex(0); $sheet = $this->xls->getActiveSheet(); $rowIterator = $sheet->getRowIterator(); $arr = array(); foreach ($rowIterator as $row) { if($row->getRowIndex() != 1) { $cellIterator = $row->getCellIterator(); foreach ($cellIterator as $cell) { $cellPath = $cell->getColumn(); if(isset($this->config->cells[$cellPath])) { if($cellPath == 'U') { if($cell->getCalculatedValue() == '00.00.0000' || $cell->getCalculatedValue() == '') { $t = '0000-00-00'; } else { $t = date( 'Y-m-d', \PHPExcel_Shared_Date::ExcelToPHP( $cell->getCalculatedValue() ) ); } $arr[$row->getRowIndex()][$this->config->cells[$cellPath]] =$t; continue; } if($cellPath == 'F') { if($cell->getCalculatedValue() == '00.00.0000' || $cell->getCalculatedValue() == '') { $t = '0000-00-00'; } else { $t = date( 'Y-m-d', \PHPExcel_Shared_Date::ExcelToPHP( $cell->getCalculatedValue() ) ); } $arr[$row->getRowIndex()][$this->config->cells[$cellPath]] =$t; continue; } $arr[$row->getRowIndex()][$this->config->cells[$cellPath]] = $cell->getCalculatedValue(); } } } } $res = $this->model->inserExcel($arr); if(!$res) { exit(); } return TRUE; } |
В итоге, работы данного метода, мы получаем следующий массив данных:
То есть в каждой ячейке данного массива, содержится массив с данными по отдельной строке таблицы Excel. Соответственно данный массив передаем методу модели insertExcel(), который сформирует SQL запрос для вставки данных в таблицу main базы данных MySql. Код метода модели insertExcel():
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
public function insertExcel($arr) { $fields = ''; foreach($arr[2] as $key => $cell) { $fields .= '`'.$key.'`'.','; } $fields = trim($fields,','); $str = ''; // INSERT INTO `main` (``,``,``..) VALUES ('','','',),(),(),(); foreach($arr as $item) { $str .= "("; foreach($item as $cell) { $str .= "'".$this->db->real_escape_string($cell)."',"; } $str = trim($str,","); $str .= "),"; } $str = trim($str,","); $query = "INSERT INTO `main` (".$fields.") VALUES ".$str; $result = $this->db->query($query); if($result) { return TRUE; } } |
Данный метод формирует SQL запрос вида INSERT INTO main (field1,field2,field3..) VALUES (‘data1′,’data2′,’data3’,),(),(),(); — для добавления всех данных из переданного массива в таблицу main базы данных. Соответственно после выполнения данного SQL запроса – информация из таблиц Microsoft Excel будет перенесена в базу данных MySql.
На этом данный урок завершен. Всего Вам доброго и удачного кодирования!
Комментарии (5)