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!
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
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.
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.
should be outside the student loop. You are printing all student details by row not column. When time changes your column also changes else keep it same.
If you still have hard time making it work start from following steps
Comment out student details code and see you can print that three times in 3 different columns? if yes then loop through student details without incrementing column and print the student data in cell by incrementing row only.
I moved $col1 =$id+3; outside the student details loop and moved the $row1=9; $col1=3;
and $row = 7; before the $datesRows. Now when I perform export then only the first sheet student details are displayed.
So as shown in image there are 3 sheets with 3 dates for 1 March, 2 March and 3 March respectively. For each date the times are displayed correctly in different columns. Now for 1 March and for time 10:30 AM To 10:45 AM the student details are correctly displayed. Now the student details for 10:45 AM To 11:10 AM time are displayed in 11:10 AM To 12:00 PM time. This is not correct. Student details for each time should be displayed in that particular time itself for each date. Now if we click on other sheets like 2 March and 3 March the student details are not displayed.
Following is the code
$row1=9;
$col1=3;
$row = 7;
foreach($datesRows as $date) // First loop to display dates on sheets
{
foreach($timedetails as $idx => $td) // Second loop to display time for each sheets
{
$col = $idx +3;
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time'])->getStyle($col.$row)
->getFont()
->setBold(true);
foreach($StudentDetails as $id=>$StudentDetais1) // Third loop to display student details for each time of particular dates
{
$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;
}
}
Below is the screen shot.
So in 1 March sheet only the student details of time 10:30 AM To 10:45 AM is displayed correctly.
Should be inside timedetails loop because it will reset to 9 for next time and student display will start from beginning again.
Give appropriate names to variables so it will be easy for you and others who are helping to see details easily.
Code below is based on assumption so it might be breaking but idea is correct to print the data.
I believe you are changing worksheet variable with each iteration of date loop to write in respective sheet.
You don’t need $col1 variable for students because it will be same column as time.
$row = 7;
// First loop to display dates on sheets
foreach($datesRows as $date) {
// Second loop to display time for each sheets
foreach($timedetails as $idx => $td) {
$studentRow = 9;
$col = $idx +3;
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time'])->getStyle($col.$row)
->getFont()
->setBold(true);
// Third loop to display student details for each time of particular dates
foreach($StudentDetails as $id=>$StudentDetais1) {
$workSheet->getCellByColumnAndRow($col, $studentRow++)->setValue($StudentDetais1['StudentName']);
$workSheet->getCellByColumnAndRow($col, $studentRow++)->setValue($StudentDetais1['Subject']);
$workSheet->getCellByColumnAndRow($col, $studentRow++)->setValue($StudentDetais1['Topic']);
$workSheet->getCellByColumnAndRow($col, $studentRow++)->setValue($StudentDetais1['Confidence']);
$studentRow++;
}
}
}
Thanks the details are correctly displayed. Now I want that Student Name, Subject ,Topic, Confidence labels should be displayed before the details as shown in image as well as the borders should be applied to details fetched as shown in image.
$styleArray = [
'font' => [
'bold' => true,
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
],
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
]
]
];
foreach($datesRows as $date) {
// Second loop to display time for each sheets
foreach($timedetails as $idx => $td) {
$studentRow = 9;
$col = $idx +3;
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time'])->getStyle($col.$row)
->applyFromArray($styleArray);
// Third loop to display student details for each time of particular dates
foreach($StudentDetails as $id=>$StudentDetais1) {
$workSheet->getCellByColumnAndRow($col, $studentRow++)->setValue($StudentDetais1['StudentName'])->getStyle($col1.$row1)->getStyle($col1.$row1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $studentRow++)->setValue($StudentDetais1['Subject'])->getStyle($col1.$row1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $studentRow++)->setValue($StudentDetais1['Topic'])->getStyle($col1.$row1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $studentRow++)->setValue($StudentDetais1['Confidence'])->getStyle($col1.$row1)->getAlignment()->setHorizontal('left');
$studentRow++;
}
}
}
Similarly the excel file that gets downloaded here with the default name as report.xlsx. But I want the teacher name to displayed as the file name. Below is the code to use the teacher name
$ascteacher = \Yii::$app->db->createCommand('SELECT distinct(UserFullName) from ascteacherreport,ascuser where
ascteacherreport.UserId=ascuser.UserId and ascteacherreport.UserId=:Id')->bindValues([':Id'=>\Yii::$app->user->identity->getonlyid()])
->queryAll();
$ascteachers;
foreach($ascteacher as $ascteacher1)
{
$ascteachers=$ascteacher1['UserFullName'];
}
So below is the lines to download the excel file. So instead of report.xlsx the teacher name should be displayed.
Declare $studnetHeaderCol outside all loops or just write 1 instead of $studnetHeaderCol since you have fixed column. Add styles by yourself. Refresh your knowledge on class, loops and read documentation about spreadsheet functions.
For file name declare private variable in class. For example, $_teacherName = ''; then assign teacher name to variable. $this->_teacherName = $ascteachers and finally $writer->save($this->_teacherName.'.xlsx') and header('Content-Disposition: attachment; filename=".$this->_teacherName.".xlsx"');
where is the worksheet? you didn’t created worksheet for each date? Previously i mentioned about that seems like you just want solution and don’t want to learn. No help from my side now on.
