Введение
Здравствуй, $habrauser!
Бывает так, что вам нужно импортировать файл Excel в базу MySQL, но готового решения нигде нет. Вот и я, когда меня попросил друг поискать легкий способ импорта, сперва решил
Начало
Итак, библиотеку я нашел, скачал и начал разбираться. Для начала нужно было подключить библиотеку и создать подключение к базе, что совсем не сложно:
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);
Далее удаляем таблицу из базы, если она существовала, и создаем новую:
$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 . ")"); }
Все дело в функцию!
Конечно, данный скрипт был бы гораздо удобнее, если бы все объединить в функцию. Поэтому итоговый результат получается такой:
Функция excel2mysql
// Подключаем библиотеку require_once "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 $rows_count = $worksheet->getHighestRow(); // Перебираем строки ��иста Excel for ($row = $columns_name_line + 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 " . $table_name . " (" . $columns_str . ") VALUES (" . $value_str . ")"); } } else { return false; } } else { return false; } } else { return false; } return true; } // Соединение с базой 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) ? "OK\n" : "FAIL\n"; // Перебираем все листы Excel и преобразуем в таблицу MySQL foreach ($PHPExcel_file->getWorksheetIterator() as $index => $worksheet) { echo excel2mysql($worksheet, $connection, "excel2mysql" . ($index != 0 ? $index : ""), 1) ? "OK\n" : "FAIL\n"; }
Заключение
Что ж, надеюсь данная статья поможет вам. Ну, или, если вы захотите
P.S.
Это моя первая, и думаю, не последняя статья. Поэтому жду ваших советов и поправок, как тут принято, в комментариях.
Update
Вижу, все-таки, мне удалось создать небольшую дискуссию, но не все понимают, почему было сделано именно так. Постараюсь объяснить.
Во-первых: с этим должен был работать пожилой человек, которому будет трудновато объяснить как сохранить файл в CSV, при этом не потеряв данные (а такое исключать нельзя, к тому же у них свой формат на файл XLS, который приходит сверху) и, тем более, как это импортировать через phpMyAdmin (который, кстати, с версии 3.4.5 не поддерживает XLS/XLSX, советую посмотреть почему) или подобное. Так что это не подходит.
Во-вторых: все это должно быть расположено на хостинге, и установка модулей как на сервер, так и для локальных программ не подходит (к тому же там Linux, а не Windows, как некоторые подумали).
В-третьих: это дело проводится раз в полгода, однако от безделья я решил написать такую функцию, способную обобщить импорт (вдруг кому нужно).
Теперь о хорошем: переписал данную функцию в класс, исправил кое-что и добавил возможность экспорта из MySQL в Excel. Забрать можно отсюда.
Извините, что не ответил в комментариях, решил что в самой статье будет уместние.
