[FIXED] How to download excel template will display with only header in Laravel Excel 3.1?

Issue

Good Day guys,.. I need to download a excel template only show with headers,..

I have good and working function code in laravel-excel version 2 of my problem,. but in version 3.1 i dont know how to code it.

this is my code in version 2;

public function downloadCoursesTemplate()
{
    $columns = array(
        'Course Code',
        'Course Description',
        'Status'
    );

    return Excel::download('Courses', function ($excel) use ($columns) {
        $excel->sheet('Courses', function ($sheet) use ($columns) {
            $sheet->fromArray($columns);
        });
    })->export('xlsx');
}

this is the output:

I want that on laravel-excel version 3.1

There have documentation in new versions but i could not find it to solve my issue.

Is anyone can help me? thanks. sorry for my English grammar.

Solution

The documentation should be pretty clear, it even has a quick start guide here

But here is what you can do:

Generate the export class

php artisan make:export CoursesTemplateExport

Change the class to the following:

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;

class CoursesTemplateExport implements FromArray, WithHeadings
{
    /**
     * @return array
     */
    public function array(): array
    {
        return [];
    }

    /**
     * @return array
     */
    public function headings(): array
    {
        return [
            'Course Code',
            'Course Description',
            'Status'
        ];
    }
}

We can implement the FromArray interface so we can just return an empty array since we don't have any data, and we can implement the WithHeadings interface to declare the headings the export should have.

In your Controller:

use App\Exports\CoursesTemplateExport;

public function downloadCoursesTemplate()
{
    return Excel::download(new CoursesTemplateExport(), 'Courses.xlsx');
}

This will result in the following excel:

Answered By - Remul

Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Read More

[FIXED] How to get real number while importing on Laravel Excel maatwebsite?

Issue

I'd like to get numbers as the real value while importing a file, e.g:

When I open the csv, cell value: 198610012009011005

But when I import that using Laravel Excel, it'll be formatted to 1.98610012009011E+17

How can I get the real value of the number (198610012009011005) ? I tried bellow code but it didn't work

$data['excel'] = Excel::load($path, function ($reader) {
          $reader->sheet(0, function ($sheet) {
              $sheet->setColumnFormat(["A" => "@"]);
          });
      })->toArray();

Solution

Actually the value you get is true. 1.98610012009011E+17 is the form of exponential value. But if you want get it as string form try this approach.

You should create a ValueBinder class.

// MyValueBinderClass
use PHPExcel_Cell;
use PHPExcel_Cell_DataType;
use PHPExcel_Cell_IValueBinder;
use PHPExcel_Cell_DefaultValueBinder;

class MyValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {

        if (is_numeric($value))
        {
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }
}

And then bind it to while you load the csv file:

$myValueBinder = new MyValueBinder;
$data = Excel::setValueBinder($myValueBinder)
    ->load($path)->toArray();

reference: http://www.maatwebsite.nl/laravel-excel/docs/import#formatting

Answered By - Dharma Saputra

Answer Checked By - Mildred Charles (PHPFixing Admin)

Read More

[FIXED] Why Laravel-Excel reads first row as column name

Issue

I need to read an .xls file and put it into an array. I'm using laravel-excel package for reading excel files.

I have a Excel file like this :

I need to have an array like this :

[
 '9921234567' => 'First Text',
 '9929876544' => 'Second Text',
 '9927654321' => 'Third Text',
 '9928765432' => 'Fourth Text',

]

What I have tried so far :

 Excel::load('sample.xls', function($reader) {
     $reader->dd();
 });

Problem:

The problem is it reads the first row as column!

0 => RowCollection {#619
      #title: "Sheet1"
      #items: array:3 [
        0 => CellCollection {#623
          #title: null
          #items: array:2 [
            9921234567 => 9929876544.0
            "first_text" => "Second Text"
          ]
        }
        1 => CellCollection {#624
          #title: null
          #items: array:2 [
            9921234567 => 9927654321.0
            "first_text" => "Third Text"
          ]
        }
        2 => CellCollection {#625
          #title: null
          #items: array:2 [
            9921234567 => 9928765432.0
            "first_text" => "Fourth Text"
          ]
        }
      ]
    }

Look, I don't want to first row values count as column name!

Any helps would be great appreciated.

Solution

You linked answer in documentation

Table heading as attributes By default the first row of the excel file will be used as attributes.

You can change the default inside the config excel::import.heading. Available options are: true|false|slugged|ascii|numeric|hashed|trans|original

I never used laravel but just set it to false and check what will happen.

Answered By - Vini

Answer Checked By - Pedro (PHPFixing Volunteer)

Read More

[FIXED] How to check if the first row or columns of Excel file is valid in Laravel Excel?

Issue

I'm trying to check if the excel file a user has uploaded has the correct columns/first row/attributes before reading into its other rows. I'm using Laravel 4 with MaatWebsite's Laravel Excel.

I have created a function which allows users to update the employee table of the system's database by importing an excel file.

Now the correct excel file, has, let's say, 3 columns: firstname, lastname, and username. If the excel file has all these attributes/columns, I will now read each of the following rows and validate each row, which isn't really a part of my problem as of now.

But if any of the 3 attribute/column is not present in the excel file, I'd ignore the request and return an error.

I tried using this, which gets lastname attribute:

$sample = Excel::selectSheetsByIndex(0)->load($readFile, function($reader){})->get(array("lastname"));

But even though lastname hasn't been found, $sample is still not null so I won't be able to check if lastname is present or not.

How can I check if the attributes/columns are present/not?

UPDATE:

The answer that I selected would work perfectly if the first row after the attributes row has all the needed attributes. For example: If it has values for firstname, lastname, and username.

But if in cases where first name value (or any attritbute value for that matter) of the first non-attribute row (attribute row referring to the column names) is missing, then the provided script would return false, even if the excel file has all the firstname, lastname, and username attributes.

So I modified the script to this:

1. First, I read the excel file. I also declared a Boolean variable to mark if the excel file is valid or not.

$excelChecker = Excel::selectSheetsByIndex(0)->load('path/to/file', function($reader){})->get()->toArray();

$excelIsValid = false;

2. Then I loop through all the results and check if at least once, all the values required (firstname, lastname, and username) have been set or are present.

   foreach($excelChecker as $ex){
   
       if(isset($ex["lastname"]) && isset($ex["firstname"]) && isset($ex["username"])){
            $excelIsValid = true;
      }
   }
   

Explanation:

If $excelIsValid is still false at the end of the loop, then not once did the file had all the attributes required. This either means the file is empty, has the wrong attribute names, or does not have any valid row. In short, the file is invalid and should not be in the system.

Solution

I prepared this sample script:

Route::get('/', function() {

    $isError = false;

    Excel::load('file.xls', function($reader) use (&$isError) {

        $firstrow = $reader->first()->toArray();

        if (isset($firstrow['firstname']) && isset($firstrow['lastname']) && isset($firstrow['username'])) {
            $rows = $reader->all();
            foreach ($rows as $row) {
                echo $row->firstname.' '.$row->lastname.' '.$row->username."<br />";
            }
        }
        else {
            $isError = true;

        }

    });
    if ($isError) {
        return View::make('error');
    }
});

The script loads file and check if there are columns you want. It doesn't check if there are more columns - of course if you want you can add exta check - count($firstow) == 3. If there is error it sets $isError to true and in route it displays a template.

Answered By - Marcin Nabiałek

Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Read More

[FIXED] How do I enable error reporting in Laravel?

Issue

I'm using Laravel 5.2 and I'm trying to import an excel sheet which contains more than 18000 records. the error below appeared. The localhost page isn’t working

localhost is currently unable to handle this request.
HTTP ERROR 500

I tried to change php.ini max_execution_time from 30 seconds to 300 seconds but nothing has been changed

EDIT

the apache error log file says: [:error] [pid 3680:tid 1724] [client ::1:54491] PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in E:\..............

Solution

through your config/app.php set 'debug' => env('APP_DEBUG', false), to true

or in a better way , check out your .env file and make sure to set the debug element to true .

EDIT

according to the error in your apache2 log

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes)

you need to increase your memory limit

ini_set('memory_limit', XXX);

Answered By - hassan

Answer Checked By - Cary Denson (PHPFixing Admin)

Read More

[FIXED] The First Repeated Data Gets Inserted

Issue

I'm working with Laravel 8 and LaravelExcel for importing an Excel file which has a user National Code id and this field must be unique for each user.

So I coded this at the Import Class:

$ncodes = DB::table('olympiad_1400')->select('mys_ncode')->get();
$n = 0; 
$repeated = [];
foreach($formatArray as $arr){
    if($ncodes->isEmpty()){
        DB::table('olympiad_1400')->insert([
            'mys_name' => $arr['name'],
            'mys_ncode' => $arr['nationalCode'],
        ]);
    }else{
        if($ncodes[$n]->mys_ncode == $arr['nationalCode']){
            DB::table('olympiad_1400')->insert([
                'mys_name' => $arr['name'],
                'mys_paid_price' => $arr['price'],
            ]);
        }else{
            array_push($moghayerat, $arr['nationalCode']);
        }
    }
}
        

Now then at the Blade, I added this:

    @if (session()->has('khata'))
        <div class="row justify-content-center">
            <div class="col-md-12 ">
                <div class="form-group alert alert-danger">
                    These national codes exist at the DB: 
                    <ul>
                    @foreach(session('khata') as $khat)
                        <li>{{ $khat }}</li>
                    @endforeach
                    </ul>
                </div>
            </div>
        </div>
    @endif

Now this works fine and rejects inserting repeated National Codes but it ONLY inserts the 1st repeated national code!

For example, if my Excel file has 10 rows (and all of the national codes already exist in the DB), it inserts the 1st row of the file, and then rejects the other repeated national codes properly.

So what's going wrong here? How can I reject ALL the National Code of repeated data from inserting into the DB?

---

UPDATE #1:

When I do try this code, it works fine:

$ncodes = DB::table('olympiad_1400')->where('mys_creator_id',auth()->user()->usr_id)->get();

$n = 0; 
$repeated = [];
foreach($formatArray as $arr){
    if($ncodes->isEmpty()){
        DB::table('olympiad_1400')->insert([
            'mys_name' => $arr['name'],
            'mys_ncode' => $arr['nationalCode'],
        ]);
    }else{
        if($ncodes[$n]->mys_ncode == $arr['nationalCode']){
            DB::table('olympiad_1400')->insert([
                'mys_name' => $arr['name'],
                'mys_paid_price' => $arr['price'],
            ]);
        }else{
            array_push($moghayerat, $arr['nationalCode']);
        }
        $n++;
    }
}

But when I remove the ->where('mys_creator_id',auth()->user()->usr_id) part, it does not show repeated data and insert them all.

The reason for this is that, the collection with mys_creator_id of auth()->user()->usr_id only contains a collection that matches with entered $arr['nationalCode'] like this:

$ncodes[$n]->mys_ncode                  $arr['nationalCode']

1274925657                              1274925657
1275119859                              1275119859
1274051096                              1274051096
1273884817                              1273884817
1273884817                              1273884817

But when I remove the where('mys_creator_id',auth()->user()->usr_id), it does not match the returned collection and that's why it keeps inserting repeated datas.

So I need something to check in the Whole Collection for repeated National Code (not just searching for an index of it).

I would really appreciate any idea or suggestion about this...

Thanks in advance.

Solution

I guess you're looking for something like this:

$ncodes = DB::table('olympiad_1400')->select('mys_ncode')->get();

// First collect all ncodes into a separate array
$usedNCodes = [];
if (!$ncodes->isEmpty()) {
    $usedNCodes = array_map(
        function($item) {
            return $item->mys_ncode
        },
        $ncodes
    );
}

$repeated = [];
foreach($formatArray as $arr){
    if (!\in_array($usedNCodes, $arr['nationalCode']) {
        // Insert all data if ncode was not found
        DB::table('olympiad_1400')->insert([
            'mys_name' => $arr['name'],
            'mys_ncode' => $arr['nationalCode'],
            'mys_paid_price' => $arr['price'],
        ]);

        // Also add new ncode to the used ncodes array, so duplicates from the $formatArray won't be inserted anyway.
        $usedNCodes[] = $arr['nationalCode'];
    } else {
        $repeated[] = $arr['nationalCode'];
    }
}

// If $formatArray had duplicates and you only need them once in the $repeated array
$repeated = array_unique($repeated);

I didn't understand why you have different kinds of insert for the cases when the table is completely empty and when you didn't find your id. If you needed to update existing records with the same ncode, use DB:update in the else branch.

Answered By - Hast

Read More

[FIXED] Ambiguous class resolution in laravel phpexcel update

Issue

I try to update the laravel with php excel while installing i found the below warning in the composer.

Error:

Warning: Ambiguous class resolution, "SettingsController" was found in both 

"C:\xampp\htdocs\mti\app\controllers\SettingsController.php" and 

"C:\xampp\htdocs\mti\app\controllers\SettingsControllerBackup.php", the first 

will be used.Warning: Ambiguous class resolution, "ClassModel" was found in both

"C:\xampp\htdocs\mti\app\models\ClassModel.php" and "C:\xampp\htdocs\mti\

app\models\LoginModel.php", the first will be used.

SettingsController:

<?php

class SettingsController extends BaseController
{

    public function ChangePasswordLayout()
    {
        return View::make('settings/changepassword/changepassword');
    }

    public function ChangePasswordProcess()
    {
        $PasswordData = Input::all();

        Validator::extend('pwdvalidation', function($field, $value, $parameters)
        {
            return Hash::check($value, Auth::user()->password);
        });

        $messages = array('pwdvalidation' => 'The Old Password is Incorrect');

        $validator = Validator::make($PasswordData, User::$rulespwd, $messages);
        if ($validator->passes()) 
        {
            $user = User::find(Auth::user()->id);
            $user->password = Hash::make(Input::get('NewPassword'));
            $user->save();
            return Redirect::to('changepassword')->withInput()->with('Messages', 'The Password Information was Updated');
        } else 
        {
            return Redirect::to('changepassword')->withInput()->withErrors($validator);
        }

    }

    public function ProfileLayout()
    {
        $user = Auth::user()->id;
        $ProfileDetailsbyid = ProfileModel::where('id', $user)->get()->toArray();   
        return View::make('settings/profile/profile')->with('ProfileDetailsbyid', $ProfileDetailsbyid);
    }

    public function ProfileUpdateProcess($data=NULL)
    {

    $user = Auth::user()->id;
    $ProfileDetailsbyid = ProfileModel::where('id', $user)->get()->toArray();

        $ProfileData = array_filter(Input::except(array('_token')));

      $validation  = Validator::make($ProfileData, ProfileModel::$rules);        
        if ($validation->passes()) 
        {

        if(!empty($ProfileData['Photo']))
    {
    Input::file('Photo')->move('assets/uploads/profilephoto/', $user . '-Photo.' . Input::file('Photo')->getClientOriginalName());
    $Photo=$user.'-Photo.' . Input::file('Photo')->getClientOriginalName();
    unset($ProfileData['Photo']);
    $ProfileData['Photo']=$Photo;
    }

           $affectedRows = ProfileModel::where('id', $user)->update($ProfileData);
            //VehicleModel::create($VehicleData);
            return Redirect::to('profile')->with('Message', 'Profile Details Update Succesfully')->with('ProfileDetailsbyid', $ProfileDetailsbyid);
        } else 
        {

            return Redirect::to('profile')->withInput()->withErrors($validation->messages())->with('ProfileDetailsbyid', $ProfileDetailsbyid);
        }
    }


}

ClassModel:

<?php
class ClassModel extends Eloquent
{

    protected $primaryKey = 'AutoID';
    protected $created_at = 'CreatedAt';
    protected $updated_at = 'UpdatedAt';
    protected $table = 'class';
    protected $guarded = array('GradeName');
    protected $fillable = array('GradeName');

    public function batch(){
        return $this->hasMany('BatchModel', 'Class');
    }

    public function studentadmissionresult(){
        return $this->hasMany('StudentAdmissionModel', 'StudentCourse');
    }

    public $timestamps = true;



    public static $rules = array(
        'GradeName' =>  array('required', 'unique:class','regex:/^./'),
        'GradeSection' => 'required',
        'GradeCode' => array('required', 'unique:class')
                             );
     public static $updaterules = array(
        'GradeName' =>  array('required','regex:/^./'),
        'GradeSection' => 'required',
        'GradeCode' => array('required')
                             );                      

}

I following this tutorial:

https://github.com/Maatwebsite/Laravel-Excel

I have try following command :

composer require maatwebsite/excel": "~1.2.1

Solution

This actually has nothing to do with the package you are installing.

Explanation

When recreating the autoload files (composer dump-autoload) after the update Composer detected that you have two classes with the exact same name (but in different files).

Class SettingsController in SettingsController.php and SettingsControllerBackup.php

and class ClassModel in ClassModel.php and LoginModel.php

Composer will then choose to use one of the two (I'm not sure how it makes that decision, it's probably just the first one it finds) and will ignore the other occurrence. - Confirmed. Composer uses first match.

Solutions

1. Delete the files if you don't need them
2. Rename the class

A good and common practice is to name the class like the file. This is a simple way to avoid such collisions because two files in the same directory can't have the same name.

Answered By - lukasgeiter

Read More

[FIXED] Reading excel file and uploading to database Laravel 5

Issue

I have this project where I should be able to upload an excel file and read the contents then upload the information to the database. So I decided to use a library to help me out which turns to be Maatwebsite/Laravel-Excel

But I tried reading the documentation http://www.maatwebsite.nl/laravel-excel/docs/import but I can't seem to find the one that I need.

For example in my excel file in the first row John, Kennedy, Male which in my database corrensponds First Name, Last Name, Gender. How can I read it and upload? Can someone help me?

Thanks!

My code as of now

public function postUploadCsv()
{
    $rules = array(
        'file' => 'required',
        'num_records' => 'required',
    );

    $validator = Validator::make(Input::all(), $rules);
    // process the form
    if ($validator->fails()) 
    {
        return Redirect::to('customer-upload')->withErrors($validator);
    }
    else 
    {
        $file = Input::file('file');
        dd($file);
        exit();
    } 
}

Solution

given your excel sheet column names are exactly as database column names following is suffice,

add following above controller class,

use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\Input;

and function code,

public function postUploadCsv()
{
    $rules = array(
        'file' => 'required',
        'num_records' => 'required',
    );

    $validator = Validator::make(Input::all(), $rules);
    // process the form
    if ($validator->fails()) 
    {
        return Redirect::to('customer-upload')->withErrors($validator);
    }
    else 
    {
        try {
            Excel::load(Input::file('file'), function ($reader) {

                foreach ($reader->toArray() as $row) {
                    User::firstOrCreate($row);
                }
            });
            \Session::flash('success', 'Users uploaded successfully.');
            return redirect(route('users.index'));
        } catch (\Exception $e) {
            \Session::flash('error', $e->getMessage());
            return redirect(route('users.index'));
        }
    } 
} 

UPDATE

Suppose you have more than one sheet in a workbook, you will have additional foreach to iterate over sheets as below,

Excel::load(Input::file('file'), function ($reader) {

     $reader->each(function($sheet) {    
         foreach ($sheet->toArray() as $row) {
            User::firstOrCreate($row);
         }
     });
});

Read More

In case you are using Laravel 5.3 and given that your excel sheet columns are not exact

Make use of the following code to suite your needs

/**
 * Import file into database Code
 *
 * @var array
 */
public function importExcel(Request $request)
{
    if($request->hasFile('import_file')){

        $path = $request->file('import_file')->getRealPath();
        $data = Excel::load($path, function($reader) {})->get();

        if(!empty($data) && $data->count()){

            foreach ($data->toArray() as $key => $value) {

                if(!empty($value)){

                    foreach ($value as $v) {        

                        $insert[] = ['title' => $v['title'], 'description' => $v['description']];

                    }
                }
            }

            if(!empty($insert)){
                Item::insert($insert);
                return back()->with('success','Insert Record successfully.');
            }
        }
    }

    return back()->with('error','Please Check your file, Something is wrong there.');

}

Check out the full tutorial here

Note that by default - Once your data is extacted from you excel sheet, all the column names are converted to lower case, and all spaces between the names are replaced with underscore.

Answered By - pinkal vansia

Read More

[FIXED] How to set background color for row in Laravel Excel?

Issue

I use Laravel Excel library and I have tried this code:

public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {

            $styleArray =  array('fill' => array(
                'color' => array('rgb' => '000000')
            ));

            $cellRange = 'A1:W1'; // All headers
            $event->sheet->getDelegate()->getStyle($cellRange)->applyFromArray($styleArray);
        },
    ];
}

As result I get headers without black background color.

Also I tried this array settings:

$styleArray = [
                    'font' => [
                        'bold' => true,
                    ],
                    'background' => [
                        'color'=> '#000000'
                    ]
                ];

I use events, not creating. Please don't recommend not relevant answers

Solution

Try this

$sheet->row(1, ['Col 1', 'Col 2', 'Col 3']); // etc etc
$sheet->row(1, function($row) { $row->setBackground('#CCCCCC'); });

You can also change $sheet->row() to $sheet->cell() and keep passing a row number as first argument.

$sheet->cell(1, function($row) { 
    $row->setBackground('#CCCCCC'); 
});

Then You can also use a more Excel-ish notation :

$sheet->cells('A1:D1', function ($cells) {
    $cells->setBackground('#008686');
    $cells->setAlignment('center');
});

Answered By - sss S

Read More