How to export data from different mysql tables in one excel file in separate sheets in yii2 basic

Yii 2.0 General Discussions
41

Actually forget about that private variable. i assumed that you have separate file for excel. :grinning:

header('Content-Disposition: attachment; filename="'.$ascteachers.'.xlsx"');
$writer->save($ascteachers.'.xlsx');
42

Yes it works.

I need to add Student Name, Subject, Topic, Confidence label as shown in image. Also need to add borders around them

screenshot
screenshot1366×768 107 KB

43

Add by yourself, if you stuck somewhere then ask with code what you have tried

44

I have declared the variable outside the first loop as $column1=2; and $rowno=9;
So foll is the code


$rowno=9;
$column1=2;

foreach($datesRows as $date) // First Loop
{
 foreach($timedetails as $idx => $td)// Second Loop
{
		$row1=9;
    $col = $idx +3;
foreach($StudentDetails as $id=>$StudentDetais1)
		  {
			$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Student Name');
			$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Subject');
			$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Topic');
			$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Confidence');


$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['StudentName'])->getStyle($col1.$row1)->getFont()
            ->setBold(true);

$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Subject'])->getStyle($col1.$row1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Topic'])->getStyle($col1.$row1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Confidence'])->getStyle($col1.$row1)->getAlignment()->setHorizontal('left');


$row1++;
$rowno++;


	}
}
}

So only on first sheet I can see Student Name, Subject, Topic, Confidence as shown in Image.

image50
image501366×768 108 KB

45

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.

1 Like
46

Actually I have given only the short code. Worksheet is already created.

$rowno=9;
$column1=2;

foreach($datesRows as $date) // First Loop
{
$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();
 foreach($timedetails as $idx => $td)// Second Loop
{
		$row1=9;
    $col = $idx +3;
foreach($StudentDetails as $id=>$StudentDetais1)
		  {
			$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Student Name');
			$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Subject');
			$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Topic');
			$workSheet->getCellByColumnAndRow($column1, $rowno++)->setValue('Confidence');


$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['StudentName'])->getStyle($col1.$row1)->getFont()
            ->setBold(true);

$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Subject'])->getStyle($col1.$row1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Topic'])->getStyle($col1.$row1)->getAlignment()->setWrapText(true);
$workSheet->getCellByColumnAndRow($col, $row1++)->setValue($StudentDetais1['Confidence'])->getStyle($col1.$row1)->getAlignment()->setHorizontal('left');


$row1++;
$rowno++;


	}
}
}