php 使用 Laravel 将表格下载为 CSV

Use Laravel to Download table as CSV

提问by Javacadabra

I am trying to export a database table using Laravelas a csvfile. I would like the user to be able to select the Export as CSVbutton and download the table as a csvfile. Currently I've gotten this code but It is not working:

我正在尝试将数据库表导出Laravel为csv文件。我希望用户能够选择Export as CSV按钮并将表格下载为csv文件。目前我已经得到了这个代码，但它不起作用：

my button:

我的按钮：

<a href="/all-tweets-csv" class="btn btn-primary">Export as CSV</a>

my route:

我的路线：

Route::get('/all-tweets-csv', function(){

    $table = Tweet::all();
    $filename = "tweets.csv";
    $handle = fopen($filename, 'w+');
    fputcsv($handle, array('tweet text', 'screen name', 'name', 'created at'));

    foreach($table as $row) {
        fputcsv($handle, array($row['tweet_text'], $row['screen_name'], $row['name'], $row['created_at']));
    }

    fclose($handle);

    $headers = array(
        'Content-Type' => 'text/csv',
    );

    return Response::download($handle, 'tweets.csv', $headers);
});

It returns me this error:

它返回给我这个错误：

 The file "Resource id #154" does not exist

And I've gathered that it is because it is trying to download a file that does not exist. Is there an alternative way I can go about modifying my code in order to download as a csv.

我发现这是因为它试图下载一个不存在的文件。有没有其他方法可以修改我的代码以便下载为csv.

采纳答案by Marcin Nabia?ek

Almost everything is fine except this line:

除了这一行，几乎一切都很好：

return Response::download($handle, 'tweets.csv', $headers);

You should change this line into:

您应该将此行更改为：

return Response::download($filename, 'tweets.csv', $headers);

回答by Erik

I stumbled in here trying to see if Laravel had something built in by default - the answers for this question worry me a bit. I agree with @andré-daniel that the proper method is to not write a file first, but his implementation is manually putting together the values, which would fail if any value contained quotes, spaces, etc.

我在这里偶然发现 Laravel 是否默认内置了一些东西 - 这个问题的答案让我有点担心。我同意@andré-daniel 的观点，正确的方法是不先写文件，但他的实现是手动将值放在一起，如果任何值包含引号、空格等，这将失败。

This is a more robust solution, using Laravel's Response::streamand php's fputcsvto format each line properly (will escape quotes, and quote necessary strings. see http://php.net/manual/en/function.fputcsv.phpfor details)

这是一个更强大的解决方案，使用 LaravelResponse::stream和 phpfputcsv正确格式化每一行（将转义引号，并引用必要的字符串。有关详细信息，请参阅http://php.net/manual/en/function.fputcsv.php）

<?php

public function download()
{
    $headers = [
            'Cache-Control'       => 'must-revalidate, post-check=0, pre-check=0'
        ,   'Content-type'        => 'text/csv'
        ,   'Content-Disposition' => 'attachment; filename=galleries.csv'
        ,   'Expires'             => '0'
        ,   'Pragma'              => 'public'
    ];

    $list = User::all()->toArray();

    # add headers for each column in the CSV download
    array_unshift($list, array_keys($list[0]));

   $callback = function() use ($list) 
    {
        $FH = fopen('php://output', 'w');
        foreach ($list as $row) { 
            fputcsv($FH, $row);
        }
        fclose($FH);
    };

    return Response::stream($callback, 200, $headers);
}

回答by Marcin Nabia?ek

EDIT: see this answerfor a better solution; I'll keep my answer below but note that it has issues like not escaping values and using unreasonable amounts of memory if generating large files.

编辑：请参阅此答案以获得更好的解决方案；我会在下面保留我的答案，但请注意，如果生成大文件，它会存在一些问题，例如不转义值和使用不合理的内存量。

You're unnecessarily creating a file on the disk; that induces disk IO and will cause issues if two people request that URL at the exact same time (two instances of the framework will write to that same file and bad stuff will happen such as serving a corrupted file or crashing with an exception).

您不必要地在磁盘上创建了一个文件；这会导致磁盘 IO，如果两个人同时请求该 URL，则会导致问题（框架的两个实例将写入同一个文件，并且会发生坏事，例如提供损坏的文件或因异常而崩溃）。

Use this instead :

改用这个：

Route::get('/all-tweets-csv', function() {
    $tweets = Tweets::all();

    // the csv file with the first row
    $output = implode(",", array('tweet text', 'screen name', 'name', 'created at'));

    foreach ($tweets as $row) {
        // iterate over each tweet and add it to the csv
        $output .=  implode(",", array($row['tweet_text'], $row['screen_name'], $row['name'], $row['created_at'])); // append each row
    }

    // headers used to make the file "downloadable", we set them manually
    // since we can't use Laravel's Response::download() function
    $headers = array(
        'Content-Type' => 'text/csv',
        'Content-Disposition' => 'attachment; filename="tweets.csv"',
        );

    // our response, this will be equivalent to your download() but
    // without using a local file
    return Response::make(rtrim($output, "\n"), 200, $headers);
});

回答by Joshua

Considering the current highest ranked answerthis is the Laravel 5.7 CSV write, note the returnchanges.

考虑到当前排名最高的答案，这是 Laravel 5.7 CSV 写入，请注意return更改。

<?php

public function download()
{
    $headers = [
            'Cache-Control'       => 'must-revalidate, post-check=0, pre-check=0'
        ,   'Content-type'        => 'text/csv'
        ,   'Content-Disposition' => 'attachment; filename=galleries.csv'
        ,   'Expires'             => '0'
        ,   'Pragma'              => 'public'
    ];

    $list = User::all()->toArray();

    # add headers for each column in the CSV download
    array_unshift($list, array_keys($list[0]));

   $callback = function() use ($list) {
        $FH = fopen('php://output', 'w');
        foreach ($list as $row) { 
            fputcsv($FH, $row);
        }
        fclose($FH);
    };

    return (new StreamedResponse($callback, 200, $headers))->sendContent();
}

回答by M Arfan

Here is the complete code to download CSV

这是下载CSV的完整代码

 $headers = array(
        'Content-Type' => 'application/vnd.ms-excel; charset=utf-8',
        'Cache-Control' => 'must-revalidate, post-check=0, pre-check=0',
        'Content-Disposition' => 'attachment; filename=abc.csv',
        'Expires' => '0',
        'Pragma' => 'public',
    );

$filename = "doenload.csv";
$handle = fopen($filename, 'w');
fputcsv($handle, [
    "id",
    "name"
]);

DB::table("tablename")->chunk(100, function ($data) use ($handle) {
    foreach ($data as $row) {
        // Add a new row with data
        fputcsv($handle, [
            $row->id,
            $row->name
        ]);
    }
});

fclose($handle);

return Response::download($filename, "download.csv", $headers);

回答by virtuexru

Everything looks good except this line:

除了这一行，一切看起来都不错：

return Response::download($handle, 'tweets.csv', $headers);

$handledoes not point to the correct file path. It should be the full path to tweets.csv, for example:

$handle没有指向正确的文件路径。它应该是 tweets.csv 的完整路径，例如：

return Response::download($file, 'tweets.csv', $headers);

where $fileshould be something like $file = '/path/to/download/tweets.csv'

哪里$file应该像$file = '/path/to/download/tweets.csv'

回答by jeremiah

I was following along with these examples and none of them were working for my for my code. I am attaching the code I wrote for my project as an example. This does not address the OP, just as an example guide. My data is in the form of an array of objects

我一直在关注这些示例，但没有一个适用于我的代码。我附上我为我的项目编写的代码作为示例。这不涉及 OP，仅作为示例指南。我的数据是一个对象数组的形式

public function Export($data) {
    $headers = array(
        "Content-type" => "text/csv",
        "Content-Disposition" => "attachment; filename=summary.csv",
        "Pragma" => "no-cache",
        "Cache-Control" => "must-revalidate, post-check=0, pre-check=0",
        "Expires" => "0"
    );
    $columns = array('Period', 'RevenueArea', 'Subs');

    $callback = function() use ($data, $columns)
    {
        $file = fopen('php://output', 'w');
        fputcsv($file, $columns);

        foreach($data as $items) {
            fputcsv($file, array($items->PERIOD, $items->REVENUE_AREA, $items->SUBS));

        }
        fclose($file);
    };
return Response::stream($callback, 200, $headers)->send();
}

If you noticed on my return statement I am including ->send() which generates the csv file and initializes the download. Where as ->sendContent(); just dumps the date to the screen. If you stumble across this post with the same problem I was having of the file not downloading, make sure to checkout the official documentation.

如果您在我的 return 语句中注意到我包含了 ->send()，它会生成 csv 文件并初始化下载。其中 ->sendContent(); 只是将日期转储到屏幕上。如果您偶然发现这篇文章与我遇到的文件未下载相同的问题，请务必查看官方文档。

https://api.symfony.com/2.3/Symfony/Component/HttpFoundation/StreamedResponse.html

https://api.symfony.com/2.3/Symfony/Component/HttpFoundation/StreamedResponse.html

回答by Tejendra Pareek

try this

尝试这个

 $file_name = "abc";
    $postStudent = Input::all();
    $ck = DB::table('loan_tags')->select('LAN')->where('liabilitiesId', $postStudent['id'])->get();
    $i = 0;
    foreach ($ck as $row) { 

                  $apps[$i]['LAN'] = $row->LAN;
                $apps[$i]['Account_number'] =   $postStudent['account_number'];
                $apps[$i]['Bank_Name'] =  $postStudent['bank_name'];
                $i++;
    }

    ob_end_clean();
    ob_start();
    Excel::create($file_name, function($excel) use($apps){
            $excel->sheet('Sheetname', function($sheet) use($apps){

               $sheet->row(1, array(
                     'LAN', 'Account number' , 'Bank Name'
                ));
                $k = 2;
                 foreach ($apps as $deta) {
                     $sheet->row($k, array($deta['LAN'],   $deta['Account_number'], $deta['Bank_Name']
                    ));
                    $k++;
                 }
            });
        })->download('xlsx');