Excel загрузить php mysql

Загрузка данных из 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

Похожие записи

Источник

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

Доброго времени суток! В прошлой статье я рассказывал Вам о библиотеке PHPSpreadsheet. Там мы рассмотрели пример записи данных в Excel файл. В данной же статье мы прочитаем с Вами Excel файл и загрузим строки из него в базу данных MySQL.

Зачем это может понадобиться? Одной из самых часто встречающихся задач при работе с интернет-магазинами является загрузка больших прайс-листов в базу данных. Делать это вручную, очевидно, не хочется, да и не стоит, так как увеличивается шанс ошибиться при вводе однотипных данных. А скрипт, который я покажу Вам далее справится с этой задачей достаточно просто — в конечном счете все будет упираться в структуру Вашего Excel файла.

Устанавливаем библиотеку PHPSpreadsheet:

Функция, которая загружает данные в базу:

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());

$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;
>
>
>
>

Файл, в котором будет вызываться функция:

require __DIR__ . ‘/../vendor/autoload.php’;

$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

Михаил Русаков

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

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

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

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

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

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

1. Кнопка:
   Она выглядит вот так:
2. Текстовая ссылка:
   Она выглядит вот так: Как создать свой сайт
3. BB-код ссылки для форумов (например, можете поставить её в подписи):

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

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

Copyright © 2010-2023 Русаков Михаил Юрьевич. Все права защищены.

Источник

3 Steps To Import Excel Spreadsheet Into MySQL With PHP

Welcome to a tutorial on how to import an Excel Spreadsheet into a MySQL database with PHP. So you have a project that requires reading some data from a spreadsheet and save them into the database? Sadly, PHP cannot read Excel files natively.

In order to import Excel files into the database:

1. We have to use a third-party library that can read Excel files. PHPSpreadsheet is a good recommendation, and we can get it easily using Composer – composer require phpoffice/phpspreadsheet
2. Create the database table, and Excel file to import.
3. Use PHPSpreadsheet to read the Excel spreadsheet, extract the data, and insert them into the database.

Just how exactly is this done? Let us walk through an example in this guide – Read on!

TLDR – QUICK SLIDES

TABLE OF CONTENTS

IMPORT EXCEL INTO MYSQL

All right, let us now get into an example of importing an Excel file into the database.

STEP 1) INSTALL PHPSPREADSHEET

• The easiest way is to use an application manager called Composer – Something like Git, quite a useful one for pulling libraries automatically. A small hassle to download and install, but a one-time effort nonetheless.
• After installing Composer, open the command prompt and navigate to your project folder – cd HTTP/FOLDER/ .
• Run composer require phpoffice/phpspreadsheet .

That’s all. Composer will automatically pull the latest version into the vendor/ folder.

STEP 2) DUMMY DATABASE TABLE & EXCEL FILE

2A) DUMMY USERS TABLE

CREATE TABLE `users` ( `id` bigint(20) NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email` (`email`); ALTER TABLE `users` MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

2B) DUMMY EXCEL FILE

Next, we have some dummy users in an Excel file that need to be imported into the above table.

STEP 3) PHP IMPORT SCRIPT

 PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); // (B) PHPSPREADSHEET TO LOAD EXCEL FILE require "vendor/autoload.php"; $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx(); $spreadsheet = $reader->load("2b-dummy.xlsx"); $worksheet = $spreadsheet->getActiveSheet(); // (C) READ DATA + IMPORT $sql = "INSERT INTO `users` (`name`, `email`) VALUES (. )"; foreach ($worksheet->getRowIterator() as $row) < // (C1) FETCH DATA FROM WORKSHEET $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(false); $data = []; foreach ($cellIterator as $cell) < $data[] = $cell->getValue(); > // (C2) INSERT INTO DATABASE print_r($data); try < $stmt = $pdo->prepare($sql); $stmt->execute($data); echo "OK - USER ID - lastInsertId()>
"; > catch (Exception $ex) < echo $ex->getMessage() . "
"; > $stmt = null; > // (D) CLOSE DATABASE CONNECTION if ($stmt !== null) < $stmt = null; >if ($pdo !== null)

Yep, this one looks a little intimidating at first but is actually very straightforward.

1. Connect to the database. Remember to change the settings to your own.
2. Load the PHPSpreadsheet library, and use it to open the dummy Excel spreadsheet.
3. Read the cells of the spreadsheet, and import them into the database.
4. The end. Close the database connection.

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

SUPPORT

600+ free tutorials & projects on Code Boxx and still growing. I insist on not turning Code Boxx into a «paid scripts and courses» business, so every little bit of support helps.

EXAMPLE CODE DOWNLOAD

Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

EXTRA BITS & LINKS

That’s all for this tutorial, and here is a small section on some extras that may be useful to you.

SPREADSHEETS WITH FORMULA

Take note that PHPSpreadSheet will not evaluate the cell formula. Do your own “convert formulas to values” in Excel, and save it as a different spreadsheet before importing.

DATE & TIME CELLS

This is yet another gotcha… Basically, convert the date/time cell into a string or text first.

LINKS & REFERENCES

• Need to do the opposite of exporting into Excel files? – Here’s how.
• Check out the PHPSpreadsheet GitHub Page here.
• Also, the official documentation.

YOUTUBE TUTORIAL

INFOGRAPHIC CHEAT SHEET

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Источник