什么是创建csv文件下载的最佳方式？

问题描述：

我有一个创建“工作票”的Web应用程序。我的客户要求报告页面使他们能够将报告作为csv文件下载。我有这个功能。本质上它是在创建报表时随时创建CSV。如果表中没有大量数据，则可以下载csv。我碰到的是，当表格太大时，我达到了可以通过url传递的字符数的限制。我怎样才能解决这个问题？这让我疯狂。下面是我的php和html代码的例子。什么是创建csv文件下载的最佳方式？

PHP代码来创建CSV下载

<?php 
$out = ''; 


$filename_prefix = 'UnInvoiced'; 





if (isset($_REQUEST['csv_hdr'])) { 
$out .= $_REQUEST['csv_hdr']; 
$out .= "\n"; 
} 

if (isset($_REQUEST['csv_output'])) { 
$out .= $_REQUEST['csv_output']; 
} 


$filename = $filename_prefix."_".date("Y-m-d_H-i",time()); 


header("Content-type: application/vnd.ms-excel"); 
header("Content-Encoding: UTF-8"); 
header("Content-type: text/csv; charset=UTF-8"); 
header("Content-disposition: csv" . date("Y-m-d") . ".csv"); 
header("Content-disposition: filename=".$filename.".csv"); 
print $out; 



exit; 
?>

PHP代码创建HTML页的最初表。

echo "<div class='panel panel-primary'> 
<div class='panel-heading'> 
<h3 class='panel-title'>Un-Invoiced Tickets</h3> 

</div> 
<table class='table'> 
<thead> 
<tr class='filters'> 
<th>Date</th> 
<th>Ticket #</th> 
<th>Client</th> 
<th>Lease</th> 
<th>Ticket Total</th> 
<th>Ordered By</th> 
</tr> 
</thead> 
<tbody>"; 


    $conn = sqlsrv_connect($DBSERV, $connection); 
    if (!$conn) { 
    die('Could not connect: ' . sqlsrv_error($conn)); 
    } 
    if ($client === 'All'){ 
    $sql="SELECT REPLACE(Client,',',' ') 
    AS Client,WorkTicketNum,Lease,Date,OrderedBy,WTTotal FROM WorkTicket WHERE Date BETWEEN '".$begin."' AND '".$end."' AND Invoiced IS NULL ORDER BY Client "; 
        }else{ 
         $sql="SELECT REPLACE(Client,',',' ') AS Client,WorkTicketNum,Lease,Date,OrderedBy,WTTotal FROM WorkTicket WHERE Date BETWEEN '".$begin."' AND '".$end."' AND Invoiced IS NULL AND Client = '".$client."' ORDER BY Client "; 
        } 
       $result =sqlsrv_query($conn, $sql); 

       while($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC)) { ?> 
        <tr> 
         <td> <?php echo $row['Date']; 
        $csv_output .= $row['Date'].", "; ?> </td> 

         <td> <?php echo $row['WorkTicketNum']; 
        $csv_output .= $row['WorkTicketNum'].", "; ?> </td> 

         <td> <?php echo $row['Client']; 
        $csv_output .= $row['Client'].", "; ?> </td> 

         <td> <?php echo $row['Lease']; 
        $csv_output .= $row['Lease'].", "; ?> </td> 

         <td> <?php echo $row['WTTotal']; 
        $csv_output .= $row['WTTotal'].", "; ?> </td> 

         <td> <?= $row['OrderedBy']; 
        $csv_output .= $row['OrderedBy']."\n "; ?> </td> 
         </tr> 
       <?php 
       } 

       echo " </tbody> 
     </table> 
    </div> 

<br /> 

<a class='btn btn-default' href='/processes/uninvoicedexport.php? csv_hdr=".urlencode($csv_hdr)."&csv_output=".urlencode($csv_output)."'>Export to CSV</a>";     
/*<form name='export' action='/processes/unsignedexport.php' method='post' target='_BLANK'> 
<input type='submit' value='Export table to CSV'> 
<input type='hidden' value=".$csv_hdr." name='csv_hdr'> 
<input type='hidden' value=".$csv_output." name='csv_output'> 
</form> 

<div id='ticketHint'><b></b></div> 
</form>";*/ 
sqlsrv_close($conn); 

?>

有人曾建议我当按下下载按钮的CSV被写入到SQL数据库，然后检索但我不能为我的生活弄清楚如何做到这一点。

不要通过在URL中的数据，只需在CSV输出页面再次查询或可能在扦会话。 – AbraCadaver 2015-02-09 18:10:44

答

可以（并且可能应该）使用MySQL输出：

SELECT * FROM tableName INTO OUTFILE 'filename.csv'

这会为您生成的文件，而无需通过您的网址需要传递的数据。当然，你可以选择你想要导出的任何字段。

谢谢！我会试试这个。我会报告结果。 – K3n5 2015-02-09 18:40:29

不客气！ @ K3n5 – baao 2015-02-09 18:40:46

答

如果你的mysql用户没有权限选择outfile并且像上面的注释一样，不要把所有的数据都传递给url。

尝试是这样的（未测试，但应该给你的想法）：

<?php 
if ($_REQUEST["myaction"]=="getcsvstuff") { 
// handle the conditions you passed into $_POST or $_REQUEST 
// set your headers, do your query and output the csv results 
} else { 
// print your form to pass what you would like 
// you may have visual results of your query here in your table etc 
// just like your example 
?> 
<form action="<?php $_SERVER["PHP_SELF"];?>" method="POST"> 
<input type="hidden" name="myaction" value="getcsvstuff" /> 
<input type="submit" value="go to csv results" /> 
... whatever else you want to pass to the above condition... 
</form> 
<?php 
} 
?>

什么是创建csv文件下载的最佳方式？

相关推荐