This article explains how thinkPHP exports csv files and uses tables to output excel. Share it with everyone for your reference, the details are as follows:
1.thinkphp export csv file
Exporting a csv file may only involve a few lines of code. There is a problem that has troubled me for a long time today, that is, some html code appears after exporting. This should not be the case. The view is empty, and there is no $this->display in the controller at the end. (). Finally, when I saw the words think_page_trace carefully, I suddenly realized that it is the tracking log of the page, which will be output by default. Finally, just add an exit after the method to end it. Here is the code:
1.IndexController.class.php
<?php namespace Home\Controller; use Think\Controller; class IndexController extends Controller { public function index(){ $hotel = M('keywords')->field('PageName,Page')->select(); $str = "关键字,名称\n"; $str = iconv('utf-8','gb2312',$str); $result = mysql_query("select PageName,Page from hotel_keywords"); while($row=mysql_fetch_array($result)){ $PageName = iconv('utf-8','gb2312',$row['PageName']); $Page = iconv('utf-8','gb2312',$row['Page']); $str .= $PageName.",".$Page."\n"; } $fileName = date('Ymd').'.csv'; $model = D('Keywords'); $model->export_csv($fileName,$str); exit; } }
2.KeywordsModel.class.php
<?php namespace Home\Model; use Think\Model; class KeywordsModel extends Model{ public function export_csv($filename, $data){ header("Content-type:text/csv"); header("Content-Disposition:attachment;filename=".$filename); header('Cache-Control:must-revalidate,post-check=0,pre-check=0'); header('Expires:0'); header('Pragma:public'); echo $data; } }
The secret lies in the exit sentence of the last sentence of IndexController.class.php code. If you do not write this sentence, the output excel will contain html source code. The screenshot is as follows:
2. Use table to output excel
The following code purchase_prospects.php
<?php require('page_header.php'); $site_id = getIfSet($_GET, 'site_id', 0); $customer_type = getIfSet($_GET, 'customer_type',0); $DB = Database::connect($site_id); if($site_id>0 && $customer_type>0){ $sql = ''; $out = ''; $short_name_array = SiteSettings::$SITE_SHORT_NAME; $short_name = $short_name_array[$site_id]; switch ($customer_type) { case '1':{ $sql = "SELECT email, CONCAT(UCASE(LEFT(firstname, 1)),SUBSTRING(firstname, 2)) AS firstname, CONCAT(UCASE(LEFT(lastname, 1)),SUBSTRING(lastname, 2)) AS lastname FROM customers WHERE site_id =$site_id AND email NOT REGEXP '.+(avanquest)|(planetart)|(novadevelop)|(qatest).+' AND email IN(SELECT a.email FROM customers a INNER JOIN orders b ON a.id=b.`customer_id` AND b.is_test=0 AND a.site_id =$site_id) AND email NOT IN (SELECT email_address FROM `newsletter_unsubscribes` WHERE site_id =$site_id);"; $res = $DB->query($sql); $out = '<table class="data_table"><tr><th>email</th><th>firstname</th><th>lastname</th></tr>'; while($row = mysql_fetch_array($res)){ $out .= '<tr><td>'.$row['email'].'</td><td>'.$row['firstname'].'</td><td>'.$row['lastname'].'</td></tr>'; } $short_name .= '_purchased'; break; } case '2':{ $DB->query("DROP TEMPORARY TABLE IF EXISTS tmp_purchase;"); $DB->query("CREATE TEMPORARY TABLE tmp_purchase SELECT a.email FROM customers a INNER JOIN orders b ON a.id=b.`customer_id` AND b.is_test=0 AND a.site_id =$site_id; "); $DB->query("DROP TEMPORARY TABLE IF EXISTS tmp_nopurchase;"); $DB->query("CREATE TEMPORARY TABLE tmp_nopurchase SELECT email FROM customers WHERE site_id =$site_id AND email NOT IN(SELECT email FROM tmp_purchase);"); if(9 != $site_id){ $datatype = SiteSettings::getPurchaseDataType($site_id); $DB->query("INSERT tmp_nopurchase SELECT DISTINCT email FROM triggered_email_data WHERE datatype='$datatype' AND email NOT IN(SELECT email FROM tmp_purchase);"); } $sql = "SELECT DISTINCT email FROM tmp_nopurchase WHERE email NOT REGEXP '.+(avanquest)|(planetart)|(novadevelop)|(qatest).+' AND Email REGEXP '[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$' AND email NOT IN (SELECT email_address FROM `newsletter_unsubscribes` WHERE site_id =$site_id);"; $res = $DB->query($sql); $out = '<table class="data_table"><tr><th>email</th></tr>'; while($row = mysql_fetch_array($res)){ $out .= '<tr><td>'.$row['email'].'</td></tr>'; } $short_name .= '_non-purchased and signup'; break; } default: break; } $out .= '</table>'; header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:filename=$short_name.xls"); echo $out; exit; } ?> <h1>Purchase Prospects Report</h1> <form name="frm" method="get" action="purchase_prospects.php"> <strong>Select Site:</strong> <select name="site_id" id="site_id"> <option value="0">== select site ==</option> <option value="1">STI</option> <option value="2">PA</option> <option value="3">CW</option> <option value="6">MCC</option> <option value="9">CB</option> <option value="4">STIUK</option> <option value="8">MCCUK</option> </select> <strong>Select Type:</strong> <select id="customer_type" name="customer_type"> <option value="0">== select type ==</option> <option value="1">purchased</option> <option value="2">non-purchased and signup</option> </select> <input id="submit" type="submit" value="Run »"> </form> <script type="text/javascript"> $(function(){ $("#submit").click(function(){ if('0' == $("#site_id").val() || '0' == $("#customer_type").val()){ alert('please select Site and Site'); return false; } }) }); </script>
You can also export ecxcel files in this way, the screenshot is as follows
Still pay attention to the last sentence of exit; without this sentence, there will be some page elements in excel.
I hope this article will be helpful to everyone’s PHP programming based on the thinkPHP framework.