excel导出百万的数据

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

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

<?php
namespace app\index\controller;
 
 
class Index
{
     
    public function index(){
        $this->excelout();
    }
     
     
    /**
     * 百万级数据导出
     */
    public function excelout(){
        //不限制执行时间，以防超时
        set_time_limit(0);
        //文件名
        $xlsName = '名字'.date('Ymd His');
        //统计总行数
        $sqlCount = 0;
     
        //表头
        $xlsCell = ['id','姓名','年龄','des'];
        //对应表头的字段
        $fields = 'id,name,age,des';
     
        //统计总行数
        $sqlCount = db('customer')->count(); 
     
        //每次取多少条
        $sqlLimit = 20000;//每次只从数据库取2000条
     
        // buffer计数器
        $cnt = 0;
     
        $fileNameArr = array();
     
        //分段执行,以免内存写满
        for ($i = 0; $i < ceil($sqlCount / $sqlLimit); $i++) {
            $fp = fopen($xlsName . '_' . $i . '.csv', 'w'); //生成临时文件
     
            $fileNameArr[] = $xlsName . '_' .  $i . '.csv';//将临时文件保存起来
     
            //第一次执行时将表头写入
            if($i == 0){
                fputcsv($fp, $xlsCell);
            }
     
            //查询出数据
            $xlsData = db('customer')->field($fields)
            ->limit($i * $sqlLimit,$sqlLimit)
            ->select()->toArray();
     
            foreach ($xlsData as $k=>$v) {
                $cnt++;
     
                //执行下一次循环之前清空缓冲区
                if ($sqlLimit == $cnt) {
                    ob_flush();
                    $cnt = 0;
                }
     
                //每行写入到临时文件
                fputcsv($fp, $v);
            }
            fclose($fp);  //每生成一个文件关闭
        }
     
        //将所有临时文件合并成一个
        foreach ($fileNameArr as $file){
            //如果是文件，提出文件内容，写入目标文件
            if(is_file($file)){
                $fileName = $file;
                //打开临时文件
                $handle1 = fopen($fileName,'r');
                //读取临时文件
                if($str = fread($handle1,filesize($fileName))){
                    //关闭临时文件
                    fclose($handle1);
                    //打开或创建要合并成的文件，往末尾插入的方式添加内容并保存
                    $handle2 = fopen($xlsName.'.csv','a+');
                    //写入内容
                    if(fwrite($handle2, $str)){
                        //关闭合并的文件，避免浪费资源
                        fclose($handle2);
                    }
                }
            }
        }
     
        //将文件压缩，避免文件太大，下载慢
        $zip = new \ZipArchive();
        $filename = $xlsName . ".zip";
        $zip->open($filename, \ZipArchive::CREATE);   //打开压缩包
        $zip->addFile($xlsName.'.csv', basename($xlsName.'.csv'));   //向压缩包中添加文件
        $zip->close();  //关闭压缩包
     
        foreach ($fileNameArr as $file) {
            unlink($file); //删除csv临时文件
        }
     
        //输出压缩文件提供下载
        header("Cache-Control: max-age=0");
        header("Content-Description: File Transfer");
        header('Content-disposition: attachment; filename=' . basename($filename)); // 文件名
        header("Content-Type: application/zip"); // zip格式的
        header("Content-Transfer-Encoding: binary"); //
        header('Content-Length: ' . filesize($filename)); //
        @readfile($filename);//输出文件;
        unlink($filename); //删除压缩包临时文件
        unlink($xlsName.'.csv'); //删除合并的临时文件
    }
     
     
     
    public function test()
    {
        $limit = 200;
        $cnt = 0;
        $xlsTitle = ['关注时间', '微信Openid', '微信昵称', '微信头像'];
         
        set_time_limit(0); 
        $sqlCount = db('customer')->count();
         
        $fileName = iconv('utf-8', 'gb2312', '助力');//文件名称
        $fileName = $fileName . date('_YmdHis');// 文件名称可根据自己情况设定
        // 输出Excel文件头，可把user.csv换成你要的文件名
        header('Content-Type: application/vnd.ms-excel;charset=utf-8');
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Cache-Control: max-age=0');
        $fileNameArr = array();
          
        for ($i = 0; $i < ceil($sqlCount / $limit); $i++) { 
            $fp = fopen($fileName . '_' . $i . '.csv', 'w'); //生成临时文件 
            // chmod('attack_ip_info_' . $i . '.csv',777);//修改可执行权限 
            $fileNameArr[] = $fileName . '_' . $i . '.csv'; // 将数据通过fputcsv写到文件句柄 
            fputcsv($fp, $xlsTitle); 
            $start = $i * $limit; 
            $dataArr = db('customer')->limit($start,$limit)->select();
            
            foreach ($dataArr as $a) {
                $cnt++;
                if ($limit == $cnt) {
                    //刷新一下输出buffer，防止由于数据过多造成问题
                    ob_flush();
                    flush();
                    $cnt = 0;
                }
                fputcsv($fp, $a);
            }
            fclose($fp);  //每生成一个文件关闭
        }
         
         
        //进行多个文件压缩
        $zip = new \ZipArchive();
        $filename = $fileName . ".zip";
        $zip->open($filename, $zip::CREATE);   //打开压缩包
        foreach ($fileNameArr as $file) {
            $zip->addFile($file, basename($file));   //向压缩包中添加文件
        }
        $zip->close();  //关闭压缩包
        foreach ($fileNameArr as $file) {
            unlink($file); //删除csv临时文件
        }
         
        //输出压缩文件提供下载
        header("Cache-Control: max-age=0");
        header("Content-Description: File Transfer");
        header('Content-disposition: attachment; filename=' . basename($filename)); // 文件名
        header("Content-Type: application/zip"); // zip格式的
        header("Content-Transfer-Encoding: binary"); //
        header('Content-Length: ' . filesize($filename)); //
        @readfile($filename);//输出文件;
        unlink($filename); //删除压缩包临时文件  
    } 
     
     
      
    
     
}