If you have a large number of records stored in a database, it could be useful to share that data as a different format. This article will go through exporting the data from a database into a spreadsheet that can be an .xls/.xlsx file for use with spreadsheet software, or as a .csv so it can be read by a large number of software. I will also go through exporting an array or object to a spreadsheet.
To start off, you’ll need a laravel project. Do note that this article will presume a reasonable knowledge of laravel.
Then you’ll need to install the Excel plugin, which you can get from here: http://www.maatwebsite.nl/laravel-excel/docs .
You’ll also need some data in a database, or in an array or object.
So to start off, I’ll demonstrate how an entire table of values can be exported to a .xlsx or .csv file.
Beginning in the controller, you’ll need to import the Excel plugin by adding
use Maatwebsite\Excel\Facades\Excel; to the top of the file.
Then you’ll need a function for this process, and within that we can get down to exporting our table. In the example I’m demonstrating, I will be exporting a table of user data. You should be able to simply just replace the name of the table/variable.
The first step is to collect the data from the table:
$users = User::all();
Next, you’ll need to open the function that builds the spreadsheet, declaring the file name (in this case, users), referencing the Excel plugin, and passing in the $users array:
Excel::create('users', function ($excel) use ($users) {
Within this function, we build the spreadsheet by specifying the name of the sheet, and passing in the $users array:
$excel->sheet('sheet1', function ($sheet) use ($users) {
$sheet->fromArray($users);
});
This process with creating the sheet can be repeated within this function if you intend to create multiple sheets; although you would need to change the name of the sheet for each one you create.
Finally, you just close out the function with your method of exporting, which could be
})->download('xlsx'); , or
})->download('csv'); , for example. The use of
download would send the file to the device of the user accessing the route. This can be replaced with
->store('xlsx', $storagePath); if you want to store the file on the server in a specific location, just passing in your intended location in place of $storagePath. Another alternative to download and store is
export.
The file type (.xlsx, .csv, etc.) can be replaced with the file extension you want to download/store the file with, such as .xls, .xlsx, .csv or .pdf.
public function csv()
{
$users = User::all();
// Generate and return the spreadsheet
Excel::create('users', function ($excel) use ($users) {
// Build the spreadsheet, passing in the users array
$excel->sheet('sheet1', function ($sheet) use ($users) {
$sheet->fromArray($users);
});
})->download('xlsx');
}
When it comes to exporting an array or object to a spreadsheet file, the process is largely the same, only the variable you’re working with isn’t from the database, but declared as an array. Here is an example of the same code as above, but done by assigning data to an array and exporting that array instead of the user table:
public function csv()
{
$users = User::all();
foreach ($users as $user) {
$userData[] = [
'ID' => $user->id,
'Name' => $user->name,
'Address' => $user->address,
'Postcode' => $user->postcode,
];
}
// Generate and return the spreadsheet
Excel::create('users', function ($excel) use ($userData) {
// Build the spreadsheet, passing in the users array
$excel->sheet('sheet1', function ($sheet) use ($userData) {
$sheet->fromArray($userData);
});
})->download('csv');
}
This example is filling the array with values from the database but you can also manually input the values, or receive values from a form request.
By doing this using an array created in the function as opposed to just outputting all the data from the database, I can specify the headers in the spreadsheet, and only export the specific fields I want to be included in the spreadsheet. But outputting from an array is the way to go when specifying specific data in the function, or when working with data from a form request. Again, as with the previous example, you can change the final line of the function to specify the file type or change the method of outputting the spreadsheet file, be it pushing the download, storing it on the server, etc.