I am exporting the data from table in excel file using php spreadsheet extension. The data is getting exported correctly but in column wise, because of which borders are not getting displayed for the columns which has less data.
Current Output:
Expected Output:
I need to export the data row wise. Following is the action export
public function actionExport()
{
// Get the report for logged in user
$userreport = Yii::$app->db->createCommand('select * from ascteacherreport where UserId=:Id')->bindValues([':Id'=>\Yii::$app->user->identity->getonlyid()])
->queryAll();
$userreport1=0;
foreach($userreport as $userreport1)
{
}
if($userreport1!=null)
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet = $spreadsheet->removeSheetByIndex(0);
//Get dates of report which needs to be displayed on each sheets
$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();
// Get times for each date report of user
$sql2 = 'SELECT Time,ASCReportDetailsId from ascteacherreportdetails, ascteacherreport WHERE ascteacherreport.ASCTeacherReportId=ascteacherreportdetails.ASCTeacherReportId AND ascteacherreport.UserId=:id AND DateofReport = :date';
// Bind user id to the query
$params = [':id' => \Yii::$app->user->identity->getonlyid()];
// Get student details for each time
$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.UserId=:userid';
$params1 = [':userid' => \Yii::$app->user->identity->getonlyid()]; // Bind user id
$col1=3;
$row = 7;
$column1=2;
$alignment_left = \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT;
$styleArray = [
'font' => [
'bold' => true,
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
],
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
]
]
];
$styleArray1 = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
],
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
]
]
];
//Loop through dates for each sheet
foreach($datesRows as $date)
{
$workSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, $date);
$spreadsheet->addSheet($workSheet);
$params[':date'] = $date;
// Execute the time query
$timedetails = \Yii::$app->db->createCommand($sql2, $params)->queryAll();
// Loop through all the times
foreach($timedetails as $idx => $td)
{
$row1=9;
$col = $idx +3;
$rowno=9;
foreach ($workSheet->getColumnIterator() as $column)
{
$workSheet->getColumnDimension($column->getColumnIndex())->setWidth(28);
}
// Display time in columns
$workSheet->getCellByColumnAndRow($col, $row)->setValue($td['Time']) >getStyle($col.$row)->applyFromArray($styleArray);
$params1[':Id']=$td['ASCReportDetailsId']; // Bind parameter id for time
$StudentDetails=\Yii::$app->db->createCommand($sql3, $params1)->queryAll();
foreach($StudentDetails as $id=>$StudentDetais1)
{
$workSheet->getCellByColumnAndRow($col, $row1++) >setValue($StudentDetais1['StudentName'])->getStyle($col1.$row1)->applyFromArray($styleArray1)->getFont()->setBold(true);
$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Subject'])->getStyle($col1.$row1)->applyFromArray($styleArray1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Topic'])->getStyle($col1.$row1)->applyFromArray($styleArray1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Confidence'])->getStyle($col1.$row1)->applyFromArray($styleArray1)->getAlignment()->setHorizontal('left');
$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Student Name')->getStyle($column1.$rowno)->applyFromArray($styleArray1);
$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Subject')->getStyle($column1.$rowno)->applyFromArray($styleArray1);
$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Topic')->getStyle($column1.$rowno)->applyFromArray($styleArray1);
$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Confidence')->getStyle($column1.$rowno)->applyFromArray($styleArray1);
}
}
}
$writer = new Xlsx($spreadsheet);
ob_start();
$writer->save('php://output');
$writer->save($ascteachers.'.xlsx');
header('Content-type: application/.xlsx');
header('Content-Disposition: attachment; filename="'.$ascteachers.'.xlsx"');
}
else
{
throw new ForbiddenHttpException("No data found to export");
}
}