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
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire