Home > Article > Backend Development > 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.
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
2. Read
Download and installation
https://github.com/viest/php-ext-xlswriterxlswriter Document
https://xlswriter-docs.viest.me/zh-cn/an-zhuang/huan-jing-yao-qiuDownload ide helper
composer require viest/php-ext-xlswriter-ide-helper:dev-masterBut 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.
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-fpmTest data: 20 columns, each column length is 19 English letters
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: "
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!