Excel export and download, excel export and download

Excel export and download, excel export and download
1. Preface

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

There are several ways to export excel using php:

This article describes how to export an excel file using a native method.

2. Key functions

2.1 ob output

• Ob_start ();
• Ob_get_contents
• Ob_end_clean
• Fopen
• Fwrite
• Fclose
• Iconv

2.2. header output 3. Use the ob function for excel Export

Ob functions are divided into three steps:

The exported excel class is attached below

<? Phpclass Excel {// start of ob function cache public static function start () {ob_start () ;}// obtain the cached content public static function save ($ path) {$ data = ob_get_contents (); ob_end_clean (); self: writeToExcel ($ path, $ data);} // write to the file private static function writeToExcel ($ fn, $ data) {$ dir = self: setDir (); $ fp = fopen ($ dir. '/'. $ fn, 'wb '); fwrite ($ fp, $ data); fclose ($ fp);} // excel is GKB by default, all public static functions to be transcoded SetChar ($ value, $ inchar = 'utf-8', $ outchar = 'gbk') {return iconv ($ inchar, $ outchar, $ value ); // conversion code} // create a directory. in linux, you need to write data to a file. The directory requires w modesty, and the file requires x permission. In order to save trouble, you can directly create a sub-folder, you do not need to modify the modest 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 requires GBK encoding. To prevent garbled code produced by transcoding, set it to GBK require 'coon. php '; require' excel. class. php '; $ SQL = 'select * from user'; $ res = $ link-> query ($ SQL ); // determine whether get data is sent if (isset ($ _ GET ['id']) {$ name = empty ($ _ GET ['id'])? 'A '. rand (1000,9999): $ _ GET ['id']; $ filename = $ name. '.xls '; // set the cache start Excel: start () ;}?> <? Php // page output generally does not require transcoding, for excel output, transcoding echo '<table width = 500 height = 25 border = 0 align = center cellpadding = 0 cellspacing = 0>'; echo '<thead> <tr> '; // set thead output echo '<td> '. excel: setChar ('number '). '</td>'; echo '<td> '. excel: setChar ('username '). '</td>'; echo '<td> '. excel: setChar ('Password '). '</td>'; echo '</thead> </tr>';?> <? Php echo '<tbody>'; // set the tbody output while ($ re = $ res-> fetch (PDO: FETCH_ASSOC) {echo '<tr> '; echo '<td> '. excel: setChar ($ re ['id']). '</td>'; echo '<td> '. excel: setChar ($ re ['user']). '</td>'; echo '<td> '. excel: setChar ($ re ['pwd']). '</td>'; echo '</tr>';} echo '</tbody> </table>';?> <? Phpif (isset ($ _ GET ['id']) {// save the file Excel: save ($ filename );} if (isset ($ _ GET ['id']) {echo '<a href = "obexcel. php "> '. excel: setChar ('complete '). '</a>';} else {echo '<a href = "obexcel. php? Id "> '. Excel: setChar ('export'). '</a>';}?>

5. Use the header to download the excel file

The excel file exported using the ob function can only be saved to the server, but cannot be downloaded (special processing is acceptable). The following describes how to use the header () function to export and download the excel file.

Mainly two header () Functions

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

Other methods are the same as exporting ob functions (the complete code below)

<? Php // download mainly depends on the two headers ("Content-type: application/vnd. ms-excel "); // specify the file type header (" Content-Disposition: filename=a.xls "); // specify the download file name require 'coon. php '; require' excel. class. php '; $ SQL = 'select * from user'; $ res = $ link-> query ($ SQL);?> <? Php // page output generally does not require transcoding, for excel output, transcoding echo '<table width = 500 height = 25 border = 0 align = center cellpadding = 0 cellspacing = 0>'; echo '<thead> <tr> '; // set thead output echo '<td> '. excel: setChar ('number '). '</td>'; echo '<td> '. excel: setChar ('username '). '</td>'; echo '<td> '. excel: setChar ('Password '). '</td>'; echo '</thead> </tr>';?> <? Php echo '<tbody>'; // set the tbody output while ($ re = $ res-> fetch (PDO: FETCH_ASSOC) {echo '<tr> '; echo '<td> '. excel: setChar ($ re ['id']). '</td>'; echo '<td> '. excel: setChar ($ re ['user']). '</td>'; echo '<td> '. excel: setChar ($ re ['pwd']). '</td>'; echo '</tr>';} echo '</tbody> </table>';?>

6. Conclusion

Here we only briefly introduce the export and download of excel. One is the ob () function and the other is the header () function. The two methods are similar, however, it is important to note that excel requires GBK encoding and the iconv function must be used for transcoding to ensure no garbled characters.

This article only briefly introduces more advanced methods that need to be explored by yourself.