Multiple Tables Into Single PHPSpreadsheet

Question

I am populating many tables which has vehicle mileage details on a monthly basis of a particular year. I have managed to popuate the table and output the results on the webpage, but I am stuck on how to take the exact tables displayed on the HTML webpage to a spreadsheet, for the excel generation I am using the PHPSpreadsheet library.

The HTML tables with data populated from the database is as below,

This table which is shown in the HTML page is just for my development purposes but the actual requirements is to generate the excel sheet report. How can i achieve this by generating the report into one single phpspreadsheet at once.

The code for fetching data from database and outputting into the tables is as follows,

<?php
if (isset($_POST['btnreport'])) {
$querymain = "SELECT * FROM users";
$resultsmain = mysqli_query($connect,$querymain);
if ($resultsmain->num_rows>0) {
    while ($userid = mysqli_fetch_assoc($resultsmain)) {
        $approvedkm = $userid['approved_kmpl'];
        ?>
        <table class="table" class="mt-3 mb-3">
        <thead class="bg-dark text-light">
        <tr>
        <th colspan="3"><?php echo $userid['company']; ?></th>
        <th colspan="3"><?php echo $userid['name']; ?></th>
        <th colspan="3"><?php echo $userid['approved_kmpl'];?> KM</th>
        </tr>
        </thead>
        <thead>
        <tr>
        <th>Month</th>
        <th>Daily Mileage</th>
        <th>Usage For Month</th>
        <th>Required Per Month</th>
        <th>Excess Used</th>
        <th>(%)</th>
        <th>KM/L</th>
        <th>Consumed Liters</th>
        <th>Cost</th>
        </tr>
        </thead>
        
        <tbody>
        <?php  
        $varglobal1;
        //Closing Mileage For Month Query
        $closingmileage = "SELECT extract(MONTH from date) as Month,
        MAX(mileage) as CloseMile FROM mileagesnew 
        WHERE user_id='".$userid['id']."' 
        AND extract(YEAR FROM date) ='2020' 
        group by Month 
        ORDER BY month desc";
        
        //Usage For Month Query
        $usageformonth = "SELECT extract(MONTH from date) as Month, MIN(mileage) as StartMile FROM 
mileagesnew WHERE user_id='".$userid['id']."' AND extract(YEAR FROM date) ='2020' group by Month 
ORDER BY Month desc";
        
        //Working Days Fetch From Table Calender
        $workingdays = "SELECT Month, Working_Days as Work FROM calender WHERE Year ='2020' group by 
Month ORDER BY Month desc";
        
        //Query To Get The Sum Of Liters Pumped Per Month
        $fuelpumped = "SELECT extract(MONTH from date) as Month, SUM(`fuel`) FROM mileagesnew WHERE 
user_id='".$userid['id']."' AND extract(YEAR FROM date) ='2020' group by Month ORDER BY Month desc";
        
        //Final Query For Calculating Fuel Cost Per Month
        $fueltotalcost = "SELECT extract(MONTH from date) as Month, SUM(`fuel_cost`) FROM mileagesnew 
WHERE user_id='".$userid['id']."' AND extract(YEAR FROM date) ='2020' group by Month ORDER BY Month 
desc";
        
        $closingres = mysqli_query($connect,$closingmileage);
        $usage = mysqli_query($connect,$usageformonth);
        $working = mysqli_query($connect,$workingdays);
        $fuelpumpedresul = mysqli_query($connect,$fuelpumped);
        $finalfuelcstres = mysqli_query($connect,$fueltotalcost);
        
        if ($closingres->num_rows>0) {        
            while ($closingrow = mysqli_fetch_assoc($closingres) AND $usagerow= 
 mysqli_fetch_assoc($usage) AND $workrow = mysqli_fetch_assoc($working) AND $fuelpumpedresrow = 
 mysqli_fetch_assoc($fuelpumpedresul) AND $flcostrow = mysqli_fetch_assoc($finalfuelcstres)) {
                
                $firstrange = $closingrow['CloseMile'];
                $lastrange = $usagerow['StartMile'];
                $usageformonthres = $firstrange - $lastrange;
                
                $reqformont = $workrow['Work'];
                $reqformonth = $approvedkm * $reqformont;
                $excessusedcal = $usageformonthres - $reqformonth;
                ?>
                <tr>
                <td><?php echo $closingrow['Month']; ?></td>
                
                <td><?php echo $closingrow['CloseMile']; ?></td>
                <td><?php echo $usageformonthres; ?></td>
                <td><?php echo $reqformonth; ?></td>
                <td><?php echo $excessusedcal; ?></td>
                <td><?php echo ($excessusedcal - $usageformonthres)/100;?>%</td>
                <td>
                <?php
                error_reporting(0);
                $kmplfinal = $usageformonthres / floatval($fuelpumpedresrow['SUM(`fuel`)']);
                if (floatval($fuelpumpedresrow['SUM(`fuel`)']) == 0) {;
                    print_r(0);
                }
                else {
                    echo round($kmplfinal,4);
                }
                ?>
                </td>
                <td><?php echo round($fuelpumpedresrow['SUM(`fuel`)'],2);?></td>
                <td><?php echo round($flcostrow['SUM(`fuel_cost`)'],2);?></td>
                </tr>
                <?php
                
            }
        } else{
            echo "No Data Found";
        }
        ?>
        
        </tbody>
        <thead class="bg-dark text-light">
        <tr>
        <th colspan="2">Total</th>
        <th>Sum Of Above</th>
        <th>Sum Of Above</th>
        <th>Excess Usage Sum</th>
        <th></th>
        <th></th>
        <th>Consumed L's Sum</th>
        <th>Total Cost</th>
        </tr>
        </thead>
        </table>
        <br>
        <br>
        <br>
        <?php
    }
}
}
?>

Answer 1

You can do it through this library easily.

Download this class and save it as php-export-data.class.php in same folder where you have this exporting script.

The following script will directly download the file.

<?php

// When executed in a browser, this script will prompt for download 
// of 'test.xls' which can then be opened by Excel or OpenOffice.

require 'php-export-data.class.php';

// 'browser' tells the library to stream the data directly to the browser.
// other options are 'file' or 'string'
// 'test.xls' is the filename that the browser will use when attempting to 
// save the download
$exporter = new ExportDataExcel('browser', 'test.xls');

$exporter->initialize(); // starts streaming data to web browser


$month = 'Month';
$mileage = 'Daily Mileage';
$usage = 'Usage For Month';
$required = 'Required Per Month';
$excess = 'Excess Used';
$percentage = '(%)';
$kmpl = 'KM/L';
$consumed = 'Consumed Liters';
$cost = 'Cost';

// Creating Heaer Row
$row = array( 
        $month,
        $mileage,
        $usage,
        $required,
        $excess,
        $percentage,
        $kmpl,
        $consumed,
        $cost
);

$exporter->addRow($row); 

// Inside your while loop ()
 
while(.....) {
     .......
     .......
    // Build a row from variables
    $row = array( 
        $month,
        $mileage,
        $usage,
        $required,
        $excess,
        $percentage,
        $kmpl,
        $consumed,
        $cost
    );
    $exporter->addRow($row); 
}



// pass addRow() an array and it converts it to Excel XML format and sends 
// it to the browser
$exporter->addRow($row); 
 

$exporter->finalize(); // writes the footer, flushes remaining data to browser.

exit(); // all done

?>