In this tutorial we’ll look into using PhpSpreadsheet library, a pure php library to read and write spreadsheets including MS Excel.
You can start by creating a fresh laravel 8 app by entering following command:
composer create-project laravel/laravel example 8.*
Now install phpspreadsheet by executing the following command inside project directory:
composer require phpoffice/phpspreadsheet
Next, setup database and Laravel environment file as usual. Create new SQLite database by creating a new empty file named ‘database.sqlite’ in ‘database’ folder. For this purpose, if you are using any *nix based OS, you can enter following commands in the app folder:
cd database
touch database.sqlite
cd ..
Now, edit .env file as follows:
DB_CONNECTION = sqlite
You can remove DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, DB_PASSWORD from .env when using SQLite database.
Enter the following command to create Salary model along with the migration:
php artisan make:model Salary -m
Open newly created migration file from database/migrations/ which is named something like …create_salaries_table.php and then insert the desired table fields e.g.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateSalariesTable extends Migration
{
public function up()
{
Schema::create('salaries', function (Blueprint $table) {
$table->id();
$table->date('joining_date');
$table->string('name');
$table->string('department');
$table->string('designation');
$table->decimal('gross_salary',14,2);
$table->decimal('medical',14,2);
$table->decimal('conveyance',14,2);
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists('salaries');
}
}
Enter following command to migrate the above migration:
php artisan migrate
Now, amend the Salary model by inserting fillable array into the model. For that, open app/Models/Salary.php and insert protected fillable array as follows:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Salary extends Model
{
use HasFactory;
protected $fillable = [
'joining_date', 'name', 'department', 'designation', 'gross_salary', 'medical', 'conveyance'
];
}
Let’s create factory for Salary model so that we can generate data quickly for testing purposes. Execute the following command to create factory file SalaryFactory.php in the folder database/factories:
php artisan make:factory SalaryFactory --model=Salary
Now, open the newly created file SalaryFactory.php and amend it as follows:
<?php
namespace Database\Factories;
use App\Models\Salary;
use Illuminate\Database\Eloquent\Factories\Factory;
use Illuminate\Support\Str;
class SalaryFactory extends Factory
{
protected $model = Salary::class;
public function definition()
{
return [
'joining_date' => $this->faker->date('Y-m-d'),
'name' => $this->faker->name(),
'department' => Str::random(10),
'designation' => Str::random(10),
'gross_salary' => $this->faker->randomFloat($nbMaxDecimals = 2, $min = 15000, $max = 50000),
'medical' => $this->faker->randomFloat($nbMaxDecimals = 2, $min = 3000, $max = 5000),
'conveyance' => $this->faker->randomFloat($nbMaxDecimals = 2, $min = 3000, $max = 5000),
];
}
}
Now, we can generate and insert fake data in the database. Here we’ll take shortcut by inserting data through tinker in the salaries table. First enter the following command to start tinker:
php artisan tinker
Then enter the following command in order to generate 10 random salaries:
Salary::factory()->count(10)->create()
Execute command ‘exit’ to leave the tinker shell.
In Laravel, there is a convenient way of handling a complex controller action – by creating a Single Action Controller. This type of controller contains single method __invoke() and can be called by controller name only without mentioning the method name. So, let’s create a Single Action Controller for generating our salary sheet:
php artisan make:controller Salary --invokable
Now, open the newly created file Salary.php in app/Http/Controllers/ and enter the following code:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use Carbon\Carbon;
class Salary extends Controller
{
public function __invoke(Request $request)
{
$spreadsheet = new Spreadsheet();
// Heading in the sheet
$spreadsheet->getActiveSheet()->mergeCells('B1:F1'); // Merging cells
$spreadsheet->getActiveSheet()->setCellValue('B1','ABC Limited - Salary Sheet for the Month of June, 2021');
$spreadsheet->getActiveSheet()->getStyle('B1')->applyFromArray(
array(
'font' => array(
'bold' => true,
'size' => 20,
),
)
);
// Datatable column headings
$colArray = ['G:G','H:H','I:I','J:J','K:K'];
foreach ($colArray as $key=>$col) {
$spreadsheet->getActiveSheet()->getStyle($col)->getNumberFormat()
->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
} // Formatting columns G to K as number formats with comma separation
$spreadsheet->getActiveSheet()->getStyle('B3:K3')->applyFromArray(
array(
'fill' => array(
'fillType' => Fill::FILL_SOLID,
'color' => array('rgb' => '484848' )
),
'font' => array(
'bold' => true,
'color' => array('rgb' => 'FFFFFF')
),
'alignment' => array(
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
'wrapText' => true,
),
)
);
$rowArray = ['ID', 'JOINING DATE', 'NAME', 'DEPARTMENT', 'DESIGNATION', 'GROSS SALARY', 'MEDICAL', 'CONVEYANCE', 'INCOME TAX', 'NET SALARY'];
$spreadsheet->getActiveSheet()->fromArray($rowArray, NULL, 'B3');
// Defining width of each column
$widthArray = ['5','5','20','30','20','20','15','15','15','15','15'];
foreach (range('A','K') as $key=>$col) {
$spreadsheet->getActiveSheet()->getColumnDimension($col)->setWidth($widthArray[$key]);
}
// Getting data from salaries table
$data = \App\Models\Salary::all()
->map(function ($item){
return [
'id' => $item->id,
'joining_date' => Carbon::parse($item->joining_date)->format('F j, Y'), // Nice date format
'name' => $item->name,
'department' => $item->department,
'designation' => $item->designation,
'gross_salary' => $item->gross_salary,
'medical' => $item->medical,
'conveyance' => $item->conveyance,
'income_tax' => ($item->gross_salary+$item->medical+$item->conveyance)*0.15, // Income tax calculation
'net_salary' => ($item->gross_salary+$item->medical+$item->conveyance)-($item->gross_salary+$item->medical+$item->conveyance)*0.15 // Net salary calculation
];
})->toArray();
$spreadsheet->getActiveSheet()->fromArray($data, NULL, 'B5'); // Putting data onto sheet
// Calculating totals for numerical columns
$cnt = count($data);
$total = [0,0,0,0,0];
$tcell = ['G','H','I','J','K'];
for($i=0;$i<$cnt;$i++){
$total[0] = $total[0] + $data[$i]['gross_salary'];
$total[1] = $total[1] + $data[$i]['medical'];
$total[2] = $total[2] + $data[$i]['conveyance'];
$total[3] = $total[3] + $data[$i]['income_tax'];
$total[4] = $total[4] + $data[$i]['net_salary'];
}
$styleArray = [
'borders' => [
'top' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['rgb' => '484848'],
],
'bottom' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DOUBLE,
'color' => ['rgb' => '484848'],
],
],
];
// Putting totals at the bottom of each numerical column
for($j=0;$j<5;$j++){
$tstr= $cnt+5;
$tcellv= $tcell[$j].strval($tstr);
$spreadsheet->getActiveSheet()->setCellValue($tcellv, $total[$j]);
$spreadsheet->getActiveSheet()->getStyle($tcellv)->applyFromArray($styleArray);
}
$writer = new Xlsx($spreadsheet);
$writer->save('Salaries.xlsx');
}
}
Finally, open routes/web.php and put the following line at the top of it:
use App\Http\Controllers\Salary;
And then insert following route into this web.php:
Route::get('/salary', Salary::class)->name('salary');
Run Laravel development server by executing:
php artisan serve
Enter the following URL in your browser:
localhost:8000/salary
“Salaries.xlsx” file has been generated and saved in public folder of the app.