Из Excel в MySQL. Небольшая функция на PHP (fixed) | Excellentricks: искусство аналитики

Содержание

Начало
Перебор и добавление
Заключение

Начало

Итак, библиотеку я нашел, скачал и начал разбираться. Для начала нужно было подключить библиотеку и создать подключение к базе, что совсем не сложно:

require_once "PHPExcel.php";  $connection = new mysqli("localhost", "user", "pass", "base");
 $connection->set_charset("utf8");

Далее нужно открыть файл Excel для чтения:

$PHPExcel_file = PHPExcel_IOFactory::load("./file.xlsx");

После открытия файла, нам нужно перебрать все листы в нем и каждый добавить в базу MySQL (можно и 1 конкретный, но об этом позже):

foreach ($PHPExcel_file->getWorksheetIterator() as $worksheet) {     // ... }

Ну а теперь самое интересное…

Перебор и добавление

Мы будем исходить из того, что таблицы у нас нет (или есть, но с другими данными) и ее нужно создать. Для этого нам нужно получить имена для столбцов (в соответствии с просьбой друга, имена могут находиться в 1 строчке таблицы):

// Строка для названий столбцов таблицы MySQL
$columns_str = "";
// Количество столбцов на листе Excel
$columns_count = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn());  
// Перебираем столбцы листа Excel и генерируем строку с именами через запятую
 for ($column = 0; $column < $columns_count;
 $column++) {     
$columns_str .= ($columns_name_on1line ? "column" . $column : $worksheet->getCellByColumnAndRow($column, 1)->getCalculatedValue()) . ","; }
  // Обрезаем строку, убирая запятую в конце $columns_str = substr($columns_str, 0, -1);

Велосипед для генерации Excel документов по шаблону

Далее удаляем таблицу из базы, если она существовала, и создаем новую:

$connection->query("DROP TABLE IF EXISTS exceltable");
 $connection->query("CREATE TABLE exceltable (" . str_replace(",", " TEXT NOT NULL,", $columns_str) . " TEXT NOT NULL)");

Как видно из кода, значения будут иметь тип TEXT. Теперь приступаем собственно к перебору ячеек и добавления их в базу. Конечно, такой алгоритм не сложно найти на просторах Stack Overflow, однако было замечено, что происходить ошибка при попытки чтения объединенных ячеек (точнее несоответствие количества столбцов и значений в запросе). Это я и решил учесть:

// Количество строк на листе Excel 
$rows_count = $worksheet->getHighestRow();  
// Перебираем строки листа Excel 
for ($row = 1; $row <= $rows_count; $row++) {
     // Строка со значениями всех столбцов в строке листа Excel
     $value_str = "";      // Перебираем столбцы листа Excel
     for ($column = 0; $column < $columns_count; $column++) {
         // Строка со значением объединенных ячеек листа Excel
         $merged_value = "";         // Ячейка листа Excel
         $cell = $worksheet->getCellByColumnAndRow($column, $row);
          // Перебираем массив объединенных ячеек листа Excel
         foreach ($worksheet->getMergeCells() as $mergedCells) {
             // Если текущая ячейка - объединенная,
             if ($cell->isInRange($mergedCells)) {
                 // то вычисляем значение первой объединенной ячейки, и используем её в качестве значения                 
                // текущей ячейки                 
$merged_value = $worksheet->getCell(explode(":", $mergedCells)[0])->getCalculatedValue();
                 break;
             }
         }
          // Проверяем, что ячейка не объединенная: если нет, то берем ее значение, иначе значение первой         // объединенной ячейки         $value_str .= "'" . (strlen($merged_value) == 0 ? $cell->getCalculatedValue() : $merged_value) . "',";     }      // Обрезаем строку, убирая запятую в конце     $value_str = substr($value_str, 0, -1);      // Добавляем строку в таблицу MySQL     $connection->query("INSERT INTO exceltable (" . $columns_str . ") VALUES (" . $value_str . ")"); }

Экспорт данных из PostgreSQL в Excel

Заключение

Что ж, надеюсь данная статья поможет вам. Ну, или, если вы захотите ~~изобрести свой велосипед, но только с моторчиком~~ написать свой скрипт, эта статья поможет вам начать.

Не все понимают, почему было сделано именно так. Постараюсь объяснить.

Во-первых: с этим должен был работать пожилой человек, которому будет трудновато объяснить как сохранить файл в CSV, при этом не потеряв данные (а такое исключать нельзя, к тому же у них свой формат на файл XLS, который приходит сверху) и, тем более, как это импортировать через phpMyAdmin (который, кстати, с версии 3.4.5 не поддерживает XLS/XLSX, советую посмотреть почему) или подобное. Так что это не подходит.

Во-вторых: все это должно быть расположено на хостинге, и установка модулей как на сервер, так и для локальных программ не подходит (к тому же там Linux, а не Windows, как некоторые подумали).

В-третьих: это дело проводится раз в полгода, однако от безделья я решил написать такую функцию, способную обобщить импорт (вдруг кому нужно).

Automation: быстрый старт или «А ну-ка, Excel, пиши за меня сам!»