Here is the code you can start with using PHPSpreadsheet
Note I didn’t include teachers because I don’t have tables and hence do not know how you fetch them. But up to here you should have an idea how to go about it
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->removeSheetByIndex(0);
$datesRows = $db->createCommand($sql)->queryColumn();//pass your query for dates
$sql2 = 'SELECT Time from ascteacherreportdetailS, ascteacherreport WHERE ascteacherreport.ASCTeacherReportId=ascteacherreportdetails.ASCTeacherReportId AND ascteacherreport.UserId=:id AND date = :date';
$params = [':id' => \Yii::$app->user->identity->getonlyid()];
foreach($datesRows as $date)
{
$workSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, $date);
$spreadsheet->addSheet($workSheet);
//set contents of the sheet
$params[':date'] = $date;
$timedetails = \Yii::$app->db->createCommand($sql2, $params)->queryAll();
//Title which is date only
$workSheet->setCellValue('A1', $date);
//Set times
foreach($timedetails as $idx => $td){
$row = $idx +3; //adjust gap to your need
$workSheet->setCellValue("B{$row}", $td['Time']);
}
}
$writer = new Xlsx($spreadsheet);
$writer->save('my-file.xlsx');
I had done composer update to install the php spreadsheet. Now in my controller where I need to use this plugin so I declared the following
<?php
namespace app\controllers;
require __DIR__ . '/../vendor/autoload.php';
use Yii;
use app\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Spreadsheet;
use app\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Writer\Xlsx;
class AscteacherreportController extends Controller
{
public function actionExport()
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');
$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');
}
}
But it gives me error as,
Unable to find ‘app\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Spreadsheet’ in file: F:\xampp\htdocs\lwa\basic/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php. Namespace missing?
<?php
namespace app\controllers;
require __DIR__ . '/../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class AscteacherreportController extends Controller
{
public function actionExport()
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');
$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');
}
}
Then the error is as Class ‘PhpOffice\PhpSpreadsheet\Spreadsheet’ not found
When I try to do composer update it gives me error as
[Composer\Downloader\TransportException]
The "https://packagist.org/packages.json" file could not be downloaded: SSL
operation failed with code 1. OpenSSL Error messages:
error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify
failed
Failed to enable crypto
failed to open stream: operation failed
I am using Yii2 basic application template. So in composer I have declared “phpoffice/phpspreadsheet”: “@dev”, and done composer update. Some how it had installed the plugin. Now as suggested in docs I have imported the files as
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new \Spreadsheet();
But now the error is Class ‘Spreadsheet’ not found
You somehow tampered with repository. It would be easier to fix with git reset --hard but I suggest you just delete vendor directory and run composer update and let composer regenerate the folder
As you told I deleted the vendor folder and done composer update and now I am not able to access the application. It gives error as
# PHP User Error – [yii\base\ErrorException](http://www.yiiframework.com/doc-2.0/yii-base-errorexception.html)
## Exception 'yii\base\InvalidArgumentException' with message 'The file or directory to be published does not exist: F:\xampp\htdocs\lwa\basic\vendor\bower/jquery/dist'
in F:\xampp\htdocs\lwa\basic\vendor\yiisoft\yii2\web\AssetManager.php:467
Now it is working. The phpspreadsheet plugin is working. Now as you have suggested I made the changes for exporting the data into excel. Now the time is getting displayed in different rows. But the time should be displayed in different columns as shown in screenshot
So time is getting displayed in C4,D4 and E4 columns respectively
First Image shows the wrong screen where the times are diplayed in new rows like C7 and C8. So in image C7 has 11:00 AM To 12:00 PM and C8 12:00 PM To 01:00 PM
But these time should be displayed in different columns. In second Image the times are displayed in C7, D7. So I want this. Time should be displayed in different columns. So the loop mentioned in code is
