使用PHP原生导出Excel和CSV文件

发表于   |   分类于   |  

最近这一周一直在折腾一个导出的需求。需求很简单就是一个导出Excel表格，最多也就几万行数据吧，但是导出的字段有点多67个,真的是什么都想要导出。

碰到的问题是导出速度巨慢，需要几分钟…最后总结了下找到几个原因：

1.数据库表里有压缩字段，导致表很臃肿，根据当前条件搜索已经没得多少优化
2.使用的Laravel插件包 maatwebsite/excel 有性能问题(虽然写起来感觉很优雅…)
3.使用的Laravel Collection遍历也有性能问题

尝试优化 Collection

关于 Laravel Collection 平时用起来挺舒服的，做做分页啥的也就那么几十条用起来没啥感觉，但是当你遍历个一万条+的时候就感觉有些慢了。后面仔细看了下 Collection 里每个都是一个模型对象里面包括各种属性和动态获取之类的东西，毕竟为了方便使用，包含了太多的东西在模型里，其实我只是想要那么几个字段，所以遍历起来感觉速度有些慢。

尝试把 collection 通过 toArray() 方法转换成数组，但是内存消耗有些严重。转成数组后然后遍历数组 在内存中操作速度确实快了不少。但是最后还是否定了这种方式

尝试优化 插件

后面仔细看了下这个插件的代码 发现写的好优雅，才疏学浅优化不了啊(自己写的太烂 不敢动人家的代码哈哈哈哈)。不过优雅的背后牺牲的都是性能… 还是放弃优化它！！！

一番折腾最后决定使用：

1.原生SQL查询生成 『laravel builder』

2.然后通过laravel 的 chunk 分段

3.然后把原生SQL查询出来的 stdClass 组装数据

4.使用PHP设置Header头信息原生导出Excel文件

最后效果还不错，如果抛开组装数据，只看导出数据生成excel的话，速度达到了毫秒级。

补充：PHP的闭包中要想改变外面的值，需要通过 use 关键字把外面的变量传递进去，同时设置 引用传递 才能更改外面的变量

PHP原生导出Excel

导出的原理其实就是通过设置Header头告诉浏览器用excel的格式来解析读取，然后通过生成HTML表格来输出内容。对于单元格的合并其实就和操作HTML表格的合并是一样的了，支持样式的属性和字段，同时还可以操作 td 的样式来控制单元格。

导出伪代码如下

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
/**
 * 导出excel文件
 *
 * @param string $excelFileName 导出的文件名
 * @param array $title excel的标题列
 * @param array $data 导出的数据
 */
public function exportExcel($excelFileName, $title, $data)
{
    $str = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\nxmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\nxmlns=\"http://www.w3.org/TR/REC-html40\">\r\n<head>\r\n<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">\r\n</head>\r\n<body>";
    $str .="<table border=1 align=center cellpadding=0 cellspacing=0>";
    // 拼接标题行
    $str .= '<tr style="height:25px;font-size:13px;font-weight: bold;">';
    foreach ($title as $key => $val) {
        $str .= '<td>'.$val.'</td>';
    }
    $str .= '</tr>';
    // 拼接数据
    foreach ($data as $key => $val) {
        $str .= '<tr style="text-align: left;height:25px;font-size:13px;">';
        foreach ($val as $v) {
            if (is_numeric($v) && $v > 100000000) {
                $str .= "<td style='vnd.ms-excel.numberformat:@'>".$v."</td>";
            } elseif (is_numeric($v) && preg_match('/^[0-9]+(\.[0-9]{2})+$/', $v)) {
                // 是两位小数的保留2位显示
                $str .= "<td style='vnd.ms-excel.numberformat:0.00'>".$v."</td>";
            } elseif (preg_match('/^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1]) (0[0-9]|1[0-9]|2[0-4]):(0[0-9]|[1-5][0-9]):(0[0-9]|[1-5][0-9])$/', $v)) {
                // 是日期
                $str .= "<td style='vnd.ms-excel.numberformat:yyyy-mm-dd\ hh\:mm\:ss'>".$v."</td>";
            } else {
                $str .= "<td>".$v."</td>";
            }
        }
        $str .= "</tr>\n";
    }
    $str .= "</table></body></html>";
    // 实现文件下载
    header("Content-Type: application/vnd.ms-excel; name='excel'");
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=" . $excelFileName);
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Pragma: no-cache");
    header("Expires: 0");
    exit($str);
}

其中我简单写了些样式和一些正则校验，不用可以删除。

主要说一下一些单元格的格式化说明。

以上的导出会有几个常见的问题：

1.对于身份证号，手机号等过大的数字 显示的时候会用科学计数法显示。如果不介意字符串显示的话可以通过设置 td 的样式来控制 style='vnd.ms-excel.numberformat:@' 这样表示用文本的方式来显示

2.对于数字如果需要保留小数位可以设置 style='vnd.ms-excel.numberformat:0.00' 需要保留几位小数就写几个0，需要注意一点如果你传入的值比保留的小数位多，那么值会被四舍五入。比如传入的是 87.98 而设置的保留小数位是 vnd.ms-excel.numberformat:0.0 那么会显示成 88.0

3.对于日期格式设置 style='vnd.ms-excel.numberformat:yyyy-mm-dd\ hh\:mm\:ss' 其中空格、冒号等都需要转义。如果使用的是HTML里的空格字符表辅助出来会有些不一样，上面是常用的格式组合，可以自己做转换。还有一些别的转换 style='vnd.ms-excel.numberformat:\"Short Date\"' 这个会显示成 2019/7/1; style='vnd.ms-excel.numberformat:\"Medium Date\"' 会显示成 1-Jul-19

4.百分比显示。 要显示百分比的话 可以使用格式 style='vnd.ms-excel.numberformat:0%' 有一点需要注意显示的时候会把你的值放大100倍。也就是说假如你想显示成 2% 你传递进来的值需要是 0.02

5.千位分隔符。如果需要数字显示带千位分隔符可以设置 style='vnd.ms-excel.numberformat:#,###' 比如 123456 就会显示成 123,456

常用的也就上面这些了 这里还有个 常用列表 不过他使用的关键字是 mso-number-format 我尝试了下 style 中使用 mso-number-format 和 vnd.ms-excel.numberformat 都可以。

PHP导出CSV

CSV文件其实是一种通用的文件，有时也被叫做文本文件。因此导出的CSV数据报文件不能设置单元格的样式，而xls属于二进制的文件需要使用Excel之类的工具才能打开。如果没有硬性要求导出这种速度更快，我看阿里云上的一些导出都是CSV格式,但是我们这边的需要非要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
/**
 * 导出CSV标准文件
 *
 * @param string $fileName 导出的文件名
 * @param array $title 标题列
 * @param array $data 导出的数据
 */
function exportCsv($fileName, $title, $data)
{
    // 清空输出流，防止有别的信息
    ob_end_clean();
    // 打开一个输出流
    ob_start();
    // 设置header信息
    header("Content-Type: text/csv");
    header("Content-Disposition:filename=". $fileName);
    // 打开文件流
    $fileHandle = fopen('php://output', 'w');
    //转码 防止乱码
    fwrite($fileHandle, chr(0xEF).chr(0xBB).chr(0xBF));
    // 先把标题写进去
    fputcsv($fileHandle, $title);
    $index = 0;
    foreach ($data as $item) {
        // 每2000条释放一次资源
        if ($index == 2000) {
            $index = 0;
            ob_flush();
            flush();
        }
        $index++;
        fputcsv($fileHandle, $item);
    }
    // 释放资源
    ob_flush();
    flush();
    ob_end_clean();
}
-------------The End-------------