README

支持Laravel 5.2及以上版本

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

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

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

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

安装

通过composer安装

composer require techsemicolon/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中为每次导出添加以下格式的日志条目

[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外观

// 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