PHPExcel. Как записать содержимое xls файла в таблицу SQL

PHPExcel. Как записать содержимое xls файла в таблицу SQL

В рамках данной заметки рассматривается вариант решения такой прикладной задачи, как запись в таблицу базы данных сайта содержимого excel-файла. Данный процесс будет осуществлен с помощью библиотеки phpexcel.

Что потребуется для успешного выполнения задачи:

  1. Сервер с поддержкой php
  2. База данных MYSQL
  3. Установленная библиотека phpexcel (устанавливается, к примеру, в корень сайта)
  4. ФТП-клиент
  5. Текстовый редактор (например, Notepad++)
  6. Excel-файл, содержимое которого нужно записать в базу данных.

В рамках данного учебного примера вся работа будет происходить в одном файле. Тестовый excel-файл далее будет обозначаться под именем «test.xls».

Шаг первый. Подключение библиотеки к файлу.

В файле, созданном специально под эту задачу, example.php следует записать следующие строки:

Первые строки объявляют кодировку страницы и параметры отображения ошибок (полезная вещь при решении сложных задач). Последняя строка же, как раз подключает библиотеку PHPExcel к нашему файлу. Естественно, следует указывать реальный путь к файлу (в зависимости от того, где была размещена библиотека). Собственно, после этого шага библиотека «активирована» и можно пользоваться функциями, которые содержатся в ней.

Шаг второй. Создание таблицы базы данных.

Далее следует создать подключение к базе данных и таблицу, в которую мы будем записывать excel-файла.

Создание подключения производится следующим образом:

Далее создаем таблицу. Количество столбцов в данном примере будет три (Предположим, что в тестовом excel-файле три колонки, которые нужно записать в БД).

Создание таблицы происходит следующим образом:

Подготовительные работы закончены, переходим к выполнению задачи.

Шаг третий. Чтение ячеек.

Первым делом нужно подгрузить необходимый excel-файл в данный скрипт. Делается это так:

Далее, если в файле несколько листов с одинаковыми данными, которые различаются по какому-либо несущественному в данной ситуации параметру (например этот файл содержит список товаров и на каждом листе представлена отдельная категория) — то нужно предварительно посчитать количество строк на каждом листе. Это можно сделать следующим образом:

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

Что происходит? Первым делом, запускается цикл по листам. Известно, что данные начинаются с 11-й строки, следовательно значение переменной row едино для всех столбцов. Далее объявляются переменные с «координатами» нужной колонки, в нашем случае это третья, пятая и одиннадцатая.

Далее устанавливается активный лист, соответствующий значению переменной $countProd на данном этапе цикла (т.е., изначально, он равен 0). На следующей строке переменной $aSheet передается значение активного листа.

Цикл for запускает «обход» строк excel-файла. Циклу уже известно, что количество строк не может быть больше, чем значение переменной $countRow. Далее, с помощью метода getCellByColumnAndRow скрипт получает значение ячейки по координатам (столбец и строка) и записывает его в переменную. Далее, с помощью метода $getValue() происходит запись в переменную $value, которая будет участвовать в записи значений в БД.

Шаг четвертый. Запись содержимого.

Строго после объявления последней переменной $value должен следовать SQL запрос на запись. В рамках данного примера он будет выглядеть следующим образом:

Тут, думаю, всё очевидно — происходит запись текущих значений $value в БД. На вопрос «Почему это делается в общем цикле, а не происходит предварительной записи в массив?» — можно ответить так: «В рамках учебного примера это несущественно. И (!) как ни странно, данный способ реализации выигрывает в производительности на слабой машине и относительно крупном (ок. 20т строк) файле.

Шаг пятый. Последние штрихи.

В заключение, нужно сделать еще две небольшие вещи.

Во-первых, нужно почистить таблицу от пустых строк (если таковые имеются). Например так:

И, во-вторых, закрыть соединение с базой:

Эти строки размещаются после закрывающей скобки цикла while, что обходит листы файла для чтения данных.

На этом всё, на выходе мы имеем таблицу БД с записанными туда данными из excel-файла, которой теперь можно «манипулировать» как угодно: Записывать ли данные из данной таблицы в «рабочие» таблицы CMS, либо использовать для каких-либо других целей.