From 44b5fdddd0722095762b648cb17faf566e229ba7 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Thu, 2 Sep 2021 12:52:19 -0400 Subject: [PATCH] SearchKit - Support download formats xlsx and ods Requires the PhpSpreadsheet package --- composer.json | 3 +- composer.lock | 513 +++++++++++++++++- .../Api4/Action/SearchDisplay/Download.php | 82 ++- .../crmSearchTasks/crmSearchTaskDownload.html | 4 +- 4 files changed, 596 insertions(+), 6 deletions(-) diff --git a/composer.json b/composer.json index 1d80653f20..a0fb65b802 100644 --- a/composer.json +++ b/composer.json @@ -86,7 +86,8 @@ "pear/db": "1.11", "civicrm/composer-compile-lib": "~0.3 || ~1.0", "ext-json": "*", - "ezyang/htmlpurifier": "^4.13" + "ezyang/htmlpurifier": "^4.13", + "phpoffice/phpspreadsheet": "^1.18" }, "scripts": { "post-install-cmd": [ diff --git a/composer.lock b/composer.lock index 5fadab4d74..32478334fc 100644 --- a/composer.lock +++ b/composer.lock @@ -4,7 +4,7 @@ "Read more about it at https://getcomposer.org/doc/01-basic-usage.md#installing-dependencies", "This file is @generated automatically" ], - "content-hash": "33f820fbbb88a8cbf7cf43f773af1773", + "content-hash": "4743047a2c21f4a7299c2b23fef473d6", "packages": [ { "name": "adrienrn/php-mimetyper", @@ -1275,6 +1275,77 @@ ], "time": "2020-07-24T15:16:12+00:00" }, + { + "name": "maennchen/zipstream-php", + "version": "2.1.0", + "source": { + "type": "git", + "url": "https://github.com/maennchen/ZipStream-PHP.git", + "reference": "c4c5803cc1f93df3d2448478ef79394a5981cc58" + }, + "dist": { + "type": "zip", + "url": "https://api.github.com/repos/maennchen/ZipStream-PHP/zipball/c4c5803cc1f93df3d2448478ef79394a5981cc58", + "reference": "c4c5803cc1f93df3d2448478ef79394a5981cc58", + "shasum": "" + }, + "require": { + "myclabs/php-enum": "^1.5", + "php": ">= 7.1", + "psr/http-message": "^1.0", + "symfony/polyfill-mbstring": "^1.0" + }, + "require-dev": { + "ext-zip": "*", + "guzzlehttp/guzzle": ">= 6.3", + "mikey179/vfsstream": "^1.6", + "phpunit/phpunit": ">= 7.5" + }, + "type": "library", + "autoload": { + "psr-4": { + "ZipStream\\": "src/" + } + }, + "notification-url": "https://packagist.org/downloads/", + "license": [ + "MIT" + ], + "authors": [ + { + "name": "Paul Duncan", + "email": "pabs@pablotron.org" + }, + { + "name": "Jonatan Männchen", + "email": "jonatan@maennchen.ch" + }, + { + "name": "Jesse Donat", + "email": "donatj@gmail.com" + }, + { + "name": "András Kolesár", + "email": "kolesar@kolesar.hu" + } + ], + "description": "ZipStream is a library for dynamically streaming dynamic zip files from PHP without writing to the disk at all on the server.", + "keywords": [ + "stream", + "zip" + ], + "support": { + "issues": "https://github.com/maennchen/ZipStream-PHP/issues", + "source": "https://github.com/maennchen/ZipStream-PHP/tree/master" + }, + "funding": [ + { + "url": "https://opencollective.com/zipstream", + "type": "open_collective" + } + ], + "time": "2020-05-30T13:11:16+00:00" + }, { "name": "marcj/topsort", "version": "1.1.0", @@ -1322,6 +1393,235 @@ ], "time": "2016-11-19T14:58:11+00:00" }, + { + "name": "markbaker/complex", + "version": "2.0.3", + "source": { + "type": "git", + "url": "https://github.com/MarkBaker/PHPComplex.git", + "reference": "6f724d7e04606fd8adaa4e3bb381c3e9db09c946" + }, + "dist": { + "type": "zip", + "url": "https://api.github.com/repos/MarkBaker/PHPComplex/zipball/6f724d7e04606fd8adaa4e3bb381c3e9db09c946", + "reference": "6f724d7e04606fd8adaa4e3bb381c3e9db09c946", + "shasum": "" + }, + "require": { + "php": "^7.2 || ^8.0" + }, + "require-dev": { + "dealerdirect/phpcodesniffer-composer-installer": "^0.7.0", + "phpcompatibility/php-compatibility": "^9.0", + "phpunit/phpunit": "^7.0 || ^8.0 || ^9.3", + "squizlabs/php_codesniffer": "^3.4" + }, + "type": "library", + "autoload": { + "psr-4": { + "Complex\\": "classes/src/" + }, + "files": [ + "classes/src/functions/abs.php", + "classes/src/functions/acos.php", + "classes/src/functions/acosh.php", + "classes/src/functions/acot.php", + "classes/src/functions/acoth.php", + "classes/src/functions/acsc.php", + "classes/src/functions/acsch.php", + "classes/src/functions/argument.php", + "classes/src/functions/asec.php", + "classes/src/functions/asech.php", + "classes/src/functions/asin.php", + "classes/src/functions/asinh.php", + "classes/src/functions/atan.php", + "classes/src/functions/atanh.php", + "classes/src/functions/conjugate.php", + "classes/src/functions/cos.php", + "classes/src/functions/cosh.php", + "classes/src/functions/cot.php", + "classes/src/functions/coth.php", + "classes/src/functions/csc.php", + "classes/src/functions/csch.php", + "classes/src/functions/exp.php", + "classes/src/functions/inverse.php", + "classes/src/functions/ln.php", + "classes/src/functions/log2.php", + "classes/src/functions/log10.php", + "classes/src/functions/negative.php", + "classes/src/functions/pow.php", + "classes/src/functions/rho.php", + "classes/src/functions/sec.php", + "classes/src/functions/sech.php", + "classes/src/functions/sin.php", + "classes/src/functions/sinh.php", + "classes/src/functions/sqrt.php", + "classes/src/functions/tan.php", + "classes/src/functions/tanh.php", + "classes/src/functions/theta.php", + "classes/src/operations/add.php", + "classes/src/operations/subtract.php", + "classes/src/operations/multiply.php", + "classes/src/operations/divideby.php", + "classes/src/operations/divideinto.php" + ] + }, + "notification-url": "https://packagist.org/downloads/", + "license": [ + "MIT" + ], + "authors": [ + { + "name": "Mark Baker", + "email": "mark@lange.demon.co.uk" + } + ], + "description": "PHP Class for working with complex numbers", + "homepage": "https://github.com/MarkBaker/PHPComplex", + "keywords": [ + "complex", + "mathematics" + ], + "support": { + "issues": "https://github.com/MarkBaker/PHPComplex/issues", + "source": "https://github.com/MarkBaker/PHPComplex/tree/2.0.3" + }, + "time": "2021-06-02T09:44:11+00:00" + }, + { + "name": "markbaker/matrix", + "version": "2.1.3", + "source": { + "type": "git", + "url": "https://github.com/MarkBaker/PHPMatrix.git", + "reference": "174395a901b5ba0925f1d790fa91bab531074b61" + }, + "dist": { + "type": "zip", + "url": "https://api.github.com/repos/MarkBaker/PHPMatrix/zipball/174395a901b5ba0925f1d790fa91bab531074b61", + "reference": "174395a901b5ba0925f1d790fa91bab531074b61", + "shasum": "" + }, + "require": { + "php": "^7.1 || ^8.0" + }, + "require-dev": { + "dealerdirect/phpcodesniffer-composer-installer": "^0.7.0", + "phpcompatibility/php-compatibility": "^9.0", + "phpdocumentor/phpdocumentor": "2.*", + "phploc/phploc": "^4.0", + "phpmd/phpmd": "2.*", + "phpunit/phpunit": "^7.0 || ^8.0 || ^9.3", + "sebastian/phpcpd": "^4.0", + "squizlabs/php_codesniffer": "^3.4" + }, + "type": "library", + "autoload": { + "psr-4": { + "Matrix\\": "classes/src/" + }, + "files": [ + "classes/src/Functions/adjoint.php", + "classes/src/Functions/antidiagonal.php", + "classes/src/Functions/cofactors.php", + "classes/src/Functions/determinant.php", + "classes/src/Functions/diagonal.php", + "classes/src/Functions/identity.php", + "classes/src/Functions/inverse.php", + "classes/src/Functions/minors.php", + "classes/src/Functions/trace.php", + "classes/src/Functions/transpose.php", + "classes/src/Operations/add.php", + "classes/src/Operations/directsum.php", + "classes/src/Operations/subtract.php", + "classes/src/Operations/multiply.php", + "classes/src/Operations/divideby.php", + "classes/src/Operations/divideinto.php" + ] + }, + "notification-url": "https://packagist.org/downloads/", + "license": [ + "MIT" + ], + "authors": [ + { + "name": "Mark Baker", + "email": "mark@demon-angel.eu" + } + ], + "description": "PHP Class for working with matrices", + "homepage": "https://github.com/MarkBaker/PHPMatrix", + "keywords": [ + "mathematics", + "matrix", + "vector" + ], + "support": { + "issues": "https://github.com/MarkBaker/PHPMatrix/issues", + "source": "https://github.com/MarkBaker/PHPMatrix/tree/2.1.3" + }, + "time": "2021-05-25T15:42:17+00:00" + }, + { + "name": "myclabs/php-enum", + "version": "1.7.7", + "source": { + "type": "git", + "url": "https://github.com/myclabs/php-enum.git", + "reference": "d178027d1e679832db9f38248fcc7200647dc2b7" + }, + "dist": { + "type": "zip", + "url": "https://api.github.com/repos/myclabs/php-enum/zipball/d178027d1e679832db9f38248fcc7200647dc2b7", + "reference": "d178027d1e679832db9f38248fcc7200647dc2b7", + "shasum": "" + }, + "require": { + "ext-json": "*", + "php": ">=7.1" + }, + "require-dev": { + "phpunit/phpunit": "^7", + "squizlabs/php_codesniffer": "1.*", + "vimeo/psalm": "^3.8" + }, + "type": "library", + "autoload": { + "psr-4": { + "MyCLabs\\Enum\\": "src/" + } + }, + "notification-url": "https://packagist.org/downloads/", + "license": [ + "MIT" + ], + "authors": [ + { + "name": "PHP Enum contributors", + "homepage": "https://github.com/myclabs/php-enum/graphs/contributors" + } + ], + "description": "PHP Enum implementation", + "homepage": "http://github.com/myclabs/php-enum", + "keywords": [ + "enum" + ], + "support": { + "issues": "https://github.com/myclabs/php-enum/issues", + "source": "https://github.com/myclabs/php-enum/tree/1.7.7" + }, + "funding": [ + { + "url": "https://github.com/mnapoli", + "type": "github" + }, + { + "url": "https://tidelift.com/funding/github/packagist/myclabs/php-enum", + "type": "tidelift" + } + ], + "time": "2020-11-14T18:14:52+00:00" + }, { "name": "padaliyajay/php-autoprefixer", "version": "1.3", @@ -2077,6 +2377,110 @@ "homepage": "https://github.com/PhenX/php-svg-lib", "time": "2019-09-11T20:02:13+00:00" }, + { + "name": "phpoffice/phpspreadsheet", + "version": "1.18.0", + "source": { + "type": "git", + "url": "https://github.com/PHPOffice/PhpSpreadsheet.git", + "reference": "418cd304e8e6b417ea79c3b29126a25dc4b1170c" + }, + "dist": { + "type": "zip", + "url": "https://api.github.com/repos/PHPOffice/PhpSpreadsheet/zipball/418cd304e8e6b417ea79c3b29126a25dc4b1170c", + "reference": "418cd304e8e6b417ea79c3b29126a25dc4b1170c", + "shasum": "" + }, + "require": { + "ext-ctype": "*", + "ext-dom": "*", + "ext-fileinfo": "*", + "ext-gd": "*", + "ext-iconv": "*", + "ext-libxml": "*", + "ext-mbstring": "*", + "ext-simplexml": "*", + "ext-xml": "*", + "ext-xmlreader": "*", + "ext-xmlwriter": "*", + "ext-zip": "*", + "ext-zlib": "*", + "ezyang/htmlpurifier": "^4.13", + "maennchen/zipstream-php": "^2.1", + "markbaker/complex": "^2.0", + "markbaker/matrix": "^2.0", + "php": "^7.2 || ^8.0", + "psr/http-client": "^1.0", + "psr/http-factory": "^1.0", + "psr/simple-cache": "^1.0" + }, + "require-dev": { + "dealerdirect/phpcodesniffer-composer-installer": "dev-master", + "dompdf/dompdf": "^1.0", + "friendsofphp/php-cs-fixer": "^2.18", + "jpgraph/jpgraph": "^4.0", + "mpdf/mpdf": "^8.0", + "phpcompatibility/php-compatibility": "^9.3", + "phpstan/phpstan": "^0.12.82", + "phpstan/phpstan-phpunit": "^0.12.18", + "phpunit/phpunit": "^8.5", + "squizlabs/php_codesniffer": "^3.5", + "tecnickcom/tcpdf": "^6.3" + }, + "suggest": { + "dompdf/dompdf": "Option for rendering PDF with PDF Writer (doesn't yet support PHP8)", + "jpgraph/jpgraph": "Option for rendering charts, or including charts with PDF or HTML Writers", + "mpdf/mpdf": "Option for rendering PDF with PDF Writer", + "tecnickcom/tcpdf": "Option for rendering PDF with PDF Writer (doesn't yet support PHP8)" + }, + "type": "library", + "autoload": { + "psr-4": { + "PhpOffice\\PhpSpreadsheet\\": "src/PhpSpreadsheet" + } + }, + "notification-url": "https://packagist.org/downloads/", + "license": [ + "MIT" + ], + "authors": [ + { + "name": "Maarten Balliauw", + "homepage": "https://blog.maartenballiauw.be" + }, + { + "name": "Mark Baker", + "homepage": "https://markbakeruk.net" + }, + { + "name": "Franck Lefevre", + "homepage": "https://rootslabs.net" + }, + { + "name": "Erik Tilt" + }, + { + "name": "Adrien Crivelli" + } + ], + "description": "PHPSpreadsheet - Read, Create and Write Spreadsheet documents in PHP - Spreadsheet engine", + "homepage": "https://github.com/PHPOffice/PhpSpreadsheet", + "keywords": [ + "OpenXML", + "excel", + "gnumeric", + "ods", + "php", + "spreadsheet", + "xls", + "xlsx" + ], + "support": { + "issues": "https://github.com/PHPOffice/PhpSpreadsheet/issues", + "source": "https://github.com/PHPOffice/PhpSpreadsheet/tree/1.18.0" + }, + "time": "2021-05-31T18:21:15+00:00" + }, { "name": "phpoffice/phpword", "version": "0.18.1", @@ -2389,6 +2793,113 @@ ], "time": "2017-02-14T16:28:37+00:00" }, + { + "name": "psr/http-client", + "version": "1.0.1", + "source": { + "type": "git", + "url": "https://github.com/php-fig/http-client.git", + "reference": "2dfb5f6c5eff0e91e20e913f8c5452ed95b86621" + }, + "dist": { + "type": "zip", + "url": "https://api.github.com/repos/php-fig/http-client/zipball/2dfb5f6c5eff0e91e20e913f8c5452ed95b86621", + "reference": "2dfb5f6c5eff0e91e20e913f8c5452ed95b86621", + "shasum": "" + }, + "require": { + "php": "^7.0 || ^8.0", + "psr/http-message": "^1.0" + }, + "type": "library", + "extra": { + "branch-alias": { + "dev-master": "1.0.x-dev" + } + }, + "autoload": { + "psr-4": { + "Psr\\Http\\Client\\": "src/" + } + }, + "notification-url": "https://packagist.org/downloads/", + "license": [ + "MIT" + ], + "authors": [ + { + "name": "PHP-FIG", + "homepage": "http://www.php-fig.org/" + } + ], + "description": "Common interface for HTTP clients", + "homepage": "https://github.com/php-fig/http-client", + "keywords": [ + "http", + "http-client", + "psr", + "psr-18" + ], + "support": { + "source": "https://github.com/php-fig/http-client/tree/master" + }, + "time": "2020-06-29T06:28:15+00:00" + }, + { + "name": "psr/http-factory", + "version": "1.0.1", + "source": { + "type": "git", + "url": "https://github.com/php-fig/http-factory.git", + "reference": "12ac7fcd07e5b077433f5f2bee95b3a771bf61be" + }, + "dist": { + "type": "zip", + "url": "https://api.github.com/repos/php-fig/http-factory/zipball/12ac7fcd07e5b077433f5f2bee95b3a771bf61be", + "reference": "12ac7fcd07e5b077433f5f2bee95b3a771bf61be", + "shasum": "" + }, + "require": { + "php": ">=7.0.0", + "psr/http-message": "^1.0" + }, + "type": "library", + "extra": { + "branch-alias": { + "dev-master": "1.0.x-dev" + } + }, + "autoload": { + "psr-4": { + "Psr\\Http\\Message\\": "src/" + } + }, + "notification-url": "https://packagist.org/downloads/", + "license": [ + "MIT" + ], + "authors": [ + { + "name": "PHP-FIG", + "homepage": "http://www.php-fig.org/" + } + ], + "description": "Common interfaces for PSR-7 HTTP message factories", + "keywords": [ + "factory", + "http", + "message", + "psr", + "psr-17", + "psr-7", + "request", + "response" + ], + "support": { + "source": "https://github.com/php-fig/http-factory/tree/master" + }, + "time": "2019-04-30T12:38:16+00:00" + }, { "name": "psr/http-message", "version": "1.0.1", diff --git a/ext/search_kit/Civi/Api4/Action/SearchDisplay/Download.php b/ext/search_kit/Civi/Api4/Action/SearchDisplay/Download.php index 0d48360306..b486b6bfe0 100644 --- a/ext/search_kit/Civi/Api4/Action/SearchDisplay/Download.php +++ b/ext/search_kit/Civi/Api4/Action/SearchDisplay/Download.php @@ -3,6 +3,8 @@ namespace Civi\Api4\Action\SearchDisplay; use League\Csv\Writer; +use PhpOffice\PhpSpreadsheet\IOFactory; +use PhpOffice\PhpSpreadsheet\Spreadsheet; /** * Download the results of a SearchDisplay as a spreadsheet. @@ -22,10 +24,21 @@ class Download extends AbstractRunAction { * * @var string * @required - * @options array,csv + * @options array,csv,xlsx,ods */ protected $format = 'array'; + private $formats = [ + 'xlsx' => [ + 'writer' => 'Xlsx', + 'mime' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', + ], + 'ods' => [ + 'writer' => 'Ods', + 'mime' => 'application/vnd.oasis.opendocument.spreadsheet', + ], + ]; + /** * @param \Civi\Api4\Generic\Result $result * @throws \API_Exception @@ -61,7 +74,7 @@ class Download extends AbstractRunAction { } } - // This weird little API spits out a file and exits instead of returning a result + // Unicode-safe filename for download $fileName = \CRM_Utils_File::makeFilenameWithUnicode($this->display['label']) . '.' . $this->format; switch ($this->format) { @@ -79,13 +92,17 @@ class Download extends AbstractRunAction { case 'csv': $this->outputCSV($rows, $columns, $fileName); break; + + default: + $this->sendHeaders($fileName); + $this->outputSpreadsheet($rows, $columns); } \CRM_Utils_System::civiExit(); } /** - * Outputs csv format directly to browser for download + * Outputs headers and CSV directly to browser for download * @param array $rows * @param array $columns * @param string $fileName @@ -108,6 +125,33 @@ class Download extends AbstractRunAction { $csv->output($fileName); } + /** + * Create PhpSpreadsheet document and output directly to browser for download + * @param array $rows + * @param array $columns + */ + private function outputSpreadsheet(array $rows, array $columns) { + $document = new Spreadsheet(); + $document->getProperties() + ->setTitle($this->display['label']); + $sheet = $document->getActiveSheet(); + + // Header row + foreach ($columns as $index => $col) { + $sheet->setCellValueByColumnAndRow($index + 1, 1, $col['label']); + } + + foreach ($rows as $rowNum => $data) { + foreach ($columns as $index => $col) { + $sheet->setCellValueByColumnAndRow($index + 1, $rowNum + 2, $this->formatColumnValue($col, $data)); + } + } + + $writer = IOFactory::createWriter($document, $this->formats[$this->format]['writer']); + + $writer->save('php://output'); + } + /** * Returns final formatted column value * @@ -125,4 +169,36 @@ class Download extends AbstractRunAction { return is_array($val) ? implode(', ', $val) : $val; } + /** + * Sets headers based on content type and file name + * + * @param string $fileName + */ + protected function sendHeaders(string $fileName) { + header('Content-Type: ' . $this->formats[$this->format]['mime']); + header('Content-Transfer-Encoding: binary'); + header('Content-Description: File Transfer'); + header('Content-Disposition: ' . $this->getContentDisposition($fileName)); + } + + /** + * Copied from \League\Csv\AbstractCsv::sendHeaders() + * @param string $fileName + * @return string + */ + protected function getContentDisposition(string $fileName) { + $flag = FILTER_FLAG_STRIP_LOW; + if (strlen($fileName) !== mb_strlen($fileName)) { + $flag |= FILTER_FLAG_STRIP_HIGH; + } + + $filenameFallback = str_replace('%', '', filter_var($fileName, FILTER_SANITIZE_STRING, $flag)); + + $disposition = sprintf('attachment; filename="%s"', str_replace('"', '\\"', $filenameFallback)); + if ($fileName !== $filenameFallback) { + $disposition .= sprintf("; filename*=utf-8''%s", rawurlencode($fileName)); + } + return $disposition; + } + } diff --git a/ext/search_kit/ang/crmSearchTasks/crmSearchTaskDownload.html b/ext/search_kit/ang/crmSearchTasks/crmSearchTaskDownload.html index 7e1dff79c2..36de774e20 100644 --- a/ext/search_kit/ang/crmSearchTasks/crmSearchTaskDownload.html +++ b/ext/search_kit/ang/crmSearchTasks/crmSearchTaskDownload.html @@ -7,7 +7,9 @@
-- 2.25.1