<MyRusakov.ru />

Написание лайфхаков на Python

Написание лайфхаков на Python

Данный курс научит Вас автоматизировать самые разные задачи на языке Python, благодаря чему Вы сэкономите на рутине тысячи и тысячи часов своей жизни. Курс состоит из 20 примеров в различных областях: файлы и директории, медиа-файлы, Интернет, Web-мастеринг, Unreal Engine. Каждый пример упрощает ту или иную задачу в своей области, при этом каждый из них создаётся прямо на Ваших глазах и тщательно комментируется.

Помимо самих уроков Вы так же получаете и множество упражнений. По сути, Вы создадите свои собственные 20 небольших проектов, благодаря чему у Вас появится и практика, и портфолио.

Подробнее
Подписка

Подпишитесь на мой канал на YouTube, где я регулярно публикую новые видео.

YouTube Подписаться

Подписавшись по E-mail, Вы будете получать уведомления о новых статьях.

Подписка Подписаться

Добавляйтесь ко мне в друзья ВКонтакте! Отзывы о сайте и обо мне оставляйте в моей группе.

Мой аккаунт Мой аккаунт Моя группа
Опрос

Зачем Вы изучаете программирование/создание сайтов?

Загрузка Excel-файла в базу данных MySQL с помощью PHP

Загрузка 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 тыс. строк) вы получите содержимое Вашего прайса в базе данных. Конечно это демонстрационный скрипт: в нем нет обработки исключений и возможных ошибок, а также окончательная версия Вашего скрипта может существенно отличаться от приведенной здесь. Это зависит от объема и сложности конкретного прайс-листа.

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления

Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.

Порекомендуйте эту статью друзьям:

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

  1. Кнопка:

    Она выглядит вот так: Как создать свой сайт

  2. Текстовая ссылка:

    Она выглядит вот так: Как создать свой сайт

  3. BB-код ссылки для форумов (например, можете поставить её в подписи):

Комментарии (0):

Для добавления комментариев надо войти в систему.
Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.