Detailed explanation of PHP using xlswriter to optimize Excel export performance (with code examples)

This article brings you relevant knowledge about PHP, and mainly talks about what is the xlswriter extension? How to use the xlswriter extension to optimize Excel export performance. Friends who are interested can take a look below. I hope it will be helpful to everyone.

About xlswriter

xlswriter is a PHP C extension designed to improve the performance of PHP when exporting large amounts of data. It supports windows / Linux. Can be used to read data in Excel 2007 XLSX files, insert multiple worksheets, write text, numbers, formulas, dates, charts, pictures and hyperlinks.

It has the following features:

1. Write

• 100% compatible Excel XLSX file
• Full Excel format
• Merge cells
• Define worksheet name
• Filter
• Chart
• Data Validation and dropdown lists
• Worksheet PNG/JPEG images
• Memory optimized mode for writing large files
• Works with Linux, FreeBSD, OpenBSD, OS X, Windows
• Compiled for 32-bit and 64-bit
• FreeBSD License
• The only dependency is zlib

2. Read

• Read data completely
• Read data with cursor
• Read by data type
• xlsx to CSV
• Performance comparison
• Thank you in advance for providing data

Download and installation

##github source code

https://github.com/viest/php-ext-xlswriter

xlswriter Document

https://xlswriter-docs.viest.me/zh-cn/an-zhuang/huan-jing-yao-qiu

Download ide helper

composer require viest/php-ext-xlswriter-ide-helper:dev-master

But I kept failing to download, so I went to the github warehouse to download directly https://github.com/viest/php-ext-xlswriter-ide -helper

Then copy several classes inside to a xlswriter_ide_helper.php file. Put this file into your project and you will get code prompts.

Install xlswriter extension

Install in docker here

docker exec -it php72-fpm bashcd /usr/local/bin
pecl install xlswriter
docker-php-ext-enable xlswriter
php -m

php --ri xlswriter
Version => 1.3.6

docker restart php72-fpm

Performance Test:

Test data: 20 columns, each column length is 19 English letters

Xlswriter

PHPSpreadSheet

##PHP_XLSXWriter

Usage example:

private function rankPersonExport($activityInfo, $list){
    $date = date('Y-m-d');
    $filename = "{$activityInfo['orgname']}-{$activityInfo['name']}-个人排行榜-{$date}";
    $header = ['名次', '用户ID', '对接账号', '姓名', '电话', '部门ID', '一级部门', '二级部门', '三级部门', '总积分', '最后积分时间', "毫秒"];
    if (!empty($activityInfo['ext'])) {
        $extArr = json_decode($activityInfo['ext'], true);
        foreach ($extArr as $errItem) {
            array_push($header, $errItem['name']);
        }
    }
    // list
    $listVal = [];
    foreach($list as $v){
        $temp = [
            $v['rank'],
            $v['userid'],
            $v['userName'],
            $v['nickName'],
            $v['phone'],
            $v['departid'],
            $v['topDepartName'],
            $v['secDepartName'],
            $v['thirdDepartName'],
            $v['score'],
            $v['updatetime'],
            $v['micro'],
        ];

        if (!empty($v['ext'])) {
            $extArr = explode('|', $v['ext']);
            foreach ($extArr as $k2 => $v2) {
                $errItemArr = explode('^', $v2);
                array_push($temp, $errItemArr[1]);
            }
        }
        array_push($listVal, $temp);
    }

    $re = downloadXLSX($filename, $header, $listVal);
    if($re){
        return $this->output(0, $re);
    }else{
        return $this->output(1, 'success');
    }}

function getTmpDir(): string{
    $tmp = ini_get('upload_tmp_dir');

    if ($tmp !== False && file_exists($tmp)) {
        return realpath($tmp);
    }

    return realpath(sys_get_temp_dir());}/**
 * download xlsx file
 *
 * @param string $filename
 * @param array $header
 * @param array $list
 * @return string errmsg
 */function downloadXLSX(string $filename, array $header, array $list): string{
    try {
        $config = ['path' => getTmpDir() . '/'];
        $excel  = (new \Vtiful\Kernel\Excel($config))->fileName($filename.'.xlsx', 'Sheet1');
        $fileHandle = $excel->getHandle();
        $format1    = new \Vtiful\Kernel\Format($fileHandle);
        $format2    = new \Vtiful\Kernel\Format($fileHandle);

        // title style
        $titleStyle = $format1->fontSize(16)
            ->bold()
            ->font("Calibri")
            ->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)
            ->toResource();

        // global style
        $globalStyle = $format2->fontSize(10)
            ->font("Calibri")
            ->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)
            ->border(\Vtiful\Kernel\Format::BORDER_THIN)
            ->toResource();

        $headerLen = count($header);

        // header
        array_unshift($list, $header);

        // title
        $title = array_fill(1, $headerLen - 1, '');
        $title[0] = $filename;
        array_unshift($list, $title);

        $end = strtoupper(chr(65 + $headerLen - 1));
        // column style
        $excel->setColumn("A:{$end}", 15, $globalStyle);
        // title
        $excel->MergeCells("A1:{$end}1", $filename)->setRow("A1", 25, $titleStyle);
        // 冻结前两行，列不冻结
        $excel->freezePanes(2, 0);
        // 数据
        $filePath = $excel->data($list)->output();

        header("Content-Disposition:attachment;filename={$filename}.xlsx");

        $re = copy($filePath, 'php://output');
        if ($re === false) {
            $err = 'failed to write output';
        } else {
            $err = '';
        }
        @unlink($filePath);

        return $err;
    } catch (\Vtiful\Kernel\Exception $e) {
        return $e->getMessage();
    }}

If you find that the downloaded file sometimes cannot be opened, it should be that you have used the official DEMO. The problem lies in filesize( ), this function is cached, so you will find that the size of the downloaded file is different from the original file. Either don't set Content-Length like I did, or clear the cache manually using clearstatcache().

Accurately, it takes 1.5s to export 50,000 records, and the effect is still very strong.

Export effect

Recommended learning: "

PHP Video Tutorial

"

The above is the detailed content of Detailed explanation of PHP using xlswriter to optimize Excel export performance (with code examples). For more information, please follow other related articles on the PHP Chinese website!