php百万级大批量数据excel 导出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | <?php class xlsTools { var $inEncode = 'utf-8' ; var $outEncode = 'gb2312' ; protected $rowCount ; //存储已经存在内存中的记录条数 protected $rowFlushCount ; // 一次flush的数据条数 public function __construct( $rowFlushCount = 1000) { $this ->rowFlushCount = $rowFlushCount ; $this ->rowCount = 0; } public function __destruct() { // TODO: Implement __destruct() method. } public function start( $param ) { // todo 文件名这里建议允许自定 ddcoder $filename = $param [ 'type' ] . '-' . date ( 'YmdHis' ) . '(' . $param [ 'name' ] . ')' ; $this ->doStart( $param [ 'title' ], $filename ); } public function doStart( $keys , $filename ) { $this ->download( $filename . '.xls' ); // php输出到缓冲区 echo '<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <style>td{vnd.ms-excel.numberformat:@}</style></head>' ; echo '<table width="100%" border="1">' ; // 有时候需要自定义表头 ddcdoer 修改于2017-08-26 if ( $keys ) { echo '<tr><th filter=all>' . implode( '</th><th filter=all>' , $keys ) . "</th> </tr>\r\n" ; } //刷新缓冲区 flush (); } //下载文件 //$mimeType = 'application/force-download' //Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet //$mimeType = 'application/vnd.ms-excel' public function download( $fname = 'data' , $data = null, $mimeType = 'application/force-download' ) { if (headers_sent( $file , $line )) { echo 'Header already sent @ ' . $file . ':' . $line ; exit (); } //header('Cache-Control: no-cache;must-revalidate'); //fix ie download bug header('Pragma: no-cache, no-store'); header( "Expires: Wed, 26 Feb 1997 08:21:57 GMT" ); if ( strpos ( $_SERVER [ "HTTP_USER_AGENT" ], 'MSIE' )) { $fname = urlencode( $fname ); header( 'Content-type: ' . $mimeType ); } else { header( 'Content-type: ' . $mimeType . ';charset=utf-8' ); } header( "Content-Disposition: attachment; filename=\"" . $fname . '"' ); //header( "Content-Description: File Transfer"); if ( $data ) { header( 'Content-Length: ' . strlen ( $data )); echo $data ; exit (); } } /** * 生成并下载csv文件 */ public function csv_export_f( $keys , $expData , $fileName ) { // export file $csv = '' ; foreach ( $keys as $key ) { $csv .= '"' . iconv("UTF-8 ", " gb2312 ", $key) . '" ,'; } $csv .= "\n" ; foreach ( $expData as $expArr ) { foreach ( $expArr as $e_key => $exp ) { $csv .= iconv( "UTF-8" , "gbk" , $this ->escapeCSV( $expArr [ $e_key ])) . ',' ; } $csv .= "\n" ; } ob_end_clean(); $fileName .= '.csv' ; header( "Content-type:text/csv" ); header( "Content-Disposition:attachment;filename=" . iconv( "UTF-8" , "gb2312" , $fileName )); header( 'Cache-Control:must-revalidate,post-check=0,pre-check=0' ); header( 'Expires:0' ); header( 'Pragma:public' ); ob_end_clean(); echo $csv ; } public function escapeCSV( $str ) { $str = str_replace ( array ( ',' , '"' , "\n\r "), array('', '" "', '' ), $str ); if ( $str == "" ) { $str = '""' ; } return $str ; } public function allData( $rows ) { foreach ( $rows as $row ) { echo '<tr><td>' . implode( '</td><td>' , $row ) . "</td></tr>\r\n" ; } flush (); } public function oneData( $row ) { echo '<tr><td>' . implode( '</td><td>' , $row ) . "</td></tr>\r\n" ; flush (); } public function end () { echo '</table>' ; flush (); } /** * 多条数据flush一次 默认1000,有初始化对象决定 */ public function multiData( $row ) { $this ->rowCount++; echo '<tr><td>' . implode( '</td><td>' , $row ) . "</td></tr>\r\n" ; if ( $this ->rowCount >= $this ->rowFlushCount) { flush (); } } } function actionHowToExportBigData() { $xlsTools = new xlsTools(); $xlsTools ->start([ 'title' => [ '列名1' , '列名2' ], //列名 'type' => 'xls' , //导出的excel的类型 'name' => '测试1000W导出' //导出的excel的文件名 ] ); /* 导出1000万条示例 */ for ( $i = 0; $i < 10000000; $i ++) { $row = [ '列值1' => 1, '列值2' => 'x' ]; $xlsTools ->multiData( $row ); } $xlsTools -> end (); } actionHowToExportBigData(); |
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库