Загрузка Excel-файла в базу данных MySQL с помощью PHP
Доброго времени суток! В прошлой статье я рассказывал Вам о библиотеке PHPSpreadsheet. Там мы рассмотрели пример записи данных в Excel файл. В данной же статье мы прочитаем с Вами Excel файл и загрузим строки из него в базу данных MySQL.
Зачем это может понадобиться? Одной из самых часто встречающихся задач при работе с интернет-магазинами является загрузка больших прайс-листов в базу данных. Делать это вручную, очевидно, не хочется, да и не стоит, так как увеличивается шанс ошибиться при вводе однотипных данных. А скрипт, который я покажу Вам далее справится с этой задачей достаточно просто - в конечном счете все будет упираться в структуру Вашего Excel файла.
Устанавливаем библиотеку PHPSpreadsheet:
C:/> composer require phpoffice/phpspreadsheet
Функция, которая загружает данные в базу:
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Shared\Date as PHPSpreadsheetDate;
/**
* @param Spreadsheet $spreadsheet - Excel-книга с данными
* @param PDO $pdo - PDO-подключение к базе данных
* @param bool $limit_execution - ограничивает количество строк экспортируемых в базу (для тестирования)
* @throws \PhpOffice\PhpSpreadsheet\Exception
*/
function excel2db(Spreadsheet $spreadsheet, PDO $pdo, $limit_execution = true)
{
// получает названия листов книги в виде массива
$sheetNames = $spreadsheet->getSheetNames();
// возвращает количество листов в книге
$sheetsCount = $spreadsheet->getSheetCount();
// проходимся по каждому листу
for ($c = 0; $c < $sheetsCount; $c++)
{
// ссылка на лист
$sheet = $spreadsheet->getSheet($c);
// последняя строка в листе
$highestRow = $sheet->getHighestRow('A');
print "Количество строк в книге #$sheetNames[$c] составляет $highestRow" . PHP_EOL;
// SQL-запрос на вставку данных в базу
$sql = "INSERT INTO products (
category, subcategory, name, price, producer, quantity, produced_at
)
VALUES (:category, :subcategory, :name, :price, :producer, :quantity, :produced_at)";
// подготовленное SQL-выражение
$stmt = $pdo->prepare($sql);
// проходимся по каждой строке в листе
// счетчик начинается с 2-ой строки, так как первая строка - это заголовок
for ($i = 2; $i < $highestRow + 1; $i++)
{
// для тестирования - заполняет только 30 строк, чтобы посмотреть, что все в порядке
if($limit_execution) {
if($i == 30) break;
}
// получаем значения из ячеек столбцов
$category = $sheet->getCell('A' . $i)->getValue();
$subcategory = $sheet->getCell('B' . $i)->getValue();
$name = $sheet->getCell('C' . $i)->getValue();
$price = $sheet->getCell('D' . $i)->getValue();
$producer = $sheet->getCell('E' . $i)->getValue();
$quantity = $sheet->getCell('G' . $i)->getValue();
// преобразуем дату из формата Excel в формат PHP
$produced_at = PHPSpreadsheetDate::excelToDateTimeObject($sheet->getCell('F' . $i)->getValue());
$produced_at = $produced_at->format('Y-m-d');
$stmt->bindParam(':category', $category);
$stmt->bindParam(':subcategory', $subcategory);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':price', $price);
$stmt->bindParam(':producer', $producer);
$stmt->bindParam(':quantity', $quantity);
$stmt->bindParam(':produced_at', $produced_at);
$res = $stmt->execute();
// если запрос на вставку выполнился успешно, выводим в консоль сообщение
if($res) {
print "Строка #$i из листа $sheetNames[$c] помещена в базу" . PHP_EOL;
}
}
}
}
Файл, в котором будет вызываться функция:
<?php
require __DIR__ . '/../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
$host = '127.0.0.1';
$db = 'products_db';
$user = 'user1';
$pass = 'userpass';
$charset = 'utf8';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opts= [
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => false,
];
// подключение к базе
$pdo = new PDO($dsn, $user, $pass, $opts);
// класс, который читает файл прайса
$reader = new Xlsx();
// получаем Excel-книгу
$spreadsheet = $reader->load('products_db_01012020.xlsx');
// замеряем время работы скрипта
$startTime = microtime(true);
// запускаем экспорт данных
excel2db($spreadsheet, $pdo, false);
$elapsedTime = round(microtime(true) - $startTime, 4);
print "Скрипт выполнился за: $elapsedTime с.";
Таким образом, после запуска данного скрипта через некоторое время (от ~15 минут при 10 тыс. строк) вы получите содержимое Вашего прайса в базе данных. Конечно это демонстрационный скрипт: в нем нет обработки исключений и возможных ошибок, а также окончательная версия Вашего скрипта может существенно отличаться от приведенной здесь. Это зависит от объема и сложности конкретного прайс-листа.
-
Создано 08.06.2021 08:53:55
-
Михаил Русаков
Комментарии (0):
Для добавления комментариев надо войти в систему.
Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.