Wednesday, 18 January 2023

Laravel export data to excel with DB query , with dynamic table and dynamic columns using laravel excel



use Illuminate\Support\Facades\Schema;

use Illuminate\Support\Facades\DB;

1) controller function

 public function getExportData(Request $request,$tableName)

    {

        $headings=[];

        $cols = array_values(Schema::getColumnListing($tableName));

        if (($key = array_search("updated_at", $cols)) !== false) {

            unset($cols[$key]);

        }

        if (($key = array_search("created_at", $cols)) !== false) {

            unset($cols[$key]);

        }

        $selectedCols=$cols;

        foreach($cols as $headKey=>$headValue){

            $headValue=ucfirst($headValue);

            $headings[]=str_replace("_", " ",  $headValue);

        }

        return Excel::download(new FinalDataExport($tableName,$selectedCols,$headings), "$tableName.xlsx");

    }


2)  Export File

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;

use Maatwebsite\Excel\Concerns\FromQuery;

use Illuminate\Support\Facades\DB;

use Illuminate\Support\Facades\Schema;

use Maatwebsite\Excel\Concerns\WithHeadings;


class FinalDataExport implements FromCollection,WithHeadings

{

    public $table_name;

    public $selectedCols;

    public $headings;

    public function __construct($table_name,$selectedCols,$headings)

    {

        $this->table_name=$table_name;

        $this->selectedCols=$selectedCols;

        $this->headings=$headings;

    }

    public function collection()

    {

        return DB::table($this->table_name)->select($this->selectedCols)->orderBy('id','ASC')->get();

    }

    public function headings(): array

    {

        return $this->headings;

    }

}

No comments:

Post a Comment

Laravel Export data to csv

 use Illuminate\Http\Response; // Define a function to export data to CSV function exportToCSV($exportData, $columns) {     $filename = ...