CSV export in laravel 5 controller

Question

So I have made a little ajax request to my reviewsController@export.

Now when I console.log() the data in my success method, the ajax response shows the correct data. However my CSV has not downloaded. So I have all the right info and have created the csv essentially.

I think this has possibly to do with setting the headers maybe?

public function export()
{
    header("Content-type: text/csv");
    header("Content-Disposition: attachment; filename=file.csv");
    header("Pragma: no-cache");
    header("Expires: 0");

    $reviews = Reviews::getReviewExport($this->hw->healthwatchID)->get();
    $columns = array('ReviewID', 'Provider', 'Title', 'Review', 'Location', 'Created', 'Anonymous', 'Escalate', 'Rating', 'Name');

    $file = fopen('php://output', 'w');
    fputcsv($file, $columns);

    foreach($reviews as $review) {
        fputcsv($file, array($review->reviewID,$review->provider,$review->title,$review->review,$review->location,$review->review_created,$review->anon,$review->escalate,$review->rating,$review->name));
    }
    exit();
}

Is there anything I am doing wrong here, or does Laravel have something to cater for this?

Answer 1

I have made a little package LaravelCsvGenerator

and placed it on packagist

Installation

$ composer require  eugene-melbourne/laravel-csv-generator

example of use in your controller

class MyController extends Controller
{

    public function getCsv(): \Symfony\Component\HttpFoundation\StreamedResponse
    {
        $data    = [
                [1, 2.1],
                [3, "hi, there"],
            ];
        $headers = ['one', 'two'];
        $data = array_merge([$headers], $data);

        return (new \LaravelCsvGenerator\LaravelCsvGenerator())
                ->setData($data)
                ->renderStream();
    }

Please, do not hesitate to comment your ideas below this answer.

Answer 2

Try this:

<?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); //use Illuminate\Support\Facades\Response;

}

Note: Works only if you don't load relationships, otherwise it will give exception

Answer 3

Try this version out - this should allow you to get a nice output using Response::stream().

public function export()
{
    $headers = array(
        "Content-type" => "text/csv",
        "Content-Disposition" => "attachment; filename=file.csv",
        "Pragma" => "no-cache",
        "Cache-Control" => "must-revalidate, post-check=0, pre-check=0",
        "Expires" => "0"
    );

    $reviews = Reviews::getReviewExport($this->hw->healthwatchID)->get();
    $columns = array('ReviewID', 'Provider', 'Title', 'Review', 'Location', 'Created', 'Anonymous', 'Escalate', 'Rating', 'Name');

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

        foreach($reviews as $review) {
            fputcsv($file, array($review->reviewID, $review->provider, $review->title, $review->review, $review->location, $review->review_created, $review->anon, $review->escalate, $review->rating, $review->name));
        }
        fclose($file);
    };
    return Response::stream($callback, 200, $headers);
}

(Adapted from this SO answer: Use Laravel to Download table as CSV)

Try using a regular link with target="_blank" rather than using JavaScript/AJAX. Because it's a file download opening in a new tab, the user experience shouldn't be too clunky.

Answer 4

The simples way

        $headers = [
        'Cache-Control'       => 'must-revalidate, post-check=0, pre-check=0'
        ,   'Content-type'        => 'text/csv'
        ,   'Content-Disposition' => 'attachment; filename=leads.csv'
        ,   'Expires'             => '0'
        ,   'Pragma'              => 'public'
    ];
    $leads = []
    return response(view('exports.leads.csv', [ 'leads' => $leads ]))
        ->withHeaders($headers);

Answer 5

This may not answer your question directly, but I'm using a package called 'thephpleague/csv' for this purpose...

To use this package:

1. composer require league/csv

2. Put the following 'use' statements in your controller:

   use Illuminate\Database\Eloquent\Collection;
   use League\Csv\Writer;
   use Schema;
   use SplTempFileObject;
   

   and any model classes you plan on using.

3. Abstract CSV creating code to a function (in your controller), e.g:

   /**
    * A function to generate a CSV for a given model collection.
    *
    * @param Collection $modelCollection
    * @param $tableName
    */
   private function createCsv(Collection $modelCollection, $tableName){
   
       $csv = Writer::createFromFileObject(new SplTempFileObject());
   
       // This creates header columns in the CSV file - probably not needed in some cases.
       $csv->insertOne(Schema::getColumnListing($tableName));
   
       foreach ($modelCollection as $data){
           $csv->insertOne($data->toArray());
       }
   
       $csv->output($tableName . '.csv');
   
   }
   

4. In your controller, create get function to retrieve/download CSV (replace 'MainMeta' with your own model class):

   public function getMainMetaData(){
   
       $mainMeta = MainMeta::all();
   
       // Note: $mainMeta is a Collection object 
       //(returning a 'collection' of data from using 'all()' function), 
       //so can be passed in below.
       $this->createCsv($mainMeta, 'main_meta');
   }
   

   When you create a route to call this function, it will download a CSV file in your browser, of your chosen Model collection/data.

5. Create a route in App\Http\routes.php like the following:

   Route::get(
       '/data/download/main_meta',
       [
           'as' => 'data/download/main_meta',
           'uses' => 'YourController@getMainMetaData'
       ]
   );
   

6. (Optional) In a blade view file (e.g. data.blade.php), include a link or button so you can easily access the download url/route:

   <p><a href="{{ URL::route('data/download/main_meta') }}" class="btn btn-lg btn-primary pull-left">Download Main Meta Data</a></p>
   

   When you click on the link, the CSV file will be downloaded in your browser. In an application I have coded, you will stay on the page you click this link.

Of course, this will differ depending on your own application. There is so much more you can do with this package (full documentation is at http://csv.thephpleague.com/). The project I am using this in is at https://github.com/rattfieldnz/bitcoin-faucet-rotator - I have just started coding on it again after a few months away, so still have a bit of refactoring/testing/tidying up to do :).

Answer 6

My approach in Laravel 5.7

/**
 * @param array $columnNames
 * @param array $rows
 * @param string $fileName
 * @return \Symfony\Component\HttpFoundation\StreamedResponse
 */
public static function getCsv($columnNames, $rows, $fileName = 'file.csv') {
    $headers = [
        "Content-type" => "text/csv",
        "Content-Disposition" => "attachment; filename=" . $fileName,
        "Pragma" => "no-cache",
        "Cache-Control" => "must-revalidate, post-check=0, pre-check=0",
        "Expires" => "0"
    ];
    $callback = function() use ($columnNames, $rows ) {
        $file = fopen('php://output', 'w');
        fputcsv($file, $columnNames);
        foreach ($rows as $row) {
            fputcsv($file, $row);
        }
        fclose($file);
    };
    return response()->stream($callback, 200, $headers);
}

public function someOtherControllerFunction() {
    $rows = [['a','b','c'],[1,2,3]];//replace this with your own array of arrays
    $columnNames = ['blah', 'yada', 'hmm'];//replace this with your own array of string column headers        
    return self::getCsv($columnNames, $rows);
}