Use Laravel to Download table as CSV

问题:

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

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.

回答1:

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);

回答2:

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.

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

<?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);
}

回答3:

EDIT: see this answer for 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).

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);
});

回答4:

Everything looks good except this line:

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

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

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

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

回答5:

Here is the complete code to download 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);

回答6:

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');

回答7:

Considering the current highest ranked answer this is the Laravel 5.7 CSV write, note the return changes.

<?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();
}