php原生导出excel文件的两种方法

最新推荐文章于 2021-12-01 16:36:22 发布

任聪聪

最新推荐文章于 2021-12-01 16:36:22 发布

阅读量8.3k

点赞数 2

分类专栏： PHP 文章标签： php excel

本文链接：https://blog.csdn.net/hj960511/article/details/51883008

版权

PHP 专栏收录该内容

64 篇文章 3 订阅

订阅专栏

第一种方法：

    $filename='文件名称';
    $filetitle='你的标题';
    if($_POST){
        set_time_limit(10000)；
        $title = '';
        ini_set('memory_limit','300M');
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        $name = $title.".xls";
        header('Content-Disposition: attachment;filename='.$name.'');
        header('Cache-Control: max-age=0');
        $where = "1=1";
        $sql = "";
        $query = DB::Query($sql);
        // PHP文件句柄，php://output 表示直接输出到浏览器 
        $fp = fopen('php://output', 'a');
        // 输出Excel列头信息 
        $head = array('ID');
        //字符替换
        $p_new_lines = array("\r\n", "\n","\t","\r","\r\n", "<pre>","</pre>","<br>","</br>","<br/>");
        $p_change_line_in_excel_cell = '';

        foreach($head as $v){
            echo iconv('utf-8','gb2312',$v) . "\t";
        }
        echo "\n";
        // 计数器 
        $cnt = 0;
        // 每隔$limit行，刷新一下输出buffer，节约资源 
        $limit = 100000;
        // 逐行取出数据，节约内存
        while ($res = mysql_fetch_assoc($query)) {
            $cnt ++;
            if ($limit == $cnt) { //刷新一下输出buffer，防止由于数据过多造成问题 
                ob_flush();
                flush();
                $cnt = 0;
            }  
            echo  trim($res['id']). "\t";
            echo "\n";
        }

    }

第二种方法：

    $filename='文件名称';
    $filetitle='你的标题';
    if($_POST){
        $title = '';
        ini_set('memory_limit','300M');
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        $name = $title.".xls";
        header('Content-Disposition: attachment;filename='.$name.'');
        header('Cache-Control: max-age=0');
        echo '<html xmlns:o="urn:schemas-microsoft-com:office:office"
           xmlns:x="urn:schemas-microsoft-com:office:excel"
           xmlns="http://www.w3.org/TR/REC-html40">
        <head>
           <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
           <meta http-equiv=Content-Type content="text/html; charset=gb2312">
           <!--[if gte mso 9]><xml>
           <x:ExcelWorkbook>
           <x:ExcelWorksheets>
             <x:ExcelWorksheet>
             <x:Name></x:Name>
             <x:WorksheetOptions>
               <x:DisplayGridlines/>
             </x:WorksheetOptions>
             </x:ExcelWorksheet>
           </x:ExcelWorksheets>
           </x:ExcelWorkbook>
           </xml><![endif]-->
        </head>';
        $where = "1=1";

        $sql = " ";
        mysql_query('set names "utf8"');
        mysql_set_charset('utf8');
        $query = DB::Query($sql);

        // PHP文件句柄，php://output 表示直接输出到浏览器 
        $fp = fopen('php://output', 'a');
        // 输出Excel列头信息 
        $head = array('ID','xxx');
        //字符替换
        $p_new_lines = array("\r\n", "\n","\t","\r","\r\n", "<pre>","</pre>","<br>","</br>","<br/>");
        $p_change_line_in_excel_cell = '';
        echo "<table>";
        echo "<tr>";
        foreach($head as $v){
            echo "<td>".iconv('utf-8','gb2312',$v)."</td>";
        }
        echo "</tr>";
        // 逐行取出数据，节约内存
        while ($res = mysql_fetch_assoc($query)) {
            echo "<tr>";
            echo  "<td style='vnd.ms-excel.numberformat:@'>".$res['id']."</td>";
            echo  "<td>".iconv('utf-8', 'gb2312', $res['xxx']."</td>";
            echo"</tr>";
        }
        echo "</table>";
    }