背景
针对PHP导出Excel的优化,在我之前的一篇文章里已经做过介绍:关于PHP内存溢出的思考,本文主要是介绍一款高性能的导出组件–xlswriter,他是一个PHP C扩展,官方文档地址,请点击。
推荐:PHP视频教程
安装
安装pecl
当我们发现pecl未安装时,则需要安装pecl。一般情况下,是安装在PHP的安装目录,示例命令如下:
# 进入PHP安装目录 cd /usr/local/php/bin curl -o go-pear.php http://pear.php.net/go-pear.phar php go-pear.php # 安装完成后,软连接到bin目录下 ln -s /usr/local/php/bin/pecl /usr/bin/pecl
安装xlswriter
pecl install xlswriter # 添加 extension = xlswriter.so 到 ini 配置
使用
具体使用可以参考官方文档,会介绍的更加详细,我这就上一段我使用中的代码:
封装的导出service
/** * 下载 * @param $header * @param $data * @param $fileName * @param $type * @return bool * @throws */ public function download($header, $data, $fileName) { $config = [ 'path' => $this->getTmpDir() . '/', ]; $now = date('YmdHis'); $fileName = $fileName . $now . '.xlsx'; $xlsxObject = new \Vtiful\Kernel\Excel($config); // Init File $fileObject = $xlsxObject->fileName($fileName); // 设置样式 $fileHandle = $fileObject->getHandle(); $format = new \Vtiful\Kernel\Format($fileHandle); $style = $format->bold()->background( \Vtiful\Kernel\Format::COLOR_YELLOW )->align(Format::FORMAT_ALIGN_VERTICAL_CENTER)->toResource(); // Writing data to a file ...... $fileObject->header($header) ->data($data) ->freezePanes(1, 0) ->setRow('A1', 20, $style); // Outptu $filePath = $fileObject->output(); // 下载 header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); header('Content-Disposition: attachment;filename="' . $fileName . '"'); header('Cache-Control: max-age=0'); header('Content-Length: ' . filesize($filePath)); header('Content-Transfer-Encoding: binary'); header('Cache-Control: must-revalidate'); header('Pragma: public'); ob_clean(); flush(); if (copy($filePath, 'php://output') === false) { throw new RuntimeException('导出失败'); } // Delete temporary file @unlink($filePath); return true; } /** * 获取临时文件夹 * @return false|string */ private function getTmpDir() { // 目录可以自定义 // return \Yii::$app->params['downloadPath']; $tmp = ini_get('upload_tmp_dir'); if ($tmp !== False && file_exists($tmp)) { return realpath($tmp); } return realpath(sys_get_temp_dir()); } /** * 读取文件 * @param $path * @param $fileName * @return array */ public function readFile($path,$fileName) { // 读取测试文件 $config = ['path' => $path]; $excel = new \Vtiful\Kernel\Excel($config); $data = $excel->openFile($fileName) ->openSheet() ->getSheetData(); return $data; }
调用处代码
导出
/** * 导出 */ public function actionExport() { try { /** * @var $searchModel SkuBarCodeSearch */ $searchModel = Yii::createObject(SkuBarCodeSearch::className()); $queryParams['SkuBarCodeSearch'] = []; $result = $searchModel->search($queryParams, true); $formatData = []; if (!empty($result)) { foreach ($result as $key => &$value) { $tmpData = [ 'sku_code' => $value['sku_code'], 'bar_code' => $value['bar_code'], 'created_at' => $value['created_at'], 'updated_at' => $value['updated_at'], ]; $formatData[] = array_values($tmpData); } unset($value); } $fields = [ 'sku_code' => 'SKU编码', 'bar_code' => '条形码', 'created_at' => '创建时间', 'updated_at' => '更新时间', ]; /** * @var $utilService UtilService */ $utilService = UtilService::getInstance(); $utilService->download(array_values($fields), $formatData, 'sku_single_code'); } catch (\Exception $e) { Yii::$app->getSession()->setFlash('error', '导出失败'); } }
导入
public function actionImportTmpSku() { try { /** * @var $utilService UtilService */ $utilService = UtilService::getInstance(); $path = '/tmp/'; // 文件目录 $fileName = 'sku_0320.xlsx'; $excelData = $utilService->readFile($path, $fileName); unset($excelData[0]); $excelData = array_merge($excelData); // ... ... 业务代码 } catch (\Exception $e) { echo $e->getMessage(); exit; } }
结论
整体使用下来,在处理大数据量时,性能相比于原来的PHPExcel确实高了很多。
本文系转载,原文地址是:
https://tsmliyun.github.io/php/PHP%E5%AF%BC%E5%87%BAExcel%E7%9A%84%E4%BC%98%E5%8C%96/
以上是关于PHP导出Excel的优化详解的详细内容。更多信息请关注PHP中文网其他相关文章!