Из Excel в MySQL. Небольшая функция на PHP

Введение

Здравствуй, $habrauser!

Бывает так, что вам нужно импортировать файл Excel в базу MySQL, но готового решения нигде нет. Вот и я, когда меня попросил друг поискать легкий способ импорта, сперва решил загуглить поискать решение. Увы запрос php excel to mysql не дал ничего конкретного, или же был довольно таки не удобен. Тогда же я решил найти библиотеку для работы с Excel на PHP, мне попалась PHPExcel. Но опять же меня ждало разочарование, запрос phpexcel to mysql не дал ничего путного (я ленивый пользователь и дальше 1й страницы не хожу). В итоге я решил создать свой велосипед скрипт, которым и хочу поделиться с вами.

Начало

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

require_once "./libraries/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);

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

$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
for ($row = $columns_name_line + 1; $row <= $worksheet->getHighestRow(); $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 . ")");
}

Все дело в функцию!

Конечно данный скрипт был бы гораздо удобнее, если бы все объединить в функцию. Поэтому итоговый результат получается такой:

// Подключаем библиотеку
require_once "./libraries/PHPExcel.php";

// Функция преобразования листа Excel в таблицу MySQL, с учетом объединенных строк и столбцов.
// Значения берутся уже вычисленными. Параметры:
//     $worksheet - лист Excel
//     $connection - соединение с MySQL (mysqli)
//     $table_name - имя таблицы MySQL
//     $columns_name_line - строка с именами столбцов таблицы MySQL (0 - имена типа column + n)
function excel2mysql($worksheet, $connection, $table_name, $columns_name_line = 0) {
  // Проверяем соединение с MySQL
  if (!$connection->connect_error) {
    // Строка для названий столбцов таблицы MySQL
    $columns_str = "";
    // Количество столбцов на листе Excel
    $columns_count = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn());

    // Перебираем столбцы листа Excel и генерируем строку с именами через запятую
    for ($column = 0; $column < $columns_count; $column++) {
      $columns_str .= ($columns_name_line == 0 ? "column" . $column : $worksheet->getCellByColumnAndRow($column, $columns_name_line)->getCalculatedValue()) . ",";
    }

    // Обрезаем строку, убирая запятую в конце
    $columns_str = substr($columns_str, 0, -1);

    // Удаляем таблицу MySQL, если она существовала
    if ($connection->query("DROP TABLE IF EXISTS " . $table_name)) {
      // Создаем таблицу MySQL
      if ($connection->query("CREATE TABLE " . $table_name . " (" . str_replace(",", " TEXT NOT NULL,", $columns_str) . " TEXT NOT NULL)")) {
        // Перебираем строки листа Excel
        for ($row = $columns_name_line + 1; $row <= $worksheet->getHighestRow(); $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 " . $table_name . " (" . $columns_str . ") VALUES (" . $value_str . ")");
        }

        // Если всё хорошо, возвращаем true, иначе false
        return true;
      } return false;
    } return false;
  } return false;
}

// Соединение с базой MySQL
$connection = new mysqli("localhost", "user", "pass", "base");
// Выбираем кодировку UTF-8
$connection->set_charset("utf8");

// Загружаем файл Excel
$PHPExcel_file = PHPExcel_IOFactory::load("./file.xlsx");

// Преобразуем первый лист Excel в таблицу MySQL
$PHPExcel_file->setActiveSheetIndex(0);
echo excel2mysql($PHPExcel_file->getActiveSheet(), $connection, "excel2mysql0", 1) ? "OKn" : "FAILn";

// Перебираем все листы Excel и преобразуем в таблицу MySQL
foreach ($PHPExcel_file->getWorksheetIterator() as $index => $worksheet) {
  echo excel2mysql($worksheet, $connection, "excel2mysql" . ($index != 0 ? $index : ""), 1) ? "OKn" : "FAILn";
}

Заключение

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

P.S.

Это моя первая, и думаю, не последняя статья. Поэтому жду ваших советов и поправок, как тут принято, в комментариях.

Автор: BOOMER_74

Источник