php导出excel结合xlswriter优化内存不足的问题

简介：xlswriter导出，PHP导出excel

关于PHP导出excel大家最先想到的一定是PHPExcel库，不过这个库已经很多年没有更新了，而且特别耗内存。我在逛PHP官方扩展库时发现了一个更好的excel导出扩展库【xlswriter】

今天我们针对xlswriter扩展库的安装及用法进行说明！

1. 安装扩展

目前xlswriter的最新版本是1.5.5，对PHP版本的要求是PHP>=7.0

也就是说，我们的PHP版本至少是PHP7或者更高的版本。在安装PHP扩展之前我们必须安装PHP，不会安装的可以看看我之前的文章【php安装php源码安装】。

下面是xlswriter扩展的安装流程：

如果你的系统中已经安装了pecl，可以使用pecl安装：

pecl install xlswriter

或者源码方式安装：

# 下载扩展
wget https://pecl.php.net/get/xlswriter-1.5.5.tgz

#解压缩
tar -zxvf xlswriter-1.5.5.tgz

# 进入解压缩后的目录
cd xlswriter-1.5.5/

# 使用 phpize 生成编译配置文件
/usr/local/php/bin/phpize

# 编译（注意--enable-reader是开启读取文件的功能）
./configure --with-php-config=/usr/local/php/bin/php-config --enable-reader

# 安装
make && make install

修改配置文件使其生效（vim /usr/local/php/lib/php.ini），在配置文件中添加如下信息：

extension=imagick.so

不知道配置文件路径的可以使用【php --ini】查看

重启php-fpm就可以正常使用了。

2. 使用xlswriter扩展库

xlswriter有一个中文的 官方文档 有兴趣的可以去看看。接下来我们还是来学习一下它的使用和测评吧。

2.1 基础使用：

//初始化xlswriter中的Excel对象
$config = [
    'path'=>__ROOT__.'/web' //xlsx文件保存的绝对路径
];
$excel = new \Vtiful\Kernel\Excel($config);
//创建导出的文件并自动创建一个工作表，第二个参数是工作表的名称，默认为：Sheet1
$excel->fileName('test.xlsx');
//设置header头部
$excel->header(['商品名','商品数量']);
//批量设置数据
$excel->data([
    ['测试商品',1],['测试商品222',2],['测试商品333',3],
]);
//输出到文件中，并返回文件的绝对路径
$filePath = $excel->output();
//手动关闭资源（默认所有资源将在变量生命周期结束时全部销毁）
$excel->close();

2.1 固定内存模式

只需要将fileName创建工作表改为constMemory即可：

//创建导出的文件并自动创建一个工作表，
//第二个参数是工作表的名称，默认为：Sheet1，
//第三个参数为是否启用zip64，wps需要关闭zip64，否则会报文件损坏
$excel->constMemory('test.xlsx','Sheet1',false);

2.2 样式设置

在特殊情况下，我们需要针对某个单元格设置不同的样式。官方已经提供了一个样式类【\Vtiful\Kernel\Format】，具体使用如下：

// 初始化样式类
$format = new \Vtiful\Kernel\Format($excel->getHandle());

//设置加粗
$format->bold();

// 设置cell的边框，其值Format中提供了常量，以【BORDER_】开头
$format->border(\Vtiful\Kernel\Format::BORDER_THIN);

//设置背景颜色，必须是整数。Format提供了相关常量，以【COLOR_】开头
$format->background(\Vtiful\Kernel\Format::COLOR_LIME);

// 设置居中，Format提供了相关常量，以【FORMAT_ALIGN_】开头
$format->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER);

// 设置字体名称
$format->font('仿宋');

// 设置字体颜色,Format提供了相关常量，以【COLOR_】开头
$format->fontColor(\Vtiful\Kernel\Format::COLOR_LIME);

// 设置字体大小,注意必须是数字
$format->fontSize(12);

样式类设置好了之后我们就可以给cell添加样式，代码如下：

/**
 * 参数一：需要设置哪一行
 * 参数二：该行的行高
 * 参数三：具体样式类
 */
$excel->setRow('A1',20,$boldStyle);

当然我们也可以在插入数据时设置某个单元格的样式，后面我们会具体说明。

2.3 插入数据

在实际的使用场景中我们可能需要通过游标从数据库中读取数据，这个时候需要一条一条读取，以减少内存的使用量。在这种场景中我们需要一条一条的插入数据到excel中，实现代码如下：

//初始化xlswriter中的Excel对象
$config = [
    'path'=>__ROOT__.'/web' //xlsx文件保存的绝对路径
];
$excel = new \Vtiful\Kernel\Excel($config);
//创建导出的文件并自动创建一个工作表，
//第二个参数是工作表的名称，默认为：Sheet1，
//第三个参数为是否启用zip64，wps需要关闭zip64，否则会报文件损坏
$excel->constMemory('test1.xlsx','Sheet1',false);
//设置样式，Format是样式类
$format    = new \Vtiful\Kernel\Format($excel->getHandle());
$format->border(\Vtiful\Kernel\Format::BORDER_THIN);
$format->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER);
// 设置字体颜色,Format也提供了相关常量，以【COLOR_】开头
$format->fontColor(\Vtiful\Kernel\Format::COLOR_BLACK);
$boldStyle = $format->bold()->toResource();
$excel->setRow('A1',20,$boldStyle);

//设置header头部
$excel->header(['订单编号','设备编号','交易单号','支付金额','支付时间']);

$contentFormat    = new \Vtiful\Kernel\Format($excel->getHandle());
$contentFormat->background(\Vtiful\Kernel\Format::COLOR_LIME);
// 设置字体名称
$contentFormat->font('华文中宋');
// 设置字体颜色,Format也提供了相关常量，以【COLOR_】开头
$contentFormat->fontColor(\Vtiful\Kernel\Format::COLOR_BLACK);
$contentFormat->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER);
// 设置字体大小,注意必须是数字
$contentFormat->fontSize(12);
$contentStyle = $contentFormat->bold()->toResource();

//插入数据的初始行数
$index = 1;

$pdo = new \PDO("数据库服务器DSN信息", "数据库用户名","数据库密码");
//设置非缓存模型
$pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$result = $pdo->query("SELECT * FROM `orders`");
if ($result) {
    while ($row = $result->fetch(\PDO::FETCH_ASSOC,\PDO::FETCH_ORI_NEXT,10)){
        //PHP处理数据逻辑
        /**
         * 参数一：在第几行插入数据
         * 参数二：在第几列插入数据
         * 参数三（选填）：单元格的样式，默认：null。必须是字符串，具体的可以参考Excel微软文档
         * 参数四（选填）：单元格的样式类，列如:上面的Format类
         */
        $excel->insertText($index+1,$key,$value,null,$contentStyle);
    }
}
//输出到文件中
$filePath = $excel->output();

//手动关闭资源（默认所有资源将在变量生命周期结束时全部销毁）
$excel->close();

数据库读取数据使用了游标的概念，具体使用大家可以看看我之前的文章【解决PHP处理大量数据时内存不足的问题】。

如果使用了框架，框架提供的批量处理数据的方式基本都有内存超限问题。所以我上面使用了原生的PDO的方式连接数据库，再用游标的方式获取数据。这样就可以很好的控制内存的使用了，还能加快执行的效率。

我个人在本地的虚拟机中测试，导出37万行数据只需要7~8秒，内存消耗维持在1~2M以内。这个效果还是很不错的，值得赞赏。

下载excel

在excel资源释放后可以通过copy函数将excel内容放到PHP的文件流中，前端自动下载文件流信息

header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//设置导出的文件名
header('Content-Disposition: attachment;filename="test2.xlsx"');
header('Content-Length: ' . filesize($filePath));
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Cache-Control: max-age=0');
header('Pragma: public');

//强制刷新缓存输出到浏览器中
ob_clean();
flush();

if (copy($filePath, 'php://output') === false) {
     //导出异常时的逻辑
}
//删除临时的excel文件
@unlink($filePath);

注意：$filePath是导出后的Excel的绝对路径

 

有什么不对的可以在我的公众号留言