1656743280
В Laravel вам не нужно писать длинный код для экспорта ваших данных, для этого уже есть пакет — Laravel Excel.
Это позволяет экспортировать данные в различные форматы, такие как XLSX, CSV, XLS, HTML и т. д.
В этом руководстве я покажу, как вы можете экспортировать данные базы данных MySQL в формат CSV и Excel, используя пакет Laravel Excel в Laravel 8.
Требование –
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
включеноphp_xml
включеноphp_gd2
включеноphp_iconv
включеноphp_simplexml
включеноphp_xmlreader
включеноphp_zlib
включеноУстановите пакет с помощью композитора —
composer require maatwebsite/excel
Если вы получаете сообщение об ошибке при выполнении вышеуказанной команды, выполните следующую команду:
composer require psr/simple-cache:^1.0 maatwebsite/excel
После этого снова выполнить –
composer require maatwebsite/excel
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
];
Выполните команду -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Это создаст новый excel.php
файл в формате config/
.
Откройте .env
файл.
Укажите хост, имя базы данных, имя пользователя и пароль.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=
Employees
с помощью миграции и добавьте несколько записей.php artisan make:migration create_employees_table
database/migrations/
папку из корня проекта.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
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'
];
}
Я создаю 2 класса экспорта только для примера —
1. Класс EmployeesExport –
php artisan make:export EmployeesExport --model=Employees
app/Exports/EmployeesExport.php
файл.Класс имеет 2 метода —
ПРИМЕЧАНИЕ . Удалите методы 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'
];
}
}
routes/web.php
файл.Завершенный код
<?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');
EmployeesController
контроллер.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
файл.EmployeesExport
_ EmployeesByAgeExportExcel
index
представление.Excel::download()
.Он принимает 2 параметра —
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
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);
}
}
Создать 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>
Используйте конструктор в классе экспорта для обработки переданных параметров и использования его для получения данных.
В этом уроке я упомянул только 2 формата экспорта — CSV и Excel, но доступно больше форматов.
Источник: https://makitweb.com
1656739140
在 Laravel 中,您不必編寫長代碼來導出數據,已經有一個可用的包 - Laravel Excel。
它允許以各種格式導出數據,例如 XLSX、CSV、XLS、HTML 等。
在本教程中,我將展示如何使用 Laravel 8 中的 Laravel Excel 包以 CSV 和 Excel 格式導出 MySQL 數據庫數據。
要求 -
^7.2\|^8.0
^5.8
^1.21
^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/excel
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
];
運行命令——
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
這將excel.php
在config/
.
打開.env
文件。
指定主機、數據庫名稱、用戶名和密碼。
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=
Employees
使用遷移創建一個新表並添加一些記錄。php artisan make:migration create_employees_table
database/migrations/
現在,從項目根目錄導航到文件夾。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
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'
];
}
我創建 2 個導出類只是為了舉例 -
1.EmployeesExport 類——
php artisan make:export EmployeesExport --model=Employees
app/Exports/EmployeesExport.php
文件。類有 2 種方法——
注意 –如果您不想添加標題行,請刪除 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()
了允許在創建類實例時發送參數並使用它來檢索數據。$result
Array 後傳入$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'
];
}
}
routes/web.php
文件。完成的代碼
<?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');
EmployeesController
控制器。php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
文件。EmployeesExport
_ EmployeesByAgeExportExcel
index
視圖。Excel::download()
。它需要 2 個參數——
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
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);
}
}
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>
在 Export 類中使用構造函數來處理傳遞的參數並使用它來獲取數據。
在本教程中,我只提到了 2 種導出格式——CSV 和 Excel,但還有更多可用的格式。
1656735060
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.
Exigence -
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
activéephp_xml
activéephp_gd2
activéephp_iconv
activéephp_simplexml
activéephp_xmlreader
activéephp_zlib
activéeInstallez le package à l'aide de composer -
composer require maatwebsite/excel
Si 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/excel
Après cela, exécutez à nouveau -
composer require maatwebsite/excel
config/app.php
le fichier.Maatwebsite\Excel\ExcelServiceProvider::class
dans 'providers'
-'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];
'Excel' => Maatwebsite\Excel\Facades\Excel::class
dans 'aliases'
-'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];
Exécutez la commande -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Cela créera un nouveau excel.php
fichier au format config/
.
Ouvrir .env
le 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=
Employees
à l'aide de la migration et ajoutez des enregistrements.php artisan make:migration create_employees_table
database/migrations/
dossier à partir de la racine du projet.create_employees_table
et ouvrez-le.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();
});
}
php artisan migrate
Employees
modèle.php artisan make:model Employees
app/Models/Employees.php
le fichier.$fillable
proprié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'
];
}
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
app/Exports/EmployeesExport.php
le fichier.La classe a 2 méthodes -
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
app/Exports/EmployeesByAgeExport.php
le fichier.__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.$result
Array pass $result
in collect()
for return –return collect($result);
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'
];
}
}
routes/web.php
le fichier.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');
EmployeesController
un contrôleur.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
le fichier.EmployeesExport
_ EmployeesByAgeExportExcel
index
vue.Excel::download()
.Il faut 2 paramètres -
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
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);
}
}
Créer index.blade.php
un fichier dans resources/views/
le dossier.
Créez 2 éléments d'ancrage -
href
sur {{ route('employees.exportcsv') }}
pour l'exportation CSV.href
sur {{ 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>
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
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.
Requisito –
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
habilitadaphp_xml
habilitadaphp_gd2
habilitadaphp_iconv
habilitadaphp_simplexml
habilitadaphp_xmlreader
habilitadaphp_zlib
habilitadaInstale el paquete usando composer –
composer require maatwebsite/excel
Si recibe un error al ejecutar el comando anterior, ejecute el siguiente comando:
composer require psr/simple-cache:^1.0 maatwebsite/excel
Después de eso nuevamente ejecute –
composer require maatwebsite/excel
config/app.php
archivo.Maatwebsite\Excel\ExcelServiceProvider::class
en 'providers'
–'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];
'Excel' => Maatwebsite\Excel\Facades\Excel::class
en 'aliases'
–'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];
Ejecute el comando –
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Esto creará un nuevo excel.php
archivo en formato config/
.
Abrir .env
archivo.
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=
Employees
mediante la migración y agregue algunos registros.php artisan make:migration create_employees_table
database/migrations/
la carpeta desde la raíz del proyecto.create_employees_table
y ábralo.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();
});
}
php artisan migrate
Employees
modelo.php artisan make:model Employees
app/Models/Employees.php
archivo.$fillable
propiedad.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'
];
}
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
app/Exports/EmployeesExport.php
archivo.La clase tiene 2 métodos:
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
app/Exports/EmployeesByAgeExport.php
archivo.__construct()
para permitir el envío de un parámetro al crear una instancia de clase y usarlo para recuperar datos.$result
matriz para el retorno:$resultcollect()return collect($result);
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'
];
}
}
routes/web.php
archivo.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');
EmployeesController
controlador.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
archivo.EmployeesExport
_ EmployeesByAgeExportExcel
index
vista.Excel::download()
.Se necesitan 2 parámetros:
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
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);
}
}
Crear index.blade.php
archivo en resources/views/
carpeta.
Crea 2 elementos de anclaje –
href
para {{ route('employees.exportcsv') }}
exportar a CSV.href
para {{ 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>
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
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.
Requerimento -
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
habilitadaphp_xml
habilitadaphp_gd2
habilitadaphp_iconv
habilitadaphp_simplexml
habilitadaphp_xmlreader
habilitadaphp_zlib
habilitadaInstale o pacote usando o compositor –
composer require maatwebsite/excel
Se você estiver recebendo um erro ao executar o comando acima, execute o comando abaixo –
composer require psr/simple-cache:^1.0 maatwebsite/excel
Depois disso, execute novamente –
composer require maatwebsite/excel
config/app.php
arquivo.Maatwebsite\Excel\ExcelServiceProvider::class
em 'providers'
–'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];
'Excel' => Maatwebsite\Excel\Facades\Excel::class
em 'aliases'
–'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];
Execute o comando -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Isso criará um novo excel.php
arquivo em config/
.
Abrir .env
arquivo.
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=
Employees
usando a migração e adicione alguns registros.php artisan make:migration create_employees_table
database/migrations/
pasta da raiz do projeto.create_employees_table
e abra-o.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();
});
}
php artisan migrate
Employees
modelo.php artisan make:model Employees
app/Models/Employees.php
arquivo.$fillable
propriedade.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'
];
}
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
app/Exports/EmployeesExport.php
arquivo.A classe tem 2 métodos –
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
app/Exports/EmployeesByAgeExport.php
arquivo.__construct()
para permitir o envio de um parâmetro ao criar a instância da classe e usá-lo para recuperar dados.$result
Array passe $result
para collect()
retorno –return collect($result);
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'
];
}
}
routes/web.php
arquivo.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');
EmployeesController
controlador.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
arquivo.EmployeesExport
_ EmployeesByAgeExportExcel
index
visualização.Excel::download()
.Leva 2 parâmetros -
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
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);
}
}
Criar index.blade.php
arquivo na resources/views/
pasta.
Crie 2 elementos âncora –
href
como {{ route('employees.exportcsv') }}
para exportação CSV.href
como {{ 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>
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, XML, ODS и т. д.
В этом руководстве я покажу, как вы можете импортировать данные CSV и Excel в базу данных MySQL, используя пакет Laravel Excel в проекте Laravel 8.
Требование –
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
включеноphp_xml
включеноphp_gd2
включеноphp_iconv
включеноphp_simplexml
включеноphp_xmlreader
включеноphp_zlib
включеноУстановите пакет с помощью композитора —
composer require maatwebsite/excel
Если вы получаете сообщение об ошибке при выполнении вышеуказанной команды, выполните следующую команду:
composer require psr/simple-cache:^1.0 maatwebsite/excel
После этого снова выполнить –
composer require maatwebsite/excel
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
];
Запустите команду -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Это создаст новый excel.php
файл в формате config/
.
Откройте .env
файл.
Укажите хост, имя базы данных, имя пользователя и пароль.
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=
Employees
с помощью миграции и добавьте несколько записей.php artisan make:migration create_employees_table
database/migrations/
папку из корня проекта.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
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'
];
}
Я создаю 2 класса импорта только для примера —
1. Класс EmployeesImport —
php artisan make:import EmployeesImport --model=Employees
app/Imports/EmployeesImport.php
файл.$row
массива и вставляет запись, если идентификатор электронной почты не существует в employees
таблице, в противном случае возвращает значение null.ПРИМЕЧАНИЕ. – Этот класс импорта начинает чтение записей с 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
файл.$rows
данных массива. В случае успешной проверки вставьте запись, если идентификатор электронной почты не существует в employees
таблице, в противном случае верните ошибку.ПРИМЕЧАНИЕ. – Если
WithHeadingRow
реализовано, то$rows
массивы содержат заголовки строк в качестве имен ключей вместо индекса.
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;
}
}
routes/web.php
файл.Завершенный код
<?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');
EmployeesController
контроллер.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
файл.EmployeesImport
_ Employees2ImportExcel
index
представление.Excel::import()
.Передать 2 параметра –
EmployeesImport
Экземпляр класса.Excel::import()
.Передать 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!');
}
}
Создать 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>
Обязательно проверьте запись перед вставкой или обновлением записей в классе Import. Укажите WithHeadingRow
в классе импорта, только если файл импорта имеет строку заголовка.
Источник: https://makitweb.com
1656718320
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.
Yêu cầu -
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
Đã bật tiện ích mở rộng PHP php_xml
Đã bật tiện ích mở rộng PHP php_gd2
Đã bật tiện ích mở rộng PHP php_iconv
Đã bật tiện ích mở rộng PHP php_simplexml
Đã bật tiện ích mở rộng PHP php_xmlreader
Đã bật tiện ích mở rộng PHP php_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/excel
Nế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/excel
Sau đó, một lần nữa thực thi -
composer require maatwebsite/excel
config/app.php
tệp.Maatwebsite\Excel\ExcelServiceProvider::class
vào 'providers'
-'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];
'Excel' => Maatwebsite\Excel\Facades\Excel::class
vào 'aliases'
-'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];
Chạy lệnh -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Điều này sẽ tạo một excel.php
tệp mới trong config/
.
Mở .env
tệ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=
Employees
bằ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
database/migrations/
thư mục từ thư mục gốc của dự án.create_employees_table
và mở nó.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();
});
}
php artisan migrate
Employees
mô hình.php artisan make:model Employees
app/Models/Employees.php
tệp.$fillable
tí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'
];
}
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
app/Exports/EmployeesExport.php
tệp.Lớp có 2 phương thức -
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
app/Exports/EmployeesByAgeExport.php
tệp.__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.$result
Array , hãy trả $result
về collect()
-return collect($result);
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'
];
}
}
routes/web.php
tệp.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');
EmployeesController
Bộ điều khiển.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
tệp.EmployeesExport
_ EmployeesByAgeExportExcel
index
chế độ xem.Excel::download()
.Nó có 2 tham số -
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
return Excel::download(new EmployeesExport, $file_name);
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);
}
}
Tạo index.blade.php
tệp trong resources/views/
thư mục.
Tạo 2 phần tử neo -
href
thành để {{ route('employees.exportcsv') }}
xuất CSV.href
thà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>
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
批量導入數據是將存儲在文件中的記錄添加到數據庫的有用功能。數據可以以 CSV、Excel、XML、ODS 等格式存儲。
在本教程中,我將展示如何在 Laravel 8 項目中使用 Laravel Excel 包將 CSV 和 Excel 數據導入 MySQL 數據庫。
要求 -
^7.2\|^8.0
^5.8
^1.21
^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/excel
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
];
運行命令——
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
這將excel.php
在config/
.
打開.env
文件。
指定主機、數據庫名稱、用戶名和密碼。
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=
Employees
使用遷移創建一個新表並添加一些記錄。php artisan make:migration create_employees_table
database/migrations/
現在,從項目根目錄導航到文件夾。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
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'
];
}
我創建 2 個導入類只是為了舉例 -
1.EmployeesImport 類——
php artisan make:import EmployeesImport --model=Employees
app/Imports/EmployeesImport.php
文件。$row
並插入一條記錄,如果電子郵件 ID 不存在於employees
表中,否則返回 null。注意——這個導入類從第一行開始讀取記錄。我解釋瞭如何在下一個 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
文件。$rows
數組數據。如果成功驗證,則如果表中不存在電子郵件 ID,則插入記錄,employees
否則返回錯誤。注 —如果
WithHeadingRow
實現,則$rows
數組包含行標題作為鍵名而不是索引。
帶有標題行的 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;
}
}
routes/web.php
文件。完成的代碼
<?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');
EmployeesController
控制器。php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
文件。EmployeesImport
_ Employees2ImportExcel
index
視圖。Excel::import()
。傳遞 2 個參數 –
EmployeesImport
類實例。Excel::import()
.傳遞 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!');
}
}
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>
確保在 Import 類中插入或更新記錄之前驗證記錄。WithHeadingRow
僅當導入文件具有標題行時才在 Import 類中指定。
1656714120
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.
Yêu cầu -
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
Đã bật tiện ích mở rộng PHP php_xml
Đã bật tiện ích mở rộng PHP php_gd2
Đã bật tiện ích mở rộng PHP php_iconv
Đã bật tiện ích mở rộng PHP php_simplexml
Đã bật tiện ích mở rộng PHP php_xmlreader
Đã bật tiện ích mở rộng PHP php_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/excel
Nế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/excel
Sau đó, một lần nữa thực thi -
composer require maatwebsite/excel
config/app.php
tệp.Maatwebsite\Excel\ExcelServiceProvider::class
vào 'providers'
-'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];
'Excel' => Maatwebsite\Excel\Facades\Excel::class
vào 'aliases'
-'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];
Chạy lệnh -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Điều này sẽ tạo một excel.php
tệp mới trong config/
.
Mở .env
tệ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=
Employees
bằ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
database/migrations/
thư mục từ thư mục gốc của dự án.create_employees_table
và mở nó.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();
});
}
php artisan migrate
Employees
mô hình.php artisan make:model Employees
app/Models/Employees.php
tệp.$fillable
tí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'
];
}
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
app/Imports/EmployeesImport.php
tệp.$row
Mảng và chèn bản ghi nếu id email không tồn tại trong employees
bảng, nếu không sẽ trả về null.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
app/Imports/Employees2Import.php
tệp.$rows
dữ 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 trong employees
bảng nếu không sẽ trả về lỗi.LƯU Ý - Nếu
WithHeadingRow
được triển khai thì$rows
Mảng chứa tiêu đề hàng dưới dạng tên khóa thay vì chỉ mục.
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;
}
}
routes/web.php
tệp.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');
EmployeesController
Bộ điều khiển.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
tệp.EmployeesImport
_ Employees2ImportExcel
index
chế độ xem.Excel::import()
.Chuyển 2 tham số -
EmployeesImport
Cá thể lớp.Excel::import()
.Chuyển 2 tham số -
Employees2Import
Cá thể lớp.public/employees.xlsx
thư 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!');
}
}
Tạo index.blade.php
tệp trong resources/views/
thư mục.
Tạo 2 <form>
-
<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.<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>
Đả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 WithHeadingRow
trong lớp Nhập nếu tệp nhập có hàng tiêu đề.
Nguồn: https://makitweb.com
1656713580
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.
Exigence -
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
activéephp_xml
activéephp_gd2
activéephp_iconv
activéephp_simplexml
activéephp_xmlreader
activéephp_zlib
activéeInstallez le package à l'aide de composer -
composer require maatwebsite/excel
Si 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/excel
Après cela, exécutez à nouveau –
composer require maatwebsite/excel
config/app.php
le fichier.Maatwebsite\Excel\ExcelServiceProvider::class
dans 'providers'
-'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];
'Excel' => Maatwebsite\Excel\Facades\Excel::class
dans 'aliases'
-'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];
Exécutez la commande -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Cela créera un nouveau excel.php
fichier au format config/
.
Ouvrir .env
le 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=
Employees
à l'aide de la migration et ajoutez des enregistrements.php artisan make:migration create_employees_table
database/migrations/
dossier à partir de la racine du projet.create_employees_table
et ouvrez-le.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();
});
}
php artisan migrate
Employees
modèle.php artisan make:model Employees
app/Models/Employees.php
le fichier.$fillable
proprié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'
];
}
Je crée 2 classes d'importation juste à titre d'exemple -
1. Classe EmployeesImport –
php artisan make:import EmployeesImport --model=Employees
app/Imports/EmployeesImport.php
le fichier.$row
Array et insère un enregistrement si l'identifiant de messagerie n'existe pas dans la employees
table, sinon renvoie null.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
app/Imports/Employees2Import.php
le fichier.$rows
les données du tableau. Si la validation est réussie, insérez un enregistrement si l'identifiant de messagerie n'existe pas dans la employees
table, sinon renvoyez une erreur.NOTE – Si
WithHeadingRow
est implémenté, alors$rows
Arrays contient l'en-tête de ligne comme noms de clé au lieu d'index.
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;
}
}
routes/web.php
le fichier.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');
EmployeesController
un contrôleur.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
le fichier.EmployeesImport
_ Employees2ImportExcel
index
vue.Excel::import()
.Passer 2 paramètres –
EmployeesImport
Instance de classe.Excel::import()
.Passer 2 paramètres –
Employees2Import
Instance de classe.public/employees.xlsx
le 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!');
}
}
Créer index.blade.php
un fichier dans resources/views/
le dossier.
Créer 2 <form>
–
<form >
action définie sur {{ route('employees.importdata') }}
. Créez un élément de fichier et un bouton d'envoi.<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>
Assurez-vous de valider l'enregistrement avant d'insérer ou de mettre à jour des enregistrements dans la classe d'importation. Spécifiez WithHeadingRow
dans Classe d'importation uniquement si le fichier d'importation comporte une ligne d'en-tête.
Source: https://makitweb.com
1656705960
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.
Requisito –
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
habilitadaphp_xml
habilitadaphp_gd2
habilitadaphp_iconv
habilitadaphp_simplexml
habilitadaphp_xmlreader
habilitadaphp_zlib
habilitadaInstale el paquete usando composer –
composer require maatwebsite/excel
Si recibe un error al ejecutar el comando anterior, ejecute el siguiente comando:
composer require psr/simple-cache:^1.0 maatwebsite/excel
Después de eso nuevamente ejecute –
composer require maatwebsite/excel
config/app.php
archivo.Maatwebsite\Excel\ExcelServiceProvider::class
en 'providers'
–'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];
'Excel' => Maatwebsite\Excel\Facades\Excel::class
en 'aliases'
–'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];
Ejecute el comando –
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Esto creará un nuevo excel.php
archivo en formato config/
.
Abrir .env
archivo.
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=
Employees
mediante la migración y agregue algunos registros.php artisan make:migration create_employees_table
database/migrations/
la carpeta desde la raíz del proyecto.create_employees_table
y ábralo.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();
});
}
php artisan migrate
Employees
modelo.php artisan make:model Employees
app/Models/Employees.php
archivo.$fillable
propiedad.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'
];
}
Estoy creando 2 clases de importación solo por ejemplo:
1. Clase de importación de empleados:
php artisan make:import EmployeesImport --model=Employees
app/Imports/EmployeesImport.php
archivo.$row
Array e inserte un registro si la identificación del correo electrónico no existe en la employees
tabla; de lo contrario, devuelva nulo.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
app/Imports/Employees2Import.php
archivo.$rows
los datos de la matriz. Si se valida con éxito, inserte el registro si la identificación del correo electrónico no existe en la employees
tabla; de lo contrario, devolverá un error.NOTA: si
WithHeadingRow
se implementa,$rows
Arrays contiene encabezados de fila como nombres clave en lugar de índice.
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;
}
}
routes/web.php
archivo.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');
EmployeesController
controlador.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
archivo.EmployeesImport
_ Employees2ImportExcel
index
vista.Excel::import()
.Pase 2 parámetros –
EmployeesImport
instancia de clase.Excel::import()
.Pase 2 parámetros –
Employees2Import
instancia de clase.public/employees.xlsx
carpeta).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!');
}
}
Crear index.blade.php
archivo en resources/views/
carpeta.
Crear 2 <form>
-
<form >
acción fijada a {{ route('employees.importdata') }}
. Cree un elemento de archivo y un botón de envío.<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>
Asegúrese de validar el registro antes de insertar o actualizar registros en la clase Importar. Especifique WithHeadingRow
en Clase de importación solo si el archivo de importación tiene una fila de encabezado.
Fuente: https://makitweb.com
1656702180
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.
Requerimento -
^7.2\|^8.0
^5.8
^1.21
^1.0
php_zip
habilitadaphp_xml
habilitadaphp_gd2
habilitadaphp_iconv
habilitadaphp_simplexml
habilitadaphp_xmlreader
habilitadaphp_zlib
habilitadaInstale o pacote usando o compositor –
composer require maatwebsite/excel
Se você estiver recebendo um erro ao executar o comando acima, execute o comando abaixo –
composer require psr/simple-cache:^1.0 maatwebsite/excel
Depois disso, execute novamente –
composer require maatwebsite/excel
config/app.php
arquivo.Maatwebsite\Excel\ExcelServiceProvider::class
em 'providers'
–'providers' => [
....
....
....
Maatwebsite\Excel\ExcelServiceProvider::class
];
'Excel' => Maatwebsite\Excel\Facades\Excel::class
em 'aliases'
–'aliases' => [
....
....
....
'Excel' => Maatwebsite\Excel\Facades\Excel::class
];
Execute o comando -
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Isso criará um novo excel.php
arquivo em config/
.
Abrir .env
arquivo.
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=
Employees
usando a migração e adicione alguns registros.php artisan make:migration create_employees_table
database/migrations/
pasta da raiz do projeto.create_employees_table
e abra-o.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();
});
}
php artisan migrate
Employees
modelo.php artisan make:model Employees
app/Models/Employees.php
arquivo.$fillable
propriedade.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'
];
}
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
app/Imports/EmployeesImport.php
arquivo.$row
Array e insere um registro se o ID do email não existir na employees
tabela, caso contrário, retorna null.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
app/Imports/Employees2Import.php
arquivo.$rows
os dados do Array. Se validado com sucesso, insira o registro se o ID do email não existir na employees
tabela, caso contrário, retorne o erro.OBSERVAÇÃO – Se
WithHeadingRow
for implementado,$rows
Arrays conterá cabeçalho de linha como nomes de chave em vez de índice.
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;
}
}
routes/web.php
arquivo.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');
EmployeesController
controlador.php artisan make:controller EmployeesController
app/Http/Controllers/EmployeesController.php
arquivo.EmployeesImport
_ Employees2ImportExcel
index
visualização.Excel::import()
.Passe 2 parâmetros –
EmployeesImport
Instância de classe.Excel::import()
.Passe 2 parâmetros –
Employees2Import
Instância de classe.public/employees.xlsx
pasta).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!');
}
}
Criar index.blade.php
arquivo na resources/views/
pasta.
Criar 2 <form>
-
<form >
ação definida para {{ route('employees.importdata') }}
. Crie um elemento de arquivo e um botão de envio.<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>
Certifique-se de validar o registro antes de inserir ou atualizar registros na classe Import. Especifique WithHeadingRow
na classe Import apenas se o arquivo de importação tiver uma linha de cabeçalho.
Fonte: https://makitweb.com
1656696660
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
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
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 INSERT
et . UPDATE
Cette 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' JOIN
instructions.
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 TABLE
or 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_expression
dé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 AS
des mots- GENERATED ALWAYS
clé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 VIRTUAL
ou 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 TABLE
requê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_name
colonne stockera automatiquement la concaténation des colonnes first_name
et .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.
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 BEFORE
le déclenchement d'une requête. En d'autres termes, une colonne générée virtuellement n'occupe aucun espace de stockage.
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.
Avantages
INSERT
et UPDATE
les requêtes n'ont pas de surcharge car MySQL n'a pas besoin de les générer.Les inconvénients
SELECT
ralentit les requêtes les impliquant.Avantages
Les inconvénients
INSERT
ou UPDATE
vient avec un surcoût car MySQL doit les générer.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.
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 .
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, ')'))))STORED
Cela 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)) VIRTUAL
Notez 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"))))STORED
Une telle colonne produirait ce qui suit :
LeBron James 12-30-1984Stephen Curry 03-14-1988
Celui-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 INSERT
ou . UPDATE
D'autre part, un déclencheur est un programme stocké que MySQL exécute automatiquement chaque fois qu'un événement INSERT
, UPDATE
ou DELETE
associé à 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