PHP研习社-数据导出工具-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-xlswriter 

2 编译报错是因为服务器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.7

pear与 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

---

文章已同步到公众号《图南日晟》欢迎关注