Business Background

In PHP environment, the export function is optimized and transformed, and the single synchronous export data volume is more than 20,000. PHP 5.6 + PHPExcel. This time PHP 7.2 + php-ext-excel-export was replaced by PHP 5.6 + PHPExcel.

Official github address https://github.com/viest/php-ext-excel-export

Extended installation

Tracking record of installation extension failure

Suggestions for Official Installation Extension

Use under Liunx

pecl install xls-writer

development environment

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

Compile and Install Extension Error Reporting

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

Issues at github( https://github.com/viest/php-ext-excel-export/issues/139
) After communicating with the government, we come to two conclusions.

1 You can try using yum install php-pecl-xlswriter

yum install php-pecl-xlswriter 

2 Compilation error is caused by low centos version of server

Contributor response

This problem only occurs in CentOS6, because CentOS6 is too old.

Technical Key Points

For the development of data export function, this paper mainly talks about synchronous export, and there are several key paths for data synchronous export function.

No matter which export extensions or components are used, the data has a reassembly process, which should minimize the number of array loops and the number of interactions between the database or third-party services. Multiple cycles of performance waste can hit the bottom line of PHP performance, such as running out of time.

For the business side, most of them want to export data in one cycle at a time, such as one month or three months. If the source of data is direct access to the database, then it is very important that the database access to SQL here must be paginated form, page size per page can be larger, such as 3000, or 5000. Do not use a query according to the conditions of all data, so in the production environment is certainly a hidden danger, running for a period of time, there will always be bottlenecks.

The performance of php-ext-excel-export is still possible, and it is verified that the data exported synchronously can be handled.

Of course, for web page export, if I were a product manager, I would prefer to use an asynchronous way to present the results in the form of a report box. If you want to ask me why, leave a suspense, I will write a summary article alone.

Core code

Extended installation

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

Extended use

Render data, set alignment, set head bold

  /**
   * Rendering data
   *
   * @param mixed $header excel title bar
   * @param mixed $data     array
   *
   * @return mixed
   */
  public function make($header, $data)
  {
      /* Suitable for new extension 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();
  }

  /**
   * export
   *
   * @return mixed
   */
  public function output()
  {
      $res = $this->handle->output();
      if ($res) {
          return ['root' => $this->filePath, 'file' => $this->fileName];
      }
  }

Common Basic Commands

View Extended Version

php --ri xlswriter

show
xlswriter support => enabled
Version => 1.2.5
libxlsxwriter headers version => 0.8.7
libxlsxwriter library version => 0.8.7

The difference between pear and pecl

pear is php package management.
pecl is php extension management.

View Extension Modules

php -m |grep xlswriter

Relevant communication links

https://github.com/viest/php-ext-excel-export/issues/134#issuecomment-507160228

https://github.com/viest/php-ext-excel-export/issues/139

There are some inaccuracies in the opinions in the article. You are welcome to comment and communicate. Welcome to pay attention to the public number "Tuonan Science and Technology"