Search form to date export Excel in Laravel 6

Laravel 6 - Search Data by Date Range and Export to Excel

In Laravel 6, you can search for records by a date range and export the filtered data to Excel using the Maatwebsite Excel package.

1. Install Maatwebsite Excel Package

Run the following command to install maatwebsite/excel:

composer require maatwebsite/excel

After installation, the service provider will be auto-discovered, so you don’t need to manually add it to config/app.php.

2. Create Database Table and Model

Ensure your database credentials are properly set in .env:

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

Create Migration for Orders Table

If you haven’t created the migration already, you can create it like this:

php artisan make:migration create_orders_table

Modify database/migrations/xxxx_xx_xx_create_orders_table.php:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateOrdersTable extends Migration
{
    public function up()
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->id();
            $table->string('customer_name');
            $table->decimal('amount', 10, 2);
            $table->date('order_date');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('orders');
    }
}

Run the migration:

php artisan migrate

Create Order Model

php artisan make:model Order

Modify app/Order.php:

namespace App;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $fillable = ['customer_name', 'amount', 'order_date'];
}

3. Create Controller

Run the command to create a controller:

php artisan make:controller OrderController

Modify app/Http/Controllers/OrderController.php:

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Order;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\OrdersExport;

class OrderController extends Controller
{
    public function index(Request $request)
    {
        $query = Order::query();

        // If 'from' and 'to' dates are provided, apply the filter
        if ($request->has('from') && $request->has('to')) {
            $from = $request->input('from');
            $to = $request->input('to');
            $query->whereBetween('order_date', [$from, $to]);
        }

        $orders = $query->get();

        return view('orders.index', compact('orders'));
    }

    public function exportExcel(Request $request)
    {
        return Excel::download(new OrdersExport($request->from, $request->to), 'orders_report.xlsx');
    }
}

4. Create Export Class

Generate an export class for the Orders:

php artisan make:export OrdersExport

Modify app/Exports/OrdersExport.php:

namespace App\Exports;

use App\Order;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class OrdersExport implements FromCollection, WithHeadings
{
    protected $from;
    protected $to;

    public function __construct($from, $to)
    {
        $this->from = $from;
        $this->to = $to;
    }

    public function collection()
    {
        return Order::whereBetween('order_date', [$this->from, $this->to])->get();
    }

    public function headings(): array
    {
        return ['ID', 'Customer Name', 'Amount', 'Order Date'];
    }
}

5. Create Blade View for Search Form

Create resources/views/orders/index.blade.php:

<!DOCTYPE html>
<html>
<head>
    <title>Search Orders and Export Excel</title>
</head>
<body>
    <h2>Search Orders</h2>

    <form method="GET" action="{{ url('/orders') }}">
        <label>From Date:</label>
        <input type="date" name="from" required>

        <label>To Date:</label>
        <input type="date" name="to" required>

        <button type="submit">Search</button>
    </form>

    @if(isset($orders))
        <h3>Order Results</h3>
        <table border="1">
            <tr>
                <th>ID</th>
                <th>Customer Name</th>
                <th>Amount</th>
                <th>Order Date</th>
            </tr>
            @foreach ($orders as $order)
            <tr>
                <td>{{ $order->id }}</td>
                <td>{{ $order->customer_name }}</td>
                <td>${{ $order->amount }}</td>
                <td>{{ $order->order_date }}</td>
            </tr>
            @endforeach
        </table>

        <form method="GET" action="{{ url('/orders/export-excel') }}">
            <input type="hidden" name="from" value="{{ request('from') }}">
            <input type="hidden" name="to" value="{{ request('to') }}">
            <button type="submit">Export to Excel</button>
        </form>
    @endif
</body>
</html>

6. Define Routes

Modify routes/web.php:

use App\Http\Controllers\OrderController;

Route::get('/orders', [OrderController::class, 'index']);
Route::get('/orders/export-excel', [OrderController::class, 'exportExcel']);

7. Run the Application

Start Laravel development server:

php artisan serve

Test the Application

Visit:
```
http://127.0.0.1:8000/orders
```
Select From Date and To Date, then click Search.
If results appear, click "Export to Excel" to download the Excel file.

8. Additional Features

Customize Excel Export Format
You can customize how your Excel looks by using more Maatwebsite Excel features, such as styling, multiple sheets, etc.

Use Chunking for Large Data Sets
If you have large amounts of data, consider using chunking:

public function collection()
{
    return Order::whereBetween('order_date', [$this->from, $this->to])->chunk(100);
}

Conclusion

✅ Dynamic date search using whereBetween().
✅ Excel export with Maatwebsite Excel.
✅ User-friendly UI with a simple search form.

Souy Soeng