PHP export and download native implementations of Excel

Believe that many small partners will use the data to export Excel function, then how to use PHP to export Excel? In addition to phpexcel and other class libraries, in fact, native can also be achieved. This article uses the native code to implement Excel export.

1. Preface

Sometimes you need to export your database table resources to Excel to make some report data.

There are several ways in which PHP exports Excel:

Use PHP's class library for export, such as Phpexcel

Exporting using native PHP methods

And this article is about how to export Excel using native methods.

2. Key functions

2.1, OB output

Ob_start (); Ob_get_contentsob_end_cleanfopenfwritefcloseiconv

2.2. Header output

Headericonv

3. Use the OB function for Excel to export

The OB function is divided into three main steps:

Cache settings Read cache write to file

Paste the exported Excel class below

<?phpclass    The start of the Excel {///OB function cache public    static function start ()    {        ob_start ();    }    Get cached content public    static function Save ($path)     {        $data = ob_get_contents ();        Ob_end_clean ();        Self::writetoexcel ($path, $data);    }    Write to file    private static function Writetoexcel ($FN, $data)     {        $dir = Self::setdir ();        $fp = fopen ($dir. '/'. $FN, ' WB ');        Fwrite ($fp, $data);        Fclose ($FP);    }    Excel default is GKB, all to transcode public    static function SetChar ($value, $inchar = ' utf-8 ', $outchar = ' GBK ')    {        return  iconv ($inchar, $outchar, $value);//Conversion encoding    }    To create a directory, the Linux system generally writes to a file, and the directory requires the W permission public    static function setdir ($dirName = ' Excel ')    {        if (!is_dir ($ DirName)) {            mkdir ($dirName);        }        return $dirName;    }}

4. Full page code

<?php header (' content-type:text/html; CHARSET=GBK ');    Excel needs GBK encoding, in order to prevent transcoding generated garbled, need to be set to GBK require ' coon.php ';    Require ' excel.class.php ';    $sql = ' SELECT * from user ';    $res = $link->query ($sql); Determine if there is a send get data if (Isset ($_get[' id ')) {$name = empty ($_get[' id '))? ' A '.        Rand (1000,9999): $_get[' id ']; $filename = $name.        '. xls ';    Sets the start of the cache Excel::start (); }?><?php//page output is generally not required to transcode, Excel output requires transcoding echo ' <table width=500 height=25 border=0 align=center cellpadding=0    Cellspacing=0> ';    Echo ' <thead><tr> '; Set thead output echo ' <td style= "border-bottom:1px solid black;padding:10px;text-align:center;" > '. Excel::setchar (' number ').    ' </td> '; Echo ' <td style= ' border-bottom:1px solid black;padding:10px;text-align:center; > '. Excel::setchar (' User name ').    ' </td> '; Echo ' <td style= ' border-bottom:1px solid black;padding:10px;text-align:center; > '. Excel::setchar (' password ').    ' </td> ';Echo ' </thead></tr> ';?    ><?php echo ' <tbody> ';        Set tbody output 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> ';? ><?phpif (Isset ($_get[' id ')) {//Save file Excel::save ($filename);} if (Isset ($_get[' id ')) {echo ' <a href= ' obexcel.php ' > '. Excel::setchar (' Finish '). ' </a> ';} else{Echo ' <a href= ' obexcel.php?id ' > '. Excel::setchar (' export '). '    </a> '; }?>

5. Download Excel using the header

Using the OB function to export Excel can only be saved to the server, not available for download (special processing or can be); The following describes using the header () function to export and download Excel

Main two header () functions

Header ("Content-type:application/vnd.ms-excel");//Specify File type    header ("Content-disposition:filename=a.xls");  Specify the download file name

Other practices are the same as the export of OB functions (complete code below)

<?php//download mainly relies on these two headers ("Content-type:application/vnd.ms-excel");//Specifies the file type header ("Content-disposition:filename  =a.xls "); Specifies the download file name require ' coon.php '; require ' excel.class.php '; $sql = ' select * from user '; $res = $link->query ($sql);? ><?php//page output is generally not required to transcode, Excel output requires transcoding echo ' <table width=500 height=25 border=0 align=center cellpadding=0 CE    Llspacing=0> ';    Echo ' <thead><tr> '; Set thead output echo ' <td style= "border-bottom:1px solid black;padding:10px;text-align:center;" > '. Excel::setchar (' number ').    ' </td> '; Echo ' <td style= ' border-bottom:1px solid black;padding:10px;text-align:center; > '. Excel::setchar (' User name ').    ' </td> '; Echo ' <td style= ' border-bottom:1px solid black;padding:10px;text-align:center; > '. Excel::setchar (' password ').    ' </td> '; Echo ' </thead></tr> ';?    ><?php echo ' <tbody> ';        Set tbody output 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> ';? >

6. Conclusion

Here is only a brief introduction of Excel export and download, one is to use the OB () function, one is the use of the header () function, the two methods are similar, but all need to note that Excel needs GBK encoding, need to use the Iconv function to transcode, to ensure that no garbled.

This article is simple to introduce, more advanced methods need to be explored by yourself.

Related recommendations:

How to use Phpexcel to realize the function of bulk upload table

Course PHP Control Excel Video Tutorial

How to implement the function of rank function in Excel by PHP