How to export MySQL data to Excel without tripartite class library in php
This article mainly shows you "php how to export MySQL data to Excel without tripartite class library", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "php how to export MySQL data to Excel without tripartite class library".
Export MySQL data to Excel without tripartite class library
If you often export data, you may encounter using a third-party library to export the upper limit, which is a very helpless thing. When there are more than 20,000 pieces of data, the export often fails. It is easy to reach the upper limit of PHP memory usage
/ / output the Excel header and change the user.csv to the file name you want: header ('Content-Type: application/vnd.ms-excel'); header (' Content-Disposition: attachment;filename= "user.csv"); header ('Cache-Control: max-age=0'); / / get the data from the database. In order to save memory, do not read the data into memory at once. Read one line from the handle to $sql =' select * from tbl where.' ; $stmt = $db- > query ($sql); / / Open the PHP file handle, and php://output means output directly to the browser $fp = fopen ('php://output', 'a'); / / output Excel listing information $head = array ('name', 'gender', 'age', 'Email',' phone','.') ; foreach ($head as $I = > $v) {/ / CSV's Excel supports GBK encoding, be sure to convert, otherwise garbled $head [$I] = iconv ('utf-8',' gbk', $v);} / / write data to the file handle fputcsv ($fp, $head) via fputcsv; / / counter $cnt = 0; / / refresh the output buffer, not too big or too small, $limit = 100000 / / fetch data line by line without wasting memory while ($row = $stmt- > fetch (Zend_Db::FETCH_NUM)) {$cnt + +; if ($limit = = $cnt) {/ / refresh the output buffer to prevent problems caused by too much data ob_flush (); flush (); $cnt = 0;} foreach ($row as $I = > $v) {$row [$] = iconv ('utf-8',' gbk', $v) } fputcsv ($fp, $row);}
The method here is to use fputcsv to write CSV files and output Excel files directly to the browser.
The above is all the contents of the article "how php exports MySQL data to Excel without tripartite class libraries". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
