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

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

WBOY
Release: 2016-06-21 08:53:30
Original
1112 people have browsed it
<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>
Copy after login



Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Recommendations
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template