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();

  

posted @   肚子圆圆  阅读(3328)  评论(0编辑  收藏  举报
编辑推荐:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示