export data in excel from multiple tables

i wanted to export data from multiple table with multiple drop down

Here Is my table names

1-products------------id

2-proattrs--product_id

3-prostocks---product_id

I wwated to get data using reference id from all tables and making to dropdown in excel file for my attribute and stock table colomns

Here Is My rotes

    Route::get('export_excel', 'ImportExportController@importExportView')->name('export_excel');
    Route::post('export', 'ImportExportController@export')->name('export');
    Route::post('import', 'ImportExportController@import')->name('import');

here is my controller

                <?php
                namespace App\Http\Controllers;
                use Validator,Redirect,Response;
                use Illuminate\Http\Request;
                use App\ProductsExport;
                use App\ProductsImport;
                use Excel;
                use DB;

            class ImportExportController extends Controller
            {
                public function importExportView()
                {
                   return view('dashboard.excel.export_excel');
                }
                public function export(Request $request)
                {
                    $request->validate([
                        'primaryCategory'=>'required'          
                    ],[
                            'primaryCategory.required' => 'Please select category'
                    ]);
                    return Excel::download(new ProductsExport($request->primaryCategory), 'products.xlsx');
                }
                public function import(Request $request)
                {
                    $request->validate([
                        'bulk_file'=>'required|max:50000|mimes:xlsx,doc,docx,ppt,pptx,ods,odt,odp'          
                    ],[
                            'bulk_file.required' => 'Please upload .xlsx file'
                    ]);
                  if($request->hasFile('bulk_file')){
                        Excel::import(new ProductsImport, request()->file('bulk_file'));
                    }
                    return redirect()->route('export_excel')->with('message','Products exported successfully!');
                }
            }

And Here Is my Model

            <?php
        namespace App;
        use Illuminate\Support\Collection;
        use Maatwebsite\Excel\Concerns\ToCollection;
        use Maatwebsite\Excel\Concerns\FromCollection;
        use Maatwebsite\Excel\Concerns\WithHeadings;
        use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
        use Maatwebsite\Excel\Concerns\WithEvents;
        use Maatwebsite\Excel\Events\AfterSheet;
        use Maatwebsite\Excel\Concerns\ShouldAutoSize;
        use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
        use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
        use DB;

        class ProductsExport implements ToCollection, FromCollection, ShouldAutoSize, WithHeadings, WithEvents, WithStrictNullComparison
        {
            protected $results;
            public $catId;
            public function __construct($catId)
            {
                $this->catId = $catId;
            }
            public function collection()
            {
                // store the results for later use
                $this->results = $this->getActionItems();
                return $this->results;
            }
            public function headings(): array
            {
                $columns = [
                    'Title',
                    'Brand',
                    'Description',
                    'Gst Rate',
                    'Handling Time',
                    'Legal Disclaimer',
                    'Special Features',
                    'Return Policy',
                    'Packet Weight',
                    'Packet Width',
                    'Packet Height',
                    'Packet Length',
                    'MetaTitle',
                    'Meta Description',
                    'Search Keywords',
                    'Slug'
                ];
                return $columns;
            }
            private function getActionItems()
            {
                $select = 'title, brand ,description, gstRate, handlingTime, legalDisclaimer, specialFeatures,returnPolicy, packetWeight, packetWidth, packetHeight, packetLength, metaTitle, metaDescription, searchKeywords, slug';
                $query = \DB::table('product_adds')->select(\DB::raw($select));
                return $query->whereRaw("FIND_IN_SET(?, primaryCategory) > 0", explode(',', $this->catId))->get();
            }
            public function registerEvents(): array
            {
                return [
                    // handle by a closure.
                    AfterSheet::class => function(AfterSheet $event) {
                        // get layout counts (add 1 to rows for heading row)
                        $row_count = $this->results->count() + 1;
                        $column_count = count($this->results);
                        
                        foreach ($this->results as $brandId) {
                          $brandName = DB::table('brands')->where('id', $brandId->brand)->get();
                                        
                        // set dropdown column
                        $drop_column = 'B';
                        // set dropdown options
                        $options = [
                            $brandName[0]->brandname
                        ];
                    } 
                        // set dropdown list for first data row
                        $validation = $event->sheet->getCell("{$drop_column}2")->getDataValidation();
                        $validation->setType(DataValidation::TYPE_LIST );
                        $validation->setErrorStyle(DataValidation::STYLE_INFORMATION );
                        $validation->setAllowBlank(false);
                        $validation->setShowInputMessage(true);
                        $validation->setShowErrorMessage(true);
                        $validation->setShowDropDown(true);
                        $validation->setErrorTitle('Input error');
                        $validation->setError('Value is not in list.');
                        $validation->setPromptTitle('Pick from list');
                        $validation->setPrompt('Please pick a value from the drop-down list.');
                        $validation->setFormula1(sprintf('"%s"',implode(',',$options)));

                        // clone validation to remaining rows
                        for ($i = 3; $i <= $row_count; $i++) {
                            $event->sheet->getCell("{$drop_column}{$i}")->setDataValidation(clone $validation);
                        }
                        // set columns to autosize
                        for ($i = 1; $i <= $column_count; $i++) {
                            $column = Coordinate::stringFromColumnIndex($i);
                            $event->sheet->getColumnDimension($column)->setAutoSize(true);
                        }
                    },
                ];
            }
        }

i wanted to know ho can i get data from multiple tables And Make DropDown For Multiple Cells using reference Id Please Help Me

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2L4soT2
via IFTTT