Excel export and download (example), excel example

Excel export and download (example), excel example

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:

1. Use the php class library for export, such as PHPExcel

2. Export using php native Methods

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

1. header
2. iconv

3. Use the ob function for excel to export

Ob functions are divided into three steps:

1. Set Cache
2. Read Cache
3. Write files

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 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 ('username '). '</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 the 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-ali Gn: center; "> '. 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 "rel =" external nofollow "> '. excel: setChar ('complete '). '</a>';} else {echo '<a href = "obexcel. php? Id "rel =" external nofollow "> '. 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 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 ('username '). '</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 the 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-ali Gn: center; "> '. 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.

The export and download of the above excel file (for example) is all the content shared by the editor. I hope to give you a reference and support for the help house.