README

支持 Lumen 5.2 及以上版本

许多应用程序需要导出到 csv 的功能。虽然实现方式多种多样，但当需要导出的数据量非常大时，许多人都会遇到性能瓶颈。PHP 应用程序可能为每个导出消耗大量的内存资源。

此包为此问题提供了解决方案，其中使用了 MySQL 的 SELECT INTO OUTFILE 方法。整个导出由 MySQL 本地执行。它非常高效、快速。它消除了 PHP 应用程序利用内存传递所选查询集合以生成 CSV 的开销。这对于经常导出大量行数据（如 ~1,00,000 行）的系统来说非常方便。

如果您担心需要传递原始查询，请不要担心。我们已经为您解决了这个问题 :)。您可以传递查询构建器，它将在内部完成其余操作，而不会给您带来额外的开销。

PS：它适用于本地数据库服务器。远程数据库服务器的功能将很快添加。

安装

通过 composer 安装

composer require techsemicolon/laravel-query-to-csv

注册服务提供者

将服务提供者添加到 bootsrap/app.php

$app->register(Lumenquerytocsv\QueryToCsvServiceProvider::class);

设置包配置

执行以下命令后，您将在 config 文件夹中找到 querytocsv.php

php artisan vendor:publish

配置

可以从 config/querytocsv.php 配置此包

1. 'default-folder' => 'csvexport'

接受一个字符串，该字符串将是存储文件夹内默认的文件夹名称，其中导出将保存。这可以在后续的使用部分 2 中动态更改。'add-timestamp' => true

接受一个布尔值。如果设置为 true，它将向指定的 csv 文件名添加时间戳，如果设置为 false，则不会添加

1. 'enable-logging' => true

建议在开发环境中或在实际需要时在生产环境中将其设置为 true。设置为 true 时，它将在日志文件 storage/logs/querytocsv.log 中为每个导出添加以下格式的日志条目

[2017-12-24 13:38:48] local.INFO: 导出执行时间：1556.09 毫秒 | 文件：/storage/csvexport/employees1514122727.csv

1. 'addlog-row-count' => true

建议在开发环境中或在实际需要时在生产环境中将其设置为 true。设置为 true 时，它将为每个导出添加行数。这仅在 'enable-logging' 设置为 true 时才有用。

[2017-12-24 13:38:48] local.INFO: 导出执行时间：1556.09 毫秒 | 文件：/storage/csvexport/employees1514122727.csv | 行数：50433

用法

1. 使用 Eloquent 进行导出

// Using eloquent query to get the select with eloquent builder. It's not required to do ->get() on the builder instance
// Note : This will not work on eger loaded eloquent relationships. For that You can use DB facade which is explained in next point 2.
$builder = Employees::select('id','name', 'designation','salary');
// Initialize QueryToCsv with Query Builder
$exportObj = QueryToCsv::setQueryBuilder($builder);
// Set csv export path
// If folder is not mentioned, it will be looked up from "default-folder" configration specified in config/querytocsv.php
$fileName = 'users'; //Required | No need to add .csv extension
$folderName = 'csv-export'; //Optional | No need to add slashes before or after
$exportObj->setExportFile($fileName, $folderName);
//Set column headers for export csv | It should match the order and count of columns selected in query builder ->select()
$exportObj->setColumnHeaders([
    'Employee Id',
    'Full Name',
    'Designation',
    'Anual Salary'
]);
// This will generate and download the CSV directly in response from controller
return $exportObj->downloadSheetAsResponse();

//OR if you do not want to download the csv in response, but just generate the csv and get the file path, you can use following instead of ->downloadSheetAsResponse()
$filePath = $exportObj->generateSheetAndGetFilePath();

1. 使用 DB Facade

// You can use DB facade instead of Eloquent like below | It's not required to do ->get() on the builder instance
$builder = DB::table('employees')->select('id','name', 'designation','salary')
// Initialize QueryToCsv with Query Builder
$exportObj = QueryToCsv::setQueryBuilder($builder);
// Set csv export path
// If folder is not mentioned, it will be looked up from "default-folder" configration specified in app/config/querytocsv.php
$fileName = 'users'; //Required | No need to add .csv extension
$folderName = 'csv-export'; //Optional | No need to add slashes before or after
$exportObj->setExportFile($fileName, $folderName);
//Set column headers for export csv | It should match the order and count of columns selected in query builder ->select()
$exportObj->setColumnHeaders([
    'Employee Id',
    'Full Name',
    'Designation',
    'Anual Salary'
]);
// This will generate and download the CSV directly in response from controller
return $exportObj->downloadSheetAsResponse();

//OR if you do not want to download the csv in response, but just generate the csv and get the file path, you can use following instead of ->downloadSheetAsResponse()
$filePath = $exportObj->generateSheetAndGetFilePath();

1. 使用原始查询

// Raw queries are not suggested to be used, but the option is available if anyone specifically needs it
// The package internally checks for common SQL injections performed
$rawQuery = "SELECT `id`,`name`, `designation`,`salary` FROM employees ORDER BY `name` DESC";
// Initialize QueryToCsv with Query Builder
$exportObj = LargeExport::setRawQuery($rawQuery);
// Set csv export path
// If folder is not mentioned, it will be looked up from "default-folder" configration specified in app/config/querytocsv.php
$fileName = 'users'; //Required | No need to add .csv extension
$folderName = 'csv-export'; //Optional | No need to add slashes before or after
$exportObj->setExportFile($fileName, $folderName);
//Set column headers for export csv | It should match the order and count of columns selected in query builder ->select()
$exportObj->setColumnHeaders([
    'Employee Id',
    'Full Name',
    'Designation',
    'Anual Salary'
]);
// This will generate and download the CSV directly in response from controller
return $exportObj->downloadSheetAsResponse();

//OR if you do not want to download the csv in response, but just generate the csv and get the file path, you can use following instead of ->downloadSheetAsResponse()
$filePath = $exportObj->generateSheetAndGetFilePath();

安全

如果您使用 setRawQuery() 方法，我们已经为您解决了常见的 SQL 注入问题。但是，如果您仍然发现与安全相关的任何问题，请通过 tech.semicolon@gmail.com 联系我们

致谢

• Mihir Bhende