How to export csv files with thinkPHP and output excel using tables,

How to export csv files with thinkPHP and output excel using tables, _PHP tutorial

WBOY

Release： 2016-07-12 09:02:14

Original

1061 people have browsed it

How thinkPHP exports csv files and uses tables to output excel,

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

<&#63;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;
 }
}

Copy after login

2.KeywordsModel.class.php

<&#63;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;
  
 }
}

Copy after login

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

<&#63;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;
}
&#63;>
<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 &raquo;">
</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>

Copy after login

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.

Articles you may be interested in:

Based on ThinkPHP uploadify upload PHPExcel to import data without refreshing
Export data to Excel instance under the integration of Dwz and thinkphp
ThinkPHP’s method of importing Excel files based on PHPExcel
ThinkPHP uses PHPExcel to implement a complete example of Excel data import and export
ThinkPHP and PHPExcel conflict resolution method