Laravel 使用PHP_XLSXWriter实现大数据量Excel导出

逃离我推掉我的手 2022-01-23 19:59 1920阅读 0赞

我在去年写过一篇关于laravel的Excel导出文章：[Laravel Excel 实现 Excel/CSV 文件导入导出功能][Laravel Excel _ Excel_CSV]，使用的是[Laravel Excel][]实现的。  
该扩展包含Excel导入导出功能，功能点是比较完善的，但是有一个地方，当实现大量数据导出到Excel的时候，会发现侠侣很慢，然后如果开启任务管理器查看的时候会发现CPU使用率飙升，内存使用率飙升，功能是没问题，但是总体导出效果不尽如人意。  
我们再来说一下另一个扩展：`PHPExcel`

# PHPExcel & PhpSpreadsheet #

[PHPExce][]l 是一个处理Excel,CVS文件的开源框架,它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格,这也是PHP至今最通用的Excel处理工具,但是它有一个非常致命的缺点: 特别占内存,对大批量的表格数据几乎会让人累觉不爱,处理速度非常慢,但是它功能非常丰富,API非常多,所以在导出复杂格式的Excel表格时,它又非常方便。所以，纠结啊~~~  
不过，PHPExcel上一版本1.8.1于2015年发布。该项目于2017年正式弃用，并于2019年永久存档。现在如果你依旧想要使用PHPExcel的话建议使用它的进阶版[PhpSpreadsheet][]，它打破了兼容性，大大提高了代码库质量（名称空间，PSR合规性，最新PHP语言功能的使用等）。  
不过，还是文章开头所说的，这个扩展，功能性非常丰富，导出复杂的Excel表格很方便，但是内存的消耗很夸张，如果你导出的是一个简单的Excel的话，建议使用一下下面这个扩展。

# PHP\_XLSXWriter #

[PHP\_XLSXWriter][PHP_XLSXWriter]设计为轻量级，并且具有最小的内存使用量。  
它旨在输出（Office 2007+）xlsx格式的Excel兼容电子表格，仅支持基本功能：

*  支持PHP 5.2.1+
 *  采用UTF-8编码输入
 *  多个工作表
 *  支持货币/日期/数字单元格格式，简单公式
 *  支持基本的单元格样式
 *  支持编写巨大的100K +行电子表格  
    总体来说：它并没有`PhpSpreadsheet`功能丰富，很多高级操作并不具备，但是它导出速度非常快，非常适合于数据量特别大，报表格式不是很复杂的导出需求。

# 基础使用 #

首先你需要安装它(建议使用composer安装)：  
如果你对`composer`的使用不太了解，建议看下我写的另一篇文章：  
[Composer详细使用及利用Composer在Github和Packagist库中发布项目][Composer_Composer_Github_Packagist]

composer require phpoffice/phpspreadsheet

我贴一个官方的demo：

$data = array(
        array('year','month','amount'),
        array('2003','1','220'),
        array('2003','2','153.5'),
    );
    
    $writer = new XLSXWriter();
    $writer->writeSheet($data);
    $writer->writeToFile('output.xlsx');

# 在Laravel中使用 #

我这里其实并没有很规则的使用Laravel的方法来进行服务注册等等，只是在Laravel框架中进行了安装，然后直接使用类的方法，你如果使用这个库，可以到时候进行注册然后使用，更加方便一点。  
安装步骤在基础使用中已经说明了，也可以查看github的文档。看一下代码吧

<?php
    
    namespace App\Http\Controllers;
    use Illuminate\Http\Request;
    class TestController extends Controller
    { 
    	/** * 导出列表 */
         public function xlsxWriter()
        { 
            $search = [];
            $title = [
    	        '姓名',
    	        '年龄',
    	        '性别',
    	        '时间'
            ];
            $data = [
                [
                  'name' => '小明',
                  'age' => 18,
                  'sex' => '男',
                  'time' => '2019-05-30 13:20:45'
                ],
                [
                   'name' => '小红',
                   'age' => 20,
                   'sex' => '女',
                   'time' => '2019-05-31 13:20:45'
                ]
            ];
            $writer = new \XLSXWriter();
            $filename = 'Excel导出-'.date('Ymd') . ".xlsx";             //文件名
            header('Content-disposition: attachment; filename="' . \XLSXWriter::sanitize_filename($filename) . '"');
            header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            header('Content-Transfer-Encoding: binary');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            $colnum = count($title) - 1;                              //查询条件合并列数
            $sheet1 = 'sheet1';                                     //工作簿名称
            if($search){ //此处是如果在有筛选条件的情况下，将第一行合并，展示筛选条件
                $where = '筛选条件：'.$search['start_time'].'--'.$search['end_time'];
                $writer->writeSheetRow(                                 //写入第一行的数据，顺便指定样式
                    $sheet1,
                    [$where],
                    ['height' => 33, 'font-size' => 10, 'font-style' => 'bold',
                        'halign' => 'center', 'valign' => 'center', 'wrap_text' => true]
                );
                //合并单元格，第一行的搜索条件需要合并单元格
                $writer->markMergedCell($sheet1, $start_row = 0, $start_col = 0, $end_row = 0, $end_col = $colnum);
            }
            //设置标题头，指定样式
            $styles1 = array('font' => '宋体', 'font-size' => 10, 'font-style' => 'bold', 'fill' => '#8DEEEE',
                'halign' => 'center', 'border' => 'left,right,top,bottom');
            $writer->writeSheetRow($sheet1, $title, $styles1);
            $styles2 = ['height' => 25];//设置单元格的高度
            foreach ($data as $row) { 
                $writer->writeSheetRow($sheet1, $row, $styles2);
            }
            $writer->writeToStdOut();exit();
        }
    }

运行后，我们看下导出的效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70]  
这时候你会发现并没有筛选条件，那是因为上面的`$search`为空，如果我把条件加上看下效果

public function xlsxWriter()
     { 
     		$search = [
                'start_time' => '2019-05-30 00:00:00',
                'end_time' => '2019-05-31 23:59:59',
            ];
            ...
            ...
            ...
            if($search){ //此处是如果在有筛选条件的情况下，将第一行合并，展示筛选条件
                $where = '筛选条件：'.$search['start_time'].'--'.$search['end_time'];
                $writer->writeSheetRow(                                 //写入第一行的数据，顺便指定样式
                    $sheet1,
                    [$where],
                    ['height' => 33, 'font-size' => 10, 'font-style' => 'bold',
                        'halign' => 'center', 'valign' => 'center', 'wrap_text' => true]
                );
                //合并单元格，第一行的搜索条件需要合并单元格
                $writer->markMergedCell($sheet1, $start_row = 0, $start_col = 0, $end_row = 0, $end_col = $colnum);
            }
           ...
           ...
        }

看下效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70 1]  
对于一些并不是很复杂的Excel整体展示上还是可以的，并且可以指定样式

# 指定列的数据类型 #

**注意**：如果你需要指定某一列单元格的数据类型，你可以添加以下代码对列数据类型进行指定。

如果暂时按照这样的话，看下效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70 2]  
由于编号被Excel识别成数字，长度过长直接采用科学计数法将长串的数值进行记录，这显然不符合我们的要求。可以这样做

public function xlsxWriter()
     { 
    		$search = [
                'start_time' => '2019-05-30 00:00:00',
                'end_time' => '2019-05-31 23:59:59',
            ];
     		$title = [
     			'编号',
    	        '姓名',
    	        '年龄',
    	        '性别',
    	        '时间'
            ];
            $data = [
                [
                  'number' => '123456789101112',
                  'name' => '小明',
                  'age' => 18,
                  'sex' => '男',
                  'time' => '2019-05-30 13:20:45'
                ],
                [
                   'number' => '123456789101113',
                   'name' => '小红',
                   'age' => 20,
                   'sex' => '女',
                   'time' => '2019-05-31 13:20:45'
                ]
            ];
            ...
            ...
            $sheet1 = 'sheet1';
             //指定列的数据类型
            foreach ($title as $key => $item){ 
                if($key == 0){ //如果是编号的话，指定数据类型为string，excel中则为文本类型
                    $col_style[] = 'string';
                }
            }
            //设置列格式，suppress_row: 去掉会多出一行数据；widths: 指定每列宽度，看自己需求是否需要指定
            $writer->writeSheetHeader($sheet1, $col_style, ['suppress_row'=>true,'widths'=>[30,30,30,30,30]] );
            ...
            ...
           
        }

再来看下效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70 3]  
`如果你需要其他类型，请参照：https://github.com/mk-j/PHP_XLSXWriter`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70 4]  
`因为测试数据比较少，所以也无法展示导出耗时及内存的使用情况，如果你自己使用的时候，上百条数据，你就会明显发现 PHP_XLSXWriter 速度非常快，并且消耗内存很少，不过当下这个扩展已经没有人维护了，不过使用起来没有影响，只是你在使用的时候遇到问题可能需要自己去解决，没有相关社区，也很久没有更新维护，但是也不失为一个轻量级的大数据量Excel导出扩展`

[Laravel Excel _ Excel_CSV]: https://blog.csdn.net/gu_wen_jie/article/details/79296470
[Laravel Excel]: https://github.com/Maatwebsite/Laravel-Excel
[PHPExce]: https://github.com/PHPOffice/PHPExcel
[PhpSpreadsheet]: https://github.com/PHPOffice/PhpSpreadsheet
[PHP_XLSXWriter]: https://github.com/mk-j/PHP_XLSXWriter
[Composer_Composer_Github_Packagist]: https://blog.csdn.net/gu_wen_jie/article/details/79285803
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70]: /images/20220124/8e3df9e4c5444ec29427ef9be7c810ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70 1]: /images/20220124/26e80b5c4dc94e898a9c112d94716b28.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70 2]: /images/20220124/273b2851484e45c4b89fe1e99f36c27c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70 3]: /images/20220124/7e9c8d89eb3e46ce9c1b0d54e2ab1ee0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1X3dlbl9qaWU_size_16_color_FFFFFF_t_70 4]: /images/20220124/d4577268befd479aa30f78f963caf8ac.png