Экспорт из Excel в MySQL

Экспорт из Excel в MySQL

От автора: в этом уроке мы рассмотрим экспорт из Excel в MySQL на PHP. В одном из уроков нашего сайта, мы с Вами изучали библиотеку PHPExcel, которая используется для работы с таблицами Microsoft Excel, используя язык PHP. При этом на примере создания прайс-листа для сайта, мы научились переносить данные из СУБД MySql в данные таблицы. Но очень часто при разработке сайтов, необходимо выполнить обратную задачу – выгрузить данные из таблиц Microsoft Excel в базу данных СУБД MySql. Поэтому в данном уроке мы займемся решением данной задачи.

скачать исходникискачать урок

Установка библиотеки PHPExcel

Для начала хотел бы обратить Ваше внимание на то, что данный урок – это своего рода продолжение урока Генерация прайс-листа в формате Excel при помощи PHP. Phpexcel, который публиковался ранее.

При этом если Вы не знакомы с библиотекой PHPExcel и с основами работы с ней, то настоятельно рекомендую просмотреть вышеуказанный урок, так как сегодня мы не будем тратить время на изучение основ. Для данного урока мы будем использовать тестовый сайт, который написан с использованием объектно-ориентированного подхода и шаблона проектирования MVC. Сейчас я не буду приводить его код, так как он будет доступен Вам в дополнительных материалах к уроку. В функционале данного сайта описан механизм загрузки на сервер файлов формата Excel и соответственно, данный механизм нам необходимо доработать, а именно, после успешной загрузки файла на сервер, необходимо прочитать содержимое данного файла и перенести данные из необходимых столбцов таблицы Excel, в базу данных MySql.

Фреймворк YII2. Быстрая разработка с современным PHP фреймворком

Узнай тонкости современной веб-разработки с помощью фреймворка YII2

Узнать подробнее

Теперь давайте установим библиотеку PHPExcel. Для этого мы воспользуемся инструментом Composer, (для тех кто не знаком с данным инструментов, рекомендую посмотреть урок Введение в Composer, поэтому открываем командную строку, переходим в каталог с тестовым сайтом и выполняем следующую команду:

composer require phpoffice/phpexcel

После установки библиотеки, в корневом каталоге тестового сайта, была создана папка vendor. В данной папке расположен файл autoload.php, который необходимо обязательно подключить в файле index.php (файл autoload.php содержит инструкции по подключению необходимых классов библиотеки).

require_once "vendor/autoload.php";

Подготовка к работе

Для данного урока нам потребуется база данных MySql, в которую мы будем осуществлять выгрузку данных. Структура таблицы main (в данную таблицу мы будем выгружать данные), тестовой базы данных:

При этом имена полей таблицы main, совпадают с заголовками столбцов документа Excel. Так как формировать SQl запрос по вставке данных, мы будем динамически и при этом, из таблицы Excel мы будем выгружать данные только из определенных столбцов, имена которых представлены в конфигурационном файле.

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.

Фреймворк YII2. Быстрая разработка с современным PHP фреймворком

Узнай тонкости современной веб-разработки с помощью фреймворка YII2

Узнать подробнее

Перенос данных из Excel в MySql

В тестовом сайте, за загрузку файла на сервер отвечает метод export(), класса Controller, при этом на данном этапе код данного метода, следующий:

public function export() {
 if(!empty($_FILES['xls']['tmp_name'])) {
 $file = $this->uploadFile($_FILES);
 
 }
  }

Обратите внимание, что после успешной загрузки файла на сервер – возвращается имя загруженного файла, для дальнейшей работы. Поэтому вызовем на исполнение метод xlsToMysql($file), который выполнит перенос данных из таблицы Excel в базу данных MySql:

public function export() {
 if(!empty($_FILES['xls']['tmp_name'])) {
 $file = $this->uploadFile($_FILES);
 
 if($this->xlsToMysql($file)) {
 header('Location:index.php');
 }
 }
  }

Код метода xlsToMysql($file), для начала, разберем по порядку, а затем я приведу его в полном виде. То есть дальнейшие пояснения относятся к коду будущего метода. Первым делом получаем объект модели и объект класса PHPExcel:

$this->model = $this->getModel();
$this->xls = $this->getPhpExcel($file);

Метод getModel() – не представляет интереса так как возвращает объект модели тестового сайта. Метод getPhpExcel($file), возвращает объект класса PHPExcel, для файла, имя которого содержится в переменной $file – то есть это имя — только что загруженного файла в систему. Другими словами мы загружаем файл с таблицей Excel, для дальнейших манипуляций, при этом возвращается объект, который мы будем использовать. Код метода getPhpExcel($file):

public function getPhpExcel($file) {
 return PHPExcel_IOFactory::load($file);
  }

Далее продолжаем описывать код метода xlsToMysql($file) и устанавливаем активный лист таблицы Excel с индексом 0 (напомню, что индексация листов Excel начинается с нуля) и получаем объект активного листа с данными:

$this->xls->setActiveSheetIndex(0);
$sheet = $this->xls->getActiveSheet();

Далее используя метод getRowIterator(), объекта активного листа – рекурсивно обходим все строки таблицы Excel и формируем объект с полученными данными:

$rowIterator = $sheet->getRowIterator();

Используя цикл foreach() мы можем пройтись по объекту $rowIterator и получить доступ к каждой строке таблицы в отдельности:

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():

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():

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.

На этом данный урок завершен. Всего Вам доброго и удачного кодирования!

Фреймворк YII2. Быстрая разработка с современным PHP фреймворком

Узнай тонкости современной веб-разработки с помощью фреймворка YII2

Узнать подробнее
Самые свежие новости IT и веб-разработки на нашем Telegram-канале

Хотите изучить MySQL?

Прямо сейчас посмотрите 24-х часовой курс по базе данных MySQL!

Смотреть курс

Метки: ,

Похожие статьи:

Комментарии Вконтакте:

Комментарии Facebook:

Комментарии (5)

  1. Андрей

    А продолжение будет? Хотелось бы больше возможностей данной библиотеки да и про остальные хотелось бы глянуть из той же серии, документация у них отвратительная.

  2. Delors

    Добрый день. Подскажите а как написать так чтобы скрипт не добавлял данные а обновлял цену товара, сравнение по артикулу к примеру.

  3. Дмитрий

    Здравствуйте.

    Скажите, пожалуйста, а какую проверку и где можно добавить на соответствие ключей.

    Допустим мне нужно строго загружать документы с ключами, как в $cells. И ни каких других ключей не должно быть и меньше этих ключей тоже не может быть. Если есть другие ключи, то выдать ошибку, что например документ не правильно заполнен. Может быть так, что допустим еще добавят один столбец случайно или прикрепят другой файл, но в другом файле тоже есть столбцы А,В,С…. и из них берутся значения и подставляются в базу данных, что не очень хорошо.

    Подскажите, пожалуйста, как это можно проверить.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Я не робот.

Spam Protection by WP-SpamFree