README

支持Laravel 7及以上版本

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

此包是为解决这一问题而设计的，它使用MySQL的SELECT INTO OUTFILE方法。整个导出由MySQL原生执行。它非常高效，快速。它消除了从PHP应用程序中利用内存传递所选查询集合以生成CSV的开销。这对于需要频繁导出大量数据（如~100,000行）的系统来说非常方便。

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

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

安装

通过composer安装

composer require midoelhawy/laravel-query-to-csv

注册服务提供程序

将服务提供程序添加到config/app.php中的providers部分

Laravelquerytocsv\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中为每次导出添加一个格式如下的事件

[2020-12-10 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外观

// 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
• Ahmed Hekal