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
foreach($timedetails as $idx => $td){
$row = 4; //adjust gap to your need
$col = $idx +7
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time']);
}
Yes It is getting displayed. Time is getting displayed in every next column as desired.
Now I want that every student details for that particular time should be displayed in that particular column.
Say if Teacher has taught 3 students say John Alex, Nick Samson, Manish Kale from 11:00 AM To 12:00 PM then thses 3 students details should be displayed in 11:00 AM To 12:00 PM column itself as shown in image.
So as you can see in 11:00 AM To 12:00 PM we can see there are 3 students details. So this should be displayed.
public function actionExport()
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet = $spreadsheet->removeSheetByIndex(0);
$sql='SELECT DateofReport from ascteacherreport where UserId=:Id order by DateofReport ASC';
$datesRows = \Yii::$app->db->createCommand($sql)->bindValues([':Id'=>\Yii::$app->user->identity->getonlyid()])
->queryColumn();
$sql2 = 'SELECT * from ascteacherreportdetailS, ascteacherreport WHERE ascteacherreport.ASCTeacherReportId=ascteacherreportdetails.ASCTeacherReportId AND ascteacherreport.UserId=:id AND DateofReport = :date';
$params = [':id' => \Yii::$app->user->identity->getonlyid()];
$sql3 = 'SELECT ascteacherreporttimedetails.StudentId,ascteacherreporttimedetails.Subject,ascteacherreporttimedetails.Topic,ascteacherreporttimedetails.Confidence from ascteacherreportdetails, ascteacherreporttimedetails, ascteacherreport
where ascteacherreportdetails.ASCReportDetailsId=ascteacherreporttimedetails.ASCReportDetailsId
and ascteacherreport.ASCTeacherReportId=ascteacherreportdetails.ASCTeacherReportId
and ascteacherreporttimedetails.ASCReportDetailsId=:Id
and ascteacherreport.UserId=:userid ';
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
foreach($timedetails as $idx => $td){
$row = 7;
$col = $idx +3;
foreach ($workSheet->getColumnIterator() as $column) {
$workSheet->getColumnDimension($column->getColumnIndex())->setWidth(22);
}
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time'])->getStyle($col.$row)
->getFont()
->setBold(true);
}
}
$writer = new Xlsx($spreadsheet);
$writer->save('report.xlsx');
$writer->save('php://output');
}
