php - 多个表到单个 PHP 电子表格

Question

我正在填充许多表格，其中包含特定年份的每月车辆里程详细信息。我已经设法填充表格并在网页上输出结果，但我被困在如何将 HTML 网页上显示的确切表格带到电子表格中，因为我正在使用 PHPSpreadsheet 库生成 excel。

从数据库填充数据的 HTML 表如下所示，

HTML页面中显示的此表仅用于我的开发目的，但实际要求是生成excel表报告。我如何通过一次将报告生成到一个 phpspreadsheet 中来实现这一点。

从数据库中获取数据并输出到表中的代码如下，

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

标签： phpmysqlwhile-loopexport-to-excelphpspreadsheet

Answer 1

你可以通过这个库轻松地做到这一点。

下载此类并将其保存php-export-data.class.php在您拥有此导出脚本的同一文件夹中。

以下脚本将直接下载文件。

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

?>