#1: Laravel Excel Export data in Excel, CSV & multiple sheet export

[Music] hello developers welcome back to the channel I have gotten lots of requests on this laravel XL package so here we are I'm going to create two videos on this laravel XL package in the first video I will focus on how we can export data into the excel file from the laravel application and second video will be on how we can import Excel data before moving ahead our one request if you like tutorials like this please subscribe to the channel and press well icon so when I upload new videos you get notified your support motivates me to make more tutorial videos now without wasting more time let's charge today in this lesson we will see how we can export data into the excel file so let's start so first let's go to the documentation and the installation step and here is the requirement of this package and make sure you fulfill these requirements and this package built on top of PHP spreadsheet package this one now let's install this package into the laravel application I have already installed the fresh laravel 7 application and in this app I will run composer command it's installed next now we don't need these step because we are on da Revell seven and laravel seven has Auto discovery feature so now we will see how we can export data into the excel file this laravel excel package provide us new artisan make export command in this command we specify the export class and then we specify the optional model name which we are going to export and this command going to generate this boilerplate code for us and it is going to export all the data of this user model let's try this out so in the terminal here I will run PHP artisan make export and in the fresh installation of laravel 7ba already have default user stable so I am going to export that users data into the excel file so here I'll create users export and we will pass model option and here we'll pass user model let's go to the editor in the app exports users x4 dot PHP and here we have a two boilerplate code for us this users export class implements from collection interface and this interface required this collection method and in this collection method we have to return collection of data and in our case we have return the all users collection next let's go to the web dot PHP and here I will create a new route users export and users export controller at x4 now let's create this controller so in the terminal here I'll run PHP artisan make controller users x4 controller sgtp controllers here we have users export controller next I'll create method export now in this method I'll say return Excel download and in this download method I will pass this users export class so here I'll say new users export and then we will pass the our excel file name let's say that is users dot xlsx extension make sure to import this class right here and here we will import this users x4 class as well now let's go to the terminal and on PHP artisan serve and next let's open this in the browser users export and here we go it it has downloaded this users excel file and let's open this and here we have all the users from the database and how easy it is here to download this excel file we have used this excel facade this laravel exit package also provide us exportable trade let's try this so in the users export class here i will say use exportable trade and now here in this controller we don't need these facades so here we can say new user export and then we can call download method and in this download method we will pass the excel file name now let's go to browser let's hit enter and here we go it has downloaded the excel file and it works exactly the same we can even make it more shorter here in this users export class it uses this exportable trade along with that here I will also add interface response able next here I will add private property dollar file name and that is users dot xlsx file name that's all we have need now in this controller and here we will just return new users export clause and it will work let's see it entered and here we go it has John already excel file works exactly same it looks nice and clean but after few days if we come back and see this code we may get confused here this code does not explain but it is doing so instead of this but I like I resolve this Excel facade in this export function like this dollar Excel and then here I'll say dollar Excel download new users export and then the excel file name and let's go to browser hit enter here this excel Cola should not be the facade class instead it should be this class now let's go browser and hit enter and it works it has downloaded the excel file here we go now here we can easily understand that we are downloading the excel file using this users export class some people do not like to add dependency injection in the method so instead of this you can also inject this dependency in the constructor method so here I'll add method construct method and in this method I'll pass Excel dependency and next here I'll say dollar this Excel is equal to dollar Excel this Excel right here and here I will define private dollar X next in this export function we will say dollar this Excel download user export class and let's go browser hit enter and it works the same here we are downloading the data in this xlsx format you can also download it with other formats as well for example XLS or CSV or other available formats you will see this in the documentation first let's try this in the browser so if we hit enter we get the data in the CSV format by default the export format is determined by the file extension but if you want to define it explicitly then you can pass it in the next parameter for example for this CSV files here we can pass Excel CSV let's go to browser hit enter and here it is downloaded in the CSV format if we go to the documentation here in the export formats and here are the formats this package supports ODS XLS HTML it can also download it in the PDF file for exporting PDF file you have to also install the respective PDF library for example for this Dom PDF we need to also install Dom PDF library let's try this so here so in the terminal let's close this and here I'll run composer require dome PDF slash dorm PDF now it is installed next here we just need to specify use a PDF and Dom PDF now let's go to browser and here let's hit enter I forgot to run our sensor command PHP artisan serve and a flash and here we go it has downloaded the PDF file and here we have all the user data here in this collection method we are using the laravel eloquent and this user all method returns the collection class let's see and let's go to browser and refresh you will see it is the illuminate database eloquent collection class now what if we have to export the data that is not using the eloquent for example we are collecting the data from the API and that is in the array format so here it will not work let's see we have data in Airy format and that returns and this in this area we have region email is reshat example.com now if we try to export this data it will give us error so how we can export this for that one solution is that we convert this data into the collection for that we can use new collection class ot laravel eloquent this one and in this collection class we pass the array data and now if we go browser and hit enter and it has export the data and it is downloaded the PDF file no no I don't want that I went download it in Excel format and here we are not using these responsible so we can delete these and pile name doesn't require so now let's move these and now hit enter and it has downloaded the excel file instead of this new collection class you prefer to use plain array then we can use from array interface let's try this so here instead of this from collection I will use Rome array now this Chrome array required array method next we don't need this new collection now we go to browser and hit enter it has given us error that is because here we have to declare the return type now I'm go to browser hit enter one more time and it has downloaded the excel file from the given array instead of this from array we can also export from blade view file let's see so here I'll say from view and next we need method view any here we need to define the data and type view this one eliminate contract view view class and next here we need to return view and here we pass the view file let's say exports dot users in the next parameter I'll pass users user class all next we will create this blade file so in the resources views here I'll create new file exports users dot blade dot PHP and here I'll create tables we had this that has TR name and the say email next year passed T body here we will call for each loop and that is users dollar user next we call TR TD let's print user name and user email now let's go browser hit enter and it has downloaded a new user excel file and here we get the all users name and email address let's go back to from collection so here is the collection method and days then it will return all users now let's go browser it entered and here we have the old data but here you can see that to view the name we have to resize these cell manually like this so instead of this what we can do here we will implement should auto size interface now let's go browser hit one more time and this time you will see that all the cell size are Auto resized according to the text size in the cell it is the user ID this is the user name it's the email address and this column is the email verified at date and this is the created date and this is the updated date this package has automatically downloaded thee all the visible columns in the user table in the user table we also have other columns like password and remember token that is not downloaded here why is that here we go to the user model here you can see the password and remember token is in the hidden property so if we have any column which we do not want to export then we can add this in the hidden property for example in this excel file we do not want to download email verified add column and we can add that email verified column right here in this hidden property but it is not the ideal solution because we could have any column which we do not want to export but we also do not want to include that into this hidden property so how we can do that for that this package provide us mapping data feature we will implement this with mapping interface and this mapping interface required this map method and in this map method we will specify the column name which we want to add let's try this so in the user export here I will implement with mapping next we will need to add method map and then we need to declare the return type carry and this method I will return array this map method will accept the user and here I want to map user ID and user email next role a user created add now go to browser hit enter I forgot to import this class no the browser hit enter now it has downloaded the user file and now we have user ID email id and created at column now here we are downloading the data from the user model only now let's say we have requirement to download the user data along with its relationship model for example let's say we have a address model and this address model associate with this user model along with this user model we have also need to export address of this user as well so how we can do that so first let's create the user and address relationship so first let's close this and here I'll create PHP artisan make model address and I will create migration address factory as well the editor let's go to database migrations here we have address and I will add table let's say string and let's say column name is country now this address table can have other column as well let's add only country next in the factory address Factory and here I'll add country and here I'll say fakir country I forget to add user ID column in the address table that is required to create relationship between user and address so dollar table big integer user ID and in the factory Erol add user ID function and next here I'll return a tree user class and create its ID now in the terminal less than the migration HP artisan migrate fresh now let's create some dummy content so in the PHP artisan tinker fairy app address class create and I will create let's say 30 address now it's created and on the PHP artisan sir next in the database refresh and we have user data and in this view the data we have 30 users next in the address table we have 30 address for each user let's close these and in the users table this user have single address so we all create method address and it will return dollar this has one address class now to verify it in the users export let's say donor user is equal to user with address and let's watch the first user and next I will dump that user here now let's go to browser refresh and we get the you the model and in the relationship we have the user address we have a relationship between using an address next instead of this user all I will return this user with addresses get and remove this next year in this map method here I will add another user address country so here what we did we have a user address relationship and each user has one address and next in this collection method we fetch the all user with his address next in the map method we added the user ID user email address and user address country the next user created ad now let's go browser and enter now we should get the country as well we have user ID email address country and created at now here I also want to add headings for these columns like ID email country and crater add for that in the documentation we have here it is here we will implement this with headings interface and then we will add this headings method let's try this here let's add with headings annex here I will add method headings and nuclear the return type array here we will return array and in this array we will specify these heading names for example first is the ID so here I'll pass hash for that and next is the email address so here so here we'll pass email next is the country so here I'll pass country lost chatting is the created item now let's go to browser and hit enter forgot to import this interface hit enter and here we have new excel file and this time it has the heading we can also change its style for example we can make it bold or we can change its background color or some other selling less series for that in the documentation extending and this extending has events for events we use this with events interface and then we need to add this register events method we have these events I mostly use this after shade event let's try this so near editor let's format move them into the next line and next here a lad with events and make sure to import this class next we need to add this register events method so we are allowed method registered events next let's declare the return type array and in this method I'll return array now in this array we will use after event going to closure function and this function is also going to accept after sheet dollar event now here in this after sheet we will change the style of this heading so for that here I'll say dollar event and then she it and then we will get this style and in this style we will pass the cell range in our case that cell range is a 1 to D 1 so here I'll pass a 1 to D 1 and next year we'll apply style apply chrome era method and in this apply from array method we will pass dial in area for example we want to make phoned bold do and now let's go to the browser hit enter now our heading text is bold we can add more style to it for example this border style they said this also right here and go to browser enter and here it is this is the new style added here if you want to read more about sale in the documentation then you have to read about more solution in the official PHP spreadsheet package and that is listed here here we will search style this option style and here in this page you can see more style options you can try this out well now let's remove this now here in our example our user data is very small now what if you have very large data so in that case if you try to download it will use more server resources and it will affect the your server performance for that this package providers from query interface in this from Kari we prepared the query for export and behind the scene this query ice-cubes it in chunks so to try this we have to implement this from query here we implement non query let's import this and next we need this query method so here we don't need this collection method instead we need is query method let's remove this and it will return query and now if we go to browser hit enter here we got added that is because right here we have used these from collection because we are using from Gary's be no longer nail this so let's remove this and hit enter again undefined method builder query error is here it should be like it should be like this user query and then with address now try this one more time and it works for now we will not see any difference because we have very small data but for the large data it will execute the query in chunks so it is very useful to use it for better performance for the large data now let's say you also want to display your logo in this export for that it provides us drawings interface this one so let's try this we will implement this with ranks interface and let's import this next we will use this drawings method so let's copy it and paste it right here now here if you need to import this class new drawing make sure to import this PHP office PHP spreadsheet worksheet drawing class next we you can set any your logo name your logo description and here is the logo path I have already a logo in the public directory here is the laravel logo so what I am going to do I am going to specify that right here public path laravel logo dot PNG next we need to specify the height of this logo let's leave 890 and then we need to specify coordinates where this logo should start right now it is v3 let's try and see the result so go to browser hit enter and now right here see the laravel logo here the logo is above the text and we don't want that so here what I will do I will start this logo from the v2 and the content of this excel should start from the a8 from here so let's see for that for the custom start here we need to import the interface with custom start cell and next we will create method start cell and it should return string and in this method here I'll return e8 and our logo should start from b2 now let's try this hit enter s open this and now you can see our logo is from b2 and here our text start from the a8 now our heading is no longer ball because in the after sheet we have specified the cell a1 and d1 here we also need to modify it to a a to d8 hit enter let's open this and here we go now it is bold right now we are only exporting one worksheet but if we need to export more worksheet so let's see this so in the documentation here is the multi sheet option so here we have a new class that implements the with multiple sheets interface and then it has this Shields method and in this sheet we can add multiple sheets so let's try this so in terminal here I'll create here I'll create new export so PHP artisan make export user multi-sheet export next in the editor here we have new user multi-sheet export and now here i will implement with the multi sheets and now we don't need this collection method and here we need method shades and it's it return type is array let's say if we have a dollar shades that is input array and here we will return dollar sheets so here what we will do i will create multiple sheets for every month and each month sheet has all the users created on that particular month for example in the january sheet i will include all the user that is created in the January month so let's say this so here I'll say let's say we have a method construct and it is going to accept int here and then here we have dollar this here is equal to dollar ear and hear a loud cry wait dollar here next here in this shape method I'll say for loop dollar month is equal to one and dollar month less than equal to 12 and older month plus plus and natural say sheets area is equal to new users export and in this export I will pass dollar this year and dollar month now in this users export plus here I'll create construct method and it is going to accept int dollar here and integer dollar month and next dollar this here is equal to dollar here and dollar this month is to dollar month and here I will define that properties private donor here and private donor month next in this query we will say which users where here is created at Dollar this here and where month is created at Dollar piece next in the user exports controller here I'll say download user multi-sheet export and here I'll pass user for particularly at let's say 2020 so let's review what we are doing here here we have created the user multi-sheet export and it has a construct method that is going to accept here which we have passed right here 2020 that is going to be here and this here is going to be 2020 and then in the sheet method we iterate the ol months and then we fetch the sheets user export and here we pass the this year and then month and finally we return that here next in the users export we accepted the year and month and then we created the query to fetch only users that is created in the given year and given month so before try this out we should have some dummy users according to the different months so let's modify the factory so in the factory user factory here I will add new column created add let's say carbon now and subtract month specified and month to subtract from one to twelve now in the terminal all done PHP artisan migrate fresh PHP artisan tinker let's create thousand users now we have some recourse next let's run the PHP artisan third and in the browser let's try this if it works oops here is the other edge it should be double underscore now let's go browser it one more time and it has downloaded the excel file and here we can see multiple sheets it has added in number to each sheet because every sheet cannot have same name if you want you can add custom name to this sheet as well so for that in this user export here we will implement new interface with title and then here we'll create method title and it should have written type string next let's return D now here in this title method we have to figure out the month we have this month which is the month number so to calculate the month here I'll use the time and then we will say create from format and helpers note m and dollar this month and after that we will format this date with capital F that will return the month now let's go to browser hit enter and let's open this downloaded excel sheet and we have name for every sheet January for a March April and so on now sometime you may not want to download this excel sheet instead you want to store it into your local server disk so for that in the controller right here instead of this download we can use store and this store method will download this generated excel sheet into the default file system specified in this laravel app so if we go to config file system the default file system is local so it will download into the local system let's see now in the application if you see storage app here is the new users excel sheet now if you don't want to store that into your local system in the third parameter you can specify the available filesystem you have for example in the file system let's say you have this s3 you can specify as 3 right here you just have to configure this s3 filesystem and it will just work this is all in this today's lesson and I hope you enjoyed this tutorial if you liked the video hit the like button share this video and don't forget to subscribe us see you in the next lesson [Music]

Channel: QiroLab

Views: 40,416

Keywords: laravel excel store csv, laravel export to pdf, laravel excel export large data, excel spreadsheet laravel, laravel excel export relationship, laravel excel export from array, how to download excel in laravel, laravel excel export multiple sheets example, phpspreadsheet laravel, laravel excel chunk export, laravel create csv file from array, laravel export csv from database, export large data to csv in laravel, laravel api export excel, qirolab

Id: VUet2wq22-o

Length: 41min 32sec (2492 seconds)

Published: Mon Jun 08 2020