PHP

PHP

PHP is a widely used, high-level, dynamic, object-oriented and interpreted scripting language primarily designed for server-side web development.

Dashboard Plugin: Build Beautiful Dashboards for Your Cakes

Dashboard for CakePHP

Build beautiful dashboards for your cakes!

This is an unstable repository and should be treated as an alpha.

sample

Requirements

  • CakePHP 3.0.0 or greater.
  • PHP 5.4.16 or greater
  • SQLite or another database driver that CakePHP can talk to (defaults to SQLite).

Install

composer require gourmet/dashboard:*

or by adding this package to your project's composer.json:

"require": {
    "gourmet/dashboard": "*"
}

Now, enable the plugin in your bootstrap.php (including bootstrap and routes):

Plugin::load('Gourmet/Dashboard', ['bootstrap' => true, 'routes' => true]);

You will also need to symlink the assets:

FromTo
plugins/Gourmet/Dashboard/webroot/assets/dashboard.csswebroot/assets/dashboard.css
plugins/Gourmet/Dashboard/webroot/assets/dashboard.jswebroot/assets/dashboard.js
plugins/Gourmet/Dashboard/webroot/fontwebroot/font/dashboard

To preview the sample dashboard, you will need to also copy some sample widgets to your app:

FromTo
plugins/Gourmet/Dashboard/samplessrc/DashboardWidget

That's it! You can now access the sample dashboard at: http://localhost/gourmet/dashboard/index

Documentation

Database Configuration

By default, Dashboard will store event data into a SQLite database in your application's tmp directory. If you cannot install pdo_sqlite, you can configure Dashboard to use a different database by defining a gourmet_dashboard connecting in your config/app.php file.

Widgets

Built-in Widgets

There are several built-in widgets, they are:

  • Clock
  • Comments
  • Graph
  • Iframe
  • Image
  • List
  • Meter
  • Number
  • Text

Configuration

There is no configuration at this time. Options will be coming soon.

Developing Your Own Widgets

You can create your own custom widgets for Gourmet/Dashboard. Until this is better documented, please refer to the sample widgets included.

Credits

Author: Gourmet
Source Code: https://github.com/gourmet/dashboard 
License: MIT license

#php #dashboard #cakephp 

Dashboard Plugin: Build Beautiful Dashboards for Your Cakes

A CakePHP Plugin to Convert Your Price From A Currency To another

cakephp-currency-converter V 3.1.0    

A CakePHP 3.x Plugin to

  • convert price from a currency to another one
  • get rate from a currency to another one

Background

Is very frequently that inside your sites you need to convert your price or simply get a currency rate from a currency to another one. This Plugin convert your price or provide to you a rate from a currency to another one currency in every currency of the world.

It works with free.currencyconverterapi.com and can store currency rates inside a database if you want to. In case you want to use database to store currency rates inside, DO NOT FORGET to create the table to store rates into (sql schema is given to you in config/table.txt).

You can configure refresh key in configuration settings to get new conversion from free.currencyconverterapi.com, if you don't want to use database, rates are updated every time with the current conversion.

If you have set to use database, for example, you can set to update currency rates every hour, this library get the currency conversion from free.currencyconverterapi.com the first time, store it inside the database and for the next hour it takes conversion rates from the database if exist. In this way reduce the request time to convert and every hour currency rates are updated.

If you haven't set to use database, instead, every time you call the library it makes a request to free.currencyconverterapi.com and gets the actual conversion rate. This solution is great if you haven't a lot of request. Instead, if you have a lot of request conversions is better to use the database configuration.


Requirements

  • CakePHP 3.x
  • PHP >=5.6

For cakephp 2.x you can download version 1.1.4


Installation & Docs

Installation

Installing the Plugin is pretty much as with every other CakePHP Plugin.

How to include

composer require "alessandrominoccheri/cakephp-currency-converter"

Details @ https://packagist.org/packages/alessandrominoccheri/cakephp-currency-converter

Enable plugin

If you haven't use composer to include the Plugin in your App make sure the plugin is enable in your config/bootstrap.php file:

Plugin::load('CurrencyConverter');

If you are already using Plugin::loadAll();, then this is not necessary.

Documentation

CurrencyConverter component

Convert price or simply get currency rate from currency to another one.

Adding the component

In the initialize method of your Controller load the component:

$this->loadComponent('CurrencyConverter.CurrencyConverter', $config);

Setting

By default component will use this following settings:

$config = [
	'database' => true, // (true|false) precise if component have to store currency rate in database
	'refresh' => 24, // (int) precise interval time for refrshing currency rate in database, in case you are using database
	'decimal' => 2 // (int) number of decimal to use for the converted price
	'round' => false // (false|int) number to divise 1 and get the sup step to round price to (eg: 4 for 0.25 step)
];
  • database (mention if component have to store currency rate in database)
  • refresh (time interval for refreshing currency rate in database)
  • decimal (number of decimal to use when formatting price)
  • round (number to divise 1 and get the sup step to round price to eg: 4 for 0.25 step)

You can change default setting and write your own.

Database

If you want to use database to store currency rate DO NOT FORGET to create the table wich will store it. SQL schema is provided in table.txt file in config folder. Note that when using database, The Plugin store rate in session to avoid requesting database at each time we want to use the component. Do not be worry even rate stored in session the Plugin keep refresh rate when it has to do.

Convert price

You can convert price from a currency to another one in your Controller. To do it you have to use convert method of the Component. Assuming you want to convert a price from EUR to GBP:

$price_converted = $this->CurrencyConverter->convert($price_to_convert, $from, $to);
  • price_to_convert: is the price to convert (Example: 10.5, '10.5' or '10,5').
  • from: is the actual price currency (Example: EUR, GBP)
  • to: is the currency that you want to convert your price to (Example: EUR, GBP)

To get a available list of currency code you can check here List of available currency code

Note: If plugin can't reach rate out from API the converted price will be 0.

Get currency rate

You can get a currency rate from a currency to another one in your Controller. To do it you have to use rate method of the Component. Assuming you want to get the rate from EUR currency to GBP currency:

$price_converted = $this->CurrencyConverter->rate($from, $to);
  • from: is the actual price currency (Example: EUR, GBP)
  • to: is the currency that you want to convert your price to (Example: EUR, GBP)

To get a available list of currency code you can check here List of available currency code

Note: If plugin can't reach rate out from API the rate, rate will be null.

CurrencyConverter helper

Convert price or simply get currency rate from currency to another one.

Adding the helper

In the initialize method of your AppView class load the component:

$this->loadHelper('CurrencyConverter.CurrencyConverter', $config);

Setting

By default component will use this following settings:

$config = [
	'database' => true, // (true|false) precise if component have to store currency rate in database
	'refresh' => 24, // (int) precise interval time for refrshing currency rate in database, in case you are using database
	'decimal' => 2 // (int) number of decimal to use for the converted price
	'round' => false // (false|int) number to divise 1 and get the sup step to round price to (eg: 4 for 0.25 step)
];
  • database (mention if component have to store currency rate in database)
  • refresh (time interval for refreshing currency rate in database)
  • decimal (number of decimal to use when formatting price)
  • round (number to divise 1 and get the sup step to round price to eg: 4 for 0.25 step)

You can change default setting and write your own.

Database

If you want to use database to store currency rate DO NOT FORGET to create the table wich will store it. SQL schema is provided in table.txt file in config folder. Note that when using database, The Plugin store rate in session to avoid requesting database at each time we want to use the helper. Do not be worry even rate stored in session the Plugin keep refresh rate when it has to do.

Convert price

You can convert price from a currency to another one in your View. To do it you have to use convert method of the Helper. Assuming you want to convert a price from EUR to GBP:

$price_converted = $this->CurrencyConverter->convert($price_to_convert, $from, $to);
  • price_to_convert: is the price to convert (Example: 10.5, '10.5' or '10,5').
  • from: is the actual price currency (Example: EUR, GBP)
  • to: is the currency that you want to convert your price to (Example: EUR, GBP)

To get a available list of currency code you can check here List of available currency code

Note: If plugin can't reach rate out from API the converted price will be 0.

Get currency rate

You can get a currency rate from a currency to another one in your View. To do it you have to use rate method of the Helper. Assuming you want to get the rate from EUR currency to GBP currency:

$price_converted = $this->CurrencyConverter->rate($from, $to);
  • from: is the actual price currency (Example: EUR, GBP)
  • to: is the currency that you want to convert your price to (Example: EUR, GBP)

To get a available list of currency code you can check here List of available currency code

Note: If plugin can't reach rate out from API the rate, rate will be null.

Contributing

Your help is greatly appreciated.


TODOs

  • Use a second one API to convert price (ex fixer.io) and purpose wanted API to convert price in Component and Helper settings.

Author: AlessandroMinoccheri
Source Code: https://github.com/AlessandroMinoccheri/cakephp-currency-converter 
License: MIT license

#php #cakephp #convert 

A CakePHP Plugin to Convert Your Price From A Currency To another

Как экспортировать базу данных MySQL в формат CSV и Excel в Laravel 8

В Laravel вам не нужно писать длинный код для экспорта ваших данных, для этого уже есть пакет — Laravel Excel.

Это позволяет экспортировать данные в различные форматы, такие как XLSX, CSV, XLS, HTML и т. д.

В этом руководстве я покажу, как вы можете экспортировать данные базы данных MySQL в формат CSV и Excel, используя пакет Laravel Excel в Laravel 8.


1. Установить пакет

Требование –

  • PHP: ^7.2\|^8.0
  • Ларавель: ^5.8
  • Таблица Php: ^1.21
  • PSR/простой кэш: ^1.0
  • Расширение PHP  php_zip включено
  • Расширение PHP  php_xml включено
  • Расширение PHP  php_gd2 включено
  • Расширение PHP  php_iconv включено
  • Расширение PHP  php_simplexml включено
  • Расширение PHP  php_xmlreader включено
  • Расширение PHP  php_zlib включено

Установите пакет с помощью композитора —

composer require maatwebsite/excel

Если вы получаете сообщение об ошибке при выполнении вышеуказанной команды, выполните следующую команду:

composer require psr/simple-cache:^1.0 maatwebsite/excel

После этого снова выполнить –

composer require maatwebsite/excel

2. Обновите app.php

  • Откройте  config/app.php файл.
  • Добавьте следующее Maatwebsite\Excel\ExcelServiceProvider::class в  'providers' -
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • Добавьте следующее  'Excel' => Maatwebsite\Excel\Facades\Excel::class в  'aliases' -
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3. Опубликовать пакет

Выполните команду -

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

Это создаст новый excel.phpфайл в формате config/.


4. Конфигурация базы данных

Откройте .envфайл.

Укажите хост, имя базы данных, имя пользователя и пароль.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Создать таблицу

  • Создайте новую таблицу Employeesс помощью миграции и добавьте несколько записей.
php artisan make:migration create_employees_table
  • Теперь перейдите в database/migrations/папку из корня проекта.
  • Найдите файл PHP, оканчивающийся на , create_employees_tableи откройте его.
  • Определите структуру таблицы в up()методе.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Запустите миграцию –
php artisan migrate
  • Таблица создана и добавлены в нее записи.

6. Модель

  • Создать Employeesмодель.
php artisan make:model Employees
  • Откройте  app/Models/Employees.php файл.
  • Укажите массово назначаемые атрибуты модели — имя пользователя, имя, адрес электронной почты и возраст, используя $fillableсвойство.

Завершенный код

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Создайте класс экспорта

Я создаю 2 класса экспорта только для примера —

1. Класс EmployeesExport –

php artisan make:export EmployeesExport --model=Employees
  • Откройте  app/Exports/EmployeesExport.php файл.

Класс имеет 2 метода —

  • collection() — Загрузить экспортные данные. Здесь вы можете либо -
    • Вернуть все записи.
    • Верните определенные столбцы или измените возвращаемый ответ, что я сделал в следующем классе экспорта.
  • заголовки () — указать строку заголовка.

ПРИМЕЧАНИЕ . Удалите методы headers(), если вы не хотите добавлять строку заголовка.

Завершенный код

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesExport implements FromCollection, WithHeadings
{

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 1. Export all data
        // return Employees::all();

        ## 2. Export specific columns
        return Employees::select('id','username','name')->get();

    }

    public function headings(): array
    {
        return [
          '#',
          'Username',
          'Name'
        ];
    }
}

2. Класс EmployeesByAgeExport –

php artisan make:export EmployeesByAgeExport --model=Employees
  • Откройте  app/Exports/EmployeesByAgeExport.php файл.
  • В этом классе я добавил, __construct()чтобы разрешить отправку параметра при создании экземпляра класса и использовать его для извлечения данных.
  • После инициализации $resultмассива передать $resultдля collect()возврата -return collect($result);
  • Упомяните имена строк заголовков в heading()методе.

Завершенный код

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesByAgeExport implements FromCollection, WithHeadings
{
    private $agegreaterthan;

    public function __construct($age=0) 
    {
        $this->agegreaterthan = $age;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 3. Conditional export and customize result
        $records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();

        $result = array();
        foreach($records as $record){
           $result[] = array(
              'id'=>$record->id,
              'username' => $record->username,
              'name' => $record->name,
              'email' => $record->email,
              'age' => $record->age,
              'status' => 1 // Custom data
           );
        }

        return collect($result);
    }

    public function headings(): array
    {
       return [
         '#',
         'Username',
         'Name',
         'Email',
         'Age',
         'Status'
       ];
    }
}

8. Маршрут

  • Откройте  routes/web.php файл.
  • Определить 4 маршрута –
    • / —  Загрузить представление индекса.
    • employee/exportcsv — Экспорт данных в формате CSV.
    • employee/exportexcel — Экспорт данных в формате Excel.
    • employee/exportbyagecsv — POST-маршрут для экспорта данных по возрасту.

Завершенный код

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');

9. Контроллер

  • Создать  EmployeesController контроллер.
php artisan make:controller EmployeesController
  • Откройте  app/Http/Controllers/EmployeesController.php файл.
  • Импорт и . EmployeesExportEmployeesByAgeExportExcel
  • Создайте 4 метода –
    • index() — Загрузить indexпредставление.
    • exportCSV() —  для вызова экспорта данных Excel::download().

Он принимает 2 параметра —

  1. Экземпляр класса.
  2. Имя файла с расширением.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesExport, $file_name);
  • exportExcel() — используйте тот же код выше и измените расширение файла на .xlsx.
  • exportByAgeCSV () — считывает значение возраста POST и передает его с EmployeesByAgeExport($age)экземпляром.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesByAgeExport($age), $file_name);

Завершенный код

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;

class EmployeesController extends Controller
{
    public function index(){
       return view('index');
    }

    // CSV Export
    public function exportCSV(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Excel Export
    public function exportExcel(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Conditional Export (csv)
    public function exportByAgeCSV(Request $request){

       $age = $request->age;

       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesByAgeExport($age), $file_name);
    }
}

10. Просмотр

Создать  index.blade.php файл в  resources/views/ папке.

Создайте 2 элемента привязки –

  1. Установите hrefдля {{ route('employees.exportcsv') }}экспорта в CSV.
  2. Установите 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> &nbsp;&nbsp;
      <a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>

      <form method='post' action="{{ route('employees.exportbyagecsv') }}">
         @csrf
         <div class="mb-3">
            <label for="age" class="form-label">Age</label>
            <input type="number" class="form-control" id="age" name="age" value="0">
         </div>

         <button type="submit" class="btn btn-success">Export</button>
      </form>
   </div>

</body>
</html>

11. Демо

Посмотреть демо


12. Заключение

Используйте конструктор в классе экспорта для обработки переданных параметров и использования его для получения данных.

В этом уроке я упомянул только 2 формата экспорта — CSV и Excel, но доступно больше форматов.

Источник: https://makitweb.com

#php #laravel #mysql 

Как экспортировать базу данных MySQL в формат CSV и Excel в Laravel 8
鈴木  治

鈴木 治

1656739140

如何在 Laravel 8 中以 CSV 和 Excel 格式導出 MySQL 數據庫數據

在 Laravel 中,您不必編寫長代碼來導出數據,已經有一個可用的包 - Laravel Excel。

它允許以各種格式導出數據,例如 XLSX、CSV、XLS、HTML 等。

在本教程中,我將展示如何使用 Laravel 8 中的 Laravel Excel 包以 CSV 和 Excel 格式導出 MySQL 數據庫數據。


1.安裝包

要求 -

  • PHP: ^7.2\|^8.0
  • 拉拉維爾: ^5.8
  • Php電子表格: ^1.21
  • psr/簡單緩存: ^1.0
  • php_zip 啟用PHP 擴展 
  • php_xml 啟用PHP 擴展 
  • php_gd2 啟用PHP 擴展 
  • php_iconv 啟用PHP 擴展 
  • php_simplexml 啟用PHP 擴展 
  • php_xmlreader 啟用PHP 擴展 
  • php_zlib 啟用PHP 擴展 

使用 composer 安裝包——

composer require maatwebsite/excel

如果您在執行上述命令時遇到錯誤,請執行以下命令 -

composer require psr/simple-cache:^1.0 maatwebsite/excel

之後再次執行 -

composer require maatwebsite/excel

2.更新app.php

  • 打開 config/app.php 文件。
  • 將以下內容添加Maatwebsite\Excel\ExcelServiceProvider::class 到 'providers' –
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • 將以下內容添加 'Excel' => Maatwebsite\Excel\Facades\Excel::class 到 'aliases' –
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3.發布包

運行命令——

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

這將excel.phpconfig/.


4. 數據庫配置

打開.env文件。

指定主機、數據庫名稱、用戶名和密碼。

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. 創建表

  • Employees使用遷移創建一個新表並添加一些記錄。
php artisan make:migration create_employees_table
  • database/migrations/現在,從項目根目錄導航到文件夾。
  • 找到一個以 結尾的 PHP 文件create_employees_table並打開它。
  • 在方法中定義表結構up()
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • 運行遷移 -
php artisan migrate
  • 表已創建並在其中添加一些記錄。

6. 型號

  • 創建Employees模型。
php artisan make:model Employees
  • 打開 app/Models/Employees.php 文件。
  • 使用該屬性指定可批量分配的模型屬性——用戶名、姓名、電子郵件和年齡$fillable

完成的代碼

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. 創建導出類

我創建 2 個導出類只是為了舉例 -

1.EmployeesExport 類——

php artisan make:export EmployeesExport --model=Employees
  • 打開 app/Exports/EmployeesExport.php 文件。

類有 2 種方法——

  • collection() –加載導出數據。在這裡,您可以——
    • 返回所有記錄。
    • 返回特定列或修改我在下一個導出類中所做的返迴響應。
  • headings() –指定標題行。

注意 –如果您不想添加標題行,請刪除 headings() 方法。

完成的代碼

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesExport implements FromCollection, WithHeadings
{

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 1. Export all data
        // return Employees::all();

        ## 2. Export specific columns
        return Employees::select('id','username','name')->get();

    }

    public function headings(): array
    {
        return [
          '#',
          'Username',
          'Name'
        ];
    }
}

2.EmployeesByAgeExport 類——

php artisan make:export EmployeesByAgeExport --model=Employees
  • 打開 app/Exports/EmployeesByAgeExport.php 文件。
  • 在這個類中,我添加__construct()了允許在創建類實例時發送參數並使用它來檢索數據。
  • 初始化$resultArray 後傳入$result返回collect()-return collect($result);
  • 在方法中提及標題行名稱heading()

完成的代碼

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesByAgeExport implements FromCollection, WithHeadings
{
    private $agegreaterthan;

    public function __construct($age=0) 
    {
        $this->agegreaterthan = $age;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 3. Conditional export and customize result
        $records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();

        $result = array();
        foreach($records as $record){
           $result[] = array(
              'id'=>$record->id,
              'username' => $record->username,
              'name' => $record->name,
              'email' => $record->email,
              'age' => $record->age,
              'status' => 1 // Custom data
           );
        }

        return collect($result);
    }

    public function headings(): array
    {
       return [
         '#',
         'Username',
         'Name',
         'Email',
         'Age',
         'Status'
       ];
    }
}

8. 路線

  • 打開 routes/web.php 文件。
  • 定義 4 條路線 –
    • / - 加載索引視圖。
    • employees/exportcsv –以 CSV 格式導出數據。
    • employees/exportexcel –以 Excel 格式導出數據。
    • employees/exportbyagecsv –按年齡導出數據的 POST 路由。

完成的代碼

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');

9.控制器

  • 創建 EmployeesController 控制器。
php artisan make:controller EmployeesController
  • 打開 app/Http/Controllers/EmployeesController.php 文件。
  • 導入和。EmployeesExportEmployeesByAgeExportExcel
  • 創建 4 種方法 –
    • index() -加載index視圖。
    • exportCSV() – 導出數據調用Excel::download()

它需要 2 個參數——

  1. 類實例。
  2. 帶擴展名的文件名。
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesExport, $file_name);
  • exportExcel() –使用與上述相同的代碼並將文件擴展名更改為 .xlsx。
  • exportByAgeCSV() –讀取 POST 年齡值並將其與EmployeesByAgeExport($age)實例一起傳遞。
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesByAgeExport($age), $file_name);

完成的代碼

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;

class EmployeesController extends Controller
{
    public function index(){
       return view('index');
    }

    // CSV Export
    public function exportCSV(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Excel Export
    public function exportExcel(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Conditional Export (csv)
    public function exportByAgeCSV(Request $request){

       $age = $request->age;

       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesByAgeExport($age), $file_name);
    }
}

10. 查看

index.blade.php 在 文件夾中創建 文件resources/views/ 。

創建 2 個錨元素 -

  1. 設置href{{ route('employees.exportcsv') }}CSV 導出。
  2. 設置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> &nbsp;&nbsp;
      <a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>

      <form method='post' action="{{ route('employees.exportbyagecsv') }}">
         @csrf
         <div class="mb-3">
            <label for="age" class="form-label">Age</label>
            <input type="number" class="form-control" id="age" name="age" value="0">
         </div>

         <button type="submit" class="btn btn-success">Export</button>
      </form>
   </div>

</body>
</html>

11. 演示

查看演示


12. 結論

在 Export 類中使用構造函數來處理傳遞的參數並使用它來獲取數據。

在本教程中,我只提到了 2 種導出格式——CSV 和 Excel,但還有更多可用的格式。

來源:https ://makitweb.com

#php #laravel #mysql 

如何在 Laravel 8 中以 CSV 和 Excel 格式導出 MySQL 數據庫數據

CakePHP-Comments: A Fully Customizable Comments Plugin for CakePHP 3

Fully customizable Comments Plugin for CakePHP 3   

The Comments plugin will allow you comment every model with the possibility to change the template in your APP.

This plugin works with a behavior and a helper you need to load to fully works.

Requirements

Installation

composer require kareylo/cakephp-comments

load the plugin in your config/bootstrap.php :

Plugin::load('Kareylo/Comments', [
    'routes' => true
]);

Add in the ModelTable you wanna be commentable the following behavior :

$this->addBehavior('Kareylo/Comments.Commentable');

The behavior can take these options :

  • modelClass : Class name of the ModelTable.
    • Default : null
  • commentClass : Name of your CommentsTable if you have one.
    • Default : Kareylo/Comments.Comments
  • foreignKey : Name of your custom foreignKey.
    • Default : ref_id
  • countComments : Put true if you wanna your model count its Comments
    • Default : false
  • fieldCounter : Name of your counter field
    • Default : comments_count

Add the following helper in your src/View/AppView.php

public function initialize()
{
    $this->loadHelper('Kareylo/Comments.Comment');
}

The helper can take these options :

  • type The HTML tag that will surround your comments
    • Default ul
    • Accept ul, ol, div
  • typeClass the CSS class your type need to have
    • Default null
  • subTypeClass The CSS class your subType need to have
    • Default null
  • loadJS Put true if you wanna default JS to be loaded
    • Default false

Usage

Get all your comments with the comments finder

$data = $this->Model->find()->where(['Model.id' => $id])->find('comments')->first();
$this->set(compact('data'));

To display your comments

$this->Comment->display($data);

You can also chose to not use display($data) and use a loop to have the full control of your template

// in your view
    <div class="row">
        <h4>Commentaires</h4>
        <ul class="comment-list">
            <?php foreach ($model->comments as $comment):
                echo $this->Comment->comment($comment);
            endforeach; ?>
        </ul>
        <!-- loadJS and display the comment Form if user is connected -->
        <?= $this->Comment->loadFormAndJS($model); ?>
    </div>

Templates

To create templates for the comment block (1 comment) and the form block, create the views you want if src/Template/Element/Comments. Example :

/** src/Template/Element/Comments/comment.ctp
* $connected is used to check is user is connected
*/
<div class="comment-avatar">
    <i class="fa fa-user"></i>
</div>
<div class="comment-container">
    <div class="comment-author">
        <?= $comment->user->username; ?>
        <span class="comment-date">on <span
                class="underline"><?= $comment->created->format("l, d M y"); ?></span> at <span
                class="underline"><?= $comment->created->format("H:i:s"); ?></span></span>
    </div>
    <div class="comment-content">
        <?= h($comment->id); ?>
    </div>
    <?php if ($connected): ?>
        <div class="comment-btn pull-left">
            <a href="#" class="reply" data-id="<?= $comment->id ?>"><i class="fa fa-reply"></i> Reply</a>
        </div>
    <?php endif; ?>
    <?php if ($comment->children): ?>
        <ul class="comment-list">
            <?php foreach ($comment->children as $child) {
                echo $this->Comment->comment($child);
            } ?>
        </ul >
    <?php endif; ?>
</div>

// src/Template/Element/Comments/form.ctp
<?= $this->Form->create($comment, ['id' => 'commentForm', 'url' => ['controller' => 'Comments', 'action' => 'add', 'plugin' => 'Comments']]); ?>
    <?= $this->Flash->render('comment'); ?>
    <?= $this->Form->control('content', ['label' => __('Commentaire'), 'type' => 'textarea']); ?>
    <?= $this->Form->hidden('ref'); ?>
    <?= $this->Form->hidden('ref_id'); ?>
    <?= $this->Form->unlockField('parent_id'); ?>
    <?= $this->Form->hidden('parent_id', ['default' => null]); ?>
    <?= $this->Form->button(__('Commenter')) ?>
<?= $this->Form->end() ?>

To create the template for your flash, just add a element in src/Template/Element/Flash/Comments and name it comment.ctp

//src/Template/Element/Flash/Comments/comment.ctp
<?php
if (!isset($params['escape']) || $params['escape'] !== false) {
    $message = h($message);
}
?>
<div class="message <?= $params['class'] ?? 'success' ?>" onclick="this.classList.add('hidden');"><?= $message ?></div>
  • $params['class'] can have the values success and error

Support

For bugs and feature requests, please use the issues section of this repository.

Contribute

Follow this guide to contribute

TODO

  •  Test cases
    •  Improved Test Cases (like test Helper)
  •  More features
  •  Translation

Author: Kareylo
Source Code: https://github.com/Kareylo/CakePHP-Comments 
License: MIT license

#php #cakephp #plugin #comments 

CakePHP-Comments: A Fully Customizable Comments Plugin for CakePHP 3
Noelia  Graham

Noelia Graham

1656735060

Exporter La Base De Données MySQL Au format CSV Et Excel Dans Laravel8

Dans Laravel, vous n'avez pas besoin d'écrire un long code pour exporter vos données, il existe déjà un package disponible pour cela - Laravel Excel.

Il permet d'exporter des données dans différents formats tels que - XLSX, CSV, XLS, HTML, etc.

Dans ce didacticiel, je montre comment vous pouvez exporter des données de base de données MySQL au format CSV et Excel à l'aide du package Laravel Excel dans Laravel 8.


1. Installer le package

Exigence -

  • PHP : ^7.2\|^8.0
  • Laravel : ^5.8
  • Feuille de calcul Php : ^1.21
  • psr/simple-cache : ^1.0
  • Extension PHP  php_zip activée
  • Extension PHP  php_xml activée
  • Extension PHP  php_gd2 activée
  • Extension PHP  php_iconv activée
  • Extension PHP  php_simplexml activée
  • Extension PHP  php_xmlreader activée
  • Extension PHP  php_zlib activée

Installez 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

2. Mettre à jour app.php

  • Ouvrir  config/app.php le fichier.
  • Ajoutez ce qui suit Maatwebsite\Excel\ExcelServiceProvider::class dans  'providers' -
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • Ajoutez ce qui suit  'Excel' => Maatwebsite\Excel\Facades\Excel::class dans  'aliases' -
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3. Publier le package

Exécutez la commande -

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

Cela créera un nouveau excel.phpfichier au format config/.


4. Configuration de la base de données

Ouvrir .envle fichier.

Spécifiez l'hôte, le nom de la base de données, le nom d'utilisateur et le mot de passe.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Créer un tableau

  • Créez une nouvelle table Employeesà l'aide de la migration et ajoutez des enregistrements.
php artisan make:migration create_employees_table
  • Maintenant, accédez au database/migrations/dossier à partir de la racine du projet.
  • Trouvez un fichier PHP qui se termine par create_employees_tableet ouvrez-le.
  • Définissez la structure de la table dans la up()méthode.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Exécutez la migration –
php artisan migrate
  • La table est créée et ajoutez-y quelques enregistrements.

6. Modèle

  • Créer un Employeesmodèle.
php artisan make:model Employees
  • Ouvrir  app/Models/Employees.php le fichier.
  • Spécifiez les attributs de modèle assignables en masse - nom d'utilisateur, nom, e-mail et âge à l'aide de la $fillablepropriété.

Code terminé

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Créer une classe d'exportation

Je crée 2 classes d'exportation juste à titre d'exemple -

1. Classe d'exportation des employés -

php artisan make:export EmployeesExport --model=Employees
  • Ouvrir  app/Exports/EmployeesExport.php le fichier.

La classe a 2 méthodes -

  • collection() – Charger les données d'exportation. Ici, vous pouvez soit -
    • Renvoie tous les enregistrements.
    • Renvoyez des colonnes spécifiques ou modifiez la réponse de retour que j'ai faite dans la prochaine classe d'exportation.
  • headers() – Spécifiez la ligne d'en-tête.

REMARQUE - Supprimez les méthodes headers() si vous ne souhaitez pas ajouter de ligne d'en-tête.

Code terminé

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesExport implements FromCollection, WithHeadings
{

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 1. Export all data
        // return Employees::all();

        ## 2. Export specific columns
        return Employees::select('id','username','name')->get();

    }

    public function headings(): array
    {
        return [
          '#',
          'Username',
          'Name'
        ];
    }
}

2. Classe EmployeesByAgeExport –

php artisan make:export EmployeesByAgeExport --model=Employees
  • Ouvrir  app/Exports/EmployeesByAgeExport.php le fichier.
  • Dans cette classe, j'ai ajouté __construct()pour autoriser l'envoi d'un paramètre lors de la création d'une instance de classe et l'utiliser pour récupérer des données.
  • Après avoir initialisé $resultArray pass $resultin collect()for return –return collect($result);
  • Mentionnez les noms des lignes d'en-tête dans la heading()méthode.

Code terminé

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesByAgeExport implements FromCollection, WithHeadings
{
    private $agegreaterthan;

    public function __construct($age=0) 
    {
        $this->agegreaterthan = $age;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 3. Conditional export and customize result
        $records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();

        $result = array();
        foreach($records as $record){
           $result[] = array(
              'id'=>$record->id,
              'username' => $record->username,
              'name' => $record->name,
              'email' => $record->email,
              'age' => $record->age,
              'status' => 1 // Custom data
           );
        }

        return collect($result);
    }

    public function headings(): array
    {
       return [
         '#',
         'Username',
         'Name',
         'Email',
         'Age',
         'Status'
       ];
    }
}

8. Itinéraire

  • Ouvrir  routes/web.php le fichier.
  • Définir 4 itinéraires –
    • / –  Charger la vue d'index.
    • employés/exportcsv – Exporter les données au format CSV.
    • employés/exportexcel – Exporter les données au format Excel.
    • employés/exportbyagecsv – Route POST pour exporter les données par âge.

Code terminé

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');

9. Contrôleur

  • Créer  EmployeesController un contrôleur.
php artisan make:controller EmployeesController
  • Ouvrir  app/Http/Controllers/EmployeesController.php le fichier.
  • Importer et . EmployeesExportEmployeesByAgeExportExcel
  • Créez 4 méthodes –
    • index() – Charger la indexvue.
    • exportCSV() –  Pour exporter des données, appelez Excel::download().

Il faut 2 paramètres -

  1. Instance de classe.
  2. Nom de fichier avec extension.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesExport, $file_name);
  • exportExcel() - Utilisez le même code ci-dessus et modifiez l'extension de fichier en .xlsx.
  • exportByAgeCSV() - Lit la valeur d'âge POST et la transmet avec l' EmployeesByAgeExport($age)instance.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesByAgeExport($age), $file_name);

Code terminé

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;

class EmployeesController extends Controller
{
    public function index(){
       return view('index');
    }

    // CSV Export
    public function exportCSV(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Excel Export
    public function exportExcel(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Conditional Export (csv)
    public function exportByAgeCSV(Request $request){

       $age = $request->age;

       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesByAgeExport($age), $file_name);
    }
}

10. Voir

Créer  index.blade.php un fichier dans  resources/views/ le dossier.

Créez 2 éléments d'ancrage -

  1. Défini hrefsur {{ route('employees.exportcsv') }}pour l'exportation CSV.
  2. Défini hrefsur {{ route('employees.exportexcel') }}pour l'exportation vers Excel.

Créez un <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Ajoutez un élément d'entrée pour entrer l'âge et un bouton d'envoi.

Code terminé

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1"> 
   <title>How to Export data to CSV & Excel format in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a> &nbsp;&nbsp;
      <a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>

      <form method='post' action="{{ route('employees.exportbyagecsv') }}">
         @csrf
         <div class="mb-3">
            <label for="age" class="form-label">Age</label>
            <input type="number" class="form-control" id="age" name="age" value="0">
         </div>

         <button type="submit" class="btn btn-success">Export</button>
      </form>
   </div>

</body>
</html>

11. Démo

Voir la démo


12.Conclusion

Utilisez le constructeur dans la classe Export pour gérer les paramètres passés et utilisez-le pour récupérer des données.

Dans ce didacticiel, je n'ai mentionné que 2 formats d'exportation - CSV et Excel, mais il existe d'autres formats disponibles.

Source: https://makitweb.com

#php #laravel #mysql 

Exporter La Base De Données MySQL Au format CSV Et Excel Dans Laravel8

Exportar Una Base De Datos MySQL En Formato CSV Y Excel En Laravel 8

En Laravel, no tiene que escribir un código largo para exportar sus datos, ya hay un paquete disponible para eso: Laravel Excel.

Permite exportar datos en varios formatos como: XLSX, CSV, XLS, HTML, etc.

En este tutorial, muestro cómo puedes exportar datos de bases de datos MySQL en formato CSV y Excel usando el paquete Laravel Excel en Laravel 8.


1. Paquete de instalación

Requisito –

  • PHP: ^7.2\|^8.0
  • Laravel: ^5.8
  • PhpHoja de cálculo: ^1.21
  • psr/caché simple: ^1.0
  • Extensión PHP  php_zip habilitada
  • Extensión PHP  php_xml habilitada
  • Extensión PHP  php_gd2 habilitada
  • Extensión PHP  php_iconv habilitada
  • Extensión PHP  php_simplexml habilitada
  • Extensión PHP  php_xmlreader habilitada
  • Extensión PHP  php_zlib habilitada

Instale 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

2. Actualizar aplicación.php

  • Abrir  config/app.php archivo.
  • Agregue lo siguiente Maatwebsite\Excel\ExcelServiceProvider::class en  'providers' –
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • Agregue lo siguiente  'Excel' => Maatwebsite\Excel\Facades\Excel::class en  'aliases' –
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3. Publicar paquete

Ejecute el comando –

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

Esto creará un nuevo excel.phparchivo en formato config/.


4. Configuración de la base de datos

Abrir .envarchivo.

Especifique el host, el nombre de la base de datos, el nombre de usuario y la contraseña.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Crear tabla

  • Cree una nueva tabla Employeesmediante la migración y agregue algunos registros.
php artisan make:migration create_employees_table
  • Ahora, navegue a database/migrations/la carpeta desde la raíz del proyecto.
  • Encuentre un archivo PHP que termine con create_employees_tabley ábralo.
  • Defina la estructura de la tabla en el up()método.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Ejecute la migración –
php artisan migrate
  • La tabla se ha creado y se agregan algunos registros en ella.

6. Modelo

  • Crear Employeesmodelo.
php artisan make:model Employees
  • Abrir  app/Models/Employees.php archivo.
  • Especifique atributos de modelo asignables en masa: nombre de usuario, nombre, correo electrónico y edad utilizando la $fillablepropiedad.

Código completado

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Crear clase de exportación

Estoy creando 2 clases de exportación solo con fines de ejemplo:

1. Clase de Exportación de Empleados –

php artisan make:export EmployeesExport --model=Employees
  • Abrir  app/Exports/EmployeesExport.php archivo.

La clase tiene 2 métodos:

  • colección () – Cargar datos de exportación. Aquí, puedes:
    • Devolver todos los registros.
    • Devuelva columnas específicas o modifique la respuesta de devolución que hice en la siguiente clase de exportación.
  • headers(): especifica la fila del encabezado.

NOTA: elimine los métodos de encabezados () si no desea agregar una fila de encabezado.

Código completado

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesExport implements FromCollection, WithHeadings
{

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 1. Export all data
        // return Employees::all();

        ## 2. Export specific columns
        return Employees::select('id','username','name')->get();

    }

    public function headings(): array
    {
        return [
          '#',
          'Username',
          'Name'
        ];
    }
}

2. Clase de exportación EmployeesByAge:

php artisan make:export EmployeesByAgeExport --model=Employees
  • Abrir  app/Exports/EmployeesByAgeExport.php archivo.
  • En esta clase, agregué __construct()para permitir el envío de un parámetro al crear una instancia de clase y usarlo para recuperar datos.
  • Después de inicializar el paso de $resultmatriz para el retorno:$resultcollect()return collect($result);
  • Mencione los nombres de las filas de encabezado en el heading()método.

Código completado

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesByAgeExport implements FromCollection, WithHeadings
{
    private $agegreaterthan;

    public function __construct($age=0) 
    {
        $this->agegreaterthan = $age;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 3. Conditional export and customize result
        $records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();

        $result = array();
        foreach($records as $record){
           $result[] = array(
              'id'=>$record->id,
              'username' => $record->username,
              'name' => $record->name,
              'email' => $record->email,
              'age' => $record->age,
              'status' => 1 // Custom data
           );
        }

        return collect($result);
    }

    public function headings(): array
    {
       return [
         '#',
         'Username',
         'Name',
         'Email',
         'Age',
         'Status'
       ];
    }
}

8. Ruta

  • Abrir  routes/web.php archivo.
  • Definir 4 rutas –
    • / –  Cargar vista de índice.
    • empleados/exportcsv: exporta datos en formato CSV.
    • empleados/exportexcel – Exportar datos en formato Excel.
    • empleados/exportbyagecsv: ruta POST para exportar datos por edad.

Código completado

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');

9. Controlador

  • Crear  EmployeesController controlador.
php artisan make:controller EmployeesController
  • Abrir  app/Http/Controllers/EmployeesController.php archivo.
  • Importar y . EmployeesExportEmployeesByAgeExportExcel
  • Crear 4 métodos –
    • index() – Cargar indexvista.
    • exportCSV():  para exportar datos, llame a Excel::download().

Se necesitan 2 parámetros:

  1. instancia de clase.
  2. Nombre de archivo con extensión.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesExport, $file_name);
  • exportExcel(): use el mismo código anterior y cambie la extensión del archivo a .xlsx.
  • exportByAgeCSV(): lea el valor de edad POST y páselo con la EmployeesByAgeExport($age)instancia.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesByAgeExport($age), $file_name);

Código completado

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;

class EmployeesController extends Controller
{
    public function index(){
       return view('index');
    }

    // CSV Export
    public function exportCSV(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Excel Export
    public function exportExcel(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Conditional Export (csv)
    public function exportByAgeCSV(Request $request){

       $age = $request->age;

       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesByAgeExport($age), $file_name);
    }
}

10. Ver

Crear  index.blade.php archivo en  resources/views/ carpeta.

Crea 2 elementos de anclaje –

  1. Configúrelo hrefpara {{ route('employees.exportcsv') }}exportar a CSV.
  2. Configurado hrefpara {{ route('employees.exportexcel') }}exportar a Excel.

Crea un <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Agregue un elemento de entrada para ingresar la edad y un botón de envío.

Código completado

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1"> 
   <title>How to Export data to CSV & Excel format in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a> &nbsp;&nbsp;
      <a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>

      <form method='post' action="{{ route('employees.exportbyagecsv') }}">
         @csrf
         <div class="mb-3">
            <label for="age" class="form-label">Age</label>
            <input type="number" class="form-control" id="age" name="age" value="0">
         </div>

         <button type="submit" class="btn btn-success">Export</button>
      </form>
   </div>

</body>
</html>

11. demostración

Ver demostración


12. Conclusión

Use el constructor en la clase Export para manejar los parámetros pasados ​​y utilícelo para obtener datos.

En este tutorial, solo mencioné 2 formatos de exportación: CSV y Excel, pero hay más formatos disponibles.

Fuente: https://makitweb.com

#php #laravel #mysql 

Exportar Una Base De Datos MySQL En Formato CSV Y Excel En Laravel 8

Cakephp-calendar: CakePHP Calendar Plugin

CakePHP Calendar plugin

A plugin to render simple calendars.

This branch is for CakePHP 4.2+. For details see version map.

Features

  • Simple and robust
  • No JS needed, more responsive than solutions like fullcalendar
  • Persistent year/month URL pieces (copy-paste and link/redirect friendly)
  • IcalView class for .ics calendar file output.

Setup

composer require dereuromark/cakephp-calendar

Then make sure the plugin is loaded in bootstrap:

bin/cake plugin load Calendar

Demo

See the demo Calendar example at the sandbox.

Usage

See Documentation.

Author: Dereuromark
Source Code: https://github.com/dereuromark/cakephp-calendar 
License: MIT license

#php #cakephp #calendar #files 

Cakephp-calendar: CakePHP Calendar Plugin
Neil  Morgan

Neil Morgan

1656727380

Como Exportar O Banco De Dados MySQL No formato CSV E Excel No Laravel

No Laravel, você não precisa escrever código longo para exportar seus dados, já existe um pacote disponível para isso – Laravel Excel.

Permite exportar dados em vários formatos como – XLSX, CSV, XLS, HTML, etc.

Neste tutorial, mostro como você pode exportar dados do banco de dados MySQL no formato CSV e Excel usando o pacote Laravel Excel no Laravel 8.


1. Instale o pacote

Requerimento -

  • PHP: ^7.2\|^8.0
  • Laravel: ^5.8
  • Planilha Php: ^1.21
  • psr/cache simples: ^1.0
  • Extensão PHP  php_zip habilitada
  • Extensão PHP  php_xml habilitada
  • Extensão PHP  php_gd2 habilitada
  • Extensão PHP  php_iconv habilitada
  • Extensão PHP  php_simplexml habilitada
  • Extensão PHP  php_xmlreader habilitada
  • Extensão PHP  php_zlib habilitada

Instale 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

2. Atualize app.php

  • Abrir  config/app.php arquivo.
  • Adicione o seguinte Maatwebsite\Excel\ExcelServiceProvider::class em  'providers' –
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • Adicione o seguinte  'Excel' => Maatwebsite\Excel\Facades\Excel::class em  'aliases' –
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3. Publicar pacote

Execute o comando -

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

Isso criará um novo excel.phparquivo em config/.


4. Configuração do banco de dados

Abrir .envarquivo.

Especifique o host, o nome do banco de dados, o nome de usuário e a senha.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Criar Tabela

  • Crie uma nova tabela Employeesusando a migração e adicione alguns registros.
php artisan make:migration create_employees_table
  • Agora, navegue até a database/migrations/pasta da raiz do projeto.
  • Encontre um arquivo PHP que termine com create_employees_tablee abra-o.
  • Defina a estrutura da tabela no up()método.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Execute a migração –
php artisan migrate
  • A tabela está sendo criada e adicione alguns registros nela.

6. Modelo

  • Criar Employeesmodelo.
php artisan make:model Employees
  • Abrir  app/Models/Employees.php arquivo.
  • Especifique atributos de modelo atribuíveis em massa – nome de usuário, nome, email e idade usando a $fillablepropriedade.

Código concluído

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Criar classe de exportação

Estou criando 2 classes de exportação apenas para fins de exemplo -

1. Classe de Exportação de Funcionários -

php artisan make:export EmployeesExport --model=Employees
  • Abrir  app/Exports/EmployeesExport.php arquivo.

A classe tem 2 métodos –

  • collection() – Carrega dados de exportação. Aqui, você pode –
    • Retorna todos os registros.
    • Retorne colunas específicas ou modifique a resposta de retorno que fiz na próxima classe Export.
  • headers() – Especifique a linha do cabeçalho.

NOTA – Remova os métodos headers() se você não quiser adicionar uma linha de cabeçalho.

Código concluído

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesExport implements FromCollection, WithHeadings
{

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 1. Export all data
        // return Employees::all();

        ## 2. Export specific columns
        return Employees::select('id','username','name')->get();

    }

    public function headings(): array
    {
        return [
          '#',
          'Username',
          'Name'
        ];
    }
}

2. FuncionáriosPor Classe de Exportação de Idade –

php artisan make:export EmployeesByAgeExport --model=Employees
  • Abrir  app/Exports/EmployeesByAgeExport.php arquivo.
  • Nesta classe, adicionei __construct()para permitir o envio de um parâmetro ao criar a instância da classe e usá-lo para recuperar dados.
  • Após inicializar o $resultArray passe $resultpara collect()retorno –return collect($result);
  • Mencione os nomes das linhas de cabeçalho no heading()método.

Código concluído

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesByAgeExport implements FromCollection, WithHeadings
{
    private $agegreaterthan;

    public function __construct($age=0) 
    {
        $this->agegreaterthan = $age;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 3. Conditional export and customize result
        $records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();

        $result = array();
        foreach($records as $record){
           $result[] = array(
              'id'=>$record->id,
              'username' => $record->username,
              'name' => $record->name,
              'email' => $record->email,
              'age' => $record->age,
              'status' => 1 // Custom data
           );
        }

        return collect($result);
    }

    public function headings(): array
    {
       return [
         '#',
         'Username',
         'Name',
         'Email',
         'Age',
         'Status'
       ];
    }
}

8. Rota

  • Abrir  routes/web.php arquivo.
  • Defina 4 rotas –
    • / –  Carregar visualização de índice.
    • funcionários/exportcsv – Exporta dados em formato CSV.
    • funcionários/exportexcel – Exporta dados em formato Excel.
    • funcionários/exportbyagecsv – rota POST para exportar dados por idade.

Código concluído

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');

9. Controlador

  • Criar  EmployeesController controlador.
php artisan make:controller EmployeesController
  • Abrir  app/Http/Controllers/EmployeesController.php arquivo.
  • Importar e . EmployeesExportEmployeesByAgeExportExcel
  • Crie 4 métodos –
    • index() – Carregar indexvisualização.
    • exportCSV() –  Para exportar dados, chame Excel::download().

Leva 2 parâmetros -

  1. Instância de classe.
  2. Nome do arquivo com extensão.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesExport, $file_name);
  • exportExcel() – Usando o mesmo código acima e altere a extensão do arquivo para .xlsx.
  • exportByAgeCSV() – Leia o valor de idade POST e passe-o com EmployeesByAgeExport($age)instância.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesByAgeExport($age), $file_name);

Código concluído

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;

class EmployeesController extends Controller
{
    public function index(){
       return view('index');
    }

    // CSV Export
    public function exportCSV(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Excel Export
    public function exportExcel(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Conditional Export (csv)
    public function exportByAgeCSV(Request $request){

       $age = $request->age;

       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesByAgeExport($age), $file_name);
    }
}

10. Visualizar

Criar  index.blade.php arquivo na  resources/views/ pasta.

Crie 2 elementos âncora –

  1. Definido hrefcomo {{ route('employees.exportcsv') }}para exportação CSV.
  2. Defina hrefcomo {{ route('employees.exportexcel') }}para exportação do Excel.

Crie um <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Adicione um elemento de entrada para inserir a idade e um botão de envio.

Código concluído

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1"> 
   <title>How to Export data to CSV & Excel format in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a> &nbsp;&nbsp;
      <a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>

      <form method='post' action="{{ route('employees.exportbyagecsv') }}">
         @csrf
         <div class="mb-3">
            <label for="age" class="form-label">Age</label>
            <input type="number" class="form-control" id="age" name="age" value="0">
         </div>

         <button type="submit" class="btn btn-success">Export</button>
      </form>
   </div>

</body>
</html>

11. Demonstração

Ver demonstração


12. Conclusão

Use o construtor na classe Export para manipular os parâmetros passados ​​e use-o para buscar dados.

Neste tutorial, mencionei apenas 2 formatos de exportação – CSV e Excel, mas há mais formatos disponíveis.

Fonte: https://makitweb.com

#php #laravel #mysql 

Como Exportar O Banco De Dados MySQL No formato CSV E Excel No Laravel

Как импортировать данные CSV и Excel в базу данных MySQL в Laravel 8

Массовый импорт данных — полезная функция для добавления записей, хранящихся в файле, в базу данных. Данные могут храниться в формате CSV, Excel, XML, ODS и т. д.

В этом руководстве я покажу, как вы можете импортировать данные CSV и Excel в базу данных MySQL, используя пакет Laravel Excel в проекте Laravel 8.


1. Установить пакет

Требование –

  • PHP: ^7.2\|^8.0
  • Ларавель: ^5.8
  • Таблица Php: ^1.21
  • PSR/простой кэш: ^1.0
  • Расширение PHP  php_zip включено
  • Расширение PHP  php_xml включено
  • Расширение PHP  php_gd2 включено
  • Расширение PHP  php_iconv включено
  • Расширение PHP  php_simplexml включено
  • Расширение PHP  php_xmlreader включено
  • Расширение PHP  php_zlib включено

Установите пакет с помощью композитора —

composer require maatwebsite/excel

Если вы получаете сообщение об ошибке при выполнении вышеуказанной команды, выполните следующую команду:

composer require psr/simple-cache:^1.0 maatwebsite/excel

После этого снова выполнить –

composer require maatwebsite/excel

2. Обновите app.php

  • Откройте  config/app.php файл.
  • Добавьте следующее Maatwebsite\Excel\ExcelServiceProvider::class в  'providers' -
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • Добавьте следующее  'Excel' => Maatwebsite\Excel\Facades\Excel::class в  'aliases' -
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3. Опубликовать пакет

Запустите команду -

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

Это создаст новый excel.phpфайл в формате config/.


4. Конфигурация базы данных

Откройте .envфайл.

Укажите хост, имя базы данных, имя пользователя и пароль.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Создать таблицу

  • Создайте новую таблицу Employeesс помощью миграции и добавьте несколько записей.
php artisan make:migration create_employees_table
  • Теперь перейдите в database/migrations/папку из корня проекта.
  • Найдите файл PHP, оканчивающийся на , create_employees_tableи откройте его.
  • Определите структуру таблицы в up()методе.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Запустите миграцию —
php artisan migrate
  • Таблица создана.

6. Модель

  • Создать Employeesмодель.
php artisan make:model Employees
  • Откройте  app/Models/Employees.php файл.
  • Укажите массово назначаемые атрибуты модели — имя пользователя, имя, адрес электронной почты и возраст, используя $fillableсвойство.

Завершенный код

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Создайте класс импорта

Я создаю 2 класса импорта только для примера —

1. Класс EmployeesImport —

php artisan make:import EmployeesImport --model=Employees
  • Откройте  app/Imports/EmployeesImport.php файл.
  • Класс имеет 1 метод —
    • model () — считывает значения из $rowмассива и вставляет запись, если идентификатор электронной почты не существует в employeesтаблице, в противном случае возвращает значение null.

ПРИМЕЧАНИЕ. – Этот класс импорта начинает чтение записей с 1-й строки. Я объяснил, как пропустить 1-ю строку, содержащую заголовок, в следующем классе импорта.

CSV-файл без строки заголовка –

пользователь1Пользователь1 и1user1@makitweb.com28
пользователь2Пользователь2 и2user2@makitweb.com24

Завершенный код

<?php
namespace App\Imports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;

class EmployeesImport implements ToModel {
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row) {
 
       // Check email already exists
       $count = Employees::where('email',$row[2])->count();
       if($count > 0){
          return null;
       }
       return new Employees([
          'username' => $row[0],
          'name' => $row[1], 
          'email' => $row[2],
          'age' => $row[3],
       ]);
    }

}

2. Класс Employees2Import –

php artisan make:import Employees2Import --model=Employees
  • Откройте  app/Imports/Employees2Import.php файл.
  • Класс имеет 2 метода –
    • collection() — проверка $rowsданных массива. В случае успешной проверки вставьте запись, если идентификатор электронной почты не существует в employeesтаблице, в противном случае верните ошибку.

ПРИМЕЧАНИЕ. – Если  WithHeadingRowреализовано, то $rowsмассивы содержат заголовки строк в качестве имен ключей вместо индекса.

  • headingRow() — возвращает позицию индекса заголовка.

CSV-файл со строкой заголовка –

Имя пользователяИмяЭл. адресВозраст
ыссёгешЙогеш Сингхyogesh@makitweb.com28
бсонарикаСонарика Бхадорияbsonarika@makitweb.com28
вышалВишал Сахуvishal@makitweb.comтридцать первый

Завершенный код

<?php

namespace App\Imports;

use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;

class Employees2Import implements ToCollection ,WithHeadingRow
{

    public function collection(Collection $rows){

       // Validate
       Validator::make($rows->toArray(), [
          '*.username' => 'required|string',
          '*.name' => 'required|string',
          '*.email' => 'required|email',
          '*.age' => 'required|integer',
       ],[
          '*.username.required'=> "The username field is required.",
          '*.username.string'=> "The username must be string.",
          '*.name.required'=> "The name field is required.",
          '*.name.string'=> "The name must be string.",
          '*.email.required'=> "The email field is required.",
          '*.email.email'=> "The email must be a valid email address.",
          '*.age.integer'=> "The age must be an integer."
       ])->validate();

       foreach ($rows as $row) {

          // Check email already exists
          $count = Employees::where('email',$row['email'])->count();
          if($count > 0){
             continue;
          }
          Employees::create([
             'username' => $row['username'],
             'name' => $row['name'], 
             'email' => $row['email'],
             'age' => $row['age'],
         ]);
       }
    }

    // Specify header row index position to skip
    public function headingRow(): int {
       return 1;
    }
}

8. Маршрут

  • Откройте  routes/web.php файл.
  • Определить 3 маршрута –
    • / —  Загрузить представление индекса.
    • employee/importdata — опубликовать маршрут для импорта данных.
    • employee/validateandimportdata — отправка маршрута для проверки и импорта данных.

Завершенный код

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');

9. Контроллер

  • Создать  EmployeesController контроллер.
php artisan make:controller EmployeesController
  • Откройте  app/Http/Controllers/EmployeesController.php файл.
  • Импорт   и  . EmployeesImportEmployees2ImportExcel
  • Создайте 3 метода –
    • index() — Загрузить indexпредставление.
    • importdata() — импортировать вызов Excel::import().

Передать 2 параметра –

  1. EmployeesImportЭкземпляр класса.
  2. Передать местоположение выбранного файла 'temp'.
    • validateAndImportdata() — снова вызов Excel::import().

Передать 2 параметра –

  1. Employees2ImportЭкземпляр класса.
  2. Передайте сохраненное местоположение файла (я сохранил файл в public/employees.xlsxпапке).

Завершенный код

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;

class EmployeesController extends Controller
{
   public function index(){
      return view('index');
   }

   // Import data 
   public function importdata(Request $request){
      Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
      return back()->with('success', 'Import successfully!');
   }

   // Validate and Import data 
   public function validateAndImportdata(Request $request){

      Excel::import(new Employees2Import, "employees.xlsx");
      return back()->with('success', 'Import successfully!');
   }

}

10. Просмотр

Создать  index.blade.php файл в  resources/views/ папке.

Создать 2 <form>

  • В 1-м <form >наборе действий {{ route('employees.importdata') }}. Создайте элемент файла и кнопку отправки.
  • Во 2-м <form>установите действие на {{ route('employees.validateandimportdata') }}. Создайте кнопку отправки.

Цикл $errors->all()для отображения ошибок.

Завершенный код

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <title>How to Import data into MySQL database in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <!-- Success message -->
      @if(Session::has('success'))
         <div class="alert alert-success">
            {{ Session::get('success') }}
         </div>
      @endif

      <form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
         @csrf
         <div class="mb-3">
            <label for="file" class="form-label">File</label>
            <input type="file" class="form-control" id="file" name="file" value="">
         </div>

         <button type="submit" class="btn btn-success">Import</button>
      </form>

      <!-- Import data with validation -->
      <h2 class='mt-5'>Validate and import data</h2>
      {{-- Display errors --}}
      @if (count($errors) > 0)
         <div class="row">
            <div class="col-md-12 ">
                <div class="alert alert-danger">
                   <ul>
                      @foreach($errors->all() as $error)
                         <li>{{ $error }} </li>
                      @endforeach 
                   </ul> 
                </div>
            </div>
         </div>
      @endif

      <form method='post' action="{{ route('employees.validateandimportdata') }}" >
         @csrf
         <button type="submit" class="btn btn-success">Import</button>
      </form>
   </div>

</body>
</html>

11. Выход

Просмотр вывода


12. Заключение

Обязательно проверьте запись перед вставкой или обновлением записей в классе Import. Укажите WithHeadingRowв классе импорта, только если файл импорта имеет строку заголовка.

Источник: https://makitweb.com

#laravel #php #mysql 

Как импортировать данные CSV и Excel в базу данных MySQL в Laravel 8

CakeMiddlewares: This is A Collection Of Cakephp Middlewares

CakeMiddlewares

This is a collection of Cakephp Middlewares.

This plugin provides commonly used middlewares to make it easier to integrate into your cakephp project.

Requirements

Installation

composer require chrisshick/cakemiddlewares

To make your application load the plugin either run:

./bin/cake plugin load chrisShick/CakeMiddlewares

or add the following line to config/bootstrap.php:

Plugin::load('chrisShick/CakeMiddlewares');

Available Middlewares

Patches & Features

  • Fork
  • Mod, fix
  • Test - This is important, so it's not unintentionally broken
  • Commit - Please do not mess with license, todo, version, etc.
  • Pull request

To ensure your PRs are considered for upstream, you MUST follow the CakePHP coding standards.

Bugs & Feedback

http://github.com/chrisShick/CakeMiddlewares/issues

Credits

This repository was inspired by Oscarotero's PSR-7 Middelwares

The requirements and readme was inspired by UseMuffin's repositories.

Author: ChrisShick
Source Code: https://github.com/chrisShick/CakeMiddlewares 
License: MIT license

#php #cakephp #middleware 

CakeMiddlewares: This is A Collection Of Cakephp Middlewares
Hoang Tran

Hoang Tran

1656718320

Cách Xuất Cơ Sở Dữ Liệu MySQL Ở Định Dạng CSV & Excel Trong Laravel 8

Trong Laravel, bạn không cần phải viết mã dài để xuất dữ liệu của mình, đã có một gói có sẵn cho việc đó - Laravel Excel.

Nó cho phép xuất dữ liệu ở nhiều định dạng khác nhau như - XLSX, CSV, XLS, HTML, v.v.

Trong hướng dẫn này, tôi chỉ cách bạn có thể xuất dữ liệu cơ sở dữ liệu MySQL ở định dạng CSV & Excel bằng cách sử dụng gói Laravel Excel trong Laravel 8.


1. Cài đặt gói

Yêu cầu -

  • PHP: ^7.2\|^8.0
  • Laravel: ^5.8
  • PhpS Spreadsheet: ^1.21
  • psr / simple-cache: ^1.0
  • php_zip Đã bật tiện ích mở rộng 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

2. Cập nhật app.php

  • Mở  config/app.php tệp.
  • Thêm phần sau Maatwebsite\Excel\ExcelServiceProvider::class vào  'providers' -
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • Thêm phần sau  'Excel' => Maatwebsite\Excel\Facades\Excel::class vào  'aliases' -
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3. Xuất bản gói

Chạy lệnh -

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

Điều này sẽ tạo một excel.phptệp mới trong config/.


4. Cấu hình cơ sở dữ liệu

Mở .envtệp.

Chỉ định máy chủ, tên cơ sở dữ liệu, tên người dùng và mật khẩu.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Tạo bảng

  • Tạo một bảng mới Employeesbằng cách sử dụng di chuyển và thêm một số bản ghi.
php artisan make:migration create_employees_table
  • Bây giờ, điều hướng đến database/migrations/thư mục từ thư mục gốc của dự án.
  • Tìm một tệp PHP có kết thúc bằng create_employees_tablevà mở nó.
  • Xác định cấu trúc bảng trong up()phương thức.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Chạy quá trình di chuyển -
php artisan migrate
  • Bảng được tạo và thêm một số bản ghi trong đó.

6. Mô hình

  • Tạo Employeesmô hình.
php artisan make:model Employees
  • Mở  app/Models/Employees.php tệp.
  • Chỉ định các thuộc tính Model có thể gán hàng loạt - tên người dùng, tên, email và tuổi bằng cách sử dụng thuộc $fillabletính.

Mã đã hoàn thành

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Tạo lớp Xuất

Tôi đang tạo 2 lớp xuất chỉ cho mục đích ví dụ -

1. Lớp Nhân viên Xuất khẩu -

php artisan make:export EmployeesExport --model=Employees
  • Mở  app/Exports/EmployeesExport.php tệp.

Lớp có 2 phương thức -

  • collection () - Tải dữ liệu xuất. Ở đây, bạn có thể -
    • Trả lại tất cả hồ sơ.
    • Trả lại các cột cụ thể hoặc sửa đổi phản hồi trả lại mà tôi đã thực hiện trong lớp Xuất tiếp theo.
  • headings () - Chỉ định hàng tiêu đề.

LƯU Ý - Loại bỏ các phương thức headings () nếu bạn không muốn thêm một hàng tiêu đề.

Mã đã hoàn thành

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesExport implements FromCollection, WithHeadings
{

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 1. Export all data
        // return Employees::all();

        ## 2. Export specific columns
        return Employees::select('id','username','name')->get();

    }

    public function headings(): array
    {
        return [
          '#',
          'Username',
          'Name'
        ];
    }
}

2. Lớp Nhân viênByAgeExport -

php artisan make:export EmployeesByAgeExport --model=Employees
  • Mở  app/Exports/EmployeesByAgeExport.php tệp.
  • Trong lớp này, tôi đã thêm __construct()để cho phép gửi một tham số trong khi tạo cá thể lớp và sử dụng nó để truy xuất dữ liệu.
  • Sau khi khởi tạo $resultArray , hãy trả $resultvề collect()-return collect($result);
  • Đề cập đến tên hàng tiêu đề trong heading()phương thức.

Mã đã hoàn thành

<?php

namespace App\Exports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class EmployeesByAgeExport implements FromCollection, WithHeadings
{
    private $agegreaterthan;

    public function __construct($age=0) 
    {
        $this->agegreaterthan = $age;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 3. Conditional export and customize result
        $records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();

        $result = array();
        foreach($records as $record){
           $result[] = array(
              'id'=>$record->id,
              'username' => $record->username,
              'name' => $record->name,
              'email' => $record->email,
              'age' => $record->age,
              'status' => 1 // Custom data
           );
        }

        return collect($result);
    }

    public function headings(): array
    {
       return [
         '#',
         'Username',
         'Name',
         'Email',
         'Age',
         'Status'
       ];
    }
}

8. Lộ trình

  • Mở  routes/web.php tệp.
  • Xác định 4 tuyến đường -
    • / -  Tải chế độ xem chỉ mục.
    • nhân viên / exportcsv - Xuất dữ liệu ở định dạng CSV.
    • nhân viên / exportexcel - Xuất dữ liệu ở định dạng Excel.
    • nhân viên / exportbyagecsv - ĐĂNG lộ trình xuất dữ liệu theo độ tuổi.

Mã đã hoàn thành

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');

9. Bộ điều khiển

  • Tạo  EmployeesController Bộ điều khiển.
php artisan make:controller EmployeesController
  • Mở  app/Http/Controllers/EmployeesController.php tệp.
  • Nhập và . EmployeesExportEmployeesByAgeExportExcel
  • Tạo 4 phương pháp -
    • index () - Tải indexchế độ xem.
    • exportCSV () -  Để xuất lệnh gọi dữ liệu Excel::download().

Nó có 2 tham số -

  1. Cá thể lớp.
  2. Tên tệp có phần mở rộng.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesExport, $file_name);
  • exportExcel () - Sử dụng cùng đoạn mã trên và thay đổi phần mở rộng tệp thành .xlsx.
  • exportByAgeCSV () - Đọc giá trị tuổi POST và chuyển nó cùng với phiên bản EmployeesByAgeExport($age).
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesByAgeExport($age), $file_name);

Mã đã hoàn thành

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;

class EmployeesController extends Controller
{
    public function index(){
       return view('index');
    }

    // CSV Export
    public function exportCSV(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Excel Export
    public function exportExcel(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Conditional Export (csv)
    public function exportByAgeCSV(Request $request){

       $age = $request->age;

       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesByAgeExport($age), $file_name);
    }
}

10. Xem

Tạo  index.blade.php tệp trong  resources/views/ thư mục.

Tạo 2 phần tử neo -

  1. Đặt hrefthành để {{ route('employees.exportcsv') }}xuất CSV.
  2. Đặt hrefthành để {{ route('employees.exportexcel') }}xuất Excel.

Tạo một <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Thêm yếu tố đầu vào để nhập tuổi và nút gửi.

Mã đã hoàn thành

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1"> 
   <title>How to Export data to CSV & Excel format in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a> &nbsp;&nbsp;
      <a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>

      <form method='post' action="{{ route('employees.exportbyagecsv') }}">
         @csrf
         <div class="mb-3">
            <label for="age" class="form-label">Age</label>
            <input type="number" class="form-control" id="age" name="age" value="0">
         </div>

         <button type="submit" class="btn btn-success">Export</button>
      </form>
   </div>

</body>
</html>

11. Demo

Xem Demo


12. Kết luận

Sử dụng hàm tạo trong lớp Export để xử lý các tham số đã truyền và sử dụng nó để tìm nạp dữ liệu.

Trong hướng dẫn này, tôi chỉ đề cập đến 2 định dạng xuất - CSV và Excel nhưng có nhiều định dạng hơn.

Nguồn: https://makitweb.com

#php #laravel #mysql 

Cách Xuất Cơ Sở Dữ Liệu MySQL Ở Định Dạng CSV & Excel Trong Laravel 8

如何在 Laravel 8 中將 CSV 和 Excel 數據導入 MySQL 數據庫

批量導入數據是將存儲在文件中的記錄添加到數據庫的有用功能。數據可以以 CSV、Excel、XML、ODS 等格式存儲。

在本教程中,我將展示如何在 Laravel 8 項目中使用 Laravel Excel 包將 CSV 和 Excel 數據導入 MySQL 數據庫。


1.安裝包

要求 -

  • PHP: ^7.2\|^8.0
  • 拉拉維爾: ^5.8
  • Php電子表格: ^1.21
  • psr/簡單緩存: ^1.0
  • php_zip 啟用PHP 擴展 
  • php_xml 啟用PHP 擴展 
  • php_gd2 啟用PHP 擴展 
  • php_iconv 啟用PHP 擴展 
  • php_simplexml 啟用PHP 擴展 
  • php_xmlreader 啟用PHP 擴展 
  • php_zlib 啟用PHP 擴展 

使用 composer 安裝包——

composer require maatwebsite/excel

如果您在執行上述命令時遇到錯誤,請執行以下命令 -

composer require psr/simple-cache:^1.0 maatwebsite/excel

之後再次執行 -

composer require maatwebsite/excel

2.更新app.php

  • 打開 config/app.php 文件。
  • 將以下內容添加Maatwebsite\Excel\ExcelServiceProvider::class 到 'providers' –
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • 將以下內容添加 'Excel' => Maatwebsite\Excel\Facades\Excel::class 到 'aliases' –
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3.發布包

運行命令——

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

這將excel.phpconfig/.


4. 數據庫配置

打開.env文件。

指定主機、數據庫名稱、用戶名和密碼。

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. 創建表

  • Employees使用遷移創建一個新表並添加一些記錄。
php artisan make:migration create_employees_table
  • database/migrations/現在,從項目根目錄導航到文件夾。
  • 找到一個以 結尾的 PHP 文件create_employees_table並打開它。
  • 在方法中定義表結構up()
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • 運行遷移 -
php artisan migrate
  • 表已創建。

6. 型號

  • 創建Employees模型。
php artisan make:model Employees
  • 打開 app/Models/Employees.php 文件。
  • 使用該屬性指定可批量分配的模型屬性——用戶名、姓名、電子郵件和年齡$fillable

完成的代碼

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. 創建導入類

我創建 2 個導入類只是為了舉例 -

1.EmployeesImport 類——

php artisan make:import EmployeesImport --model=Employees
  • 打開 app/Imports/EmployeesImport.php 文件。
  • 類有 1 種方法——
    • model() –從 Array 中讀取值$row並插入一條記錄,如果電子郵件 ID 不存在於employees表中,否則返回 null。

注意——這個導入類從第一行開始讀取記錄。我解釋瞭如何在下一個 Import 類中跳過包含標題的第一行。

沒有標題行的 CSV 文件 –

用戶1用戶 1 u1user1@makitweb.com28
用戶2用戶 2 u2user2@makitweb.com24

完成的代碼

<?php
namespace App\Imports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;

class EmployeesImport implements ToModel {
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row) {
 
       // Check email already exists
       $count = Employees::where('email',$row[2])->count();
       if($count > 0){
          return null;
       }
       return new Employees([
          'username' => $row[0],
          'name' => $row[1], 
          'email' => $row[2],
          'age' => $row[3],
       ]);
    }

}

2.Employees2Import 類——

php artisan make:import Employees2Import --model=Employees
  • 打開 app/Imports/Employees2Import.php 文件。
  • 類有 2 種方法——
    • collection() -驗證$rows數組數據。如果成功驗證,則如果表中不存在電子郵件 ID,則插入記錄,employees否則返回錯誤。

注 —如果  WithHeadingRow實現,則$rows數組包含行標題作為鍵名而不是索引。

  • headingRow() -返回標題索引位置。

帶有標題行的 CSV 文件 –

用戶名姓名電子郵件年齡
yssyogesh約格甚·辛格yogesh@makitweb.com28
布索納里卡索娜麗卡·巴多利亞bsonarika@makitweb.com28
維沙爾維沙爾薩胡vishal@makitweb.com第三十一

完成的代碼

<?php

namespace App\Imports;

use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;

class Employees2Import implements ToCollection ,WithHeadingRow
{

    public function collection(Collection $rows){

       // Validate
       Validator::make($rows->toArray(), [
          '*.username' => 'required|string',
          '*.name' => 'required|string',
          '*.email' => 'required|email',
          '*.age' => 'required|integer',
       ],[
          '*.username.required'=> "The username field is required.",
          '*.username.string'=> "The username must be string.",
          '*.name.required'=> "The name field is required.",
          '*.name.string'=> "The name must be string.",
          '*.email.required'=> "The email field is required.",
          '*.email.email'=> "The email must be a valid email address.",
          '*.age.integer'=> "The age must be an integer."
       ])->validate();

       foreach ($rows as $row) {

          // Check email already exists
          $count = Employees::where('email',$row['email'])->count();
          if($count > 0){
             continue;
          }
          Employees::create([
             'username' => $row['username'],
             'name' => $row['name'], 
             'email' => $row['email'],
             'age' => $row['age'],
         ]);
       }
    }

    // Specify header row index position to skip
    public function headingRow(): int {
       return 1;
    }
}

8. 路線

  • 打開 routes/web.php 文件。
  • 定義 3 條路線 –
    • / - 加載索引視圖。
    • employees/importdata –發布導入數據的路徑。
    • employees/validateandimportdata –發布路由以驗證和導入數據。

完成的代碼

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');

9.控制器

  • 創建 EmployeesController 控制器。
php artisan make:controller EmployeesController
  • 打開 app/Http/Controllers/EmployeesController.php 文件。
  • 導入  和 。EmployeesImportEmployees2ImportExcel
  • 創建 3 個方法 –
    • index() -加載index視圖。
    • importdata() –導入調用Excel::import()

傳遞 2 個參數 –

  1. EmployeesImport類實例。
  2. 傳遞選定的文件“臨時”位置。
    • validateAndImportdata() –再次調用Excel::import().

傳遞 2 個參數 –

  1. Employees2Import類實例。
  2. 傳遞存儲的文件位置(我已將文件存儲在文件public/employees.xlsx夾中)。

完成的代碼

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;

class EmployeesController extends Controller
{
   public function index(){
      return view('index');
   }

   // Import data 
   public function importdata(Request $request){
      Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
      return back()->with('success', 'Import successfully!');
   }

   // Validate and Import data 
   public function validateAndImportdata(Request $request){

      Excel::import(new Employees2Import, "employees.xlsx");
      return back()->with('success', 'Import successfully!');
   }

}

10. 查看

index.blade.php 在 文件夾中創建 文件resources/views/ 。

創建 2 <form>

  • 在第一<form >組動作中{{ route('employees.importdata') }}。創建一個文件元素和一個提交按鈕。
  • 在第二<form>組動作中{{ route('employees.validateandimportdata') }}。創建提交按鈕。

循環$errors->all()顯示錯誤。

完成的代碼

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <title>How to Import data into MySQL database in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <!-- Success message -->
      @if(Session::has('success'))
         <div class="alert alert-success">
            {{ Session::get('success') }}
         </div>
      @endif

      <form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
         @csrf
         <div class="mb-3">
            <label for="file" class="form-label">File</label>
            <input type="file" class="form-control" id="file" name="file" value="">
         </div>

         <button type="submit" class="btn btn-success">Import</button>
      </form>

      <!-- Import data with validation -->
      <h2 class='mt-5'>Validate and import data</h2>
      {{-- Display errors --}}
      @if (count($errors) > 0)
         <div class="row">
            <div class="col-md-12 ">
                <div class="alert alert-danger">
                   <ul>
                      @foreach($errors->all() as $error)
                         <li>{{ $error }} </li>
                      @endforeach 
                   </ul> 
                </div>
            </div>
         </div>
      @endif

      <form method='post' action="{{ route('employees.validateandimportdata') }}" >
         @csrf
         <button type="submit" class="btn btn-success">Import</button>
      </form>
   </div>

</body>
</html>

11. 輸出

查看輸出


12. 結論

確保在 Import 類中插入或更新記錄之前驗證記錄。WithHeadingRow僅當導入文件具有標題行時才在 Import 類中指定。

來源:https ://makitweb.com

#laravel #php #mysql 

如何在 Laravel 8 中將 CSV 和 Excel 數據導入 MySQL 數據庫
Duyen Hoang

Duyen Hoang

1656714120

Cách Nhập Dữ Liệu CSV & Excel Vào Cơ Sở Dữ Liệu MySQL Trong Laravel 8

Nhập hàng loạt dữ liệu là một tính năng hữu ích để thêm các bản ghi được lưu trữ trong một tệp vào cơ sở dữ liệu. Dữ liệu có thể được lưu trữ ở định dạng - CSV, Excel, XML, ODS, v.v.

Trong hướng dẫn này, tôi chỉ cách bạn có thể nhập dữ liệu CSV & Excel vào cơ sở dữ liệu MySQL bằng cách sử dụng gói Laravel Excel trong dự án Laravel 8.


1. Cài đặt gói

Yêu cầu -

  • PHP: ^7.2\|^8.0
  • Laravel: ^5.8
  • PhpS Spreadsheet: ^1.21
  • psr / simple-cache: ^1.0
  • php_zip Đã bật tiện ích mở rộng 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

2. Cập nhật app.php

  • Mở  config/app.php tệp.
  • Thêm phần sau Maatwebsite\Excel\ExcelServiceProvider::class vào  'providers' -
'providers' => [
      ....
      ....
      ....  
      Maatwebsite\Excel\ExcelServiceProvider::class
];
  • Thêm phần sau  'Excel' => Maatwebsite\Excel\Facades\Excel::class vào  'aliases' -
'aliases' => [
     .... 
     .... 
     .... 
     'Excel' => Maatwebsite\Excel\Facades\Excel::class
];

3. Xuất bản gói

Chạy lệnh -

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

Điều này sẽ tạo một excel.phptệp mới trong config/.


4. Cấu hình cơ sở dữ liệu

Mở .envtệp.

Chỉ định máy chủ, tên cơ sở dữ liệu, tên người dùng và mật khẩu.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Tạo bảng

  • Tạo một bảng mới Employeesbằng cách sử dụng di chuyển và thêm một số bản ghi.
php artisan make:migration create_employees_table
  • Bây giờ, điều hướng đến database/migrations/thư mục từ thư mục gốc của dự án.
  • Tìm một tệp PHP có kết thúc bằng create_employees_tablevà mở nó.
  • Xác định cấu trúc bảng trong up()phương thức.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Chạy quá trình di chuyển -
php artisan migrate
  • Bảng đã được tạo.

6. Mô hình

  • Tạo Employeesmô hình.
php artisan make:model Employees
  • Mở  app/Models/Employees.php tệp.
  • Chỉ định các thuộc tính Model có thể gán hàng loạt - tên người dùng, tên, email và tuổi bằng cách sử dụng thuộc $fillabletính.

Mã đã hoàn thành

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Tạo lớp Nhập

Tôi đang tạo 2 lớp nhập chỉ cho mục đích ví dụ -

1. Lớp nhân viênImport -

php artisan make:import EmployeesImport --model=Employees
  • Mở  app/Imports/EmployeesImport.php tệp.
  • Lớp có 1 phương thức -
    • model () - Đọc giá trị từ $rowMảng và chèn bản ghi nếu id email không tồn tại trong employeesbả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 đề -

user1Người dùng1 u1user1@makitweb.com28
user2Người dùng2 u2user2@makitweb.com24

Mã đã hoàn thành

<?php
namespace App\Imports;

use App\Models\Employees;
use Maatwebsite\Excel\Concerns\ToModel;

class EmployeesImport implements ToModel {
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row) {
 
       // Check email already exists
       $count = Employees::where('email',$row[2])->count();
       if($count > 0){
          return null;
       }
       return new Employees([
          'username' => $row[0],
          'name' => $row[1], 
          'email' => $row[2],
          'age' => $row[3],
       ]);
    }

}

2. Lớp nhân viên2Import -

php artisan make:import Employees2Import --model=Employees
  • Mở  app/Imports/Employees2Import.php tệp.
  • Lớp có 2 phương thức -
    • collection () - Xác thực $rowsdữ liệu Mảng. Nếu xác thực thành công thì hãy chèn bản ghi nếu id email không tồn tại trong employeesbảng nếu không sẽ trả về lỗi.

LƯU Ý - Nếu  WithHeadingRowđược triển khai thì $rowsMảng chứa tiêu đề hàng dưới dạng tên khóa thay vì chỉ mục.

  • headerRow () - Trả về vị trí chỉ mục tiêu đề.

Tệp CSV có hàng tiêu đề -

tên tài khoảnTênE-mailTuổi tác
yssyogeshYogesh singhyogesh@makitweb.com28
bsonarikaSonarika Bhadoriabsonarika@makitweb.com28
vishalVishal Sahuvishal@makitweb.com31

Mã đã hoàn thành

<?php

namespace App\Imports;

use App\Models\Employees;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;

class Employees2Import implements ToCollection ,WithHeadingRow
{

    public function collection(Collection $rows){

       // Validate
       Validator::make($rows->toArray(), [
          '*.username' => 'required|string',
          '*.name' => 'required|string',
          '*.email' => 'required|email',
          '*.age' => 'required|integer',
       ],[
          '*.username.required'=> "The username field is required.",
          '*.username.string'=> "The username must be string.",
          '*.name.required'=> "The name field is required.",
          '*.name.string'=> "The name must be string.",
          '*.email.required'=> "The email field is required.",
          '*.email.email'=> "The email must be a valid email address.",
          '*.age.integer'=> "The age must be an integer."
       ])->validate();

       foreach ($rows as $row) {

          // Check email already exists
          $count = Employees::where('email',$row['email'])->count();
          if($count > 0){
             continue;
          }
          Employees::create([
             'username' => $row['username'],
             'name' => $row['name'], 
             'email' => $row['email'],
             'age' => $row['age'],
         ]);
       }
    }

    // Specify header row index position to skip
    public function headingRow(): int {
       return 1;
    }
}

8. Lộ trình

  • Mở  routes/web.php tệp.
  • Xác định 3 tuyến đường -
    • / -  Tải chế độ xem chỉ mục.
    • nhân viên / importdata - Đăng tuyến để Nhập dữ liệu.
    • nhân viên / validateandimportdata - Đăng tuyến để xác thực và nhập dữ liệu.

Mã đã hoàn thành

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::post('employees/importdata/', [EmployeesController::class, 'importData'])->name('employees.importdata');
Route::post('employees/validateandimportdata/', [EmployeesController::class, 'validateAndImportdata'])->name('employees.validateandimportdata');

9. Bộ điều khiển

  • Tạo  EmployeesController Bộ điều khiển.
php artisan make:controller EmployeesController
  • Mở  app/Http/Controllers/EmployeesController.php tệp.
  • Nhập   và  . EmployeesImportEmployees2ImportExcel
  • Tạo 3 phương pháp -
    • index () - Tải indexchế độ xem.
    • importdata () - Để nhập cuộc gọi Excel::import().

Chuyển 2 tham số -

  1. EmployeesImportCá thể lớp.
  2. Vượt qua vị trí 'tạm thời' của tệp đã chọn.
    • validateAndImportdata () - Một lần nữa gọi Excel::import().

Chuyển 2 tham số -

  1. Employees2ImportCá thể lớp.
  2. Chuyển vị trí tệp được lưu trữ (Tôi đã lưu tệp trong public/employees.xlsxthư mục).

Mã đã hoàn thành

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Imports\EmployeesImport;
use App\Imports\Employees2Import;
use Excel;

class EmployeesController extends Controller
{
   public function index(){
      return view('index');
   }

   // Import data 
   public function importdata(Request $request){
      Excel::import(new EmployeesImport, $request->file('file')->store('temp'));
      return back()->with('success', 'Import successfully!');
   }

   // Validate and Import data 
   public function validateAndImportdata(Request $request){

      Excel::import(new Employees2Import, "employees.xlsx");
      return back()->with('success', 'Import successfully!');
   }

}

10. Xem

Tạo  index.blade.php tệp trong  resources/views/ thư mục.

Tạo 2 <form>-

  • Trong <form >hành động đầu tiên đặt thành {{ route('employees.importdata') }}. Tạo một phần tử tệp và một nút gửi.
  • Trong <form>hành động đặt thứ 2 thành {{ route('employees.validateandimportdata') }}. Tạo nút gửi.

Lặp $errors->all()lại để hiển thị lỗi.

Mã đã hoàn thành

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <title>How to Import data into MySQL database in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <!-- Success message -->
      @if(Session::has('success'))
         <div class="alert alert-success">
            {{ Session::get('success') }}
         </div>
      @endif

      <form method='post' action="{{ route('employees.importdata') }}" enctype="multipart/form-data">
         @csrf
         <div class="mb-3">
            <label for="file" class="form-label">File</label>
            <input type="file" class="form-control" id="file" name="file" value="">
         </div>

         <button type="submit" class="btn btn-success">Import</button>
      </form>

      <!-- Import data with validation -->
      <h2 class='mt-5'>Validate and import data</h2>
      {{-- Display errors --}}
      @if (count($errors) > 0)
         <div class="row">
            <div class="col-md-12 ">
                <div class="alert alert-danger">
                   <ul>
                      @foreach($errors->all() as $error)
                         <li>{{ $error }} </li>
                      @endforeach 
                   </ul> 
                </div>
            </div>
         </div>
      @endif

      <form method='post' action="{{ route('employees.validateandimportdata') }}" >
         @csrf
         <button type="submit" class="btn btn-success">Import</button>
      </form>
   </div>

</body>
</html>

11. Đầu ra

Xem đầu ra


12. Kết luận

Đảm bảo xác thực bản ghi trước khi chèn hoặc cập nhật bản ghi trong lớp Nhập. Chỉ định WithHeadingRowtrong lớp Nhập nếu tệp nhập có hàng tiêu đề.

Nguồn: https://makitweb.com

#laravel #php #mysql 

Cách Nhập Dữ Liệu CSV & Excel Vào Cơ Sở Dữ Liệu MySQL Trong Laravel 8

CakeImpersonate: CakePHP Impersonate Plugin

CakeImpersonate Plugin 

Impersonate Component

A component that stores the current authentication session and creates new session for impersonating Users. User can revert back to original authentication sessions without the need to re-login.

Warning

Always double check that an attacker cannot "spoof" other users in the controller actions. To prevent hijacking of users accounts that the current request User shouldn't/wouldn't have normal access to. You should enable CsfrComponent and SecurityComponent in your Controller when loading this component.

This Plugin does circumvent default authentication mechanisms

Requirement

  1. CakePHP 3.7 and above.

Installation/Upgrading

composer require jomweb/cake-impersonate:"^3.0"

Plugin Load

Open \src\Application.php add

$this->addPlugin('CakeImpersonate');

to your bootstrap() method or call bin/cake plugin load CakeImpersonate

Component Load

Load the component from controller

$this->loadComponent('CakeImpersonate.Impersonate'); 

Configure Session Key

Open configure\app.php and add

'Impersonate' => [
    'sessionKey' => 'OriginalAuth'
]

to the return []; or use Configure::write('Impersonate.sessionKey', 'OriginalAuth'); when loading the component.

Usage

Impersonate user

This requires the request to be a POST, PUT, DELETE so it can be protected by SecurityComponent and CsrfComponent

$this->Impersonate->login($userIdToImpersonate);

Check current user is impersonated

$this->Impersonate->isImpersonated();

Logout from impersonating

$this->Impersonate->logout();

Author: jomweb
Source Code: https://github.com/jomweb/CakeImpersonate 
License: MIT license

#php #cakephp #authentication 

CakeImpersonate: CakePHP Impersonate Plugin