Build a Livewire CSV Importer/Exporter
This episode is for members only

Sign up to access "Build a Livewire CSV Importer/Exporter" right now.

Get started
Already a member? Sign in to continue
Playing
08. Exporting with Laravel Excel

Episodes

0%
Your progress
  • Total: 2h 18m
  • Played: 0m
  • Remaining: 2h 18m
Join or sign in to track your progress

Transcript

00:00
We're going to use the Laravel Excel package to handle our exports and our imports. Let's go ahead and get this installed and then I'm going to show you how we can configure this to export the data that we've selected. And then as we go through the course, we're going to set up queuing and just make this a little bit more efficient,
00:16
add some more chain jobs to alert the user, all that good stuff. So if we head over to the documentation under the installation section, of course, we can go ahead and just pull this in. Let's go over and pull this into our project and I'll guide you through the rest of the steps that we need to take. OK, if we head back over to the browser, it looks like pretty much everything we need to do has been done.
00:39
OK, so the first thing that we need here is to define out an exporter so we can specifically generate these with the package now. So we can say PHP artisan make export and we can go ahead and choose the model and the export suffix. So you can call these anything you want, doesn't really matter. But typically we would do this. So let's go ahead and do this, pass in the model flag and pass in the model that we're actually working with, which is a transaction.
01:08
And that's gone ahead and created that over an app and exports like so. And you can, of course, again, put these anywhere you want. OK, let's just take a look at the base of this. If you've never worked with this package before and see what this looks like. So the first thing you'll notice that this implement from collection.
01:26
So essentially what we are saying here is that we are going to use the transaction collection and return all of these items as part of a collection. Now, what we don't want to do is use a collection. Why is that? Well, because if you think about it over in our export model, when we get to the point where we do actually export issues in the exporter, we've just created, we need to take into account the fact that we need a query to be built up only selecting the IDs that we have chosen.
01:57
Or if we don't have any IDs, then we're going to assume that we're selecting everything. So the first thing that we're going to change about this exporter is we want this to be from a query instead. Now, what this will make us do is add in the query method stuff. We can get rid of the collection one now because we're not working with a collection.
02:18
And now we just need to implement the query method. Now here we can just do this transaction query and that's it. So what that will do is behind the scenes when we do actually export this, it will do something like get or chunk depending on how we are doing this to go ahead and grab all these records and export them. So let's think about only exporting the records we've chosen because to test this, we don't really want to export 100,000 records.
02:46
It's going to be a little bit too slow and we want to see this instantly before we get to queuing. So somewhere in here, I know that I'm going to want to pass in the IDs to export. So let's go ahead and create our constructor here and let's create a protected array with a bunch of IDs that we want to export and that's it. So let's also put in the exportable trait into here that just changes the way that we can actually use this and I'll show you that in a moment.
03:17
And then under the query, we know that we're going to get a bunch of IDs in, but we could potentially have no IDs. So what we can do is we can be a bit clever and say something like, well, when we have a count under this ID. So when we actually have some IDs filled in this array, then we want to run this closure and continue to build onto the query. And what's that going to be? Well, we just want to find the IDs that we want to export.
03:42
So we just say query, find, and then we just want to pass in this ID. That's it. Now, otherwise, this closure will not be called if we don't have any IDs in there. So if IDs is an empty array, transaction query will return all 100,000 records. That is pretty much what we need to do. We can now start to look at exporting this data and then we'll start to tidy up by selecting the specific data that we actually do want to export.
04:10
So to actually export this, then let's see what we need to do. So we're going to say Excel. So we're going to use the facade here and we're going to say store and we're going to choose where we want to store this. So let's just take a look here. The export object is the first argument. So that is a new transaction export from under our exports.
04:33
And remember, that takes in the IDs that we want to export as an argument. The second argument to this, let's just take a look at this, the store method. And this is the file path, the disk and the writer type. So for the file path, this is going to be export and file.
04:53
So that's the file name that we've given it. We're going to store this on the local file system. We'll talk about that a little bit later because we're going to want to make sure that this is securely tucked away privately, but we can still download it. And then we're going to pull in the namespace for the just the CSV export type. So this is Excel and it's from just Excel here and it's CSV.
05:18
So that's the format that we want to export this as. Don't worry too much about this because, of course, we're going to change this over to queuing a little bit later. Let's just see if this works, though. So this should get stored over in our storage section on the app. So I'm going to go over and I'm going to select 10 records to export.
05:38
Let's try this out and let's head back over to our editor. And there we go. Great. So that has successfully exported the 10 records that we chose. If I'd not chosen any, this should go ahead and export 100,000. We won't try that out just yet. We'll wait till we get to queuing to try that because it's just going to take a huge amount of time.
06:02
But now we have actually successfully exported out the transactions that we need, which is great. Now, the good thing is this is inside of this private directory which cannot be accessed by anyone. So although it's within our file system, we can limit this to only allow the user who should be able to download this to actually download it. So that is pretty much how we export. But let's just open this up and take a look at what we have here.
06:30
So you can see that we've got quite a few columns in here. We could switch this over to data just to make it a little bit clearer. So we've got column one, which is the actual ID from the database. Do we really need that? Probably not, because we know that column two, that's our transaction ID. That's the unique thing. Column three is the descriptor. Column four doesn't look right.
06:52
It's basically given us a JavaScript object of the amount, the currency, the formatted value from that package. Then we've got the date, but then we've also got the create that date and update that date. So we don't need all of this data. So let's go ahead and get rid of this transaction. And let's go over to our transaction export and customize the data that we're actually exporting.
07:15
To do this, we're going to go ahead and implement, first of all, the with mapping interface and then the with headings interface. So this package is very much interface driven when we go ahead and create exporters or importers. So let's go ahead and add the method stubs for both of these. With headings requires that you have a headings method. With mapping requires that we have a map method. So both of these can pretty much be used in conjunction.
07:45
So we already know what the headings are going to be. Let's go ahead and return an array with all of the headings that we want to see. So we've got a transaction ID that will create a header for that. We've got a descriptor. We have got an amount and we've got a date that should be in the right order, but we'll check that in a second. In terms of mapping this data, that's pretty much straightforward.
08:07
We just want to return the data from this row. Now, this row will be a transaction model. So all we need to do here is just say row. In fact, I'm going to let's call that transaction and let's swap that over to transaction just so it's a little bit clearer here. So we'll say transaction and the first one is going to be the transaction ID. The second one is going to be the descriptor. The third one is going to be the amount and the fourth one is going to be the date.
08:39
Now, just by doing this, that should format that properly for us now. So we get the dollar and cent amount. Let's check it out, though, and let's go ahead and export this again. OK, let's go and just choose 10 of these. Click export selected, export them out, head back over and we'll open this up. And there we go. So we've got headers. So the first row here is the headers and then we have the transaction ID, the descriptor, the formatted amount,
09:07
which is important, and the actual date of the transaction. We don't have anything like the create that date or update that date from our database and we don't have the ID from our database. So there we go. We have a nice export formatted as well now. OK, so the last thing that I want to do is I don't want this transaction export in here.
09:28
I want this to be reusable. So remember, this export model should be able to deal with any type of model. Now, so far, that's the case because we knew up the model here. We use the model table name to create this out regardless of which model we're using. But this is a specific transaction export. So what do we do here?
09:50
Well, what I would do is over on the transaction model, I would have some sort of interface. So let's go over to models and transaction and let's create an interface for here. That's something like exportable. So then we can just apply that to any of the models going forward in our application. We don't have that at the moment. So let's go ahead and create it out.
10:15
So we could do that directly in the exports directory. Let's do that. Why not? And let's say exportable. Let's make this an interface. And what do we want to see here? Well, we want an exporter method which will return for this specific model, the exporter that we want to see in this case will be the transaction export.
10:36
So let's say exporter and we'll just leave that as it is for now. So now over on our model, we can implement the exporter interface or exportable interface. Go ahead and add the exporter method stub and that method stub is going to return to us a new instance of the transaction export that we created earlier. Now, bear in mind over in our model, we need to pass the IDs through to here.
11:03
So what we can actually do is we can always accept in here a spread out list of arguments that we then pass through into the transaction export. So over on here, let's make sure the method stub matches and then we're going to spread out and pass through any arguments directly into there. So hopefully that makes sense. You don't need to create an interface for this, but it just means that now for any other models that you potentially want to export, you can just apply that. OK, so what do we need to do now? Well, we just need to switch over the transaction export here and that needs to be model.
11:41
So the model that we created up here earlier and exporter and then we pass through the IDs that goes through to here and then the IDs get passed through to the transaction export and we're good to go. So it just means that we can just pass any arguments through to any of the exporters now. OK, let's try this out and see if it still works. So let's select two pages, maybe just to test this. Let's export these 20 records. Go over. That record is or that export has been created and we should see that over here now.
12:19
And there we go. We've got 20 records. It says 21 because, of course, the first is the header. There we go. Great. So that is now working and we have kept this so the export model can be used for any model. OK, this is great. But for anything, when we get into like the thousands, hundreds of thousands, millions of records, this is just going to be way too slow. What we need for this is a queue. Let's take a look in the next couple of episodes about how to queue the process of exporting.

Episode summary

In this episode, we dig into how to export data from your Laravel app using the popular Laravel Excel package. First, we get the package installed and set up, making sure you know where to find everything in the documentation. Together, we set up a new export class for transactions using Artisan commands, and we talk through how you can customize what gets exported — you’ll learn why it’s better to use a query over eager-loading a full collection if you might be exporting lots of records at once.

We build out a flexible exporter that lets you choose specific transaction IDs to export, and we leave the door open to exporting all records if no specific ones are chosen. Then we actually export some data, save the file locally (private by default), and review how the data looks in the file.

Next, we dive into customizing the export to only include the columns we want by using interfaces like WithMapping and WithHeadings. That helps make your reports much cleaner and easier to use. We also set things up to make the exporter reusable for other models, using a quick interface so you can easily adapt the pattern anywhere in your app.

By the end, we have a working, flexible export setup that’s ready for thousands (or even millions) of records—but as you'll see, for massive exports, you’ll definitely want to queue the process, which we’ll cover in upcoming episodes. Stay tuned!

Episode discussion

No comments, yet. Be the first!