php-ext-excel-export扩展使用札记
php-ext-excel-export扩展使用札记
业务背景
PHP环境下的导出功能优化改造，单次同步导出数据量在2万之上。原有导出功能使用的PHP5.6+PHPExcel.这一次换成了PHP7.2+php-ext-excel-export。
官方github地址 https://github.com/viest/php-ext-excel-export
扩展安装
安装扩展失败的追踪记录
官方安装扩展的建议
Liunx 下使用
pecl install xls-writer开发环境
PHP 7.2.13 (cli) (built: Dec 8 2018 12:27:01) ( NTS ) Copyright (c) 1997-2018 The PHP Group Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies with Zend OPcache v7.2.7, Copyright (c) 1999-2018, by Zend Technologies
CentOS 6.7
编译安装扩展报错
sudo make && make install
In file included from /usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c:186:
/usr/local/src/php-ext-excel-export/library/third_party/minizip/crypt.h:35: error: expected ‘;’, ‘,’ or ‘)’ before ‘*’ token
/usr/local/src/php-ext-excel-export/library/third_party/minizip/crypt.h:48: error: expected ‘;’, ‘,’ or ‘)’ before ‘*’ token
/usr/local/src/php-ext-excel-export/library/third_party/minizip/crypt.h:65: error: expected ‘;’, ‘,’ or ‘)’ before ‘*’ token
/usr/local/src/php-ext-excel-export/library/third_party/minizip/crypt.h:94: error: expected ‘;’, ‘,’ or ‘)’ before ‘*’ token
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c: In function ‘zipOpenNewFileInZip4_64’:
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c:1245: error: ‘curfile64_info’ has no member named ‘crypt_header_size’
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c:1251: error: ‘curfile64_info’ has no member named ‘pcrc_32_tab’
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c:1254: error: ‘curfile64_info’ has no member named ‘pcrc_32_tab’
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c:1255: error: ‘curfile64_info’ has no member named ‘crypt_header_size’
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c: In function ‘zip64FlushWriteBuffer’:
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c:1375: error: ‘curfile64_info’ has no member named ‘pcrc_32_tab’
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c:1375: error: ‘curfile64_info’ has no member named ‘pcrc_32_tab’
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c: In function ‘zipCloseFileInZipRaw64’:
/usr/local/src/php-ext-excel-export/library/third_party/minizip/zip.c:1610: error: ‘curfile64_info’ has no member named ‘crypt_header_size’
make: *** [library/third_party/minizip/zip.lo] Error 1在github 以Issues（https://github.com/viest/php-ext-excel-export/issues/139 ）的方式与官方沟通后，大家得出两个结论
1 可以尝试使用 yum install php-pecl-xlswriter 解决
yum install php-pecl-xlswriter2 编译报错是因为服务器centos版本过低造成
贡献者回复
This problem only occurs in CentOS6, because CentOS6 is too old.
技术关键点
对于数据导出功能的开发，这里主要说同步导出，对于数据同步导出功能有几个关键路径。
1 不管使用哪种导出扩展或者组件，数据都有一个重新组装的过程，应该尽量减少这个过程中的数组循环次数和数据库或者第三方服务的交互次数。多次循环的性能浪费会触碰到PHP的性能底线，如运行超时。
2 对于业务方来说，多数希望一次导出一个周期内的数据，比如一个月或者3个月。如果数据的来源是直接访问数据库而来，那么有一点非常重要，这里的数据库访问SQL一定要采用分页的形式，每页的pagesize可以比较大，比如3000，或者5000。切忌采用一次按照查询条件查询出来所有数据，这样在生产环境中肯定是一个隐患，运行一段时间后，总会遇到瓶颈。
php-ext-excel-export这个扩展的性能还是可以的，验证过同步导出5-6万的数据还可以应付。
当然了，对于web页面导出这样的功能，如果我是产品经理，我更倾向与使用异步方式，以报表箱的形式呈现结果。你要问我原因，留个悬念，我会单独写个总结文章。
核心代码
扩展安装
sudo yum install php-pecl-xlswriter
Total 968 kB/s | 147 kB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : libxlsxwriter-0.8.7-1.el6.remi.x86_64 1/2
Installing : php-pecl-xlswriter-1.2.5-1.el6.remi.7.2.x86_64 2/2
Verifying : php-pecl-xlswriter-1.2.5-1.el6.remi.7.2.x86_64 1/2
Verifying : libxlsxwriter-0.8.7-1.el6.remi.x86_64 2/2
Installed:
php-pecl-xlswriter.x86_64 0:1.2.5-1.el6.remi.7.2
Dependency Installed:
libxlsxwriter.x86_64 0:0.8.7-1.el6.remi扩展使用
渲染数据，设置对齐方式，设置头部粗体
/**
* 渲染数据
*
* @param mixed $header excel标题栏
* @param mixed $data 数组
*
* @return mixed
*/
public function make($header, $data)
{
/* 适用于新扩展1.2.5 start*/
$fileHandle = $this->handle->getHandle();
$format = new \Vtiful\Kernel\Format($fileHandle);
$alignStyle = $format
->align(Format::FORMAT_ALIGN_LEFT, Format::FORMAT_ALIGN_LEFT)
->toResource();
$this->handle->header($header)
->data($data)
->setRow('A1', 30, $boldStyle)->setRow('A1', 30, $alignStyle)
->output();
}
/**
* 导出
*
* @return mixed
*/
public function output()
{
$res = $this->handle->output();
if ($res) {
return ['root' => $this->filePath, 'file' => $this->fileName];
}
}常用基本命令
查看扩展版本
php --ri xlswriter
show
xlswriter support => enabled
Version => 1.2.5
libxlsxwriter headers version => 0.8.7
libxlsxwriter library version => 0.8.7pear与 pecl区别
pear is php package management. pecl is php extension management.
查看扩展模块
php -m |grep xlswriter
相关沟通链接
https://github.com/viest/php-ext-excel-export/issues/134#issuecomment-507160228
https://github.com/viest/php-ext-excel-export/issues/139