to install maatwebsite
composer require maatwebsite/excel
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
php artisan make:export UsersExOIRT
<?php
namespace App\Exports;
use App\Models\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class UsersExport implements FromCollection, WithHeadings
{
public function collection()
{
return User::select('id', 'name', 'email')->get();
}
public function headings(): array
{
return [
'ID',
'Name',
'Email',
// Add more headers as needed
];
}
}
==
<?php
namespace App\Http\Controllers\Api;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
class ExcelController extends Controller
{
public function exportUsers()
{
return Excel::download(new UsersExport(), 'users.xlsx');
}
public function s()
{
Excel::import(new UsersImport(), request()->file('file'));
return redirect('/')->with('success', 'Users imported successfully!');
}
public function importUsers(Request $request)
{
try {
if ($request->hasFile('file_input')) {
$filePaths = [];
$file=$request->file('file_input');
$filePath = $file->store('file_input', 'public');
}
$storage_path =storage_path('app/public/'.$filePath);
if (!file_exists($storage_path )) {
$this->error('File not found: ' . $storage_path );
return 'file not found';
}
$import =new UsersImport();
$import->import($storage_path);
$validationErrors = $import->getValidationErrors();
if (!empty($validationErrors)) {
// Handle validation errors
return response()->json([
'status' => 'error',
'message' => 'Validation errors during import:',
'data'=>implode(', ', $validationErrors)
], 422);
}
return response()->json([
'status' => 'success',
'message' => 'Excel Imported Successfully',
], 201);
} catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
$failures = $e->failures();
$errors = [];
foreach ($failures as $failure) {
$rowIndex = $failure->row(); // Get the row index where the error occurred
$columnName = $failure->attribute(); // Get the column name where the error occurred
$errorMessage = $failure->errors()[0]; // Get the error message for the column
$errors[] = "Row $rowIndex: $columnName - $errorMessage"; // Combine row index, column name, and error message
}
return response()->json([
'status' => 'error',
'message' => 'Validation errors during import:',
'data'=>implode(', ', $validationErrors)
], 422);
}catch (\Maatwebsite\Excel\Exceptions\Skipped $e) {
return 'vamsi';
}
catch (\Exception $e) {
// Handle any exceptions that occur during the process.
return response()->json([
'status' => 'error',
'message' => 'Validation errors during import:',
'data'=> $e->getMessage()
], 422);
}
}
}
=
Route::get('export-users', [ExcelController::class, 'exportUsers']);
Route::post('import-users', [ExcelController::class, 'importUsers'])->name('import_users');
=
<a href="{{ url('admin/export-users') }}">Export Users</a>
=
EXAMPLES TO EXPORT
<?php
namespace App\Exports;
use App\Models\Companies;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Illuminate\Support\Collection;
use App\Models\UserHasBatch;
use App\Models\Datalog;
class CompaniesExport implements FromCollection, WithHeadings
{
protected $userId;
protected $batchIds;
protected $companyIds;
public function __construct($userId)
{
$this->userId = $userId;
$this->batchIds=UserHasBatch::where('user_id', $this->userId)->pluck('batch_id');
$this->companyIds=Datalog::whereIn('batch_id',$this->batchIds)->pluck('company_id');
}
public function collection()
{
$data = new Companies();
$data= $data->whereIn('id',$this->companyIds);
$data = $data->orderBy('id', 'desc');
$data = $data->get();
\Log::info($data);
$keys=[
'created_at',
'business_name',
'public_id',
'headcount',
'sic',
'sic_description',
'website',
'contacts.0.email',
'contacts.0.numbers.0.number',
'sites.0.address1',
'sites.0.address2',
'sites.0.address3',
'sites.0.town',
'sites.0.city',
'sites.0.county',
'sites.0.locality',
'sites.0.postcode'
];
$index =0;
$extractedData = $data->map(function ($item,$index) use ($keys) {
$extractedItem = [];
$extractedItem[] = $index + 1;
foreach ($keys as $key) {
$value = data_get($item, $key, null);
$extractedItem[] = $value;
}
return $extractedItem;
});
return collect([$extractedData]);
}
public function headings(): array
{
return ['SI NO','Date','Business Name','Public ID','Headcount','SIC','SICDescription','Website', 'Email',
'Numbers',
'Address1',
'Address2',
'Address3',
'Town',
'City',
'County',
'Locality',
'Postcode'
];
}
}
==
IMPORT EXCEL
php artisan make:import UsersImport
or cards import
we hav so many row sin eaxch excel
ist row is header
we willnot process this
we will take it as reference
then remaining rows are data
we have to valdiate eadh row and each column
if all data is right then we will proces otherwise it will show errors
firstvalidate
show custom mesage
and show that error to ui
if no erro r
process tghat
process => to model
validation --> rules and custom messages
error at catch
public function importUsers(Request $request)
{
try {
if ($request->hasFile('file_input')) {
$filePaths = [];
$file=$request->file('file_input');
$filePath = $file->store('file_input', 'public');
}
$storage_path =storage_path('app/public/'.$filePath);
if (!file_exists($storage_path )) {
$this->error('File not found: ' . $storage_path );
return 'file not found';
}
$import =new UsersImport();
$import->import($storage_path);
$validationErrors = $import->getValidationErrors();
if (!empty($validationErrors)) {
// Handle validation errors
return response()->json([
'status' => 'error',
'message' => 'Validation errors during import:',
'data'=>implode(', ', $validationErrors)
], 422);
}
return response()->json([
'status' => 'success',
'message' => 'Excel Imported Successfully',
], 201);
} catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
$failures = $e->failures();
$errors = [];
foreach ($failures as $failure) {
$rowIndex = $failure->row(); // Get the row index where the error occurred
$columnName = $failure->attribute(); // Get the column name where the error occurred
$errorMessage = $failure->errors()[0]; // Get the error message for the column
$errors[] = "Row $rowIndex: $columnName - $errorMessage"; // Combine row index, column name, and error message
}
return response()->json([
'status' => 'error',
'message' => 'Validation errors during import:',
'data'=>implode(', ', $validationErrors)
], 422);
}catch (\Maatwebsite\Excel\Exceptions\Skipped $e) {
return 'vamsi';
}
catch (\Exception $e) {
// Handle any exceptions that occur during the process.
return response()->json([
'status' => 'error',
'message' => 'Validation errors during import:',
'data'=> $e->getMessage()
], 422);
}
}
==
<?php
namespace App\Imports;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use App\Models\Card;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\SkipsEmptyRows;
use Maatwebsite\Excel\Concerns\Importable;
use Illuminate\Support\Facades\Validator;
class UsersImport implements ToModel, WithStartRow, WithChunkReading, WithCustomCsvSettings,WithHeadingRow,SkipsEmptyRows,withValidation
{
use Importable;
private $validationErrors = [];
public function headingRow(): int
{
return 1; // Change this to the row number where your headers are located
}
public function model(array $row)
{
return new Card([
'account_type' => $row['account_type'],
'name' => $row['name'],
'description' => $row['description'],
'formula' => $row['formula'],
'upi_id' => $row['upi_id'],
// Add more fields as needed
]);
}
public function startRow(): int
{
return 2; // Skip the first row (header row)
}
// WithChunkReading implementation
public function chunkSize(): int
{
return 1000; // Process 1000 rows at a time
}
// WithValidation implementation
public function rules(): array
{
return [
'account_type' => 'required|in:savings,credit_card,loan',
'name' => 'required|string|max:255',
'description' => 'nullable|string',
'formula' => 'nullable|string',
'upi_id' => 'required|string|max:255',
];
}
public function customValidationMessages()
{
return [
'account_type.required' => 'The account type field is required.',
'account_type.in' => 'The account type must be one of: savings, credit_card, loan',
'name.required' => 'The name field is required.',
'name.string' => 'The name field must be a string.',
'name.max' => 'The name field must not exceed 255 characters.',
'description.string' => 'The description field must be a string.',
'formula.string' => 'The formula field must be a string.',
'upi_id.required' => 'The UPI ID field is required.',
'upi_id.string' => 'The UPI ID field must be a string.',
'upi_id.max' => 'The UPI ID field must not exceed 255 characters.',
];
}
public function getCsvSettings(): array
{
return [
'input_encoding' => 'UTF-8',
'delimiter' => ',',
'enclosure' => '"',
'escape' => '\\',
'validation_messages' => $this->customValidationMessages(),
];
}
public function onError(\Throwable $e)
{
if ($e instanceof \Maatwebsite\Excel\Validators\ValidationException) {
$failures = $e->failures();
foreach ($failures as $failure) {
$rowIndex = $failure->row();
$columnName = $failure->attribute();
$errorMessage = $failure->errors()[0];
$this->validationErrors[] = "Row $rowIndex: $columnName - $errorMessage";
}
} else {
\Log::error('An error occurred during import: ' . $e->getMessage());
}
}
// public function onError(\Throwable $e)
// {
// if ($e instanceof \Maatwebsite\Excel\Validators\ValidationException) {
// $failures = $e->failures();
// $errors = [];
// foreach ($failures as $failure) {
// $rowIndex = $failure->row(); // Get the row index where the error occurred
// $columnName = $failure->attribute(); // Get the column name where the error occurred
// $errorMessage = $failure->errors()[0]; // Get the error message for the column
// $errors[] = "Row $rowIndex: $columnName - $errorMessage"; // Combine row index, column name, and error message
// }
// // Log validation errors
// \Log::error('Validation errors during import: ' . implode(', ', $errors));
// } else {
// // Log other errors
// \Log::error('An error occurred during import: ' . $e->getMessage());
// }
// }
public function getValidationErrors()
{
return $this->validationErrors;
}
}
====
start row => tells from where data process in excle
headeing => it tell whcih row is header
chuinksize => it is for how many rows execute
rules
validatiaion messages
getcsvssettigns tells which message we can take
import ui
<button type="button" class="btn btn-primary" data-toggle="modal" data-target="#myModal">
Bulk Upload
</button>
==
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
@php
$elements = [
[
'method'=>'input',
'label' => 'Choose File',
'key' => 'file_input',
'place_holder' => 'Choose File',
'type' => 'file',
'required'=>true,
'readonly'=>false
],
];
$modal_name='Upload File';
$action_name='Upload';
$route_name='import_users';
@endphp
@include('components.modal-form', ['elements' => $elements,'modal_name'=>$modal_name,'action_name'=>$action_name,'route_name'=>$route_name])
</div>
=
<div class="modal-dialog" role="document">
<div class="modal-content">
<!-- Modal Header -->
<div class="modal-header">
<h5 class="modal-title" id="myModalLabel">{{$modal_name}}</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<!-- Modal Body -->
<div class="modal-body">
<!-- Form to upload file -->
<form method="POST" action="{{ route($route_name) }}" enctype="multipart/form-data">
@csrf
@include('components.form-elements', ['elements' => $elements])
<button type="submit" class="btn btn-primary">{{$action_name}}</button>
</form>
</div>
<!-- Modal Footer -->
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</div>
</div>
==
1:08:06 to08
some examples
<?php
namespace App\Imports;
use App\Models\Companies;
use App\Models\Datalog;
use Exception;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsEmptyRows;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithStartRow;
use Maatwebsite\Excel\Concerns\WithUpserts;
use Maatwebsite\Excel\Concerns\WithValidation;
use Throwable;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Validator;
use App\Events\NotificationEvent;
use App\Http\Traits\EventFormatter;
/**
* @property array $errors
*/
class CompaniesImport implements ToModel, WithChunkReading, ShouldQueue, WithStartRow, SkipsEmptyRows, WithUpserts, SkipsOnError
{
use Dispatchable, Queueable, Importable, EventFormatter;
private $headers = [];
/**
* @var Collection
*/
private $references = [];
/**
* @var mixed|null
*/
private $batchData;
private $errors = [];
public function __construct($headers, $batchInfo = null)
{
$this->headers = $headers;
$this->batchData = $batchInfo;
$this->references = Companies::all("id", "public_id")->pluck("public_id", "id");
}
public function chunkSize(): int
{
return 10000;
}
public function batchSize(): int
{
return 20;
}
public function setHeaders($headers)
{
$this->headers = $headers;
}
public function startRow(): int
{
return 2;
}
/**
* @param array $row
*
* @return Companies
*/
public function rules(): array
{
return [
'business_name' => 'required|string|max:255',
// 'public_id' => 'nullable|string',
// 'data_urn' => 'nullable|string',
'reg' => 'required|integer',
// 'type' => 'nullable|integer',
'website' => 'required|string|max:255',
'headcount' => 'required|integer',
'est_turnover' => 'required|integer',
'sic' => 'required|integer',
'sic_description' => 'required|string',
];
}
public function model(array $row)
{
$data = [
'id' => null,
"business_name"=>"",
"public_id" => "",
"data_urn" => "",
"reg" => null,
"website" => null,
"headcount" => 0,
"type" => 0,
"est_turnover" => 0,
"sic" => null,
"sic_description" => null,
"created_at" => now(),
"updated_at" => now()
];
if ($this->headers == "") {
return null;
}
foreach ($this->headers as $key => $header) {
$data[$header] = $row[$key];
}
$validator = Validator::make($data, $this->rules());
if ($validator->fails()) {
// Log the validation error and skip the iteration
\Log::error('companies Validation error: ' . $validator->errors()->first() . "(" . json_encode($row) . ")");
$this->notification([
"title" => 'Companies Validation Error',
"subtitle" => $validator->errors()->first() . "(" . json_encode($row) . ")",
"type" => 'light-success'
]);
return null;
}
// foreach ($this->headers as $key => $header) {
// $data[$header] = $row[$header];
// }
try {
$uniqid = env("COMPANY_PREFIX") . "/" . rand(1000000, 9999999);
while (in_array($uniqid, (array)$this->references, true)) {
$uniqid =env("COMPANY_PREFIX") . "/" . rand(1000000, 9999999);
$data['public_id'] = $uniqid;
}
$company = Companies::updateOrCreate([
"business_name" => $data['business_name']
],
$data
);
if ($company->wasRecentlyCreated) {
$company->public_id = $uniqid;
}
if ($this->batchData != null) {
$batch = $this->batchData;
// dd($batch);
$batch->company_id = $company->id;
// $batch->status = 1;
$databatch = Datalog::firstOrCreate(["company_id" => $company->id]);
$batch = $databatch->update((array)$batch);
$batch = null;
}
return $company;
} catch (Exception $e) {
\Log::error('companies errror: ' .$e->getMessage());
die($e->getMessage());
}
}
public function getErrors()
{
return $this->errors ?? [];
}
public function uniqueBy()
{
return ['business_name'];
}
public function onError(Throwable $e)
{
// TODO: Implement onError() method.
$this->errors[] = $e->getMessage();
}
}
=
php artisan import:batch '{"batchData":{"batch_id":"VAMSI-89589","sector_id":6,"provider_id":2,"user":3,"user_name":"testing","licensed_until":"2023-12-30 10:31:28"},"Company":["business_name","reg","company_type","website","headcount","est_turnover","sic","sic_description"],"Sites":{"0":"business_name","14":"address1","15":"address2","16":"address3","17":"town","18":"city","19":"county","20":"locality","21":"postcode"},"Contacts":{"0":"business_name","9":"title","10":"forename","11":"surname","12":"position","13":"email"},"Contact_Numbers":{"8":"number","13":"email"}}' /var/www/html/storage/app/public/files/vkguptha.xlsx
=
import batch
<?php
namespace App\Console\Commands;
use App\Events\Test;
use App\Imports\CompaniesImport;
use App\Jobs\ProcessContactNumbers;
use App\Jobs\ProcessContacts;
use App\Jobs\ProcessSites;
use App\Models\User;
use Illuminate\Bus\Batch;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Bus;
use App\Events\NotificationEvent;
use Mockery\Exception;
use App\Models\UserHasBatch;
use App\Helpers\SqsHelper;
class ImportManager extends Command
{
protected $sqsHelper;
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'import:batch {config} {importfile}';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Command for importing Batch Data';
protected $chunkSize = 100;
/**
* Create a new command instance.
*
* @return void
*/
public function __construct()
{
parent::__construct();
}
/**
* Execute the console command.
* TODO: See if you can chain these
* @return int
*/
public function handle()
{
try {
$sqsHelper = new SqsHelper();
$sqsHelper->check();
$id = uniqid();
$file = $this->argument("importfile");
$data = json_decode($this->argument("config"));
if (!file_exists($file)) {
$this->error("File does not exist!");
return false;
}
$data->batchData->batch_id = $data->batchData->batch_id . $id;
$name=$data->batchData->batch_id;
UserHasBatch::create([
'user_id' => $data->batchData->user,
'batch_id' => $data->batchData->batch_id,
]);
(new CompaniesImport(($data->Company ?? ""), $data->batchData))->queue($file)->allOnQueue('excel-import');
Bus::batch([
new ProcessSites(($data->Sites ?? ""), $file),
new ProcessContacts(($data->Contacts ?? ""), $file),
new ProcessContactNumbers(($data->Contact_Numbers ?? ""), $file)
])->name($name)->onQueue('bus-excel-import')->then(function (Batch $batch) use ($data) {
$user = User::find($data->batchData->user);
$user_parts = str_replace("|", ".", $user->sub);
event(new Test([
"title" => 'Data Manager',
"subtitle" => 'Batch uploaded successfully',
"type" => 'light-success'
], $user_parts));
})->dispatch();
} catch (Exception $e) {
\Log::error($e);
$this->error($e->getMessage());
}
// endregion
return "Done";
}
}
==
No comments:
Post a Comment