PHP uses native methods to export Excel instance sharing

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:

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

2. 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_contents

• Ob_end_clean

• fopen

• Fwrite

• Fclose

• Iconv

2.2. Header output

1. Header

2. Iconv

3. Use the OB function for Excel to export

The OB function is divided into three main steps:

1. Settings for caching

2. Read cache

3. Write 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, Linux system generally to write to files, directories need w modesty, and files need x permission, in order to save trouble directly to create subfolders, can not need to modify the humble 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.