1 <?php
 2 require_once "vendor/autoload.php";
 3 
 4 //importing phpspreadsheet library
 5 use PhpOffice\PhpSpreadsheet\Spreadsheet;
 6 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 7 
 8 spl_autoload_register(function ($class_name) { // https://www.php.net/manual/en/language.oop5.autoload.php
 9     include $class_name . '.class.php';
10 });
11 
12 //adapted from the phpspreadsheet documentation https://github.com/PHPOffice/PhpSpreadsheet
13 $sqlData = new GetMySQLData();
14 $spreadsheet = new Spreadsheet();
15 $Excel_writer = new Xlsx($spreadsheet);
16 
17 //iterating 3 times to create 3 worksheets to represent the 3 tables in MySQL
18 for($l = 0; $l < 3; $l++){
19     //setting the active spreadsheet, starting at the first one at index 0
20     $spreadsheet->setActiveSheetIndex($l);
21     $activeSheet = $spreadsheet->getActiveSheet();
22     
23     switch($l){
24         case 0:
25             $table = 'inquiry';
26             break;
27         case 1:
28             $table = 'customers';
29             break;
30         case 2:
31             $table = 'day_plans';
32             break;
33     }
34     //setting the title
35     $activeSheet->setTitle($table);
36     $columnsQuery = $sqlData->getColumns($table);
37     //the MySQL colums result is turned into an assoicative array and is used to populate the first row cells for the excel worksheet
38     if($columnsQuery->num_rows > 0){
39         $column = 'A';
40         while($row = $columnsQuery->fetch_assoc()) {
41             $activeSheet->setCellValue($column."1" , $row['Field']);
42             $fields[] = $row['Field'];
43             $column++;
44         }
45     }
46     //the data within the MySQL table is returned as an assoicative array and is then used to populate cells in the worksheet
47     $query = $sqlData->getTableData($table);
48     if($query->num_rows > 0){
49         $i = 2;
50         while($row = $query->fetch_assoc()){
51             $column = 'A';
52             for($j = 0; $j < count($fields); $j++){
53                 $activeSheet->setCellValue($column.$i , $row[$fields[$j]]);
54                 $column++;
55             }
56             $i++;
57         }
58         unset($fields);
59     }
60     //this if statement ensures that an extra worksheet is not made since when creating the spreadsheet, a worksheet is already made.
61     if($l != 2){
62         $spreadsheet->createSheet();
63     }
64 }
65 //naming the file
66 $filename = 'entire_database_'.date("Y-m-d").'.xlsx';
67 
68 //making this html file to start a download when opened
69 header('Content-Type: application/vnd.ms-excel');
70 header('Content-Disposition: attachment;filename='. $filename);
71 header('Cache-Control: max-age=0');
72 $Excel_writer->save('php://output');
73 
74 ?>