Home > Article > Backend Development > Native implementation method of exporting and downloading excel with PHP
I believe many friends will use the function of exporting data to EXCEL, so how to use PHP to export EXCEL? In addition to class libraries such as PHPExcel, native programming is also possible. This article uses native code to implement EXCEL export.
1. Preface
Sometimes it is necessary to export database table resources to excel to make some report data.
There are several methods for php to export excel:
Use php's class library to export, such as PHPExcel
Use php's native method to export
This article introduces how to export excel through native methods
2, key functions
2.1, ob output
ob_start(); ob_get_contents ob_end_clean fopen fwrite fclose iconv
2.2. Header output
header iconv
3. Use the ob function to export from excel
The ob function is mainly divided into three steps:
进行缓存的设置 读取缓存 写入文件
Paste the exported excel class below
<?php class Excel { // ob函数缓存的开始 public static function start () { ob_start(); } // 获取缓存内容 public static function save ($path) { $data = ob_get_contents(); ob_end_clean(); self::writeToExcel($path, $data); } //写到文件 private static function writeToExcel ($fn, $data) { $dir = self::setDir(); $fp = fopen($dir . '/'. $fn, 'wb'); fwrite($fp, $data); fclose($fp); } //excel默认是GKB,所有要转码 public static function setChar ($value, $inchar = 'utf-8', $outchar ='gbk') { return iconv($inchar, $outchar, $value);//转化编码 } //创建目录,linux系统一般要写到文件,目录需要w权限 public static function setDir($dirName = 'excel') { if(!is_dir($dirName)) { mkdir($dirName); } return $dirName; } }
4. Complete page code
<?php header('Content-Type: text/html; charset=GBK'); //excel需要GBK编码,为了防止转码产生的乱码,需要设置成GBK require 'coon.php'; require 'excel.class.php'; $sql = 'select * from user'; $res = $link->query($sql); //判断是否有发送get数据 if(isset($_GET['id'])){ $name = empty($_GET['id']) ? 'a' . rand(1000,9999) : $_GET['id'] ; $filename = $name . '.xls'; //设置缓存的开始 Excel::start(); } ?> <?php //页面输出一般是不需要转码的,excel输出才需要转码 echo '<table width=500 height=25 border=0 align=center cellpadding=0 cellspacing=0>'; echo '<thead><tr>'; //设置thead输出 echo '<td style="border-bottom:1px solid black;padding:10px;text-align:center;">' . Excel::setChar('编号') . '</td>'; echo '<td style="border-bottom:1px solid black;padding:10px;text-align:center;">' . Excel::setChar('用户名') . '</td>'; echo '<td style="border-bottom:1px solid black;padding:10px;text-align:center;">' . Excel::setChar('密码') . '</td>'; echo '</thead></tr>'; ?> <?php echo '<tbody>'; //设置tbody输出 while($re = $res->fetch(PDO::FETCH_ASSOC)){ echo '<tr>'; echo '<td style="border-bottom:1px solid black;padding:10px;text-align:center;">' . Excel::setChar($re['id']) . '</td>'; echo '<td style="border-bottom:1px solid black;padding:10px;text-align:center;">' . Excel::setChar($re['user']) . '</td>'; echo '<td style="border-bottom:1px solid black;padding:10px;text-align:center;">' . Excel::setChar($re['pwd']) . '</td>'; echo '</tr>'; } echo '</tbody></table>'; ?> <?php if(isset($_GET['id'])){ //保存文件 Excel::save($filename); } if(isset($_GET['id'])){ echo '<a href="obexcel.php">' .Excel::setChar('完成') .'</a>'; }else{ echo '<a href="obexcel.php?id">' . Excel::setChar('导出') .'</a>'; } ?>
5. Use header to download excel
Using the ob function to export excel can only be saved to the server and cannot be downloaded (it is still possible under special processing); the following describes how to use the header() function to export and download excel
The two main header() functions
header("Content-type:application/vnd.ms-excel");//指定文件类型 header("Content-Disposition:filename=a.xls" ); //指定下载文件名
Other methods are the same as the export of the ob function (complete code below)
query($sql); ?> '; echo ''; //设置thead输出 echo ' '; ?> '; //设置tbody输出 while($re = $res->fetch(PDO::FETCH_ASSOC)){ echo '' . Excel::setChar('编号') . ' '; echo '' . Excel::setChar('用户名') . ' '; echo '' . Excel::setChar('密码') . ' '; echo ''; echo ' '; } echo ''; ?>' . Excel::setChar($re['id']) . ' '; echo '' . Excel::setChar($re['user']) . ' '; echo '' . Excel::setChar($re['pwd']) . ' '; echo '
6. Conclusion
Here is a brief introduction to the export and download of excel. One is to use the ob() function, and the other is to use the header() function. , the two methods are similar, but it should be noted that excel requires GBK encoding, and the iconv function needs to be used for transcoding to ensure that no garbled characters appear.
This article only briefly introduces it, more advanced methods need to be explored by yourself.
Related recommendations:
How to use PHPExcel to implement the function of batch uploading tables
[Course] PHP Excel Control Video Tutorial
How to implement the rank function in excel
The above is the detailed content of Native implementation method of exporting and downloading excel with PHP. For more information, please follow other related articles on the PHP Chinese website!