От автора: в этом уроке мы рассмотрим экспорт из 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 мы будем выгружать данные только из определенных столбцов, имена которых представлены в конфигурационном файле.
01.class Config {02.03.public $cells = array(04.'D'=>'order',05.'F'=>'date port',06.'B'=>'customer',07.'G'=>'country',08.'J'=>'products',09.'K'=>'grade',10.'U'=>'date',11.'M'=>'size',12.'P'=>'length',13.'Q'=>'quantity',14.'U'=>'date',15.'V'=>'gruzo',16.17.);18.}То есть, как Вы видите в свойстве $cells класса Config, содержится массив с именами столбцов, таблицы Excel, данные которых подлежат выгрузке в базу данных. При этом в качестве ключей используются имена столбцов, а в качестве значений — заголовки каждого столбца и соответственно заголовки столбцов одноименны с полями таблицы main, базы данных. Таким образом, добавляя или удаляя ячейки из данного массива, мы указываем какие данные необходимо загрузить из таблицы Excel.
Перенос данных из Excel в MySql
В тестовом сайте, за загрузку файла на сервер отвечает метод export(), класса Controller, при этом на данном этапе код данного метода, следующий:
1.public function export() {2.if(!empty($_FILES['xls']['tmp_name'])) {3.$file = $this->uploadFile($_FILES);4.5.}6.}Обратите внимание, что после успешной загрузки файла на сервер – возвращается имя загруженного файла, для дальнейшей работы. Поэтому вызовем на исполнение метод xlsToMysql($file), который выполнит перенос данных из таблицы Excel в базу данных MySql:
01.public function export() {02.if(!empty($_FILES['xls']['tmp_name'])) {03.$file = $this->uploadFile($_FILES);04.05.if($this->xlsToMysql($file)) {06.header('Location:index.php');07.}08.}09.}Код метода xlsToMysql($file), для начала, разберем по порядку, а затем я приведу его в полном виде. То есть дальнейшие пояснения относятся к коду будущего метода. Первым делом получаем объект модели и объект класса PHPExcel:
1.$this->model = $this->getModel();2.$this->xls = $this->getPhpExcel($file);Метод getModel() – не представляет интереса так как возвращает объект модели тестового сайта. Метод getPhpExcel($file), возвращает объект класса PHPExcel, для файла, имя которого содержится в переменной $file – то есть это имя — только что загруженного файла в систему. Другими словами мы загружаем файл с таблицей Excel, для дальнейших манипуляций, при этом возвращается объект, который мы будем использовать. Код метода getPhpExcel($file):
1.public function getPhpExcel($file) {2.return PHPExcel_IOFactory::load($file);3.}Далее продолжаем описывать код метода xlsToMysql($file) и устанавливаем активный лист таблицы Excel с индексом 0 (напомню, что индексация листов Excel начинается с нуля) и получаем объект активного листа с данными:
1.$this->xls->setActiveSheetIndex(0);2.$sheet = $this->xls->getActiveSheet();Далее используя метод getRowIterator(), объекта активного листа – рекурсивно обходим все строки таблицы Excel и формируем объект с полученными данными:
1.$rowIterator = $sheet->getRowIterator();Используя цикл foreach() мы можем пройтись по объекту $rowIterator и получить доступ к каждой строке таблицы в отдельности:
01.foreach($rowIterator as $row) {02.if($row->getRowIndex() != 1) {03.$cellIterator = $row->getCellIterator();04.foreach($cellIterator as $cell) {05.$cellPath = $cell->getColumn();06.if(isset($this->config->cells[$cellPath])) {07.$arr[$row->getRowIndex()][$this->config->cells[$cellPath]] = $cell->getCalculatedValue();08.}09.}10.}11.}При этом на каждой итерации цикла в переменную $row, попадает объект одной строки таблицы Excel. Для определения индекса каждой строки, используем метод getRowIndex(). Далее, если на текущей итерации индекс строки не равен 1, так как в первой строке располагается шапка таблицы, значит, рекурсивно обходим все ячейки текущей строки, используя метод getCellIterator(). А затем, аналогично проходимся в цикле foreach(), по полученному объекту, получая, тем самым, доступ к каждой ячейке в отдельности. Для получения имени столбца, для текущей ячейки – используем метод getColumn(). При этом, если имя ячейки, присутствует в массиве свойства cells, объекта Config, значит, получаем данные текущей ячейки, используя метод getCalculatedValue().
Хотел бы обратить Ваше внимание, на следующее – если в некой ячейке таблицы содержится дата, то для преобразования данной даты в формат языка PHP, необходимо использовать статический метод ExcelToPHP($cell->getCalculatedValue()) класса PHPExcel_Shared_Date, который вернет дату из ячейки таблицы Excel, в формате timestamp. Полный код метода xlsToMysql():
01.protected function xlsToMysql ($file) {02.03.$this->model = $this->getModel();04.$this->xls = $this->getPhpExcel($file);05.06.$this->xls->setActiveSheetIndex(0);07.$sheet = $this->xls->getActiveSheet();08.09.$rowIterator = $sheet->getRowIterator();10.11.$arr = array();12.foreach ($rowIterator as $row) {13.14.if($row->getRowIndex() != 1) {15.16.$cellIterator = $row->getCellIterator();17.foreach ($cellIterator as $cell) {18.$cellPath = $cell->getColumn();19.if(isset($this->config->cells[$cellPath])) {20.21.if($cellPath == 'U') {22.if($cell->getCalculatedValue() == '00.00.0000' || $cell->getCalculatedValue() == '') {23.$t = '0000-00-00';24.}25.else {26.$t = date( 'Y-m-d', \PHPExcel_Shared_Date::ExcelToPHP( $cell->getCalculatedValue() ) );27.}28.29.$arr[$row->getRowIndex()][$this->config->cells[$cellPath]] =$t;30.continue;31.}32.33.if($cellPath == 'F') {34.if($cell->getCalculatedValue() == '00.00.0000' || $cell->getCalculatedValue() == '') {35.$t = '0000-00-00';36.}37.else {38.$t = date( 'Y-m-d', \PHPExcel_Shared_Date::ExcelToPHP( $cell->getCalculatedValue() ) );39.}40.41.$arr[$row->getRowIndex()][$this->config->cells[$cellPath]] =$t;42.continue;43.}44.45.$arr[$row->getRowIndex()][$this->config->cells[$cellPath]] = $cell->getCalculatedValue();46.}47.}48.49.50.}51.52.}53.54.$res = $this->model->inserExcel($arr);55.if(!$res) {56.exit();57.}58.return TRUE;59.}В итоге, работы данного метода, мы получаем следующий массив данных:
То есть в каждой ячейке данного массива, содержится массив с данными по отдельной строке таблицы Excel. Соответственно данный массив передаем методу модели insertExcel(), который сформирует SQL запрос для вставки данных в таблицу main базы данных MySql. Код метода модели insertExcel():
01.public function insertExcel($arr) {02.03.$fields = '';04.05.foreach($arr[2] as $key => $cell) {06.$fields .= '`'.$key.'`'.',';07.}08.$fields = trim($fields,',');09.10.$str = '';11.// INSERT INTO `main` (``,``,``..) VALUES ('','','',),(),(),();12.foreach($arr as $item) {13.$str .= "(";14.foreach($item as $cell) {15.$str .= "'".$this->db->real_escape_string($cell)."',";16.}17.$str = trim($str,",");18.$str .= "),";19.}20.$str = trim($str,",");21.$query = "INSERT INTO `main` (".$fields.") VALUES ".$str;22.23.$result = $this->db->query($query);24.if($result) {25.return TRUE;26.}27.28.}Данный метод формирует SQL запрос вида INSERT INTO `main` (`field1`,`field2`,`field3`..) VALUES (‘data1′,’data2′,’data3′,),(),(),(); — для добавления всех данных из переданного массива в таблицу main базы данных. Соответственно после выполнения данного SQL запроса – информация из таблиц Microsoft Excel будет перенесена в базу данных MySql.
На этом данный урок завершен. Всего Вам доброго и удачного кодирования!
Источник: https://webformyself.com/eksport-iz-excel-v-mysql/