Laravel Excel - Useful Laravel Packages

what's up guy is andre here and today we're going to take a look at how we can export laravel collections to excel this is going to be a part of my premium course so if you like this video definitely sign up at useful laravel packages calm alright let's take a look at nerville excel this package allows you to export your laravel collections to excel format now for me personally I always try to format my data so everything I need is displayed nicely in the browser nonetheless a lot of people are these my clients still prefer to have the option to view data or at least export their data in excel format so this package is a wrapper around another package called PHP spreadsheet which has even more functionality if you would like to happily customize your spreadsheet so for level excel its main functionality is just to be able to export your level collections it has some basic features for formatting which we will take a look at and it'll also take a look at some of the basics for PHP spreadsheet in a later example alright so let's get started let's go to the Quick Start sorry let's go to the installation I mean and let's go ahead and composer require so yeah I have a laravel app scaffolded out and the only thing I've done is setup the database let's go ahead and pose a require it okay that's done let me just add some data to the database so it's gonna do this use the factory just so we have some data to play with and export to excel it's going to make 50 records in the database using this Factory oh sorry I need to migrate let's try it again okay so now there are 50 records in the database okay so let's go to the quick start here and there is a CLI tool that this package exposes and you can give it the model you want to export so let's go ahead and do this so we'll just do that and it makes a new class called a user's export in-app exports users export and it's just going to export everything or whatever we give it here that's what's gonna export all right so let's see how to use this okay so all we have to do is call this and this should download a new Excel spreadsheet so I'm just gonna do everything in the rots file and make a new one here for let's call it download download actually now let's use my other snippet this one so download and then let me just paste that in just make sure to import Excel and I believe it's the façades one and that should do it let's go ahead and visit that route slash download the users export not found sorry support that okay let's try again and if you look down here it successfully downloaded it so let's open it up and yeah that's really small but I think you get the point it's basically the database dumped out to a laravel Excel spreadsheet the users table specifically okay so let's take a look at some of the other features let's go to exporting collections I guess I'll show you if you want to use a custom query here so if you only want certain headings you can just select them just like you would do in eloquent and say we only wanted the ID and the name where ID for some reason you only want the ID is greater than 25 so they should only have two columns with ID is greater than 25 ever so let's see if this works fresh sorry we have to call get sorry has to make a collection so make sure we call get okay there's a new one there and there you go there's only this set coming back as specified in our eloquent query okay let's take a look at storing on the disk so we go here so instead of Excel download we can just do Excel store and by default it will store it in whatever your file system is set to and you can change that if you like to use a cloud provider or s3 or whatever you want so let's just go back in here let's change this to store and this should make a new file in app storage so let's see if this works so I think you can't return it when you're trying to store it on this so let's just do that say done here see if that works okay let's check if it is in where's it storage yeah storage up so there it is right there cool now if you want another format all you have to do is change the extension here so if you want CSV just do that and we should have a user's CSV so there it is yes reformat it cool so in terms of performance for smaller exports what we've been doing is fine but for larger datasets we should use this from query concern so the only difference we have to do is implement from query so let me just grab this and it's go back to our our class here sorry let's do that and then it's changed from collection to from query and now we don't want it to be a collection and this should result in the same thing but it is optimized for performance sorry I need to change this function to query instead of collection yeah should be query there you go and yeah same result but if you have larger datasets definitely make use of this alright let's take a look at how to map rows so if you want your row to have other data besides what's coming back from the database then you can use this function so let's go ahead and grab this and go back to our user exports put that in there and let's grab this whole function and I'm going to put this back to just user all actually now I'll just leave it selecting every column so like that and I want to download so we can see what we're doing here so I'll change this back to download remove this and return this alright so back here let's paste that map function in before we do this let's just see what we have in our Excel spreadsheet so back here hey so as expected this is just everything dumped from our users table so this is the Creator that this is the creator that this is the updated at and this is the emailed verified at field which is new in there well 5.7 okay so just quick let's go ahead and go back into here and it's pasted this map function in so this is going to be a user obviously and this is going to be each column so for example instead of just the user name we can add whatever else we want in here so custom text and then append the user name and then for the second column we can just do the others email and see if this works I think we need to add with mapping it okay hey see what happens let's open this one up and there we go we have then we just zoom in for you guys we have these custom columns as specified in our mappings so you can do whatever you want with them alright what is next so similar to that we can also add headings so let's grab that which can be useful so let's just do with headings and then with headings and then scribe this and for us specifically we are just returning the name and the email so we can put that in their name email and let's see if this works and their their school okay so let's take a look at formatting columns if you want to format specific columns in an Excel format so we have to use this call them formats function and import all this necessary stuff so let's import this put that here and let's do with column formatting let's grab this so let's also return the created ads just so we have a date here actually yeah for date we have to do this so let's grab this and let's do this user and date is that and say created at here and then now we can paste this in and this would be a B it will be C so we just need to see as in date and see if this works number format and not found import that cool and there it is and there is our date format it in Excel format cool okay now let's take a look at multiple sheets if you want to have tabs across the bottom then we can go ahead and do that as well let's pull in this use mode with multiple sheets here and what else we have to implement it maybe we also need this exportable let's do that and if they use it okay what else so we need to make use of this sheets function and let's just do that here I'll put it right here and this is gonna iterate over how many worksheets you want to make so in this example it's just making 12 one for each month and it's making a new one called invoices per month sheet so let's go ahead and copy this invoices per month sheet it's a whole class and we'll change it a bit just put that in here let's call it users per month sheet PHP just at PHP here and the changes to users per month okay so so yeah you can pass parameters into a constructor if you need data to be passed around but I'm not going to do that just for demo purposes so it's gonna be the same sheet for each one I want to show you that you can create multiple sheets and I'm sure you can figure it out from there and this is the query that's gonna be run for each sheet so you just do a user query and we can just wear let's do the same thing ID greater than 25 and yeah this will be the title of the worksheet but since we're not passing any parameter then I'll just leave it as month okay so let's see if that works users per month of sheet not found import that sorry I named this incorrectly should be sheet and now I should be able to import it okay try it again user enough found here user okay let's see if we have multiple sheets so we don't I think it's because so I couldn't get these sheets working and the reason for that it was because I need to change this to excel format I guess a CSV doesn't support multiple sheets if I do that there we go and now I have multiple sheets and Excel automatically changed the sheet name because it can't be the same so now let's take a look at how we can format some of this excel spreadsheet as you can see I added points to the database and we'll take a look at that in a second but the first thing I want to do is say for example we just want to bold this so how do we do that to do that we have to make use of events and for this particular case we just want to hook into the after sheet event and then bold it after it's exported so let's go ahead and do that so I'm gonna grab this register events method and it's also implement this with events let's do that let's go to our code let's implement with events and import that and it's based in that method put it right here and the one I'm interested in is after sheet so let's change these two and import this okay and here is where I want to bold that top row here where the headings are so this is where PHP spreadsheet comes into play and this is a huge library so if you're trying to do something just go through the documentation here but I just want to bold it so I'm gonna make a new variable it's an array called style array and then I'm gonna say the font it's another array and then I'm gonna say bold true okay sorry for this dollar I want this to be outside okay now in here we just have to do event sheet so get the current sheet get style and we want to give it so in our case it is a 1 2 G 1 so a 1 2 G 1 and we can do apply from array in this style array and that should pull that top row so we do this again I made a typo a play from array fly from array style array we have to pass that in such to use style array okay and there you go it is bolded now cool ok so say for example you wanted to have this sum of this entire column down here and we can totally do that as well so let's go into our code and it's just add another one here so an event sheet this time we're gonna call a set cell value method then let's give it this cell we want to set in this case you should do it dynamically as in get the number of rows returned if there's headings add one so you know it goes here but in our case I'm just gonna hard-coded something but II 27 ye 27 and we can give it what we want so I'm sorry that should be closed and we want it to be equal sum and in our case it's ii ii ii ii 50s e 2 to e 26 again this should be dynamic but it's gonna hard coded for demo sake and let's see if that works okay and there's this sum there okay so let me show you a few more things say you wanted the data to start later like you wanted some stuff above it you can just do you can listen for the before sheet event and just populate it with whatever data you want so let's copy that and paste that in but now it's gonna be before sheet and make sure to import that and now I can do event sheet set sell value say a1 what something something in the first cell will just say hello and then and now the exported data should be pushed down on one row cool and you can even do stuff like this so I'm just gonna paste it in to save some time but you can insert rows before so we're gonna insert two rows before row 7 and this is going to insert a column before two column three for a so let's see how that looks actually that's not gonna work you have to do that in the after sheet event so yeah some right here say let me skip with this and put this just to show you that you can insert rows or columns so he does anymore and format it however you like so there you go it's push it over that way and it's add some rows there cool so there you have it guys we managed to take a look at how we can export that layer bail collections as Excel spreadsheets hopefully you can start using it in in your applications so if you like this please sign up at a useful laravel package comm where I'm working on my premium course and looking at other packages like this please like comment and subscribe if you haven't already done so thanks for watching guys see you in the next one okay thanks and bye [Music]

Channel: Andre Madarang

Views: 58,192

Keywords: laravel excel, excel laravel, laravel excel spreadsheet, laravel excel example, laravel excel maatwebsite, laravel excel 3.0, laravel excel 3.0 example, excel export laravel, laravel excel export, useful laravel packages, andre madarang, drehimself, laravel package excel, php spreadsheet, laravel php spreadsheet

Id: 2FH72e6OjeQ

Length: 24min 43sec (1483 seconds)

Published: Mon Sep 17 2018