Rumah > php教程 > php手册 > PHP实现导出MySQL数据到Excel文件

PHP实现导出MySQL数据到Excel文件

WBOY
Lepaskan: 2016-06-21 08:53:30
asal
1113 orang telah melayarinya
<ol class="dp-xml">
<li class="alt"><span><span>// 输出Excel文件头,可把user.csv换成你要的文件名  </span></span></li>
<li><span> </span></li>
<li class="alt"><span>  header('Content-Type: application/vnd.ms-excel');  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  header('Content-Disposition: attachment;</span><span class="attribute">filename</span><span>=</span><span class="attribute-value">"user.csv"</span><span>');  </span>
</li>
<li><span> </span></li>
<li class="alt">
<span>  header('Cache-Control: </span><span class="attribute">max-age</span><span>=</span><span class="attribute-value">0</span><span>');  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  // 从数据库中获取数据,为了节省内存,不要把数据一次性读到内存,从句柄中一行一行读即可  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  $</span><span class="attribute">sql</span><span> = </span><span class="attribute-value">'select * from tbl where ……'</span><span>;  </span>
</li>
<li><span> </span></li>
<li class="alt">
<span>  $</span><span class="attribute">stmt</span><span> = $db-</span><span class="tag">></span><span>query($sql);  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  // 打开PHP文件句柄,php://output 表示直接输出到浏览器  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  $</span><span class="attribute">fp</span><span> = </span><span class="attribute-value">fopen</span><span>('php://output', 'a');  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  // 输出Excel列名信息  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  $</span><span class="attribute">head</span><span> = </span><span class="attribute-value">array</span><span>('姓名', '性别', '年龄', 'Email', '电话', '……');  </span>
</li>
<li><span> </span></li>
<li class="alt">
<span>  foreach ($head as $</span><span class="attribute">i</span><span> =</span><span class="tag">></span><span> $v) {  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  // CSV的Excel支持GBK编码,一定要转换,否则乱码  </span></li>
<li><span> </span></li>
<li class="alt"><span>  $head[$i] = iconv('utf-8', 'gbk', $v);  </span></li>
<li><span> </span></li>
<li class="alt"><span>  }  </span></li>
<li><span> </span></li>
<li class="alt"><span>  // 将数据通过fputcsv写到文件句柄  </span></li>
<li><span> </span></li>
<li class="alt"><span>  fputcsv($fp, $head);  </span></li>
<li><span> </span></li>
<li class="alt"><span>  // 计数器  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  $</span><span class="attribute">cnt</span><span> = </span><span class="attribute-value">0</span><span>;  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  // 每隔$limit行,刷新一下输出buffer,不要太大,也不要太小  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  $</span><span class="attribute">limit</span><span> = </span><span class="attribute-value">100000</span><span>;  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  // 逐行取出数据,不浪费内存  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  while ($</span><span class="attribute">row</span><span> = $stmt-</span><span class="tag">></span><span>fetch(Zend_Db::FETCH_NUM)) {  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  $cnt ++;  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  if ($</span><span class="attribute">limit</span><span> == $cnt) { //刷新一下输出buffer,防止由于数据过多造成问题  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  ob_flush();  </span></li>
<li><span> </span></li>
<li class="alt"><span>  flush();  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  $</span><span class="attribute">cnt</span><span> = </span><span class="attribute-value">0</span><span>;  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  }  </span></li>
<li><span> </span></li>
<li class="alt">
<span>  foreach ($row as $</span><span class="attribute">i</span><span> =</span><span class="tag">></span><span> $v) {  </span>
</li>
<li><span> </span></li>
<li class="alt"><span>  $row[$i] = iconv('utf-8', 'gbk', $v);  </span></li>
<li><span> </span></li>
<li class="alt"><span>  }  </span></li>
<li><span> </span></li>
<li class="alt"><span>  fputcsv($fp, $row);  </span></li>
<li><span> </span></li>
<li class="alt"><span>  }   </span></li>
</ol>
Salin selepas log masuk



Label berkaitan:
sumber:php.cn
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Cadangan popular
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan