使用php和mysql导出生成的excel文件大小非常大

Question

I am using php and mysql to generate excel file. Every thing works fine. I am using html table code and exporting data to excel file. But the downloaded file is having bigger size for 20k records it is showing nearly 35 MB. Below is my code .

<?php 
$generated_date =  date("Y-m-d-H-i-s"); 
$filename = 'order_detail_report'.$generated_date;
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
?>

Html table code

<table width="100%" border="0" cellspacing="0" cellpadding="0" >
  <tr>
    <th class="excel_style">Order Number</th>
    <th class="excel_style">EAN</th>
    <th class="excel_style">Article #</th>
    <th class="excel_style">Amazon Price</th>
    <th class="excel_style">ERP Price</th>
    <th class="excel_style">Requested Quantity</th>
    <th class="excel_style">Dispatch Quantity</th>
    <th class="excel_style">Rejected Quantity</th>
    <th class="excel_style">Cancelled Quantity</th>
  </tr>

While loop

<?php 
$query = "my select query";
$res = mysqli_query($mysqliConn,$query);
while($result= mysqli_fetch_assoc($res)){
?>
<tr>
<td class="tdstyle"><?php echo $result['order_id'];?></td>
<td class="tdstyle"><?php echo $result['ean'];?></td>
<td class="tdstyle"><?php echo $result['article_no'];?></td>
<td class="tdstyle"><?php echo $result['net_price'];?></td>
<td class="tdstyle"><?php echo $result['erp_price'];?></td>
<td class="tdstyle"><?php echo $result['requested_quantity'];?></td>
<td class="tdstyle"><?php echo $result['dispatch_quantity']?></td>
<td class="tdstyle"><?php echo $result['rejected_quanity'];?></td>
<td class="tdstyle"><?php echo $result['cancelled_quantity']?></td>
</tr>
<?php } ?>
</table>

How to solve this size of the file problem? Any help would be greatly appreciated. If I use phpexcel library I am getting some out of memory exception. So client asked me don't use phpexcel library.

Answer 1

It is not clear what is exact problem - the size of result generated file or the memory getting exhausting?

But, lets assume that the problem is memory getting exhausting. Do you really need to generate excel(.xslx) files or CSV format is acceptable? You can generate CSV file with a really low memory usage like this

<?php 
$fp = fopen('file.csv', 'w');
$query = "my select query";
$res = mysqli_query($mysqliConn,$query);
while($result= mysqli_fetch_assoc($res)) {
    $fields = [
        $result['order_id'],
        $result['ean'],
        $result['article_no'],
        $result['net_price'],
        $result['erp_price'],
        $result['requested_quantity'],
        $result['dispatch_quantity'],
        $result['rejected_quantity'],
        $result['cancelled_quantity']
    ]
    fputcsv($fp, $fields);
}
fclose($fp);
?>