如何使用mysql，而xlsxwriter类的循环？

xxhby3vn 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(343)

我正在使用xlsxwriter，但对我来说没有任何效果，我尝试了许多方法将mysql数据库行数据转换为excel行数据，但都没有效果：(

<?php
include_once("xlsxwriter.class.php");
include("connection.php");
ini_set('display_errors', 0);
ini_set('log_errors', 1);
error_reporting(E_ALL & ~E_NOTICE);

$filename = "PM.xlsx";
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');

$rows = array(); 

                   $sql = "SELECT DATE_FORMAT(jobcard.Open_date_time,' %d-%b-%y') AS datee,vehicles_data.Frame_no, jobcard.Jobc_id,jobcard.serv_nature,jobcard.Customer_name,jobc_invoice.Lnet, jobc_invoice.Pnet, jobc_invoice.Snet, jobcard.Mileage,customer_data.cust_type,vehicles_data.model_year,jobcard.Veh_reg_no,jobcard.comp_appointed, customer_data.mobile,IF(variant_codes.Make IS NULL,'Others',variant_codes.Make) as make FROM `jobcard` LEFT OUTER JOIN jobc_invoice ON jobcard.Jobc_id=jobc_invoice.Jobc_id LEFT OUTER JOIN vehicles_data ON jobcard.Vehicle_id=vehicles_data.Vehicle_id LEFT OUTER JOIN variant_codes ON vehicles_data.Model=variant_codes.Model LEFT OUTER JOIN customer_data ON jobcard.Customer_id=customer_data.Customer_id ORDER BY `make` ASC";

                   $result=mysqli_query($conn,$sql) or die(mysqli_error($sql));
 while($rowz = mysqli_fetch_row($result))
                   {
                        $rows[] = $rowz;
                   }

$writer = new XLSXWriter();
$writer->setAuthor('Some Author'); 
foreach($rows as $row)
    $writer->writeSheetRow('Sheet1', $row);
$writer->writeToStdOut();

exit(0);

甚至尝试了这个，玩数组，但没有任何成果。

$row_excel=array();
$row_numb=0;
 $writer = new XLSXWriter();
$writer->setAuthor('Some Author');             
                   $result=mysqli_query($conn,$sql) or die(mysqli_error($sql));
 while($row = mysqli_fetch_row($result))
                   {
                       $row_excel[$row_numb]=$row;
                       $writer->writeSheetRow('Sheet1', $row_excel[$row_numb]);

$row_numb++；}

$writer->writeToStdOut();
exit(0);

mysql php

来源：https://stackoverflow.com/questions/50129210/how-to-use-mysql-while-loop-with-xlsxwriter-class

3条答案

按热度按时间

nr7wwzry1#

试试这个：

while ($row = mysqli_fetch_row($result)) {

    $data = array();

    for ($i = 0; $i < mysqli_num_fields($result); $i++) {
        $data[$i] = $row[$i];
    }

    $writer->writeSheetRow('Sheet1', $data);
}

$writer->writeToStdOut();
exit(0);

赞(0）回复(0）举报 2021-06-21

c9x0cxw02#

$data = array();
  $result=mysqli_query($conn,$sql) or die(mysqli_error($sql));

while ($row = mysqli_fetch_row($result)) {

echo "<br/>";
    for ($i = 0; $i < mysqli_num_fields($result); $i++) {
        $data[$i] = $row[$i];
        echo $row[$i];
    }

}

我尝试了这个代码，它显示了数据库中的数据。

赞(0）回复(0）举报 2021-06-21

lo8azlld3#

嘿，我在回答一个有点老的问题，但它可能会帮助一些人，如果有相同的问题，如“岩石和我”。
另外，我还要处理大量的行，比如50000到100000。所以我甚至尝试了一些其他的解决方案，比如分块我的结果，尝试导出到csv等等。
我还尝试了所有带有header和$writer->writetostdout（）的强制下载；但每一个逻辑都是徒劳的。
但经过一两天的努力，我终于把它编码为“放弃一切”，相信我，它也可以在20秒内下载100000行。。这是我的代码，所以不用查询和文件名的会话变量，您可以随意使用。另外，我会把一些虚拟变量，因为它是为我们的客户之一。

<?php
    include your required libraries..

    include_once("lib/xlsxwriter.class.php");
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>Download IDs</title>

</head>
<body>

        <?php
            $sqlsel = $_SESSION['rptQry'];
            $ressql = mysqli_query($sqlsel);
            $currentdt = date('Ymdhis');
            $filename = $currentdt."_".$_SESSION['rptFileName'];
            $header = array(
                'Header1'=>'integer',               
                'Header 2'=>'integer',              
                'Header 3'=>'string',
                'Header 4' =>'string'
            );      

            $dataArray = array();

            $writer = new XLSXWriter();
            $writer->setAuthor('Adaptable Services');
            $writer->writeSheetHeader('ExportedIDs', $header);

            while($res = mysqli_fetch_array($ressql)){
                $writer->writeSheetRow('Sheet1',$res);
            }

            $writer->writeToFile('/var/www/html/tmpxls/'.$filename);
            echo '<script>location.href="www.example.com/tmpxls/'.$filename.'";</script>';
        ?>

</body>
</html>

赞(0）回复(0）举报 2021-06-21

我来回答

如何使用mysql，而xlsxwriter类的循环？

3条答案

相关问题

热门标签

最新问答