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

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

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

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

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

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

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

Теперь давайте установим библиотеку 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.

Перенос данных из 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.

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

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

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

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

Метки: ,

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

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

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

  1. Андрей

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

  2. Delors

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

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

Ваш 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