Від автора: у цьому уроці ми розглянемо експорт з 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 містить інструкції по підключенню необхідних класів бібліотеки).
Підготовка до роботи
Для даного уроку нам потрібно база даних 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’=>’кількість’,
‘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.
На цьому даний урок завершено. Всього Вам доброго і вдалого кодування!
