I thought you are using Yii which does autoload for you.
Why are you autoloading vendor manually?
I thought you are using Yii which does autoload for you.
Why are you autoloading vendor manually?
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
Now I am facing this issue when trying to do composer update
[RuntimeException]
Failed to execute git show-ref --head -d
fatal: git show-ref: bad ref refs/remotes/origin/2.1? (0000000000000000000000000000000000000000)
When you import a class with use you don’t add \. This is basic PHP.
So it should be
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
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
Check your config/web.php` do you have these lines? And if they are there what are the values?
'aliases' => [
'@bower' => '@vendor/bower-asset',
'@npm' => '@vendor/npm-asset',
],
Yes thank you.
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
Alway post wrong screen vs expected screen to get quick help!
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
Wrong Screen
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
Correct Expected Image
foreach($timedetails as $idx => $td){
$row = $idx +7; //adjust gap to your need
$workSheet->setCellValue("C{$row}", $td['Time']);
}
So here we need to make some modifications.
Well in that case you need to use row and column
foreach($timedetails as $idx => $td){
$row = 4; //adjust gap to your need
$col = $idx +7
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time']);
}
You will need to adjust that to your need!
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');
}That one you have to figure it out yourself.
I cannot get my head around your queries and I don’t know how schema looks like.
But it is the same as adding time. this time for each time you will be incrementing column instead of row. It should not be complex anyway!
Yes sure. I have modified the code as below.
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 student.StudentName, ascteacherreporttimedetails.Subject,ascteacherreporttimedetails.Topic,ascteacherreporttimedetails.Confidence from ascteacherreportdetails, student, ascteacherreporttimedetails, ascteacherreport
where ascteacherreportdetails.ASCReportDetailsId=ascteacherreporttimedetails.ASCReportDetailsId
and ascteacherreport.ASCTeacherReportId=ascteacherreportdetails.ASCTeacherReportId
and student.StudentId=ascteacherreporttimedetails.StudentId
and ascteacherreportdetails.ASCReportDetailsId=:Id
and ascteacherreport.DateofReport=:date
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;
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time'])->getStyle($col.$row)
->getFont()
->setBold(true);
$params1[':date']=$date;
$params1[':Id']=$td['ASCReportDetailsId'];
$StudentDetails=\Yii::$app->db->createCommand($sql3, $params1)->queryAll();
foreach($StudentDetails as $id=>$StudentDetais1)
{
$row1 = 9;
$col1 = $id +3;
$workSheet->getCellByColumnAndRow($col1, $row1++) ->setValue($StudentDetais1['StudentName']);
$workSheet->getCellByColumnAndRow($col1, $row1++)- >setValue($StudentDetais1['Subject']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Topic']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Confidence']);
$col1++;
$row1++;
}
}
}
$writer = new Xlsx($spreadsheet);
$writer->save('report.xlsx');
$writer->save('php://output');
}
But when I export, the details are not getting displayed in cell properly.
For eg : For date 1 March 2022 there are 3 times 10:30 AM To 10:45 AM, 10:45 AM To 11:10 AM and 11:10 AM To 12:00 PM . So for time 10:30 AM To 10:45 AM 3 student details are there in table. as shown in Expected Image
Expected Image
Wrong Image
Tables structure
Table 1 . ascteacherreport
ASCTeacherReportId
ASCId
UserId
DateofReport
Table 2 . ascteacherreportdetails
ASCReportDetailsId
ASCTeacherReportId
Time
Table 3 . ascteacherreporttimedetails
ASCTeacherReportTimeDetailsId
ASCReportDetailsId
StudentId
Subject
Topic
Confidence
I guess your problem is in this code
foreach($StudentDetails as $id=>$StudentDetais1)
{
$row1 = 9;
$col1 = $id +3;
$workSheet->getCellByColumnAndRow($col1, $row1++) ->setValue($StudentDetais1['StudentName']);
$workSheet->getCellByColumnAndRow($col1, $row1++)- >setValue($StudentDetais1['Subject']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Topic']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Confidence']);
$col1++;
$row1++;
}
You change them at the end of the loop and at the next iteration you overwrite them with
$row1 = 9;
$col1 = $id +3;
Move initialization outside foreach loop and it shoul append instead of overwrite
I changed the code as below. But still it is not getting displayed correctly.
$row1=9;
$col1='C';
foreach($StudentDetails as $id=>$StudentDetais1)
{
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['StudentName']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Subject']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Topic']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Confidence']);
$col1 = $id +3;
}
Wrong Image
You probably realize this function takes integer and your col1 isn’t
read and understand basic functions of spreadsheet from documentation first. Secondary build query/array the way needed to be printed in excel. You have two way to print the data. 1) Go by column and fill the cells by jumping between them. 2) Go by row and print the data easily but it will require you to build the array first.
I modified the code as
foreach($timedetails as $idx => $td){
$row = 7;
$col = $idx +3;
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time'])->getStyle($col.$row)
->getFont()
->setBold(true);
$params1[':Id']=$td['ASCReportDetailsId'];
$StudentDetails=\Yii::$app->db->createCommand($sql3, $params1)->queryAll();
$row1=9;
$col1=3; //Made change here by setting column index to 3 i.e column C
foreach($StudentDetails as $id=>$StudentDetais1)
{
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['StudentName']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Subject']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Topic']);
$workSheet->getCellByColumnAndRow($col1, $row1++)->setValue($StudentDetais1['Confidence']);
$row1++;
$col1 =$id+3;
}
}
}
I changed the code as $col1=3; by setting column index to int. But still no change. Data is not correctly displayed.
Every column which is represented as time for each sheet. So details belonging to each time for each sheet should be displayed in that particular column.
So as in Image the arrow in yellow color representst the times for 1 March. These times are stored and fetched from ascteacherreportdetails table with Primary Key ASCReportDetailsId and in code these times are in $timedetails
Now for each time there are different student details. These student details are stored and fetched from ascteacherreporttimedetails table with the primary key ASCTeacherReportTimedDetailsId
Here in code these student details are stored in $StudentDetails
So for each day, for each time the student details should be displayed in that particular time column.
