0

I have two database tables on a web page which I want to export on a click of a button to a single excel sheet. I have installed PHPOffice/PhpSpreadsheet as a commentator suggested. I was able to export data using foreach loop like this:

    $query = "SELECT * FROM employees WHERE name LIKE 'Adam%'";
    $query_run = mysqli_query($con,$query);
    
    if(mysqli_num_rows($query_run)>0)
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
    
        $sheet->setCellValue('A1', 'ID');
        $sheet->setCellValue('B1', 'Name');
        $sheet->setCellValue('C1', 'Address');
        $sheet->setCellValue('D1', 'Salary');
        $rowCount = 2;
        
        foreach($query_run as $data)
        {
            $sheet->setCellValue('A' . $rowCount, $data['id']);
            $sheet->setCellValue('B' . $rowCount, $data['name']);
            $sheet->setCellValue('C' . $rowCount, $data['address']);
            $sheet->setCellValue('D' . $rowCount, $data['salary']);
            
            
            $rowCount++;
            
    }

        $writer = new Csv($spreadsheet);
        $final_fileName = $filename.'.csv';
        header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . urlencode($final_fileName). '"');
        $writer->save('php://output');

But, I want the exported tables to be on top of each other with their own respective headers using the table structure I already have in my Php snippet below. What will be the best way to accomplish this without having to restructure the entire code:

Desired Output (Excel Sheet):

enter image description here

Php Snippet:

//Table 1
<div>
                    $sql = "SELECT * FROM employees";
                    if($result = $mysqli->query($sql)){
                        if($result->num_rows > 0){
                            echo '<table class="table table-bordered table-striped">';
                                echo "<thead>";
                                    echo "<tr>";
                                        echo "<th>#</th>";
                                        echo "<th>Name</th>";
                                        echo "<th>Address</th>";
                                        echo "<th>Salary</th>";
                                  
                                    echo "</tr>";
                                echo "</thead>";
                                echo "<tbody>";
                                while($row = $result->fetch_array()){
                                    echo "<tr>";
                                        echo "<td>" . $row['id'] . "</td>";
                                        echo "<td>" . $row['name'] . "</td>";
                                        echo "<td>" . $row['address'] . "</td>";
                                        echo "<td>" . $row['salary'] . "</td>";

                                    echo "</tr>";
                                }
                                echo "</tbody>";                            
                            echo "</table>";
                            // Free result set
                            $result->free();
                        }
</div>

//Table 2
<div>
                    $sql2 = "SELECT * FROM employeesDetails";
                    if($result = $mysqli->query($sql2)){
                        if($result->num_rows > 0){
                            echo '<table class="table table-bordered table-striped">';
                                echo "<thead>";
                                    echo "<tr>";
                                        echo "<th>#</th>";
                                        echo "<th>Name</th>";
                                        echo "<th>Gender</th>";
                                        echo "<th>Age</th>";
                                    
                                    echo "</tr>";
                                echo "</thead>";
                                echo "<tbody>";
                                while($row = $result->fetch_array()){
                                    echo "<tr>";
                                        echo "<td>" . $row['id'] . "</td>";
                                        echo "<td>" . $row['name'] . "</td>";
                                        echo "<td>" . $row['gender'] . "</td>";
                                        echo "<td>" . $row['age'] . "</td>";

                                    echo "</tr>";
                                }
                                echo "</tbody>";                            
                            echo "</table>";
                            // Free result set
                            $result->free();
                        } 
</div>
  • 1
    I would recommend https://github.com/PHPOffice/PhpSpreadsheet it makes a lot easier to generate the excel – Honsa Stunna Dec 13 '21 at 12:11
  • 1
    @HonsaStunna I installed the phpspreadsheet as you suggested; but, I am having difficulty in implementing it using the code structure that I already have in my Php. Kindly help. Thank you. –  Dec 14 '21 at 00:39
  • Just get the data, and transform them. You have to adjust your code, follow the instructions https://phpspreadsheet.readthedocs.io/en/latest/#hello-world – Honsa Stunna Dec 14 '21 at 04:50

1 Answers1

0

Before the variable $writer Merge the rows and and enter the value table 2 and then repeate the same process used above for retrieving the table data for the next table. The heading names can be given directly with $rowcount. Use foreach and get the values from the table. Then continue with

$writer=......

For more info about formatting, visit https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/

Brill Brenhill
  • 11
  • 4