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

Yii 2.0 General Discussions
1

I am using Yii2 basic. I have 3 tables with the fields as,

ascteacherreport - ASCTeacherReportId,ASCId,UserId, DateofReport

ascteacherreportdetails - ASCReportDetailsId,ASCTeacherReportId, Time

ascteacherreporttimedetails - ASCTeacherReportTimeDetailsId, ASCReportDetailsId, StudentId ,Subject, Topic, Confidence

The relation between these 3 tables is as One ascteacherreport has many ascteacherreportdetails and One ascteacherreportdetails has many ascteacherreporttimedetails.

Now I am generating the data into excel using PHPExcel library. So the user creates the report for Date say 1st March for three hours like 11 to 12, 12 to 1 and 1 to 2.

And there are many student details for each hour(time) of each day. Say Student John for 11 to 12, 12 to 1 and 1 to 2. Similarly for student Nick for 11 to 12, 12 to 1 and 1 to 2 for 1st March.

Now each dates should be exported as new sheets in one excel file. And for each date sheet the particular time details and student details should be displayed. So for 1 st March there are time details for 2 hours like 11 to 12 and 12 to 1. Then the time should be displayed in 1st March sheet in Column C5 and D5.

Similarly for each time there are many student records. For 11 to 12 there are 3 student records then those student records should be displayed in 11 to 12 column.

I have a function actionExport in controller. The excel file gets downloaded and I can see the dates in different sheets coming but there is sheet Worksheet which should not be displayed.

The time is not displayed properly for each sheet

public function actionExport()
    {

    $objPHPExcel = new \PHPExcel();

    $date = \Yii::$app->db->createCommand('SELECT ASCTeacherReportId, ASCId, DateofReport from 
                          ascteacherreport where UserId=:Id order by ASCTeacherReportId DESC')->bindValues([':Id'=>\Yii::$app->user->identity->getonlyid()])->queryAll();

    $timedetails = \Yii::$app->db->createCommand('SELECT Time from ascteacherreportdetailS, 
             ascteacherreport WHERE ascteacherreport.ASCTeacherReportId=ascteacherreportdetails.ASCTeacherReportId AND
ascteacherreport.UserId=:id1')->bindValues([':id1'=>\Yii::$app->user->identity->getonlyid()])
               ->queryAll();

      foreach($date as $date1) 
     {
         $objWorkSheet = $objPHPExcel->createSheet(0); //Setting index when creating
  
         $objWorkSheet->setTitle(''.$date1['DateofReport']);

          foreach($timedetails as $timedetails1)
        {
                 $row = 8;
                 $lastColumn = $objWorkSheet->getHighestColumn();
                 $lastColumn++;
                for ($column = 'C'; $column != $lastColumn; $column++) 
                {
                     $objWorksheet->setCellValue($column.'8',''.$timedetails1['Time']);
                }
        }
}

ob_end_clean();

header('Content-type: application/.xlsx');
header('Content-Disposition: attachment; filename="report.xlsx"');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

}

Below is the excel file screenshot where dates are displayed in sheets. For each dates the time should be displayed in the respective sheets.

image
image1217×711 39.3 KB

2

I don’t understand your question!

3

The user creates report for each day. They select the time and fill the student details for each time. For eg The user A created the report for today 7 March 2022 then she selects the time like 11 to 12 and enter the details for students say student 1, student 2. Then the user selects another time 12 to 1 and enters the student dta. So One date has many time and each time has many studdent details. So in excel sheet each shhet is created according to the date for which she created the report. Now for 7March she taught for two hours say 1 to 2 and 2 to 3. Now these time should be displayed in 7 March sheet in two different columns. Similarly for 8 March new sheet is created and then how manu hours teacher taufht the student should be displayed in different columns in 8 March sheet. So for each time like 8 March 11 to 12 she taught for 5 studdents then in 11 to 12 column the student nmes should be displayed along with what topic, subject and confidence she taught should be displayed.

So what is happening here if teacher taught students for 3 hours like 11 to 12, 12 to 1 and 1 to 2 then in 8 March sheet these time 11 to 12, 12 to 1 and 1 to 2 should be displayed in 3 different columns. Similarly for 9 march, 10 march and so on their respective time and student details should be displayed.

So in loop what should we make changes?

4

Ok, I see.
Can you create a sample, correct excel with sample data and incorrect (current results) with the same data. I think that will give me a clear picture of what you are trying to accomplish.

One note is, you should be using PHPSpreasheet not PHPExcel.
RIP PHPExcel…!

1 Like
5

image
image1366×768 106 KB

So here if you see the imagenthe teacher has exported the data in excel sheet. so the sheet would be like as follows:

Each sheet will be titled as the date for which the teacher has created the report. So in image you can see that sheets are 1 March and 3 March. Now for 1 March the teacher has taught for 3 hours for different students, hours like 11:00 AM to 12:00 PM, 12:00 PM TO 01:00 PM and 01:00 PM TO 02:00 PM.

Here for each hour teacher taught for number of student are shown. Here for 11:00 AM to 12:00 PM the teacher has taught for 3 students like John Alex, Nick Samson and Manish Kale. Similarly for 12:00 PM To 01:00 PM and 01:00 PM To 02:00 PM

Similarly if you click on sheet 2 March you can see the details of 2 March.

Now I get the result as the last hour of 2 March in all the sheets for all dates

image2
image21366×768 100 KB

Thank you

1 Like
6

Here is the code you can start with using PHPSpreadsheet
Note I didn’t include teachers because I don’t have tables and hence do not know how you fetch them. But up to here you should have an idea how to go about it

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->removeSheetByIndex(0);

$datesRows = $db->createCommand($sql)->queryColumn();//pass your query for dates

$sql2 = 'SELECT Time from ascteacherreportdetailS,  ascteacherreport WHERE ascteacherreport.ASCTeacherReportId=ascteacherreportdetails.ASCTeacherReportId AND ascteacherreport.UserId=:id AND date = :date';
$params = [':id' => \Yii::$app->user->identity->getonlyid()];

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 
    $workSheet->setCellValue('A1', $date);

    //Set times
    foreach($timedetails as $idx => $td){
        $row = $idx +3; //adjust gap to your need
        $workSheet->setCellValue("B{$row}", $td['Time']);
    }
}

$writer = new Xlsx($spreadsheet);
$writer->save('my-file.xlsx');
7

Sure I will try the solution

1 Like
8

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?

9

use PhpOffice\PhpSpreadsheet; ?

10

If I write the following

use PhpOffice\PhpSpreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;



$spreadsheet = new \Spreadsheet();

the error is Class ‘Spreadsheet’ not found

11

What if you remove the backslash? (From memory I think that means global namespace.)

12

If I edit the code as

<?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
13

I don’t know. But I found this:
https://stackoverflow.com/questions/27206719/composer-update-fails-while-updating-from-packagist

14

Thank you . I will check

15

I thought you are using Yii which does autoload for you.
Why are you autoloading vendor manually?

16

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

17

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)
18

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();
19

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

20

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