В рамках данной заметки рассматривается вариант решения такой прикладной задачи, как запись в таблицу базы данных сайта содержимого excel-файла. Данный процесс будет осуществлен с помощью библиотеки phpexcel.
Что потребуется для успешного выполнения задачи:
- Сервер с поддержкой php
- База данных MYSQL
- Установленная библиотека phpexcel (устанавливается, к примеру, в корень сайта)
- ФТП-клиент
- Текстовый редактор (например, Notepad++)
- Excel-файл, содержимое которого нужно записать в базу данных.
В рамках данного учебного примера вся работа будет происходить в одном файле. Тестовый excel-файл далее будет обозначаться под именем «test.xls».
Шаг первый. Подключение библиотеки к файлу.
В файле, созданном специально под эту задачу, example.php следует записать следующие строки:
<?php
header( 'Content-Type: text/html; charset=utf-8' );
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
require_once 'PHPExcel-1.8/Classes/PHPExcel.php';
Первые строки объявляют кодировку страницы и параметры отображения ошибок (полезная вещь при решении сложных задач). Последняя строка же, как раз подключает библиотеку PHPExcel к нашему файлу. Естественно, следует указывать реальный путь к файлу (в зависимости от того, где была размещена библиотека). Собственно, после этого шага библиотека «активирована» и можно пользоваться функциями, которые содержатся в ней.
Шаг второй. Создание таблицы базы данных.
Далее следует создать подключение к базе данных и таблицу, в которую мы будем записывать excel-файла.
Создание подключения производится следующим образом:
//Параметры подключения
$hostname = 'hostname';
$username = 'username';
$passwordname = 'passwordname';
$basename = 'basename';
// Открываем соединение с базой данных
$conn = new mysqli($hostname, $username, $passwordname, $basename) or die ('Невозможно открыть базу');
//Устанавливаем кодировку
$query = "SET NAMES 'utf8'";
if (mysqli_query($conn,$query) === TRUE) {}
Далее создаем таблицу. Количество столбцов в данном примере будет три (Предположим, что в тестовом excel-файле три колонки, которые нужно записать в БД).
Создание таблицы происходит следующим образом:
$sql_1 = "DROP TABLE IF EXISTS testNew";
$result_1 = $conn->query($sql_1) or die("ERR_1 " . mysqli_error($conn));
$sql_2 = "CREATE TABLE testNew(
A_MPN varchar(64) NULL DEFAULT NULL,
A_NAME varchar(255) NULL DEFAULT NULL,
A_PRICE decimal(15,4) NULL DEFAULT NULL,
PRIMARY KEY (A_MPN)
) ENGINE=MyISAM DEFAULT CHARSET=utf8";
$result_2 = $conn->query($sql_2) or die("ERR_Main_tbl " . mysqli_error($conn));
if($result_2){};
Подготовительные работы закончены, переходим к выполнению задачи.
Шаг третий. Чтение ячеек.
Первым делом нужно подгрузить необходимый excel-файл в данный скрипт. Делается это так:
$pExcel = PHPExcel_IOFactory::load('test.xls');
Далее, если в файле несколько листов с одинаковыми данными, которые различаются по какому-либо несущественному в данной ситуации параметру (например этот файл содержит список товаров и на каждом листе представлена отдельная категория) — то нужно предварительно посчитать количество строк на каждом листе. Это можно сделать следующим образом:
// Цикл по листам Excel-файла
$i = 0;
foreach ($pExcel->getWorksheetIterator() as $worksheet) {
// выгружаем данные из объекта в массив
$tables[] = $worksheet->toArray();
$i++;
}
$countRow = 0;
foreach( $tables as $table ) {
foreach($table as $row) {
$countRow++;
}
}
Следующим шагом является, собственно, чтение содержимого файла. Сначала приводится код, потом даются небольшие комментарии относительно того, что он делает.
$countProd=0;
while ($countProd<$i){
// Данные начинаются с одиннадцатой строки
$row = 11;
//Для артикула
$col3 = 3;
//Для имени
$col5 = 5;
//Для цены
$col11 = 11;
$pExcel->setActiveSheetIndex($countProd);
$aSheet = $pExcel->getActiveSheet();
for ($row; $row <= $countRow; $row ++){
$cell3 = $aSheet->getCellByColumnAndRow($col3, $row);
$cell5 = $aSheet->getCellByColumnAndRow($col5, $row);
$cell11 = $aSheet->getCellByColumnAndRow($col11, $row);
$value3 = $cell3->getValue();
$value5 = $cell5->getValue();
$value11 = $cell11->getValue();
}
$countProd++;
}
Что происходит? Первым делом, запускается цикл по листам. Известно, что данные начинаются с 11-й строки, следовательно значение переменной row едино для всех столбцов. Далее объявляются переменные с «координатами» нужной колонки, в нашем случае это третья, пятая и одиннадцатая.
Далее устанавливается активный лист, соответствующий значению переменной $countProd на данном этапе цикла (т.е., изначально, он равен 0). На следующей строке переменной $aSheet передается значение активного листа.
Цикл for запускает «обход» строк excel-файла. Циклу уже известно, что количество строк не может быть больше, чем значение переменной $countRow. Далее, с помощью метода getCellByColumnAndRow скрипт получает значение ячейки по координатам (столбец и строка) и записывает его в переменную. Далее, с помощью метода $getValue() происходит запись в переменную $value, которая будет участвовать в записи значений в БД.
Шаг четвертый. Запись содержимого.
Строго после объявления последней переменной $value должен следовать SQL запрос на запись. В рамках данного примера он будет выглядеть следующим образом:
$sql_1 = "INSERT INTO testNew( A_MPN, A_NAME, A_PRICE ) VALUES('$value3','$value5','$value11')";
$result_1 = $conn->query($sql_1) or die("<br>ERR_ARTICLE " . mysqli_error($conn));
Тут, думаю, всё очевидно — происходит запись текущих значений $value в БД. На вопрос «Почему это делается в общем цикле, а не происходит предварительной записи в массив?» — можно ответить так: «В рамках учебного примера это несущественно. И (!) как ни странно, данный способ реализации выигрывает в производительности на слабой машине и относительно крупном (ок. 20т строк) файле.
Шаг пятый. Последние штрихи.
В заключение, нужно сделать еще две небольшие вещи.
Во-первых, нужно почистить таблицу от пустых строк (если таковые имеются). Например так:
$sql_1 = "DELETE FROM testNew WHERE A_MPN = ''";
$result_1 = $conn->query($sql_1) or die("<br>ERR_FIN " . mysqli_error($conn));
И, во-вторых, закрыть соединение с базой:
mysqli_close($conn);
Эти строки размещаются после закрывающей скобки цикла while, что обходит листы файла для чтения данных.
На этом всё, на выходе мы имеем таблицу БД с записанными туда данными из excel-файла, которой теперь можно «манипулировать» как угодно: Записывать ли данные из данной таблицы в «рабочие» таблицы CMS, либо использовать для каких-либо других целей.