How to export lines db table to csv file with phpspreadsheet

In laravel 5.7 with "phpoffice/phpspreadsheet": "^1.6" app I need to export lines db table to csv file, like :


$dataLines= [ 
    ['field1'=>'000000000', 'field2'=>1111111111111],
    ['field1'=>'11000000000', 'field2'=>221111111111111],
    ['field1'=>'31000000000', 'field2'=>321111111111111],
];


$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet);

$writer->save('/path/12345.csv');

But with code above I have empty file and I did find not way to write content of $dataLines array. Also as I need to write 1 row as fields name and next rows from db, have I to prepare 1st row with fields name and rest rows (only values without fields name) manually ? Are there some mothods to make it automatically ?

Talinon

6 years ago

Level 51

@petrogromovo I'd recommend taking a look at Laravel-Excel. It's built on-top of PhpSpreadsheet, but makes working with Laravel much easier. You can export from Collections, Arrays, even views. Mapping the data, with header rows is pretty easy, too, which answers your question about needing to manually program it.

For example: https://docs.laravel-excel.com/3.1/exports/mapping.html

The newer versions are OOP based, which I also find nice.

https://github.com/Maatwebsite/Laravel-Excel

Robbeck

6 years ago

Best Answer

Level 7

If you don't have a spreadsheet and just want to write a csv file with data from an array, then I think PhpSpreadsheet isn't the way to go.

There's a built in function called fputcsv which should be enough for your purpose.

In your case, you can use it like this:


$header = ["field1", "field2"];
$dataLines = [
    ["field1" => "000000000", "field2" => 1111111111111],
    ["field1" => "11000000000", "field2" => 221111111111111],
    ["field1" => "31000000000", "field2" => 321111111111111],
];

$fp = fopen("/path/12345.csv", "w");

// write field header
fputcsv($fp, $header);

// write the remaining data
foreach ($dataLines as $line) {
    fputcsv($fp, $line);
}

fclose($fp);

2 likes

technoigniters

1 year ago

Level 1

@Robbeck Thanks dear, it worked for me and exported csv file correctly :)

1 like

PetroGromovo

6 years ago

Level 5

Thanks! Looks like fputcsv works good. Could you please also give a hint how to download the generated csv file in browser (saving it in temp dir I suppose ) ?

PetroGromovo

6 years ago

Level 5

I found how to write to csv file, next I need to write csv file and download in browsers download function using method :

\Response::download(

I try to upload it to tmp directory and checking value I see

        $sys_get_temp_dir= sys_get_temp_dir();

line above has /tmp value

Next I save it as :

        $dest_csv_file= $sys_get_temp_dir . '/box_rooms_'.time().'.csv';
        $header = ["Id",... ];

        $fp = fopen($dest_csv_file, "w");

        fputcsv($fp, $header);

        foreach ($storageSpacesCollection as $line) {
            fputcsv($fp, $line);
        }

        fclose($fp);

But searching on my local ubuntu 18(on server I also have ubuntu) I found generated file as

/tmp/systemd-private-6a9ea6844b9c4c94883d23e4fb3e2215-apache2.service-shqCeo/tmp/box_rooms_1576324869.csv

That was very strange, as I do not know how read it from tmp path. Can it be done?

Please or to participate in this conversation.