Из Excel в MySQL. Небольшая функция на PHP (fixed)
Бывает так, что вам нужно импортировать файл Excel в базу MySQL, но готового решения нигде нет. Вот и я, когда меня попросил друг поискать легкий способ импорта, сперва решил загуглить поискать решение. Увы, запрос php excel to mysql не дал ничего конкретного, или же описанные способы были довольно таки не удобны. Тогда же я решил найти библиотеку для работы с Excel на PHP, и мне попалась PHPExcel. Но опять же меня ждало разочарование, запрос phpexcel to mysql не дал ничего путного (я ленивый пользователь и дальше 1й страницы не хожу). В итоге я решил создать свой велосипед скрипт, которым и хочу поделиться с вами.
Начало
Итак, библиотеку я нашел, скачал и начал разбираться. Для начала нужно было подключить библиотеку и создать подключение к базе, что совсем не сложно:
require_once "PHPExcel.php"; $connection = new mysqli("localhost", "user", "pass", "base"); $connection->set_charset("utf8"); $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 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 "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 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) Заключение
Что ж, надеюсь данная статья поможет вам. Ну, или, если вы захотите изобрести свой велосипед, но только с моторчиком написать свой скрипт, эта статья поможет вам начать.
P.S.
Это моя первая, и думаю, не последняя статья. Поэтому жду ваших советов и поправок, как тут принято, в комментариях.
Update
Вижу, все-таки, мне удалось создать небольшую дискуссию, но не все понимают, почему было сделано именно так. Постараюсь объяснить.
Во-первых: с этим должен был работать пожилой человек, которому будет трудновато объяснить как сохранить файл в CSV, при этом не потеряв данные (а такое исключать нельзя, к тому же у них свой формат на файл XLS, который приходит сверху) и, тем более, как это импортировать через phpMyAdmin (который, кстати, с версии 3.4.5 не поддерживает XLS/XLSX, советую посмотреть почему) или подобное. Так что это не подходит.
Во-вторых: все это должно быть расположено на хостинге, и установка модулей как на сервер, так и для локальных программ не подходит (к тому же там Linux, а не Windows, как некоторые подумали).
В-третьих: это дело проводится раз в полгода, однако от безделья я решил написать такую функцию, способную обобщить импорт (вдруг кому нужно).
Теперь о хорошем: переписал данную функцию в класс, исправил кое-что и добавил возможность экспорта из MySQL в Excel. Забрать можно отсюда.
Извините, что не ответил в комментариях, решил что в самой статье будет уместние.
Загрузка данных из Excel в MySQL с помощью PHP
Не так давно появилась задача – загрузить данные из Ecxel файла в базу сайта, и реализовать механизм так, чтобы быстро можно было обновлять эти данные, т.е. загружать новый файл. Решил попробовать парсер Excel-файлов phpExcelReader, но сколько не бился с ним, получал ошибку типа “… is not readable”. Поэтому решил использовать библиотеку PHPExcel.
Загружать будем прайс-лист:
| Артикул | Наименование | Количество | Цена | Валюта | Единица |
| 123456 | Апельсин | 20 | 50000 | руб | килограмм |
| 123457 | Мандарин | 20 | 54000 | руб | килограмм |
| 123458 | Яблоко | 50 | 23500 | руб | килограмм |
| id | article | name | quantity | price | currency | unit |
Скачав архив с библиотекой и открыв его вы увидите несколько папок и файлов – это описание и примеры использования библиотеки. Нам понадобится папка “Classes” – распаковываем архив и загружаем её, например, в корень сайта.
Итак, предположим, что форму загрузки мы сделали, библиотеку подключили, начнем писать скрипт обработки файла и загрузки в базу данных.
include 'db_conn.php'; // подключаемся к базе данных $loadfile = $_POST['file_name']; // получаем имя загруженного файла require_once $_SERVER['DOCUMENT_ROOT']."/Classes/PHPExcel/IOFactory.php"; // подключаем класс для доступа к файлу $objPHPExcel = PHPExcel_IOFactory::load($_SERVER['DOCUMENT_ROOT']."/uploads/".$loadfile); foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) // цикл обходит страницы файла < $highestRow = $worksheet->getHighestRow(); // получаем количество строк $highestColumn = $worksheet->getHighestColumn(); // а так можно получить количество колонок for ($row = 1; $row getCellByColumnAndRow(0, $row); //артикул $cell2 = $worksheet->getCellByColumnAndRow(1, $row); //наименование $cell3 = $worksheet->getCellByColumnAndRow(2, $row); //количество $cell4 = $worksheet->getCellByColumnAndRow(3, $row); //цена $cell5 = $worksheet->getCellByColumnAndRow(4, $row); //валюта $cell6 = $worksheet->getCellByColumnAndRow(5, $row); //единица измерения $sql = "INSERT INTO `price` (`article`,`name`,`quantity`,`price`,`currency`,`unit`) VALUES ('$cell1','$cell2','$cell3','$cell4','$cell5','$cell6')"; $query = mysql_query($sql) or die('Ошибка чтения записи: '.mysql_error()); > > В принципе, второй цикл можно усовершенствовать, используя количество колонок, но у меня файл всегда будет стандартный, поэтому не вижу в этом смысла.
На CyberForum.ru нашел перевод документации к PHPExcel, который сделал SrgKord скачать можно тут: Документация разработчика PHPExcel (RUS).7z