MySQL
1656743280
Как экспортировать базу данных MySQL в формат CSV и Excel в Laravel 8
В Laravel вам не нужно писать длинный код для экспорта ваших данных, для этого уже есть пакет — Laravel Excel.
Это позволяет экспортировать данные в различные форматы, такие как XLSX, CSV, XLS, HTML и т. д.
В этом руководстве я покажу, как вы можете экспортировать данные базы данных MySQL в формат CSV и Excel, используя пакет Laravel Excel в Laravel 8.
1. Установить пакет
Требование –
- PHP:
^7.2\|^8.0 - Ларавель:
^5.8 - Таблица Php:
^1.21 - PSR/простой кэш:
^1.0 - Расширение PHP
php_zipвключено - Расширение PHP
php_xmlвключено - Расширение PHP
php_gd2включено - Расширение PHP
php_iconvвключено - Расширение PHP
php_simplexmlвключено - Расширение PHP
php_xmlreaderвключено - Расширение PHP
php_zlibвключено
Установите пакет с помощью композитора —
composer require maatwebsite/excelЕсли вы получаете сообщение об ошибке при выполнении вышеуказанной команды, выполните следующую команду:
composer require psr/simple-cache:^1.0 maatwebsite/excelПосле этого снова выполнить –
composer require maatwebsite/excel2. Обновите app.php
- Откройте
config/app.phpфайл. - Добавьте следующее
Maatwebsite\Excel\ExcelServiceProvider::classв'providers'-
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Добавьте следующее
'Excel' => Maatwebsite\Excel\Facades\Excel::classв'aliases'-
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Опубликовать пакет
Выполните команду -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configЭто создаст новый excel.phpфайл в формате config/.
4. Конфигурация базы данных
Откройте .envфайл.
Укажите хост, имя базы данных, имя пользователя и пароль.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Создать таблицу
- Создайте новую таблицу
Employeesс помощью миграции и добавьте несколько записей.
php artisan make:migration create_employees_table- Теперь перейдите в
database/migrations/папку из корня проекта. - Найдите файл PHP, оканчивающийся на ,
create_employees_tableи откройте его. - Определите структуру таблицы в
up()методе.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Запустите миграцию –
php artisan migrate- Таблица создана и добавлены в нее записи.
6. Модель
- Создать
Employeesмодель.
php artisan make:model Employees- Откройте
app/Models/Employees.phpфайл. - Укажите массово назначаемые атрибуты модели — имя пользователя, имя, адрес электронной почты и возраст, используя
$fillableсвойство.
Завершенный код
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Создайте класс экспорта
Я создаю 2 класса экспорта только для примера —
1. Класс EmployeesExport –
php artisan make:export EmployeesExport --model=Employees- Откройте
app/Exports/EmployeesExport.phpфайл.
Класс имеет 2 метода —
- collection() — Загрузить экспортные данные. Здесь вы можете либо -
- Вернуть все записи.
- Верните определенные столбцы или измените возвращаемый ответ, что я сделал в следующем классе экспорта.
- заголовки () — указать строку заголовка.
ПРИМЕЧАНИЕ . Удалите методы headers(), если вы не хотите добавлять строку заголовка.
Завершенный код
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 1. Export all data
// return Employees::all();
## 2. Export specific columns
return Employees::select('id','username','name')->get();
}
public function headings(): array
{
return [
'#',
'Username',
'Name'
];
}
}2. Класс EmployeesByAgeExport –
php artisan make:export EmployeesByAgeExport --model=Employees- Откройте
app/Exports/EmployeesByAgeExport.phpфайл. - В этом классе я добавил,
__construct()чтобы разрешить отправку параметра при создании экземпляра класса и использовать его для извлечения данных. - После инициализации
$resultмассива передать$resultдляcollect()возврата -return collect($result); - Упомяните имена строк заголовков в
heading()методе.
Завершенный код
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesByAgeExport implements FromCollection, WithHeadings
{
private $agegreaterthan;
public function __construct($age=0)
{
$this->agegreaterthan = $age;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 3. Conditional export and customize result
$records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();
$result = array();
foreach($records as $record){
$result[] = array(
'id'=>$record->id,
'username' => $record->username,
'name' => $record->name,
'email' => $record->email,
'age' => $record->age,
'status' => 1 // Custom data
);
}
return collect($result);
}
public function headings(): array
{
return [
'#',
'Username',
'Name',
'Email',
'Age',
'Status'
];
}
}8. Маршрут
- Откройте
routes/web.phpфайл. - Определить 4 маршрута –
- / — Загрузить представление индекса.
- employee/exportcsv — Экспорт данных в формате CSV.
- employee/exportexcel — Экспорт данных в формате Excel.
- employee/exportbyagecsv — POST-маршрут для экспорта данных по возрасту.
Завершенный код
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');9. Контроллер
- Создать
EmployeesControllerконтроллер.
php artisan make:controller EmployeesController- Откройте
app/Http/Controllers/EmployeesController.phpфайл. - Импорт и .
EmployeesExport_EmployeesByAgeExportExcel - Создайте 4 метода –
- index() — Загрузить
indexпредставление. - exportCSV() — для вызова экспорта данных
Excel::download().
- index() — Загрузить
Он принимает 2 параметра —
- Экземпляр класса.
- Имя файла с расширением.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);- exportExcel() — используйте тот же код выше и измените расширение файла на .xlsx.
- exportByAgeCSV () — считывает значение возраста POST и передает его с
EmployeesByAgeExport($age)экземпляром.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);Завершенный код
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// CSV Export
public function exportCSV(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
}
// Excel Export
public function exportExcel(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
return Excel::download(new EmployeesExport, $file_name);
}
// Conditional Export (csv)
public function exportByAgeCSV(Request $request){
$age = $request->age;
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);
}
}10. Просмотр
Создать index.blade.php файл в resources/views/ папке.
Создайте 2 элемента привязки –
- Установите
hrefдля{{ route('employees.exportcsv') }}экспорта в CSV. - Установите
hrefдля{{ route('employees.exportexcel') }}экспорта в Excel.
Создайте <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Добавьте элемент ввода для ввода возраста и кнопку отправки.
Завершенный код
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Export data to CSV & Excel format in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a>
<a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>
<form method='post' action="{{ route('employees.exportbyagecsv') }}">
@csrf
<div class="mb-3">
<label for="age" class="form-label">Age</label>
<input type="number" class="form-control" id="age" name="age" value="0">
</div>
<button type="submit" class="btn btn-success">Export</button>
</form>
</div>
</body>
</html>11. Демо
12. Заключение
Используйте конструктор в классе экспорта для обработки переданных параметров и использования его для получения данных.
В этом уроке я упомянул только 2 формата экспорта — CSV и Excel, но доступно больше форматов.
Источник: https://makitweb.com
1656739140
如何在 Laravel 8 中以 CSV 和 Excel 格式導出 MySQL 數據庫數據
在 Laravel 中，您不必編寫長代碼來導出數據，已經有一個可用的包 - Laravel Excel。
它允許以各種格式導出數據，例如 XLSX、CSV、XLS、HTML 等。
在本教程中，我將展示如何使用 Laravel 8 中的 Laravel Excel 包以 CSV 和 Excel 格式導出 MySQL 數據庫數據。
1.安裝包
要求 -
- PHP：
^7.2\|^8.0 - 拉拉維爾：
^5.8 - Php電子表格：
^1.21 - psr/簡單緩存：
^1.0 php_zip啟用PHP 擴展php_xml啟用PHP 擴展php_gd2啟用PHP 擴展php_iconv啟用PHP 擴展php_simplexml啟用PHP 擴展php_xmlreader啟用PHP 擴展php_zlib啟用PHP 擴展
使用 composer 安裝包——
composer require maatwebsite/excel如果您在執行上述命令時遇到錯誤，請執行以下命令 -
composer require psr/simple-cache:^1.0 maatwebsite/excel之後再次執行 -
composer require maatwebsite/excel2.更新app.php
- 打開
config/app.php文件。 - 將以下內容添加
Maatwebsite\Excel\ExcelServiceProvider::class到'providers'–
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- 將以下內容添加
'Excel' => Maatwebsite\Excel\Facades\Excel::class到'aliases'–
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3.發布包
運行命令——
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config這將excel.php在config/.
4. 數據庫配置
打開.env文件。
指定主機、數據庫名稱、用戶名和密碼。
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. 創建表
Employees使用遷移創建一個新表並添加一些記錄。
php artisan make:migration create_employees_tabledatabase/migrations/現在，從項目根目錄導航到文件夾。- 找到一個以 結尾的 PHP 文件
create_employees_table並打開它。 - 在方法中定義表結構
up()。
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- 運行遷移 -
php artisan migrate- 表已創建並在其中添加一些記錄。
6. 型號
- 創建
Employees模型。
php artisan make:model Employees- 打開
app/Models/Employees.php文件。 - 使用該屬性指定可批量分配的模型屬性——用戶名、姓名、電子郵件和年齡
$fillable。
完成的代碼
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. 創建導出類
我創建 2 個導出類只是為了舉例 -
1.EmployeesExport 類——
php artisan make:export EmployeesExport --model=Employees- 打開
app/Exports/EmployeesExport.php文件。
類有 2 種方法——
- collection() –加載導出數據。在這裡，您可以——
- 返回所有記錄。
- 返回特定列或修改我在下一個導出類中所做的返迴響應。
- headings() –指定標題行。
注意 –如果您不想添加標題行，請刪除 headings() 方法。
完成的代碼
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 1. Export all data
// return Employees::all();
## 2. Export specific columns
return Employees::select('id','username','name')->get();
}
public function headings(): array
{
return [
'#',
'Username',
'Name'
];
}
}2.EmployeesByAgeExport 類——
php artisan make:export EmployeesByAgeExport --model=Employees- 打開
app/Exports/EmployeesByAgeExport.php文件。 - 在這個類中，我添加
__construct()了允許在創建類實例時發送參數並使用它來檢索數據。 - 初始化
$resultArray 後傳入$result返回collect()-return collect($result); - 在方法中提及標題行名稱
heading()。
完成的代碼
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesByAgeExport implements FromCollection, WithHeadings
{
private $agegreaterthan;
public function __construct($age=0)
{
$this->agegreaterthan = $age;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 3. Conditional export and customize result
$records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();
$result = array();
foreach($records as $record){
$result[] = array(
'id'=>$record->id,
'username' => $record->username,
'name' => $record->name,
'email' => $record->email,
'age' => $record->age,
'status' => 1 // Custom data
);
}
return collect($result);
}
public function headings(): array
{
return [
'#',
'Username',
'Name',
'Email',
'Age',
'Status'
];
}
}8. 路線
- 打開
routes/web.php文件。 - 定義 4 條路線 –
- / - 加載索引視圖。
- employees/exportcsv –以 CSV 格式導出數據。
- employees/exportexcel –以 Excel 格式導出數據。
- employees/exportbyagecsv –按年齡導出數據的 POST 路由。
完成的代碼
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');9.控制器
- 創建
EmployeesController控制器。
php artisan make:controller EmployeesController- 打開
app/Http/Controllers/EmployeesController.php文件。 - 導入和。
EmployeesExport_EmployeesByAgeExportExcel - 創建 4 種方法 –
- index() -加載
index視圖。 - exportCSV() – 導出數據調用
Excel::download()。
- index() -加載
它需要 2 個參數——
- 類實例。
- 帶擴展名的文件名。
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);- exportExcel() –使用與上述相同的代碼並將文件擴展名更改為 .xlsx。
- exportByAgeCSV() –讀取 POST 年齡值並將其與
EmployeesByAgeExport($age)實例一起傳遞。
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);完成的代碼
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// CSV Export
public function exportCSV(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
}
// Excel Export
public function exportExcel(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
return Excel::download(new EmployeesExport, $file_name);
}
// Conditional Export (csv)
public function exportByAgeCSV(Request $request){
$age = $request->age;
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);
}
}10. 查看
index.blade.php 在 文件夾中創建 文件resources/views/ 。
創建 2 個錨元素 -
- 設置
href為{{ route('employees.exportcsv') }}CSV 導出。 - 設置
href為{{ route('employees.exportexcel') }}Excel 導出。
創建一個<form method='post' action="{{ route('employees.exportbyagecsv') }}">. 添加輸入元素以輸入年齡和提交按鈕。
完成的代碼
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Export data to CSV & Excel format in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a>
<a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>
<form method='post' action="{{ route('employees.exportbyagecsv') }}">
@csrf
<div class="mb-3">
<label for="age" class="form-label">Age</label>
<input type="number" class="form-control" id="age" name="age" value="0">
</div>
<button type="submit" class="btn btn-success">Export</button>
</form>
</div>
</body>
</html>11. 演示
12. 結論
在 Export 類中使用構造函數來處理傳遞的參數並使用它來獲取數據。
在本教程中，我只提到了 2 種導出格式——CSV 和 Excel，但還有更多可用的格式。
1656735060
Exporter La Base De Données MySQL Au format CSV Et Excel Dans Laravel8
Dans Laravel, vous n'avez pas besoin d'écrire un long code pour exporter vos données, il existe déjà un package disponible pour cela - Laravel Excel.
Il permet d'exporter des données dans différents formats tels que - XLSX, CSV, XLS, HTML, etc.
Dans ce didacticiel, je montre comment vous pouvez exporter des données de base de données MySQL au format CSV et Excel à l'aide du package Laravel Excel dans Laravel 8.
1. Installer le package
Exigence -
- PHP :
^7.2\|^8.0 - Laravel :
^5.8 - Feuille de calcul Php :
^1.21 - psr/simple-cache :
^1.0 - Extension PHP
php_zipactivée - Extension PHP
php_xmlactivée - Extension PHP
php_gd2activée - Extension PHP
php_iconvactivée - Extension PHP
php_simplexmlactivée - Extension PHP
php_xmlreaderactivée - Extension PHP
php_zlibactivée
Installez le package à l'aide de composer -
composer require maatwebsite/excelSi vous obtenez une erreur lors de l'exécution de la commande ci-dessus, exécutez la commande ci-dessous -
composer require psr/simple-cache:^1.0 maatwebsite/excelAprès cela, exécutez à nouveau -
composer require maatwebsite/excel2. Mettre à jour app.php
- Ouvrir
config/app.phple fichier. - Ajoutez ce qui suit
Maatwebsite\Excel\ExcelServiceProvider::classdans'providers'-
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Ajoutez ce qui suit
'Excel' => Maatwebsite\Excel\Facades\Excel::classdans'aliases'-
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Publier le package
Exécutez la commande -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configCela créera un nouveau excel.phpfichier au format config/.
4. Configuration de la base de données
Ouvrir .envle fichier.
Spécifiez l'hôte, le nom de la base de données, le nom d'utilisateur et le mot de passe.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Créer un tableau
- Créez une nouvelle table
Employeesà l'aide de la migration et ajoutez des enregistrements.
php artisan make:migration create_employees_table- Maintenant, accédez au
database/migrations/dossier à partir de la racine du projet. - Trouvez un fichier PHP qui se termine par
create_employees_tableet ouvrez-le. - Définissez la structure de la table dans la
up()méthode.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Exécutez la migration –
php artisan migrate- La table est créée et ajoutez-y quelques enregistrements.
6. Modèle
- Créer un
Employeesmodèle.
php artisan make:model Employees- Ouvrir
app/Models/Employees.phple fichier. - Spécifiez les attributs de modèle assignables en masse - nom d'utilisateur, nom, e-mail et âge à l'aide de la
$fillablepropriété.
Code terminé
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Créer une classe d'exportation
Je crée 2 classes d'exportation juste à titre d'exemple -
1. Classe d'exportation des employés -
php artisan make:export EmployeesExport --model=Employees- Ouvrir
app/Exports/EmployeesExport.phple fichier.
La classe a 2 méthodes -
- collection() – Charger les données d'exportation. Ici, vous pouvez soit -
- Renvoie tous les enregistrements.
- Renvoyez des colonnes spécifiques ou modifiez la réponse de retour que j'ai faite dans la prochaine classe d'exportation.
- headers() – Spécifiez la ligne d'en-tête.
REMARQUE - Supprimez les méthodes headers() si vous ne souhaitez pas ajouter de ligne d'en-tête.
Code terminé
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 1. Export all data
// return Employees::all();
## 2. Export specific columns
return Employees::select('id','username','name')->get();
}
public function headings(): array
{
return [
'#',
'Username',
'Name'
];
}
}2. Classe EmployeesByAgeExport –
php artisan make:export EmployeesByAgeExport --model=Employees- Ouvrir
app/Exports/EmployeesByAgeExport.phple fichier. - Dans cette classe, j'ai ajouté
__construct()pour autoriser l'envoi d'un paramètre lors de la création d'une instance de classe et l'utiliser pour récupérer des données. - Après avoir initialisé
$resultArray pass$resultincollect()for return –return collect($result); - Mentionnez les noms des lignes d'en-tête dans la
heading()méthode.
Code terminé
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesByAgeExport implements FromCollection, WithHeadings
{
private $agegreaterthan;
public function __construct($age=0)
{
$this->agegreaterthan = $age;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 3. Conditional export and customize result
$records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();
$result = array();
foreach($records as $record){
$result[] = array(
'id'=>$record->id,
'username' => $record->username,
'name' => $record->name,
'email' => $record->email,
'age' => $record->age,
'status' => 1 // Custom data
);
}
return collect($result);
}
public function headings(): array
{
return [
'#',
'Username',
'Name',
'Email',
'Age',
'Status'
];
}
}8. Itinéraire
- Ouvrir
routes/web.phple fichier. - Définir 4 itinéraires –
- / – Charger la vue d'index.
- employés/exportcsv – Exporter les données au format CSV.
- employés/exportexcel – Exporter les données au format Excel.
- employés/exportbyagecsv – Route POST pour exporter les données par âge.
Code terminé
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');9. Contrôleur
- Créer
EmployeesControllerun contrôleur.
php artisan make:controller EmployeesController- Ouvrir
app/Http/Controllers/EmployeesController.phple fichier. - Importer et .
EmployeesExport_EmployeesByAgeExportExcel - Créez 4 méthodes –
- index() – Charger la
indexvue. - exportCSV() – Pour exporter des données, appelez
Excel::download().
- index() – Charger la
Il faut 2 paramètres -
- Instance de classe.
- Nom de fichier avec extension.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);- exportExcel() - Utilisez le même code ci-dessus et modifiez l'extension de fichier en .xlsx.
- exportByAgeCSV() - Lit la valeur d'âge POST et la transmet avec l'
EmployeesByAgeExport($age)instance.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);Code terminé
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// CSV Export
public function exportCSV(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
}
// Excel Export
public function exportExcel(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
return Excel::download(new EmployeesExport, $file_name);
}
// Conditional Export (csv)
public function exportByAgeCSV(Request $request){
$age = $request->age;
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);
}
}10. Voir
Créer index.blade.php un fichier dans resources/views/ le dossier.
Créez 2 éléments d'ancrage -
- Défini
hrefsur{{ route('employees.exportcsv') }}pour l'exportation CSV. - Défini
hrefsur{{ route('employees.exportexcel') }}pour l'exportation vers Excel.
Créez un <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Ajoutez un élément d'entrée pour entrer l'âge et un bouton d'envoi.
Code terminé
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Export data to CSV & Excel format in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a>
<a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>
<form method='post' action="{{ route('employees.exportbyagecsv') }}">
@csrf
<div class="mb-3">
<label for="age" class="form-label">Age</label>
<input type="number" class="form-control" id="age" name="age" value="0">
</div>
<button type="submit" class="btn btn-success">Export</button>
</form>
</div>
</body>
</html>11. Démo
12.Conclusion
Utilisez le constructeur dans la classe Export pour gérer les paramètres passés et utilisez-le pour récupérer des données.
Dans ce didacticiel, je n'ai mentionné que 2 formats d'exportation - CSV et Excel, mais il existe d'autres formats disponibles.
Source: https://makitweb.com
1656731220
Exportar Una Base De Datos MySQL En Formato CSV Y Excel En Laravel 8
En Laravel, no tiene que escribir un código largo para exportar sus datos, ya hay un paquete disponible para eso: Laravel Excel.
Permite exportar datos en varios formatos como: XLSX, CSV, XLS, HTML, etc.
En este tutorial, muestro cómo puedes exportar datos de bases de datos MySQL en formato CSV y Excel usando el paquete Laravel Excel en Laravel 8.
1. Paquete de instalación
Requisito –
- PHP:
^7.2\|^8.0 - Laravel:
^5.8 - PhpHoja de cálculo:
^1.21 - psr/caché simple:
^1.0 - Extensión PHP
php_ziphabilitada - Extensión PHP
php_xmlhabilitada - Extensión PHP
php_gd2habilitada - Extensión PHP
php_iconvhabilitada - Extensión PHP
php_simplexmlhabilitada - Extensión PHP
php_xmlreaderhabilitada - Extensión PHP
php_zlibhabilitada
Instale el paquete usando composer –
composer require maatwebsite/excelSi recibe un error al ejecutar el comando anterior, ejecute el siguiente comando:
composer require psr/simple-cache:^1.0 maatwebsite/excelDespués de eso nuevamente ejecute –
composer require maatwebsite/excel2. Actualizar aplicación.php
- Abrir
config/app.phparchivo. - Agregue lo siguiente
Maatwebsite\Excel\ExcelServiceProvider::classen'providers'–
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Agregue lo siguiente
'Excel' => Maatwebsite\Excel\Facades\Excel::classen'aliases'–
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Publicar paquete
Ejecute el comando –
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configEsto creará un nuevo excel.phparchivo en formato config/.
4. Configuración de la base de datos
Abrir .envarchivo.
Especifique el host, el nombre de la base de datos, el nombre de usuario y la contraseña.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Crear tabla
- Cree una nueva tabla
Employeesmediante la migración y agregue algunos registros.
php artisan make:migration create_employees_table- Ahora, navegue a
database/migrations/la carpeta desde la raíz del proyecto. - Encuentre un archivo PHP que termine con
create_employees_tabley ábralo. - Defina la estructura de la tabla en el
up()método.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Ejecute la migración –
php artisan migrate- La tabla se ha creado y se agregan algunos registros en ella.
6. Modelo
- Crear
Employeesmodelo.
php artisan make:model Employees- Abrir
app/Models/Employees.phparchivo. - Especifique atributos de modelo asignables en masa: nombre de usuario, nombre, correo electrónico y edad utilizando la
$fillablepropiedad.
Código completado
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Crear clase de exportación
Estoy creando 2 clases de exportación solo con fines de ejemplo:
1. Clase de Exportación de Empleados –
php artisan make:export EmployeesExport --model=Employees- Abrir
app/Exports/EmployeesExport.phparchivo.
La clase tiene 2 métodos:
- colección () – Cargar datos de exportación. Aquí, puedes:
- Devolver todos los registros.
- Devuelva columnas específicas o modifique la respuesta de devolución que hice en la siguiente clase de exportación.
- headers(): especifica la fila del encabezado.
NOTA: elimine los métodos de encabezados () si no desea agregar una fila de encabezado.
Código completado
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 1. Export all data
// return Employees::all();
## 2. Export specific columns
return Employees::select('id','username','name')->get();
}
public function headings(): array
{
return [
'#',
'Username',
'Name'
];
}
}2. Clase de exportación EmployeesByAge:
php artisan make:export EmployeesByAgeExport --model=Employees- Abrir
app/Exports/EmployeesByAgeExport.phparchivo. - En esta clase, agregué
__construct()para permitir el envío de un parámetro al crear una instancia de clase y usarlo para recuperar datos. - Después de inicializar el paso de
$resultmatriz para el retorno:$resultcollect()return collect($result); - Mencione los nombres de las filas de encabezado en el
heading()método.
Código completado
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesByAgeExport implements FromCollection, WithHeadings
{
private $agegreaterthan;
public function __construct($age=0)
{
$this->agegreaterthan = $age;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 3. Conditional export and customize result
$records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();
$result = array();
foreach($records as $record){
$result[] = array(
'id'=>$record->id,
'username' => $record->username,
'name' => $record->name,
'email' => $record->email,
'age' => $record->age,
'status' => 1 // Custom data
);
}
return collect($result);
}
public function headings(): array
{
return [
'#',
'Username',
'Name',
'Email',
'Age',
'Status'
];
}
}8. Ruta
- Abrir
routes/web.phparchivo. - Definir 4 rutas –
- / – Cargar vista de índice.
- empleados/exportcsv: exporta datos en formato CSV.
- empleados/exportexcel – Exportar datos en formato Excel.
- empleados/exportbyagecsv: ruta POST para exportar datos por edad.
Código completado
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');9. Controlador
- Crear
EmployeesControllercontrolador.
php artisan make:controller EmployeesController- Abrir
app/Http/Controllers/EmployeesController.phparchivo. - Importar y .
EmployeesExport_EmployeesByAgeExportExcel - Crear 4 métodos –
- index() – Cargar
indexvista. - exportCSV(): para exportar datos, llame a
Excel::download().
- index() – Cargar
Se necesitan 2 parámetros:
- instancia de clase.
- Nombre de archivo con extensión.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);- exportExcel(): use el mismo código anterior y cambie la extensión del archivo a .xlsx.
- exportByAgeCSV(): lea el valor de edad POST y páselo con la
EmployeesByAgeExport($age)instancia.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);Código completado
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// CSV Export
public function exportCSV(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
}
// Excel Export
public function exportExcel(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
return Excel::download(new EmployeesExport, $file_name);
}
// Conditional Export (csv)
public function exportByAgeCSV(Request $request){
$age = $request->age;
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);
}
}10. Ver
Crear index.blade.php archivo en resources/views/ carpeta.
Crea 2 elementos de anclaje –
- Configúrelo
hrefpara{{ route('employees.exportcsv') }}exportar a CSV. - Configurado
hrefpara{{ route('employees.exportexcel') }}exportar a Excel.
Crea un <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Agregue un elemento de entrada para ingresar la edad y un botón de envío.
Código completado
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Export data to CSV & Excel format in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a>
<a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>
<form method='post' action="{{ route('employees.exportbyagecsv') }}">
@csrf
<div class="mb-3">
<label for="age" class="form-label">Age</label>
<input type="number" class="form-control" id="age" name="age" value="0">
</div>
<button type="submit" class="btn btn-success">Export</button>
</form>
</div>
</body>
</html>11. demostración
12. Conclusión
Use el constructor en la clase Export para manejar los parámetros pasados y utilícelo para obtener datos.
En este tutorial, solo mencioné 2 formatos de exportación: CSV y Excel, pero hay más formatos disponibles.
Fuente: https://makitweb.com
1656727380
Como Exportar O Banco De Dados MySQL No formato CSV E Excel No Laravel
No Laravel, você não precisa escrever código longo para exportar seus dados, já existe um pacote disponível para isso – Laravel Excel.
Permite exportar dados em vários formatos como – XLSX, CSV, XLS, HTML, etc.
Neste tutorial, mostro como você pode exportar dados do banco de dados MySQL no formato CSV e Excel usando o pacote Laravel Excel no Laravel 8.
1. Instale o pacote
Requerimento -
- PHP:
^7.2\|^8.0 - Laravel:
^5.8 - Planilha Php:
^1.21 - psr/cache simples:
^1.0 - Extensão PHP
php_ziphabilitada - Extensão PHP
php_xmlhabilitada - Extensão PHP
php_gd2habilitada - Extensão PHP
php_iconvhabilitada - Extensão PHP
php_simplexmlhabilitada - Extensão PHP
php_xmlreaderhabilitada - Extensão PHP
php_zlibhabilitada
Instale o pacote usando o compositor –
composer require maatwebsite/excelSe você estiver recebendo um erro ao executar o comando acima, execute o comando abaixo –
composer require psr/simple-cache:^1.0 maatwebsite/excelDepois disso, execute novamente –
composer require maatwebsite/excel2. Atualize app.php
- Abrir
config/app.phparquivo. - Adicione o seguinte
Maatwebsite\Excel\ExcelServiceProvider::classem'providers'–
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Adicione o seguinte
'Excel' => Maatwebsite\Excel\Facades\Excel::classem'aliases'–
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Publicar pacote
Execute o comando -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configIsso criará um novo excel.phparquivo em config/.
4. Configuração do banco de dados
Abrir .envarquivo.
Especifique o host, o nome do banco de dados, o nome de usuário e a senha.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Criar Tabela
- Crie uma nova tabela
Employeesusando a migração e adicione alguns registros.
php artisan make:migration create_employees_table- Agora, navegue até a
database/migrations/pasta da raiz do projeto. - Encontre um arquivo PHP que termine com
create_employees_tablee abra-o. - Defina a estrutura da tabela no
up()método.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Execute a migração –
php artisan migrate- A tabela está sendo criada e adicione alguns registros nela.
6. Modelo
- Criar
Employeesmodelo.
php artisan make:model Employees- Abrir
app/Models/Employees.phparquivo. - Especifique atributos de modelo atribuíveis em massa – nome de usuário, nome, email e idade usando a
$fillablepropriedade.
Código concluído
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Criar classe de exportação
Estou criando 2 classes de exportação apenas para fins de exemplo -
1. Classe de Exportação de Funcionários -
php artisan make:export EmployeesExport --model=Employees- Abrir
app/Exports/EmployeesExport.phparquivo.
A classe tem 2 métodos –
- collection() – Carrega dados de exportação. Aqui, você pode –
- Retorna todos os registros.
- Retorne colunas específicas ou modifique a resposta de retorno que fiz na próxima classe Export.
- headers() – Especifique a linha do cabeçalho.
NOTA – Remova os métodos headers() se você não quiser adicionar uma linha de cabeçalho.
Código concluído
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 1. Export all data
// return Employees::all();
## 2. Export specific columns
return Employees::select('id','username','name')->get();
}
public function headings(): array
{
return [
'#',
'Username',
'Name'
];
}
}2. FuncionáriosPor Classe de Exportação de Idade –
php artisan make:export EmployeesByAgeExport --model=Employees- Abrir
app/Exports/EmployeesByAgeExport.phparquivo. - Nesta classe, adicionei
__construct()para permitir o envio de um parâmetro ao criar a instância da classe e usá-lo para recuperar dados. - Após inicializar o
$resultArray passe$resultparacollect()retorno –return collect($result); - Mencione os nomes das linhas de cabeçalho no
heading()método.
Código concluído
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesByAgeExport implements FromCollection, WithHeadings
{
private $agegreaterthan;
public function __construct($age=0)
{
$this->agegreaterthan = $age;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 3. Conditional export and customize result
$records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();
$result = array();
foreach($records as $record){
$result[] = array(
'id'=>$record->id,
'username' => $record->username,
'name' => $record->name,
'email' => $record->email,
'age' => $record->age,
'status' => 1 // Custom data
);
}
return collect($result);
}
public function headings(): array
{
return [
'#',
'Username',
'Name',
'Email',
'Age',
'Status'
];
}
}8. Rota
- Abrir
routes/web.phparquivo. - Defina 4 rotas –
- / – Carregar visualização de índice.
- funcionários/exportcsv – Exporta dados em formato CSV.
- funcionários/exportexcel – Exporta dados em formato Excel.
- funcionários/exportbyagecsv – rota POST para exportar dados por idade.
Código concluído
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');9. Controlador
- Criar
EmployeesControllercontrolador.
php artisan make:controller EmployeesController- Abrir
app/Http/Controllers/EmployeesController.phparquivo. - Importar e .
EmployeesExport_EmployeesByAgeExportExcel - Crie 4 métodos –
- index() – Carregar
indexvisualização. - exportCSV() – Para exportar dados, chame
Excel::download().
- index() – Carregar
Leva 2 parâmetros -
- Instância de classe.
- Nome do arquivo com extensão.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);- exportExcel() – Usando o mesmo código acima e altere a extensão do arquivo para .xlsx.
- exportByAgeCSV() – Leia o valor de idade POST e passe-o com
EmployeesByAgeExport($age)instância.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);Código concluído
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// CSV Export
public function exportCSV(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
}
// Excel Export
public function exportExcel(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
return Excel::download(new EmployeesExport, $file_name);
}
// Conditional Export (csv)
public function exportByAgeCSV(Request $request){
$age = $request->age;
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);
}
}10. Visualizar
Criar index.blade.php arquivo na resources/views/ pasta.
Crie 2 elementos âncora –
- Definido
hrefcomo{{ route('employees.exportcsv') }}para exportação CSV. - Defina
hrefcomo{{ route('employees.exportexcel') }}para exportação do Excel.
Crie um <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Adicione um elemento de entrada para inserir a idade e um botão de envio.
Código concluído
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Export data to CSV & Excel format in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a>
<a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>
<form method='post' action="{{ route('employees.exportbyagecsv') }}">
@csrf
<div class="mb-3">
<label for="age" class="form-label">Age</label>
<input type="number" class="form-control" id="age" name="age" value="0">
</div>
<button type="submit" class="btn btn-success">Export</button>
</form>
</div>
</body>
</html>11. Demonstração
12. Conclusão
Use o construtor na classe Export para manipular os parâmetros passados e use-o para buscar dados.
Neste tutorial, mencionei apenas 2 formatos de exportação – CSV e Excel, mas há mais formatos disponíveis.
Fonte: https://makitweb.com
1656724800
Как импортировать данные CSV и Excel в базу данных MySQL в Laravel 8
Массовый импорт данных — полезная функция для добавления записей, хранящихся в файле, в базу данных. Данные могут храниться в формате CSV, Excel, XML, ODS и т. д.
В этом руководстве я покажу, как вы можете импортировать данные CSV и Excel в базу данных MySQL, используя пакет Laravel Excel в проекте Laravel 8.
1. Установить пакет
Требование –
- PHP:
^7.2\|^8.0 - Ларавель:
^5.8 - Таблица Php:
^1.21 - PSR/простой кэш:
^1.0 - Расширение PHP
php_zipвключено - Расширение PHP
php_xmlвключено - Расширение PHP
php_gd2включено - Расширение PHP
php_iconvвключено - Расширение PHP
php_simplexmlвключено - Расширение PHP
php_xmlreaderвключено - Расширение PHP
php_zlibвключено
Установите пакет с помощью композитора —
composer require maatwebsite/excelЕсли вы получаете сообщение об ошибке при выполнении вышеуказанной команды, выполните следующую команду:
composer require psr/simple-cache:^1.0 maatwebsite/excelПосле этого снова выполнить –
composer require maatwebsite/excel2. Обновите app.php
- Откройте
config/app.phpфайл. - Добавьте следующее
Maatwebsite\Excel\ExcelServiceProvider::classв'providers'-
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Добавьте следующее
'Excel' => Maatwebsite\Excel\Facades\Excel::classв'aliases'-
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Опубликовать пакет
Запустите команду -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configЭто создаст новый excel.phpфайл в формате config/.
4. Конфигурация базы данных
Откройте .envфайл.
Укажите хост, имя базы данных, имя пользователя и пароль.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Создать таблицу
- Создайте новую таблицу
Employeesс помощью миграции и добавьте несколько записей.
php artisan make:migration create_employees_table- Теперь перейдите в
database/migrations/папку из корня проекта. - Найдите файл PHP, оканчивающийся на ,
create_employees_tableи откройте его. - Определите структуру таблицы в
up()методе.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Запустите миграцию —
php artisan migrate- Таблица создана.
6. Модель
- Создать
Employeesмодель.
php artisan make:model Employees- Откройте
app/Models/Employees.phpфайл. - Укажите массово назначаемые атрибуты модели — имя пользователя, имя, адрес электронной почты и возраст, используя
$fillableсвойство.
Завершенный код
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Создайте класс импорта
Я создаю 2 класса импорта только для примера —
1. Класс EmployeesImport —
php artisan make:import EmployeesImport --model=Employees- Откройте
app/Imports/EmployeesImport.phpфайл. - Класс имеет 1 метод —
- model () — считывает значения из
$rowмассива и вставляет запись, если идентификатор электронной почты не существует вemployeesтаблице, в противном случае возвращает значение null.
- model () — считывает значения из
ПРИМЕЧАНИЕ. – Этот класс импорта начинает чтение записей с 1-й строки. Я объяснил, как пропустить 1-ю строку, содержащую заголовок, в следующем классе импорта.
CSV-файл без строки заголовка –
| пользователь1 | Пользователь1 и1 | user1@makitweb.com | 28 |
| пользователь2 | Пользователь2 и2 | user2@makitweb.com | 24 |
Завершенный код
<?php
namespace App\Imports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;
class EmployeesImport implements ToModel {
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row) {
// Check email already exists
$count = Employees::where('email',$row[2])->count();
if($count > 0){
return null;
}
return new Employees([
'username' => $row[0],
'name' => $row[1],
'email' => $row[2],
'age' => $row[3],
]);
}
}2. Класс Employees2Import –
php artisan make:import Employees2Import --model=Employees- Откройте
app/Imports/Employees2Import.phpфайл. - Класс имеет 2 метода –
- collection() — проверка
$rowsданных массива. В случае успешной проверки вставьте запись, если идентификатор электронной почты не существует вemployeesтаблице, в противном случае верните ошибку.
- collection() — проверка
ПРИМЕЧАНИЕ. – Если
WithHeadingRowреализовано, то$rowsмассивы содержат заголовки строк в качестве имен ключей вместо индекса.
- headingRow() — возвращает позицию индекса заголовка.
CSV-файл со строкой заголовка –
| Имя пользователя | Имя | Эл. адрес | Возраст |
|---|---|---|---|
| ыссёгеш | Йогеш Сингх | yogesh@makitweb.com | 28 |
| бсонарика | Сонарика Бхадория | bsonarika@makitweb.com | 28 |
| вышал | Вишал Саху | vishal@makitweb.com | тридцать первый |
Завершенный код
<?php
namespace App\Imports;
use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;
class Employees2Import implements ToCollection ,WithHeadingRow
{
public function collection(Collection $rows){
// Validate
Validator::make($rows->toArray(), [
'*.username' => 'required|string',
'*.name' => 'required|string',
'*.email' => 'required|email',
'*.age' => 'required|integer',
],[
'*.username.required'=> "The username field is required.",
'*.username.string'=> "The username must be string.",
'*.name.required'=> "The name field is required.",
'*.name.string'=> "The name must be string.",
'*.email.required'=> "The email field is required.",
'*.email.email'=> "The email must be a valid email address.",
'*.age.integer'=> "The age must be an integer."
])->validate();
foreach ($rows as $row) {
// Check email already exists
$count = Employees::where('email',$row['email'])->count();
if($count > 0){
continue;
}
Employees::create([
'username' => $row['username'],
'name' => $row['name'],
'email' => $row['email'],
'age' => $row['age'],
]);
}
}
// Specify header row index position to skip
public function headingRow(): int {
return 1;
}
}8. Маршрут
- Откройте
routes/web.phpфайл. - Определить 3 маршрута –
- / — Загрузить представление индекса.
- employee/importdata — опубликовать маршрут для импорта данных.
- employee/validateandimportdata — отправка маршрута для проверки и импорта данных.
Завершенный код
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');9. Контроллер
- Создать
EmployeesControllerконтроллер.
php artisan make:controller EmployeesController- Откройте
app/Http/Controllers/EmployeesController.phpфайл. - Импорт и .
EmployeesImport_Employees2ImportExcel - Создайте 3 метода –
- index() — Загрузить
indexпредставление. - importdata() — импортировать вызов
Excel::import().
- index() — Загрузить
Передать 2 параметра –
EmployeesImportЭкземпляр класса.- Передать местоположение выбранного файла 'temp'.
- validateAndImportdata() — снова вызов
Excel::import().
- validateAndImportdata() — снова вызов
Передать 2 параметра –
Employees2ImportЭкземпляр класса.- Передайте сохраненное местоположение файла (я сохранил файл в
public/employees.xlsxпапке).
Завершенный код
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// Import data
public function importdata(Request $request){
Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
return back()->with('success', 'Import successfully!');
}
// Validate and Import data
public function validateAndImportdata(Request $request){
Excel::import(new Employees2Import, "employees.xlsx");
return back()->with('success', 'Import successfully!');
}
}10. Просмотр
Создать index.blade.php файл в resources/views/ папке.
Создать 2 <form>–
- В 1-м
<form >наборе действий{{ route('employees.importdata') }}. Создайте элемент файла и кнопку отправки. - Во 2-м
<form>установите действие на{{ route('employees.validateandimportdata') }}. Создайте кнопку отправки.
Цикл $errors->all()для отображения ошибок.
Завершенный код
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Import data into MySQL database in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<!-- Success message -->
@if(Session::has('success'))
<div class="alert alert-success">
{{ Session::get('success') }}
</div>
@endif
<form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
@csrf
<div class="mb-3">
<label for="file" class="form-label">File</label>
<input type="file" class="form-control" id="file" name="file" value="">
</div>
<button type="submit" class="btn btn-success">Import</button>
</form>
<!-- Import data with validation -->
<h2 class='mt-5'>Validate and import data</h2>
{{-- Display errors --}}
@if (count($errors) > 0)
<div class="row">
<div class="col-md-12 ">
<div class="alert alert-danger">
<ul>
@foreach($errors->all() as $error)
<li>{{ $error }} </li>
@endforeach
</ul>
</div>
</div>
</div>
@endif
<form method='post' action="{{ route('employees.validateandimportdata') }}" >
@csrf
<button type="submit" class="btn btn-success">Import</button>
</form>
</div>
</body>
</html>11. Выход
12. Заключение
Обязательно проверьте запись перед вставкой или обновлением записей в классе Import. Укажите WithHeadingRowв классе импорта, только если файл импорта имеет строку заголовка.
Источник: https://makitweb.com
1656718320
Cách Xuất Cơ Sở Dữ Liệu MySQL Ở Định Dạng CSV & Excel Trong Laravel 8
Trong Laravel, bạn không cần phải viết mã dài để xuất dữ liệu của mình, đã có một gói có sẵn cho việc đó - Laravel Excel.
Nó cho phép xuất dữ liệu ở nhiều định dạng khác nhau như - XLSX, CSV, XLS, HTML, v.v.
Trong hướng dẫn này, tôi chỉ cách bạn có thể xuất dữ liệu cơ sở dữ liệu MySQL ở định dạng CSV & Excel bằng cách sử dụng gói Laravel Excel trong Laravel 8.
1. Cài đặt gói
Yêu cầu -
- PHP:
^7.2\|^8.0 - Laravel:
^5.8 - PhpS Spreadsheet:
^1.21 - psr / simple-cache:
^1.0 php_zipĐã bật tiện ích mở rộng PHPphp_xmlĐã bật tiện ích mở rộng PHPphp_gd2Đã bật tiện ích mở rộng PHPphp_iconvĐã bật tiện ích mở rộng PHPphp_simplexmlĐã bật tiện ích mở rộng PHPphp_xmlreaderĐã bật tiện ích mở rộng PHPphp_zlibĐã bật tiện ích mở rộng PHP
Cài đặt gói bằng trình soạn nhạc -
composer require maatwebsite/excelNếu bạn gặp lỗi khi thực hiện lệnh trên, hãy thực hiện lệnh dưới đây:
composer require psr/simple-cache:^1.0 maatwebsite/excelSau đó, một lần nữa thực thi -
composer require maatwebsite/excel2. Cập nhật app.php
- Mở
config/app.phptệp. - Thêm phần sau
Maatwebsite\Excel\ExcelServiceProvider::classvào'providers'-
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Thêm phần sau
'Excel' => Maatwebsite\Excel\Facades\Excel::classvào'aliases'-
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Xuất bản gói
Chạy lệnh -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configĐiều này sẽ tạo một excel.phptệp mới trong config/.
4. Cấu hình cơ sở dữ liệu
Mở .envtệp.
Chỉ định máy chủ, tên cơ sở dữ liệu, tên người dùng và mật khẩu.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Tạo bảng
- Tạo một bảng mới
Employeesbằng cách sử dụng di chuyển và thêm một số bản ghi.
php artisan make:migration create_employees_table- Bây giờ, điều hướng đến
database/migrations/thư mục từ thư mục gốc của dự án. - Tìm một tệp PHP có kết thúc bằng
create_employees_tablevà mở nó. - Xác định cấu trúc bảng trong
up()phương thức.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Chạy quá trình di chuyển -
php artisan migrate- Bảng được tạo và thêm một số bản ghi trong đó.
6. Mô hình
- Tạo
Employeesmô hình.
php artisan make:model Employees- Mở
app/Models/Employees.phptệp. - Chỉ định các thuộc tính Model có thể gán hàng loạt - tên người dùng, tên, email và tuổi bằng cách sử dụng thuộc
$fillabletính.
Mã đã hoàn thành
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Tạo lớp Xuất
Tôi đang tạo 2 lớp xuất chỉ cho mục đích ví dụ -
1. Lớp Nhân viên Xuất khẩu -
php artisan make:export EmployeesExport --model=Employees- Mở
app/Exports/EmployeesExport.phptệp.
Lớp có 2 phương thức -
- collection () - Tải dữ liệu xuất. Ở đây, bạn có thể -
- Trả lại tất cả hồ sơ.
- Trả lại các cột cụ thể hoặc sửa đổi phản hồi trả lại mà tôi đã thực hiện trong lớp Xuất tiếp theo.
- headings () - Chỉ định hàng tiêu đề.
LƯU Ý - Loại bỏ các phương thức headings () nếu bạn không muốn thêm một hàng tiêu đề.
Mã đã hoàn thành
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 1. Export all data
// return Employees::all();
## 2. Export specific columns
return Employees::select('id','username','name')->get();
}
public function headings(): array
{
return [
'#',
'Username',
'Name'
];
}
}2. Lớp Nhân viênByAgeExport -
php artisan make:export EmployeesByAgeExport --model=Employees- Mở
app/Exports/EmployeesByAgeExport.phptệp. - Trong lớp này, tôi đã thêm
__construct()để cho phép gửi một tham số trong khi tạo cá thể lớp và sử dụng nó để truy xuất dữ liệu. - Sau khi khởi tạo
$resultArray , hãy trả$resultvềcollect()-return collect($result); - Đề cập đến tên hàng tiêu đề trong
heading()phương thức.
Mã đã hoàn thành
<?php
namespace App\Exports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class EmployeesByAgeExport implements FromCollection, WithHeadings
{
private $agegreaterthan;
public function __construct($age=0)
{
$this->agegreaterthan = $age;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
## 3. Conditional export and customize result
$records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();
$result = array();
foreach($records as $record){
$result[] = array(
'id'=>$record->id,
'username' => $record->username,
'name' => $record->name,
'email' => $record->email,
'age' => $record->age,
'status' => 1 // Custom data
);
}
return collect($result);
}
public function headings(): array
{
return [
'#',
'Username',
'Name',
'Email',
'Age',
'Status'
];
}
}8. Lộ trình
- Mở
routes/web.phptệp. - Xác định 4 tuyến đường -
- / - Tải chế độ xem chỉ mục.
- nhân viên / exportcsv - Xuất dữ liệu ở định dạng CSV.
- nhân viên / exportexcel - Xuất dữ liệu ở định dạng Excel.
- nhân viên / exportbyagecsv - ĐĂNG lộ trình xuất dữ liệu theo độ tuổi.
Mã đã hoàn thành
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');9. Bộ điều khiển
- Tạo
EmployeesControllerBộ điều khiển.
php artisan make:controller EmployeesController- Mở
app/Http/Controllers/EmployeesController.phptệp. - Nhập và .
EmployeesExport_EmployeesByAgeExportExcel - Tạo 4 phương pháp -
- index () - Tải
indexchế độ xem. - exportCSV () - Để xuất lệnh gọi dữ liệu
Excel::download().
- index () - Tải
Nó có 2 tham số -
- Cá thể lớp.
- Tên tệp có phần mở rộng.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);- exportExcel () - Sử dụng cùng đoạn mã trên và thay đổi phần mở rộng tệp thành .xlsx.
- exportByAgeCSV () - Đọc giá trị tuổi POST và chuyển nó cùng với phiên bản
EmployeesByAgeExport($age).
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);Mã đã hoàn thành
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// CSV Export
public function exportCSV(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
}
// Excel Export
public function exportExcel(){
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
return Excel::download(new EmployeesExport, $file_name);
}
// Conditional Export (csv)
public function exportByAgeCSV(Request $request){
$age = $request->age;
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesByAgeExport($age), $file_name);
}
}10. Xem
Tạo index.blade.php tệp trong resources/views/ thư mục.
Tạo 2 phần tử neo -
- Đặt
hrefthành để{{ route('employees.exportcsv') }}xuất CSV. - Đặt
hrefthành để{{ route('employees.exportexcel') }}xuất Excel.
Tạo một <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Thêm yếu tố đầu vào để nhập tuổi và nút gửi.
Mã đã hoàn thành
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Export data to CSV & Excel format in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a>
<a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>
<form method='post' action="{{ route('employees.exportbyagecsv') }}">
@csrf
<div class="mb-3">
<label for="age" class="form-label">Age</label>
<input type="number" class="form-control" id="age" name="age" value="0">
</div>
<button type="submit" class="btn btn-success">Export</button>
</form>
</div>
</body>
</html>11. Demo
12. Kết luận
Sử dụng hàm tạo trong lớp Export để xử lý các tham số đã truyền và sử dụng nó để tìm nạp dữ liệu.
Trong hướng dẫn này, tôi chỉ đề cập đến 2 định dạng xuất - CSV và Excel nhưng có nhiều định dạng hơn.
Nguồn: https://makitweb.com
1656717300
如何在 Laravel 8 中將 CSV 和 Excel 數據導入 MySQL 數據庫
批量導入數據是將存儲在文件中的記錄添加到數據庫的有用功能。數據可以以 CSV、Excel、XML、ODS 等格式存儲。
在本教程中，我將展示如何在 Laravel 8 項目中使用 Laravel Excel 包將 CSV 和 Excel 數據導入 MySQL 數據庫。
1.安裝包
要求 -
- PHP：
^7.2\|^8.0 - 拉拉維爾：
^5.8 - Php電子表格：
^1.21 - psr/簡單緩存：
^1.0 php_zip啟用PHP 擴展php_xml啟用PHP 擴展php_gd2啟用PHP 擴展php_iconv啟用PHP 擴展php_simplexml啟用PHP 擴展php_xmlreader啟用PHP 擴展php_zlib啟用PHP 擴展
使用 composer 安裝包——
composer require maatwebsite/excel如果您在執行上述命令時遇到錯誤，請執行以下命令 -
composer require psr/simple-cache:^1.0 maatwebsite/excel之後再次執行 -
composer require maatwebsite/excel2.更新app.php
- 打開
config/app.php文件。 - 將以下內容添加
Maatwebsite\Excel\ExcelServiceProvider::class到'providers'–
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- 將以下內容添加
'Excel' => Maatwebsite\Excel\Facades\Excel::class到'aliases'–
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3.發布包
運行命令——
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config這將excel.php在config/.
4. 數據庫配置
打開.env文件。
指定主機、數據庫名稱、用戶名和密碼。
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. 創建表
Employees使用遷移創建一個新表並添加一些記錄。
php artisan make:migration create_employees_tabledatabase/migrations/現在，從項目根目錄導航到文件夾。- 找到一個以 結尾的 PHP 文件
create_employees_table並打開它。 - 在方法中定義表結構
up()。
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- 運行遷移 -
php artisan migrate- 表已創建。
6. 型號
- 創建
Employees模型。
php artisan make:model Employees- 打開
app/Models/Employees.php文件。 - 使用該屬性指定可批量分配的模型屬性——用戶名、姓名、電子郵件和年齡
$fillable。
完成的代碼
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. 創建導入類
我創建 2 個導入類只是為了舉例 -
1.EmployeesImport 類——
php artisan make:import EmployeesImport --model=Employees- 打開
app/Imports/EmployeesImport.php文件。 - 類有 1 種方法——
- model() –從 Array 中讀取值
$row並插入一條記錄，如果電子郵件 ID 不存在於employees表中，否則返回 null。
- model() –從 Array 中讀取值
注意——這個導入類從第一行開始讀取記錄。我解釋瞭如何在下一個 Import 類中跳過包含標題的第一行。
沒有標題行的 CSV 文件 –
| 用戶1 | 用戶 1 u1 | user1@makitweb.com | 28 |
| 用戶2 | 用戶 2 u2 | user2@makitweb.com | 24 |
完成的代碼
<?php
namespace App\Imports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;
class EmployeesImport implements ToModel {
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row) {
// Check email already exists
$count = Employees::where('email',$row[2])->count();
if($count > 0){
return null;
}
return new Employees([
'username' => $row[0],
'name' => $row[1],
'email' => $row[2],
'age' => $row[3],
]);
}
}2.Employees2Import 類——
php artisan make:import Employees2Import --model=Employees- 打開
app/Imports/Employees2Import.php文件。 - 類有 2 種方法——
- collection() -驗證
$rows數組數據。如果成功驗證，則如果表中不存在電子郵件 ID，則插入記錄，employees否則返回錯誤。
- collection() -驗證
注 —如果
WithHeadingRow實現，則$rows數組包含行標題作為鍵名而不是索引。
- headingRow() -返回標題索引位置。
帶有標題行的 CSV 文件 –
| 用戶名 | 姓名 | 電子郵件 | 年齡 |
|---|---|---|---|
| yssyogesh | 約格甚·辛格 | yogesh@makitweb.com | 28 |
| 布索納里卡 | 索娜麗卡·巴多利亞 | bsonarika@makitweb.com | 28 |
| 維沙爾 | 維沙爾薩胡 | vishal@makitweb.com | 第三十一 |
完成的代碼
<?php
namespace App\Imports;
use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;
class Employees2Import implements ToCollection ,WithHeadingRow
{
public function collection(Collection $rows){
// Validate
Validator::make($rows->toArray(), [
'*.username' => 'required|string',
'*.name' => 'required|string',
'*.email' => 'required|email',
'*.age' => 'required|integer',
],[
'*.username.required'=> "The username field is required.",
'*.username.string'=> "The username must be string.",
'*.name.required'=> "The name field is required.",
'*.name.string'=> "The name must be string.",
'*.email.required'=> "The email field is required.",
'*.email.email'=> "The email must be a valid email address.",
'*.age.integer'=> "The age must be an integer."
])->validate();
foreach ($rows as $row) {
// Check email already exists
$count = Employees::where('email',$row['email'])->count();
if($count > 0){
continue;
}
Employees::create([
'username' => $row['username'],
'name' => $row['name'],
'email' => $row['email'],
'age' => $row['age'],
]);
}
}
// Specify header row index position to skip
public function headingRow(): int {
return 1;
}
}8. 路線
- 打開
routes/web.php文件。 - 定義 3 條路線 –
- / - 加載索引視圖。
- employees/importdata –發布導入數據的路徑。
- employees/validateandimportdata –發布路由以驗證和導入數據。
完成的代碼
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');9.控制器
- 創建
EmployeesController控制器。
php artisan make:controller EmployeesController- 打開
app/Http/Controllers/EmployeesController.php文件。 - 導入 和 。
EmployeesImport_Employees2ImportExcel - 創建 3 個方法 –
- index() -加載
index視圖。 - importdata() –導入調用
Excel::import()。
- index() -加載
傳遞 2 個參數 –
EmployeesImport類實例。- 傳遞選定的文件“臨時”位置。
- validateAndImportdata() –再次調用
Excel::import().
- validateAndImportdata() –再次調用
傳遞 2 個參數 –
Employees2Import類實例。- 傳遞存儲的文件位置（我已將文件存儲在文件
public/employees.xlsx夾中）。
完成的代碼
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// Import data
public function importdata(Request $request){
Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
return back()->with('success', 'Import successfully!');
}
// Validate and Import data
public function validateAndImportdata(Request $request){
Excel::import(new Employees2Import, "employees.xlsx");
return back()->with('success', 'Import successfully!');
}
}10. 查看
index.blade.php 在 文件夾中創建 文件resources/views/ 。
創建 2 <form>–
- 在第一
<form >組動作中{{ route('employees.importdata') }}。創建一個文件元素和一個提交按鈕。 - 在第二
<form>組動作中{{ route('employees.validateandimportdata') }}。創建提交按鈕。
循環$errors->all()顯示錯誤。
完成的代碼
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Import data into MySQL database in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<!-- Success message -->
@if(Session::has('success'))
<div class="alert alert-success">
{{ Session::get('success') }}
</div>
@endif
<form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
@csrf
<div class="mb-3">
<label for="file" class="form-label">File</label>
<input type="file" class="form-control" id="file" name="file" value="">
</div>
<button type="submit" class="btn btn-success">Import</button>
</form>
<!-- Import data with validation -->
<h2 class='mt-5'>Validate and import data</h2>
{{-- Display errors --}}
@if (count($errors) > 0)
<div class="row">
<div class="col-md-12 ">
<div class="alert alert-danger">
<ul>
@foreach($errors->all() as $error)
<li>{{ $error }} </li>
@endforeach
</ul>
</div>
</div>
</div>
@endif
<form method='post' action="{{ route('employees.validateandimportdata') }}" >
@csrf
<button type="submit" class="btn btn-success">Import</button>
</form>
</div>
</body>
</html>11. 輸出
12. 結論
確保在 Import 類中插入或更新記錄之前驗證記錄。WithHeadingRow僅當導入文件具有標題行時才在 Import 類中指定。
1656714120
Cách Nhập Dữ Liệu CSV & Excel Vào Cơ Sở Dữ Liệu MySQL Trong Laravel 8
Nhập hàng loạt dữ liệu là một tính năng hữu ích để thêm các bản ghi được lưu trữ trong một tệp vào cơ sở dữ liệu. Dữ liệu có thể được lưu trữ ở định dạng - CSV, Excel, XML, ODS, v.v.
Trong hướng dẫn này, tôi chỉ cách bạn có thể nhập dữ liệu CSV & Excel vào cơ sở dữ liệu MySQL bằng cách sử dụng gói Laravel Excel trong dự án Laravel 8.
1. Cài đặt gói
Yêu cầu -
- PHP:
^7.2\|^8.0 - Laravel:
^5.8 - PhpS Spreadsheet:
^1.21 - psr / simple-cache:
^1.0 php_zipĐã bật tiện ích mở rộng PHPphp_xmlĐã bật tiện ích mở rộng PHPphp_gd2Đã bật tiện ích mở rộng PHPphp_iconvĐã bật tiện ích mở rộng PHPphp_simplexmlĐã bật tiện ích mở rộng PHPphp_xmlreaderĐã bật tiện ích mở rộng PHPphp_zlibĐã bật tiện ích mở rộng PHP
Cài đặt gói bằng trình soạn nhạc -
composer require maatwebsite/excelNếu bạn gặp lỗi khi thực hiện lệnh trên, hãy thực hiện lệnh dưới đây:
composer require psr/simple-cache:^1.0 maatwebsite/excelSau đó, một lần nữa thực thi -
composer require maatwebsite/excel2. Cập nhật app.php
- Mở
config/app.phptệp. - Thêm phần sau
Maatwebsite\Excel\ExcelServiceProvider::classvào'providers'-
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Thêm phần sau
'Excel' => Maatwebsite\Excel\Facades\Excel::classvào'aliases'-
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Xuất bản gói
Chạy lệnh -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configĐiều này sẽ tạo một excel.phptệp mới trong config/.
4. Cấu hình cơ sở dữ liệu
Mở .envtệp.
Chỉ định máy chủ, tên cơ sở dữ liệu, tên người dùng và mật khẩu.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Tạo bảng
- Tạo một bảng mới
Employeesbằng cách sử dụng di chuyển và thêm một số bản ghi.
php artisan make:migration create_employees_table- Bây giờ, điều hướng đến
database/migrations/thư mục từ thư mục gốc của dự án. - Tìm một tệp PHP có kết thúc bằng
create_employees_tablevà mở nó. - Xác định cấu trúc bảng trong
up()phương thức.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Chạy quá trình di chuyển -
php artisan migrate- Bảng đã được tạo.
6. Mô hình
- Tạo
Employeesmô hình.
php artisan make:model Employees- Mở
app/Models/Employees.phptệp. - Chỉ định các thuộc tính Model có thể gán hàng loạt - tên người dùng, tên, email và tuổi bằng cách sử dụng thuộc
$fillabletính.
Mã đã hoàn thành
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Tạo lớp Nhập
Tôi đang tạo 2 lớp nhập chỉ cho mục đích ví dụ -
1. Lớp nhân viênImport -
php artisan make:import EmployeesImport --model=Employees- Mở
app/Imports/EmployeesImport.phptệp. - Lớp có 1 phương thức -
- model () - Đọc giá trị từ
$rowMảng và chèn bản ghi nếu id email không tồn tại trongemployeesbảng, nếu không sẽ trả về null.
- model () - Đọc giá trị từ
LƯU Ý - Lớp nhập này bắt đầu đọc các bản ghi từ hàng đầu tiên. Tôi đã giải thích cách bỏ qua hàng đầu tiên chứa tiêu đề trong lớp Nhập tiếp theo.
Tệp CSV không có hàng tiêu đề -
| user1 | Người dùng1 u1 | user1@makitweb.com | 28 |
| user2 | Người dùng2 u2 | user2@makitweb.com | 24 |
Mã đã hoàn thành
<?php
namespace App\Imports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;
class EmployeesImport implements ToModel {
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row) {
// Check email already exists
$count = Employees::where('email',$row[2])->count();
if($count > 0){
return null;
}
return new Employees([
'username' => $row[0],
'name' => $row[1],
'email' => $row[2],
'age' => $row[3],
]);
}
}2. Lớp nhân viên2Import -
php artisan make:import Employees2Import --model=Employees- Mở
app/Imports/Employees2Import.phptệp. - Lớp có 2 phương thức -
- collection () - Xác thực
$rowsdữ liệu Mảng. Nếu xác thực thành công thì hãy chèn bản ghi nếu id email không tồn tại trongemployeesbảng nếu không sẽ trả về lỗi.
- collection () - Xác thực
LƯU Ý - Nếu
WithHeadingRowđược triển khai thì$rowsMảng chứa tiêu đề hàng dưới dạng tên khóa thay vì chỉ mục.
- headerRow () - Trả về vị trí chỉ mục tiêu đề.
Tệp CSV có hàng tiêu đề -
| tên tài khoản | Tên | Tuổi tác | |
|---|---|---|---|
| yssyogesh | Yogesh singh | yogesh@makitweb.com | 28 |
| bsonarika | Sonarika Bhadoria | bsonarika@makitweb.com | 28 |
| vishal | Vishal Sahu | vishal@makitweb.com | 31 |
Mã đã hoàn thành
<?php
namespace App\Imports;
use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;
class Employees2Import implements ToCollection ,WithHeadingRow
{
public function collection(Collection $rows){
// Validate
Validator::make($rows->toArray(), [
'*.username' => 'required|string',
'*.name' => 'required|string',
'*.email' => 'required|email',
'*.age' => 'required|integer',
],[
'*.username.required'=> "The username field is required.",
'*.username.string'=> "The username must be string.",
'*.name.required'=> "The name field is required.",
'*.name.string'=> "The name must be string.",
'*.email.required'=> "The email field is required.",
'*.email.email'=> "The email must be a valid email address.",
'*.age.integer'=> "The age must be an integer."
])->validate();
foreach ($rows as $row) {
// Check email already exists
$count = Employees::where('email',$row['email'])->count();
if($count > 0){
continue;
}
Employees::create([
'username' => $row['username'],
'name' => $row['name'],
'email' => $row['email'],
'age' => $row['age'],
]);
}
}
// Specify header row index position to skip
public function headingRow(): int {
return 1;
}
}8. Lộ trình
- Mở
routes/web.phptệp. - Xác định 3 tuyến đường -
- / - Tải chế độ xem chỉ mục.
- nhân viên / importdata - Đăng tuyến để Nhập dữ liệu.
- nhân viên / validateandimportdata - Đăng tuyến để xác thực và nhập dữ liệu.
Mã đã hoàn thành
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');9. Bộ điều khiển
- Tạo
EmployeesControllerBộ điều khiển.
php artisan make:controller EmployeesController- Mở
app/Http/Controllers/EmployeesController.phptệp. - Nhập và .
EmployeesImport_Employees2ImportExcel - Tạo 3 phương pháp -
- index () - Tải
indexchế độ xem. - importdata () - Để nhập cuộc gọi
Excel::import().
- index () - Tải
Chuyển 2 tham số -
EmployeesImportCá thể lớp.- Vượt qua vị trí 'tạm thời' của tệp đã chọn.
- validateAndImportdata () - Một lần nữa gọi
Excel::import().
- validateAndImportdata () - Một lần nữa gọi
Chuyển 2 tham số -
Employees2ImportCá thể lớp.- Chuyển vị trí tệp được lưu trữ (Tôi đã lưu tệp trong
public/employees.xlsxthư mục).
Mã đã hoàn thành
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// Import data
public function importdata(Request $request){
Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
return back()->with('success', 'Import successfully!');
}
// Validate and Import data
public function validateAndImportdata(Request $request){
Excel::import(new Employees2Import, "employees.xlsx");
return back()->with('success', 'Import successfully!');
}
}10. Xem
Tạo index.blade.php tệp trong resources/views/ thư mục.
Tạo 2 <form>-
- Trong
<form >hành động đầu tiên đặt thành{{ route('employees.importdata') }}. Tạo một phần tử tệp và một nút gửi. - Trong
<form>hành động đặt thứ 2 thành{{ route('employees.validateandimportdata') }}. Tạo nút gửi.
Lặp $errors->all()lại để hiển thị lỗi.
Mã đã hoàn thành
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Import data into MySQL database in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<!-- Success message -->
@if(Session::has('success'))
<div class="alert alert-success">
{{ Session::get('success') }}
</div>
@endif
<form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
@csrf
<div class="mb-3">
<label for="file" class="form-label">File</label>
<input type="file" class="form-control" id="file" name="file" value="">
</div>
<button type="submit" class="btn btn-success">Import</button>
</form>
<!-- Import data with validation -->
<h2 class='mt-5'>Validate and import data</h2>
{{-- Display errors --}}
@if (count($errors) > 0)
<div class="row">
<div class="col-md-12 ">
<div class="alert alert-danger">
<ul>
@foreach($errors->all() as $error)
<li>{{ $error }} </li>
@endforeach
</ul>
</div>
</div>
</div>
@endif
<form method='post' action="{{ route('employees.validateandimportdata') }}" >
@csrf
<button type="submit" class="btn btn-success">Import</button>
</form>
</div>
</body>
</html>11. Đầu ra
12. Kết luận
Đảm bảo xác thực bản ghi trước khi chèn hoặc cập nhật bản ghi trong lớp Nhập. Chỉ định WithHeadingRowtrong lớp Nhập nếu tệp nhập có hàng tiêu đề.
Nguồn: https://makitweb.com
1656713580
Comment Importer Des Données CSV Et Excel Dans MySQL Dans Laravel 8
L'importation de données en bloc est une fonctionnalité utile pour ajouter des enregistrements stockés dans un fichier à la base de données. Les données peuvent être stockées au format – CSV, Excel, XML, ODS, etc.
Dans ce tutoriel, je montre comment vous pouvez importer des données CSV et Excel dans la base de données MySQL à l'aide du package Laravel Excel dans le projet Laravel 8.
1. Installer le package
Exigence -
- PHP :
^7.2\|^8.0 - Laravel :
^5.8 - Feuille de calcul Php :
^1.21 - psr/simple-cache :
^1.0 - Extension PHP
php_zipactivée - Extension PHP
php_xmlactivée - Extension PHP
php_gd2activée - Extension PHP
php_iconvactivée - Extension PHP
php_simplexmlactivée - Extension PHP
php_xmlreaderactivée - Extension PHP
php_zlibactivée
Installez le package à l'aide de composer -
composer require maatwebsite/excelSi vous obtenez une erreur lors de l'exécution de la commande ci-dessus, exécutez la commande ci-dessous -
composer require psr/simple-cache:^1.0 maatwebsite/excelAprès cela, exécutez à nouveau –
composer require maatwebsite/excel2. Mettre à jour app.php
- Ouvrir
config/app.phple fichier. - Ajoutez ce qui suit
Maatwebsite\Excel\ExcelServiceProvider::classdans'providers'-
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Ajoutez ce qui suit
'Excel' => Maatwebsite\Excel\Facades\Excel::classdans'aliases'-
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Publier le package
Exécutez la commande -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configCela créera un nouveau excel.phpfichier au format config/.
4. Configuration de la base de données
Ouvrir .envle fichier.
Spécifiez l'hôte, le nom de la base de données, le nom d'utilisateur et le mot de passe.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Créer un tableau
- Créez une nouvelle table
Employeesà l'aide de la migration et ajoutez des enregistrements.
php artisan make:migration create_employees_table- Maintenant, accédez au
database/migrations/dossier à partir de la racine du projet. - Trouvez un fichier PHP qui se termine par
create_employees_tableet ouvrez-le. - Définissez la structure de la table dans la
up()méthode.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Exécutez la migration –
php artisan migrate- Le tableau a été créé.
6. Modèle
- Créer un
Employeesmodèle.
php artisan make:model Employees- Ouvrir
app/Models/Employees.phple fichier. - Spécifiez les attributs de modèle assignables en masse - nom d'utilisateur, nom, e-mail et âge à l'aide de la
$fillablepropriété.
Code terminé
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Créer une classe d'importation
Je crée 2 classes d'importation juste à titre d'exemple -
1. Classe EmployeesImport –
php artisan make:import EmployeesImport --model=Employees- Ouvrir
app/Imports/EmployeesImport.phple fichier. - La classe a 1 méthode -
- model() - Lit les valeurs de
$rowArray et insère un enregistrement si l'identifiant de messagerie n'existe pas dans laemployeestable, sinon renvoie null.
- model() - Lit les valeurs de
REMARQUE - Cette classe d'importation commence à lire les enregistrements à partir de la 1ère ligne. J'ai expliqué comment ignorer la 1ère ligne qui contient l'en-tête dans la prochaine classe d'importation.
Fichier CSV sans ligne d'en-tête –
| utilisateur1 | Utilisateur1 u1 | user1@makitweb.com | 28 |
| utilisateur2 | Utilisateur2 u2 | user2@makitweb.com | 24 |
Code terminé
<?php
namespace App\Imports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;
class EmployeesImport implements ToModel {
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row) {
// Check email already exists
$count = Employees::where('email',$row[2])->count();
if($count > 0){
return null;
}
return new Employees([
'username' => $row[0],
'name' => $row[1],
'email' => $row[2],
'age' => $row[3],
]);
}
}2. Classe Employees2Import -
php artisan make:import Employees2Import --model=Employees- Ouvrir
app/Imports/Employees2Import.phple fichier. - La classe a 2 méthodes -
- collection() – Valide
$rowsles données du tableau. Si la validation est réussie, insérez un enregistrement si l'identifiant de messagerie n'existe pas dans laemployeestable, sinon renvoyez une erreur.
- collection() – Valide
NOTE – Si
WithHeadingRowest implémenté, alors$rowsArrays contient l'en-tête de ligne comme noms de clé au lieu d'index.
- headerRow() – Renvoie la position de l'index d'en-tête.
Fichier CSV avec ligne d'en-tête –
| Nom d'utilisateur | Nom | Âge | |
|---|---|---|---|
| yssyogesh | Yogesh singh | yogesh@makitweb.com | 28 |
| bsonarika | Sonarika Bhadoria | bsonarika@makitweb.com | 28 |
| vishal | Vishal Sahu | vishal@makitweb.com | trente et un |
Code terminé
<?php
namespace App\Imports;
use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;
class Employees2Import implements ToCollection ,WithHeadingRow
{
public function collection(Collection $rows){
// Validate
Validator::make($rows->toArray(), [
'*.username' => 'required|string',
'*.name' => 'required|string',
'*.email' => 'required|email',
'*.age' => 'required|integer',
],[
'*.username.required'=> "The username field is required.",
'*.username.string'=> "The username must be string.",
'*.name.required'=> "The name field is required.",
'*.name.string'=> "The name must be string.",
'*.email.required'=> "The email field is required.",
'*.email.email'=> "The email must be a valid email address.",
'*.age.integer'=> "The age must be an integer."
])->validate();
foreach ($rows as $row) {
// Check email already exists
$count = Employees::where('email',$row['email'])->count();
if($count > 0){
continue;
}
Employees::create([
'username' => $row['username'],
'name' => $row['name'],
'email' => $row['email'],
'age' => $row['age'],
]);
}
}
// Specify header row index position to skip
public function headingRow(): int {
return 1;
}
}8. Itinéraire
- Ouvrir
routes/web.phple fichier. - Définir 3 itinéraires –
- / – Charger la vue d'index.
- employés/importdata – Publier la route vers les données d'importation.
- employee/validateandimportdata – Route de publication pour valider et importer des données.
Code terminé
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');9. Contrôleur
- Créer
EmployeesControllerun contrôleur.
php artisan make:controller EmployeesController- Ouvrir
app/Http/Controllers/EmployeesController.phple fichier. - Importer et .
EmployeesImport_Employees2ImportExcel - Créez 3 méthodes –
- index() – Charger la
indexvue. - importdata() – Pour importer, appelez
Excel::import().
- index() – Charger la
Passer 2 paramètres –
EmployeesImportInstance de classe.- Passer l'emplacement 'temp' du fichier sélectionné.
- validateAndImportdata() – Appelez à nouveau
Excel::import().
- validateAndImportdata() – Appelez à nouveau
Passer 2 paramètres –
Employees2ImportInstance de classe.- Passez l'emplacement du fichier stocké (j'ai stocké le fichier dans
public/employees.xlsxle dossier).
Code terminé
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// Import data
public function importdata(Request $request){
Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
return back()->with('success', 'Import successfully!');
}
// Validate and Import data
public function validateAndImportdata(Request $request){
Excel::import(new Employees2Import, "employees.xlsx");
return back()->with('success', 'Import successfully!');
}
}10. Voir
Créer index.blade.php un fichier dans resources/views/ le dossier.
Créer 2 <form>–
- Dans la 1ère
<form >action définie sur{{ route('employees.importdata') }}. Créez un élément de fichier et un bouton d'envoi. - Dans la 2ème
<form>action définie sur{{ route('employees.validateandimportdata') }}. Créez un bouton de soumission.
Boucle sur $errors->all()pour afficher les erreurs.
Code terminé
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Import data into MySQL database in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<!-- Success message -->
@if(Session::has('success'))
<div class="alert alert-success">
{{ Session::get('success') }}
</div>
@endif
<form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
@csrf
<div class="mb-3">
<label for="file" class="form-label">File</label>
<input type="file" class="form-control" id="file" name="file" value="">
</div>
<button type="submit" class="btn btn-success">Import</button>
</form>
<!-- Import data with validation -->
<h2 class='mt-5'>Validate and import data</h2>
{{-- Display errors --}}
@if (count($errors) > 0)
<div class="row">
<div class="col-md-12 ">
<div class="alert alert-danger">
<ul>
@foreach($errors->all() as $error)
<li>{{ $error }} </li>
@endforeach
</ul>
</div>
</div>
</div>
@endif
<form method='post' action="{{ route('employees.validateandimportdata') }}" >
@csrf
<button type="submit" class="btn btn-success">Import</button>
</form>
</div>
</body>
</html>11. Sortie
12.Conclusion
Assurez-vous de valider l'enregistrement avant d'insérer ou de mettre à jour des enregistrements dans la classe d'importation. Spécifiez WithHeadingRowdans Classe d'importation uniquement si le fichier d'importation comporte une ligne d'en-tête.
Source: https://makitweb.com
1656705960
Cómo Importar Datos CSV Y Excel A La Base De Datos MySQL En Laravel 8
La importación masiva de datos es una característica útil para agregar registros almacenados en un archivo a la base de datos. Los datos pueden almacenarse en formato CSV, Excel, XML, ODS, etc.
En este tutorial, muestro cómo puede importar datos CSV y Excel a la base de datos MySQL usando el paquete Laravel Excel en el proyecto Laravel 8.
1. Paquete de instalación
Requisito –
- PHP:
^7.2\|^8.0 - Laravel:
^5.8 - PhpHoja de cálculo:
^1.21 - psr/caché simple:
^1.0 - Extensión PHP
php_ziphabilitada - Extensión PHP
php_xmlhabilitada - Extensión PHP
php_gd2habilitada - Extensión PHP
php_iconvhabilitada - Extensión PHP
php_simplexmlhabilitada - Extensión PHP
php_xmlreaderhabilitada - Extensión PHP
php_zlibhabilitada
Instale el paquete usando composer –
composer require maatwebsite/excelSi recibe un error al ejecutar el comando anterior, ejecute el siguiente comando:
composer require psr/simple-cache:^1.0 maatwebsite/excelDespués de eso nuevamente ejecute –
composer require maatwebsite/excel2. Actualizar aplicación.php
- Abrir
config/app.phparchivo. - Agregue lo siguiente
Maatwebsite\Excel\ExcelServiceProvider::classen'providers'–
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Agregue lo siguiente
'Excel' => Maatwebsite\Excel\Facades\Excel::classen'aliases'–
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Publicar paquete
Ejecute el comando –
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configEsto creará un nuevo excel.phparchivo en formato config/.
4. Configuración de la base de datos
Abrir .envarchivo.
Especifique el host, el nombre de la base de datos, el nombre de usuario y la contraseña.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Crear tabla
- Cree una nueva tabla
Employeesmediante la migración y agregue algunos registros.
php artisan make:migration create_employees_table- Ahora, navegue a
database/migrations/la carpeta desde la raíz del proyecto. - Encuentre un archivo PHP que termine con
create_employees_tabley ábralo. - Defina la estructura de la tabla en el
up()método.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Ejecute la migración –
php artisan migrate- La tabla ha sido creada.
6. Modelo
- Crear
Employeesmodelo.
php artisan make:model Employees- Abrir
app/Models/Employees.phparchivo. - Especifique atributos de modelo asignables en masa: nombre de usuario, nombre, correo electrónico y edad utilizando la
$fillablepropiedad.
Código completado
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Crear clase de importación
Estoy creando 2 clases de importación solo por ejemplo:
1. Clase de importación de empleados:
php artisan make:import EmployeesImport --model=Employees- Abrir
app/Imports/EmployeesImport.phparchivo. - La clase tiene 1 método:
- model (): lea los valores de
$rowArray e inserte un registro si la identificación del correo electrónico no existe en laemployeestabla; de lo contrario, devuelva nulo.
- model (): lea los valores de
NOTA: esta clase de importación comienza a leer registros desde la primera fila. Expliqué cómo omitir la primera fila que contiene el encabezado en la siguiente clase de Importación.
Archivo CSV sin fila de encabezado –
| usuario1 | Usuario1 u1 | usuario1@makitweb.com | 28 |
| usuario2 | Usuario2 u2 | usuario2@makitweb.com | 24 |
Código completado
<?php
namespace App\Imports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;
class EmployeesImport implements ToModel {
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row) {
// Check email already exists
$count = Employees::where('email',$row[2])->count();
if($count > 0){
return null;
}
return new Employees([
'username' => $row[0],
'name' => $row[1],
'email' => $row[2],
'age' => $row[3],
]);
}
}2. Clase Employees2Import –
php artisan make:import Employees2Import --model=Employees- Abrir
app/Imports/Employees2Import.phparchivo. - La clase tiene 2 métodos:
- colección () - Valida
$rowslos datos de la matriz. Si se valida con éxito, inserte el registro si la identificación del correo electrónico no existe en laemployeestabla; de lo contrario, devolverá un error.
- colección () - Valida
NOTA: si
WithHeadingRowse implementa,$rowsArrays contiene encabezados de fila como nombres clave en lugar de índice.
- headerRow(): devuelve la posición del índice de encabezado.
Archivo CSV con fila de encabezado –
| Nombre de usuario | Nombre | Correo electrónico | Años |
|---|---|---|---|
| yssyogesh | yogesh singh | yogesh@makitweb.com | 28 |
| bsonarika | Sonarika Bhadoria | bsonarika@makitweb.com | 28 |
| vishal | Vishal Sahu | vishal@makitweb.com | treinta y uno |
Código completado
<?php
namespace App\Imports;
use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;
class Employees2Import implements ToCollection ,WithHeadingRow
{
public function collection(Collection $rows){
// Validate
Validator::make($rows->toArray(), [
'*.username' => 'required|string',
'*.name' => 'required|string',
'*.email' => 'required|email',
'*.age' => 'required|integer',
],[
'*.username.required'=> "The username field is required.",
'*.username.string'=> "The username must be string.",
'*.name.required'=> "The name field is required.",
'*.name.string'=> "The name must be string.",
'*.email.required'=> "The email field is required.",
'*.email.email'=> "The email must be a valid email address.",
'*.age.integer'=> "The age must be an integer."
])->validate();
foreach ($rows as $row) {
// Check email already exists
$count = Employees::where('email',$row['email'])->count();
if($count > 0){
continue;
}
Employees::create([
'username' => $row['username'],
'name' => $row['name'],
'email' => $row['email'],
'age' => $row['age'],
]);
}
}
// Specify header row index position to skip
public function headingRow(): int {
return 1;
}
}8. Ruta
- Abrir
routes/web.phparchivo. - Defina 3 rutas –
- / – Cargar vista de índice.
- empleados/importdata: Publicar ruta para importar datos.
- empleados/validar e importar datos: ruta de publicación para validar e importar datos.
Código completado
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');9. Controlador
- Crear
EmployeesControllercontrolador.
php artisan make:controller EmployeesController- Abrir
app/Http/Controllers/EmployeesController.phparchivo. - Importar y .
EmployeesImport_Employees2ImportExcel - Crear 3 métodos –
- index() – Cargar
indexvista. - importdata() – Para importar llamar a
Excel::import().
- index() – Cargar
Pase 2 parámetros –
EmployeesImportinstancia de clase.- Pasar la ubicación 'temp' del archivo seleccionado.
- validarAndImportdata() – Nuevamente llamar
Excel::import().
- validarAndImportdata() – Nuevamente llamar
Pase 2 parámetros –
Employees2Importinstancia de clase.- Pase la ubicación del archivo almacenado (he almacenado el archivo en una
public/employees.xlsxcarpeta).
Código completado
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// Import data
public function importdata(Request $request){
Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
return back()->with('success', 'Import successfully!');
}
// Validate and Import data
public function validateAndImportdata(Request $request){
Excel::import(new Employees2Import, "employees.xlsx");
return back()->with('success', 'Import successfully!');
}
}10. Ver
Crear index.blade.php archivo en resources/views/ carpeta.
Crear 2 <form>-
- En la 1ra
<form >acción fijada a{{ route('employees.importdata') }}. Cree un elemento de archivo y un botón de envío. - En la 2ª
<form>acción fijada a{{ route('employees.validateandimportdata') }}. Crear un botón de envío.
Bucle $errors->all()para mostrar errores.
Código completado
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Import data into MySQL database in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<!-- Success message -->
@if(Session::has('success'))
<div class="alert alert-success">
{{ Session::get('success') }}
</div>
@endif
<form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
@csrf
<div class="mb-3">
<label for="file" class="form-label">File</label>
<input type="file" class="form-control" id="file" name="file" value="">
</div>
<button type="submit" class="btn btn-success">Import</button>
</form>
<!-- Import data with validation -->
<h2 class='mt-5'>Validate and import data</h2>
{{-- Display errors --}}
@if (count($errors) > 0)
<div class="row">
<div class="col-md-12 ">
<div class="alert alert-danger">
<ul>
@foreach($errors->all() as $error)
<li>{{ $error }} </li>
@endforeach
</ul>
</div>
</div>
</div>
@endif
<form method='post' action="{{ route('employees.validateandimportdata') }}" >
@csrf
<button type="submit" class="btn btn-success">Import</button>
</form>
</div>
</body>
</html>11. Salida
12. Conclusión
Asegúrese de validar el registro antes de insertar o actualizar registros en la clase Importar. Especifique WithHeadingRowen Clase de importación solo si el archivo de importación tiene una fila de encabezado.
Fuente: https://makitweb.com
1656702180
Como Importar Dados CSV E Excel Para O Banco De Dados MySQL No Laravel
A importação de dados em massa é um recurso útil para adicionar registros armazenados em um arquivo ao banco de dados. Os dados podem ser armazenados em formato – CSV, Excel, XML, ODS, etc.
Neste tutorial, mostro como você pode importar dados CSV e Excel para o banco de dados MySQL usando o pacote Laravel Excel no projeto Laravel 8.
1. Instale o pacote
Requerimento -
- PHP:
^7.2\|^8.0 - Laravel:
^5.8 - Planilha Php:
^1.21 - psr/cache simples:
^1.0 - Extensão PHP
php_ziphabilitada - Extensão PHP
php_xmlhabilitada - Extensão PHP
php_gd2habilitada - Extensão PHP
php_iconvhabilitada - Extensão PHP
php_simplexmlhabilitada - Extensão PHP
php_xmlreaderhabilitada - Extensão PHP
php_zlibhabilitada
Instale o pacote usando o compositor –
composer require maatwebsite/excelSe você estiver recebendo um erro ao executar o comando acima, execute o comando abaixo –
composer require psr/simple-cache:^1.0 maatwebsite/excelDepois disso, execute novamente –
composer require maatwebsite/excel2. Atualize app.php
- Abrir
config/app.phparquivo. - Adicione o seguinte
Maatwebsite\Excel\ExcelServiceProvider::classem'providers'–
'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];- Adicione o seguinte
'Excel' => Maatwebsite\Excel\Facades\Excel::classem'aliases'–
'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];3. Publicar pacote
Execute o comando -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configIsso criará um novo excel.phparquivo em config/.
4. Configuração do banco de dados
Abrir .envarquivo.
Especifique o host, o nome do banco de dados, o nome de usuário e a senha.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=5. Criar Tabela
- Crie uma nova tabela
Employeesusando a migração e adicione alguns registros.
php artisan make:migration create_employees_table- Agora, navegue até a
database/migrations/pasta da raiz do projeto. - Encontre um arquivo PHP que termine com
create_employees_tablee abra-o. - Defina a estrutura da tabela no
up()método.
public function up()
{
Schema::create('employees', function (Blueprint $table) {
$table->id();
$table->string('username');
$table->string('name');
$table->string('email');
$table->smallInteger('age');
$table->timestamps();
});
}- Execute a migração –
php artisan migrate- A tabela foi criada.
6. Modelo
- Criar
Employeesmodelo.
php artisan make:model Employees- Abrir
app/Models/Employees.phparquivo. - Especifique atributos de modelo atribuíveis em massa – nome de usuário, nome, email e idade usando a
$fillablepropriedade.
Código concluído
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Employees extends Model
{
use HasFactory;
protected $fillable = [
'username','name','email','age'
];
}7. Criar classe de importação
Estou criando 2 classes de importação apenas para fins de exemplo -
1. Classe de importação de funcionários -
php artisan make:import EmployeesImport --model=Employees- Abrir
app/Imports/EmployeesImport.phparquivo. - A classe tem 1 método –
- model() – Lê os valores do
$rowArray e insere um registro se o ID do email não existir naemployeestabela, caso contrário, retorna null.
- model() – Lê os valores do
NOTA – Esta classe de importação começa a ler os registros da 1ª linha. Expliquei como pular a primeira linha que contém o título na próxima classe de importação.
Arquivo CSV sem linha de cabeçalho –
| usuário1 | Usuário1 u1 | user1@makitweb.com | 28 |
| usuário2 | Usuário2 u2 | user2@makitweb.com | 24 |
Código concluído
<?php
namespace App\Imports;
use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;
class EmployeesImport implements ToModel {
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row) {
// Check email already exists
$count = Employees::where('email',$row[2])->count();
if($count > 0){
return null;
}
return new Employees([
'username' => $row[0],
'name' => $row[1],
'email' => $row[2],
'age' => $row[3],
]);
}
}2. Empregados2 Classe de Importação -
php artisan make:import Employees2Import --model=Employees- Abrir
app/Imports/Employees2Import.phparquivo. - A classe tem 2 métodos –
- collection() – Valida
$rowsos dados do Array. Se validado com sucesso, insira o registro se o ID do email não existir naemployeestabela, caso contrário, retorne o erro.
- collection() – Valida
OBSERVAÇÃO – Se
WithHeadingRowfor implementado,$rowsArrays conterá cabeçalho de linha como nomes de chave em vez de índice.
- headerRow() – Retorna a posição do índice do título.
Arquivo CSV com linha de título –
| Nome de usuário | Nome | Era | |
|---|---|---|---|
| yssyogesh | Yogesh singh | yogesh@makitweb.com | 28 |
| bsonarika | Sonarika Bhadoria | bsonarika@makitweb.com | 28 |
| vishal | Vishal Sahu | vishal@makitweb.com | trigésimo primeiro |
Código concluído
<?php
namespace App\Imports;
use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;
class Employees2Import implements ToCollection ,WithHeadingRow
{
public function collection(Collection $rows){
// Validate
Validator::make($rows->toArray(), [
'*.username' => 'required|string',
'*.name' => 'required|string',
'*.email' => 'required|email',
'*.age' => 'required|integer',
],[
'*.username.required'=> "The username field is required.",
'*.username.string'=> "The username must be string.",
'*.name.required'=> "The name field is required.",
'*.name.string'=> "The name must be string.",
'*.email.required'=> "The email field is required.",
'*.email.email'=> "The email must be a valid email address.",
'*.age.integer'=> "The age must be an integer."
])->validate();
foreach ($rows as $row) {
// Check email already exists
$count = Employees::where('email',$row['email'])->count();
if($count > 0){
continue;
}
Employees::create([
'username' => $row['username'],
'name' => $row['name'],
'email' => $row['email'],
'age' => $row['age'],
]);
}
}
// Specify header row index position to skip
public function headingRow(): int {
return 1;
}
}8. Rota
- Abrir
routes/web.phparquivo. - Defina 3 rotas –
- / – Carregar visualização de índice.
- empregados/importdata – Post rota para Importar dados.
- funcionários/validateandimportdata – Post rota para validar e importar dados.
Código concluído
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;
Route::get('/', [EmployeesController::class, 'index'])->name('home');
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');9. Controlador
- Criar
EmployeesControllercontrolador.
php artisan make:controller EmployeesController- Abrir
app/Http/Controllers/EmployeesController.phparquivo. - Importar e .
EmployeesImport_Employees2ImportExcel - Crie 3 métodos –
- index() – Carregar
indexvisualização. - importdata() – Para importar chame
Excel::import().
- index() – Carregar
Passe 2 parâmetros –
EmployeesImportInstância de classe.- Passe o local 'temp' do arquivo selecionado.
- validateAndImportdata() – Novamente chame
Excel::import().
- validateAndImportdata() – Novamente chame
Passe 2 parâmetros –
Employees2ImportInstância de classe.- Passe o local do arquivo armazenado (guardei o arquivo na
public/employees.xlsxpasta).
Código concluído
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;
class EmployeesController extends Controller
{
public function index(){
return view('index');
}
// Import data
public function importdata(Request $request){
Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
return back()->with('success', 'Import successfully!');
}
// Validate and Import data
public function validateAndImportdata(Request $request){
Excel::import(new Employees2Import, "employees.xlsx");
return back()->with('success', 'Import successfully!');
}
}10. Visualizar
Criar index.blade.php arquivo na resources/views/ pasta.
Criar 2 <form>-
- Na 1ª
<form >ação definida para{{ route('employees.importdata') }}. Crie um elemento de arquivo e um botão de envio. - Na 2ª
<form>ação defina para{{ route('employees.validateandimportdata') }}. Crie um botão de envio.
Faça um loop $errors->all()para exibir erros.
Código concluído
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Import data into MySQL database in Laravel 8</title>
<!-- CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>
<div class="container mt-5">
<!-- Success message -->
@if(Session::has('success'))
<div class="alert alert-success">
{{ Session::get('success') }}
</div>
@endif
<form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
@csrf
<div class="mb-3">
<label for="file" class="form-label">File</label>
<input type="file" class="form-control" id="file" name="file" value="">
</div>
<button type="submit" class="btn btn-success">Import</button>
</form>
<!-- Import data with validation -->
<h2 class='mt-5'>Validate and import data</h2>
{{-- Display errors --}}
@if (count($errors) > 0)
<div class="row">
<div class="col-md-12 ">
<div class="alert alert-danger">
<ul>
@foreach($errors->all() as $error)
<li>{{ $error }} </li>
@endforeach
</ul>
</div>
</div>
</div>
@endif
<form method='post' action="{{ route('employees.validateandimportdata') }}" >
@csrf
<button type="submit" class="btn btn-success">Import</button>
</form>
</div>
</body>
</html>11. Saída
12. Conclusão
Certifique-se de validar o registro antes de inserir ou atualizar registros na classe Import. Especifique WithHeadingRowna classe Import apenas se o arquivo de importação tiver uma linha de cabeçalho.
Fonte: https://makitweb.com
1656696660
How to Import CSV & Excel Data into MySQL Database In Laravel 8
Bulk importing data is a useful feature to add records stored in a file to the database. Data may be stored in – CSV, Excel, XML, ODS, etc format.
In this tutorial, I show how you can import CSV & Excel data to MySQL database using Laravel Excel package in Laravel 8 project.
Source: https://makitweb.com
1656693480
How to Export MySQL Database Data in CSV & Excel Format In Laravel 8
In Laravel, you don’t have to write long code to export your data, there is already a package available for that – Laravel Excel.
It allows exporting data in various formats like – XLSX, CSV, XLS, HTML, etc.
In this tutorial, I show how you can export MySQL database data in CSV & Excel format using the Laravel Excel package in Laravel 8.
Source: https://makitweb.com
1656626400
Comment Maîtriser Les Colonnes Générées Dans MySQL
Les colonnes générées par MySQL se présentent comme un outil puissant, facile à utiliser et avancé pour tous ceux qui souhaitent ajouter des données générées automatiquement à leurs tables. Dans cet article, nous apprendrons tout ce que vous devez savoir pour les maîtriser.
Les colonnes générées vous permettent de stocker des données générées automatiquement dans une table sans utiliser les clauses INSERTet . UPDATECette fonctionnalité utile fait partie de MySQL depuis la version 5.7 et représente une approche alternative aux déclencheurs lorsqu'il s'agit de générer des données. De plus, les colonnes générées peuvent vous aider à rendre votre requête plus simple et plus efficace.
Apprenons maintenant tout ce que vous devez savoir pour maîtriser les colonnes générées dans MySQL.
Qu'est-ce qu'une colonne générée par MySQL ?
Une colonne générée est similaire à une colonne normale, mais vous ne pouvez pas modifier sa valeur manuellement. En effet, une expression définit comment générer la valeur d'une colonne générée en fonction des autres valeurs lues à partir des autres colonnes de la même ligne.
Ainsi, une colonne générée fonctionne dans le domaine d'une table et sa définition ne peut pas impliquer d' JOINinstructions.
En d'autres termes, vous pouvez considérer une colonne générée comme une sorte de vue mais limitée aux colonnes. Notez que les colonnes générées sont différentes des déclencheurs SQL et que vous ne pouvez les définir que lorsque vous utilisez des instructions CREATE TABLEor avec la syntaxe ci-dessous : ALTER TABLE
generate_column_name column_type [GENERATED ALWAYS] AS (generation_expression)[VIRTUAL | STORED] [NOT NULL | NULL][UNIQUE [KEY]] [[PRIMARY] KEY][COMMENT 'string']La AS (generated_column_expression)clause spécifie que la colonne que vous ajoutez ou mettez à jour dans une table est une colonne générée. Le generation_expressiondéfinit l'expression que MySQL utilisera pour calculer les valeurs de colonne, et il ne peut pas référencer une autre colonne générée ou quoi que ce soit d'autre que les colonnes de la table actuelle. Notez également que l'expression de génération ne peut impliquer que des fonctions immuables.
Par exemple, vous ne pouvez pas utiliser la CURDATE()fonction qui renvoie la date actuelle dans une définition d'expression de colonne générée car il s'agit d'une fonction mutable.
Vous pouvez également précéder ASdes mots- GENERATED ALWAYSclés pour rendre plus explicite la nature générée de la colonne, mais cela est facultatif. Ensuite, vous pouvez indiquer si le type de la colonne générée est VIRTUALou STORED. Vous apprendrez la différence entre les deux types dans le chapitre ci-dessous. Par défaut, si cela n'est pas explicitement spécifié dans la requête, MySQL marque une colonne générée comme VIRTUAL.
Voyons maintenant la syntaxe de la colonne générée en action dans une CREATE TABLErequête :
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,email VARCHAR(100) NOT NULL,first_name VARCHAR(60) NOT NULL,last_name VARCHAR(60) NOT NULL,full_name varchar(120) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)));Dans cet exemple, la full_namecolonne stockera automatiquement la concaténation des colonnes first_nameet .last_name
Quels types de colonnes générées existent dans MySQL ?
Comme expliqué précédemment, vous pouvez définir une colonne générée comme VIRTUAL ou STORED. Examinons maintenant de plus près ces deux types.
Colonnes générées virtuellement
MySQL ne stocke pas une colonne générée marquée comme VIRTUAL. Cela signifie que MySQL évalue sa valeur à la volée lorsque cela est nécessaire. Cela se produit généralement immédiatement après BEFOREle déclenchement d'une requête. En d'autres termes, une colonne générée virtuellement n'occupe aucun espace de stockage.
Colonnes générées stockées
MySQL stocke toute colonne générée marquée comme STORED. Cela signifie que MySQL se charge d'évaluer sa valeur et de la stocker sur le disque chaque fois que vous insérez ou mettez à jour une ligne. En d'autres termes, une colonne stockée nécessite un espace de stockage comme s'il s'agissait d'une colonne normale.
Colonnes générées virtuellement vs colonnes générées stockées
Apprenons maintenant plus sur les avantages et les inconvénients des colonnes générées virtuelles et stockées.
Colonnes générées virtuellement
Avantages
- Leur création est instantanée car MySQL n'a qu'à modifier les métadonnées de la table.
- Ils ne nécessitent aucun espace disque.
INSERTetUPDATEles requêtes n'ont pas de surcharge car MySQL n'a pas besoin de les générer.
Les inconvénients
- MySQL doit les évaluer lors de la lecture d'une table, ce qui
SELECTralentit les requêtes les impliquant.
Colonnes générées stockées
Avantages
- MySQL peut les lire comme s'il s'agissait de colonnes normales, ce qui assure une récupération rapide sans surcharge.
Les inconvénients
- Lorsqu'il est ajouté à une nouvelle table, MySQL doit reconstruire toute la table.
INSERTouUPDATEvient avec un surcoût car MySQL doit les générer.- Ils nécessitent de l'espace disque.
Notez également que vous pouvez mélanger les colonnes VIRTUAL et STORED dans une table, et qu'elles prennent toutes deux en charge les index MySQL et les index secondaires. Mais comme expliqué dans la documentation officielle , les index secondaires sur les colonnes virtuelles occupent moins d'espace et de mémoire par rapport aux colonnes générées stockées. Ainsi, les colonnes générées virtuellement sont plus efficaces lorsqu'il s'agit d'index secondaires.
Pourquoi adopter les colonnes générées ?
Il existe plusieurs raisons d'adopter des colonnes générées, mais les trois ci-dessous sont les plus importantes.
- Ils vous fournissent des capacités de cache pour rendre vos requêtes plus rapides : les colonnes générées vous donnent la possibilité de définir des colonnes contenant des données utiles afin que vous puissiez ensuite les récupérer efficacement chaque fois que vous en avez besoin.
- Ils vous permettent de simplifier les expressions de requête : au lieu de rendre vos requêtes complexes, vous pouvez répartir la complexité sur les colonnes générées, puis les utiliser dans des opérations de filtrage simples.
- Ils vous permettent de définir des index fonctionnels : MySQL implémente les index fonctionnels sous forme de colonnes générées virtuelles cachées. En d'autres termes, les colonnes générées vous donnent la possibilité de définir des index efficaces et avancés impliquant des fonctions MySQL.
Colonnes générées par MySQL en action dans des exemples concrets
Voyons maintenant les colonnes générées en action dans quelques exemples issus de mon expérience en tant que développeur backend collaborant avec des startups data-driven .
Utiliser une colonne générée pour concaténer des colonnes pour des raisons de cohérence
Lors du développement de l'interface de votre application, vous remarquerez peut-être certains modèles dans la représentation des données. Par exemple, dans le sport, les joueurs d'une équipe sont généralement identifiés selon le format suivant :
first_name [first_carachter_middle_name.] last_name [(jersey_number)]Comme vous pouvez le voir, vous pouvez facilement générer ce champ de données en agrégeant les quatre colonnes avec la colonne générée suivante :
string_identifier VARCHAR(255) GENERATED ALWAYS AS(CONCAT(first_name, ' ', IF(middle_name IS NULL, '', CONCAT(LEFT(middle_name, 1), '. ')), last_name, IF(jersey_number IS NULL, '', CONCAT(' (', jersey_number, ')'))))STOREDCela produirait :
Cristiano Ronaldo (7)Lionel A. Messi (10)Dans ce cas, une colonne générée vous permet de normaliser le format des champs de données directement au niveau de la base de données. De plus, une colonne générée stockée évite la surcharge inévitable de la construction de ce champ chaque fois que vous en avez besoin.
Utilisation d'une colonne générée pour générer automatiquement des identifiants hachés
En règle générale, vous utilisez les ID de vos ressources dans l'URL de votre site Web ou des API REST pour récupérer les données dont vous avez besoin. Mais exposer publiquement vos identifiants pourrait poser un problème de sécurité. Cela est particulièrement vrai lorsque vous vous retrouvez à utiliser des identifiants auto-incrémentiels, qui sont faciles à prévoir et facilitent le scraping ou les attaques de robots.
Pour éviter cela, vous pouvez penser à masquer vos identifiants d'origine en utilisant des identifiants publics générés automatiquement, aléatoires et plus sécurisés. Vous pouvez y parvenir avec une colonne générée virtuellement en hachant vos ID comme suit :
public_id VARCHAR(40) GENERATED ALWAYS AS SHA1(CONCAT("PLAYER", id)) VIRTUALNotez que pour éviter de générer des valeurs de hachage connues, vous pouvez concaténer votre ID avec un mot-clé spécial. En savoir plus sur les fonctions de chiffrement et de compression MySQL ici .
Définition d'une colonne générée pour simplifier le filtrage des données
Lors du filtrage des données, certaines colonnes sont plus utiles que d'autres. De plus, vous devez souvent modifier la représentation des valeurs stockées dans les colonnes pour rendre le filtrage plus simple ou plus intuitif. Au lieu de le faire dans chaque requête de filtre, vous pouvez définir une colonne générée utile stockant les informations dont vous avez besoin pour effectuer le filtrage dans le format souhaité.
Par exemple, vous pouvez définir une colonne générée pour trouver plus facilement les joueurs d'une équipe de basket comme suit :
filter_string VARCHAR(255) GENERATED ALWAYS AS(CONCAT(first_name, ' ', last_name, IF(birthdate IS NULL, '', CONCAT(' ', DATE_FORMAT(birthdate, "%m-%d-%Y"))))STOREDUne telle colonne produirait ce qui suit :
LeBron James 12-30-1984Stephen Curry 03-14-1988Celui-ci contient des données utiles pour le filtrage et avec la date de naissance du joueur au format américain.
Colonnes générées vs déclencheurs
Comme expliqué précédemment, vous ne pouvez utiliser que des colonnes générées dans une table. De plus, ils ne peuvent impliquer que des fonctions immuables et MySQL génère leurs valeurs en réponse à une requête INSERTou . UPDATED'autre part, un déclencheur est un programme stocké que MySQL exécute automatiquement chaque fois qu'un événement INSERT, UPDATEou DELETEassocié à une table particulière se produit.
En d'autres termes, les déclencheurs peuvent impliquer plusieurs tables et toutes les fonctions MySQL. Cela en fait une solution plus complète par rapport aux colonnes générées. Dans le même temps, les déclencheurs MySQL sont intrinsèquement plus complexes à utiliser et à définir et également plus lents que les colonnes générées.
Dernières pensées
Les colonnes générées par MySQL sont sans aucun doute un excellent outil qui peut servir à de nombreuses utilisations et apporter plusieurs avantages. Leur adoption est simple et efficace, bien que peu de développeurs de bases de données les utilisent. Dans tous les cas, savoir comment en tirer parti peut devenir essentiel pour rendre votre application backend plus rapide, plus propre et moins complexe.
J'espère qu'en expliquant les cas d'utilisation des colonnes générées à travers des exemples concrets, j'ai pu vous aider à acquérir plus de connaissances dans le monde des bases de données, et jusqu'à la prochaine fois.
Source : https://betterprogramming.pub/a-complete-guide-to-generated-columns-in-mysql-90cfbdbeac64
