Asp.Net Core 5 - Excel import and Export functionalities

hello friends thank you for watching this video i am muhammad and today we'll be implementing excel import and export in asp.net core 5. so what are we going to be covering today first we're going to be discussing the ingredients that we need and then we're going to be jumping directly into coding as always you will find the source code in the description down below please like share and subscribe if you like this video it will really help the channel so what are the ingredients we need basically two things we need visual studio code and the net 5 sdk you can find the links on the screen or in the description down below and the installation process is very straightforward now let's jump into coding the first thing that we're going to be doing is we're going to be checking the version of our dotnet core sdk so how are we going to be doing that so we're going to utilize the version for us to check that so inside our terminal we're going to type not version we can see that we have version 5.0.02 next let me navigate to the correct directory and from here i'm going to be creating our application and we're basically we're going to utilize an mvc application and we're going to be utilizing the new keyword so dot nut new mvc dash on and we're going to call it sample excel great this should take a few seconds perfect now that we have our application created let's navigate to it and let's open it in visual studio code okay great so the first thing that we're going to be doing is we're going to open our terminal and we're going to build the application to make sure everything is building as it should be so dotnet and it should take also a few seconds to be completed great the next thing that we're going to be doing is we're going to be running the application so that not run and then what we're going to be doing is we're going to go to our web browser and we're going to be visiting this url so localhost port 5000 and we're gonna go to the index page perfect and we can see now the application is running as it should be great the next step is we're gonna be utilizing a nuget package in order for us to accomplish this and this nuget package is called eplusplus.com so we're going to be utilizing the nut add package e plus dot core and we're gonna be utilizing this version number one point five point great this should take also a few seconds great now that we have the nuget package installed let us check that it has installed successfully by going to sample.xl.cspros oh sorry i have misspelled a plus plus so it put the l perfect now it has installed successfully great so once we have done that the next step for us is we need to start creating our model so inside the models folder we're going to be creating a new class and we're going to call it user and inside the user class we're going to have three properties so the first gonna be public string name gotcha now we're gonna have an email and i'm gonna have a mobile phone number great now that we have created our model now let's start by creating our controller so inside the controllers folder let's create a new class i will gonna call it users controller great now once we have created our class now let's import from the controller class inherit directly from here and let's fix the references great now let us create our first action and this action is basically going to be the index action so public i action result and we're going to call it index it's going to be a very simple action so we're going to return a view and basically here this action is only going to be showing us the list of users so so for us to mimic an actual uh table or basically an actual database operation what we're going to be doing is we're going to be creating a sample function we're going to call it private it's going to return a list of user and basically we're going to call this function or method get user list and we're going to be hard coding some of the user information just so we can make some kind of a database operation great now all we're going to be doing is we're going to make a var users equal the new list of user and we're going to be filling it up and let's return it directly great and here all we're going to be doing is a new user and the first user is going to be myself email equal muhammad.email.com the second one is going to be name equal muhammad and last one is phone which is going to be one one one one one okay this is the first user now let's create another two and the second one is gonna be let's say donald duck i will call it donald and maybe here we can mickey mouse and here we're gonna make it mickey and let's update the phone number two two two two and three three three three okay great now that we have a sample user list which is gonna be mimicking our database operation so let's add a comment here saying that it's gonna be making the database operation great now once we have done that uh let us use this list in the index and let's create a variable called var users and we're going to return this user list here and basically what we're going to be doing is we're going to returning this to the index perfect now inside our users folder let's create a new folder and we're going to call this folder users and inside this users folder what we're going to be doing is we're going to be creating a new file and this file is going to call it index.html.css html sorry great now that we have done this the first thing is we're going to be adding the model and basically it's going to be a list of sample excel i think it's models dot user yep and then after that basically we're going to be utilizing some bootstrap classes so the class row and here we're just going to put the two buttons one for the import one for the export so div class call dash md-6 and basically we're just going to create an href asp these actions are not created yet but we're going to be creating them so for now let's let's just add them export to excel and it's going to be asp dash controller and it's going to be users let's give it a class of btn btn dash primary and we'll give it a name of export to excel great now let's copy this instead of retyping it and here it's gonna be import from excel and we need to change the action name from export to import great now once we have done that let us just create a small table so the class row and then inside this we're going to create a div class called md 12 and then inside this we're going to create a table and we're going to make it a class table bootstrap classes we're going to be using and then we're going to put the t header and then we're going to put the h sorry the td uh okay tr and inside the tr we're gonna put that it is so dd email let's copy this name and phone great now let's create the body t body great now what we're going to be doing is we're going to also create a loop so at for each and this one's just going to loop through the model that we have and we're going to call it user and basically we need to create rows so tr and inside this they are going to create tds and basically we're gonna be at user.email and then.name.phone so let's copy these that name dot phone perfect so let's try it out and see what do we have so let's not run great now let's go to our web browser and let's refresh perfect now let's go to our users controllers and we can see here that we have the email name phone with every all of the information that we have now we're gonna add the export functionality great so how do we do that first let us stop the application and inside the user controllers what we're going to be doing is we're going to be creating a new method a new action so in here let's create a new action public i action result and we're going to call it export to excel let's just make sure that this is the same name that we have added in the index perfect so what we're going to be doing is we're going to be utilizing the same list of users that we had before so of our users equal get user list now we have the list of user which is basically here getting the information from our mimik db okay great now let's start by creating the excel file so start exporting to excel first we're going to be doing is we're going to be creating a stream and this inside the stream this is going to be the file that we're going to be downloading to the user's machine so stream equal new memory stream great once we have done that now we're going to be utilizing the package that we have installed let us fix this reference great now here what we're going to be doing is we're going to be using using and then var i'm going to call it xl package basically an excel package equal new excel package and we're going to pass the stream into it great knowledge after this fix the references perfect so now we have done that the next step for us is we're going to be defining a new worksheet inside excel so define a worksheet and this works it's going to be basically var worksheet equal excel package dot workbook dot worksheets dot add and we're gonna call this worksheet users because it's gonna be having users information inside the next step basically if we want to add some say coloring or some styling into it you don't really have to do that but i'm just showing it to you here so you know how you can add it so okay far i'm gonna call it named custom style equal let's say excel package dot workbook dot styles dot create named styles and we're gonna create it custom style perfect now let's add a command here we're gonna call it styling great now once we have done that now let's add some styles so custom style dot style dot font dot underline true just some random styling so we can just see it and then custom style dot style dot font dot color dot set color and we're gonna be utilizing the color dot uh for example say red let's fix the references great and after that now we need to specify from which row we're gonna be starting populating our excel sheet so we're gonna sit here first row so we're gonna put it uh make it as a var and start row without sorry block the end so it's gonna be start row equal to five great so once we have done that now we're just gonna do var row because this is the one we're gonna be sorry iterating through equal start row great now the next step after that is basically we're gonna be creating the worksheet as we have said and we're gonna be calling it sample so we're gonna have the first worksheet dot cells and we're going to be utilizing the a1 so basically the first cell that we have and we're going to give it inside this value sample so basically we're giving a sample sample user export for example let's call it like that so this is the first uh row when the first cell inside our excel sheet will get is going to have this value now what we're going to be doing is we're going to make this let's say this sheet or this row is a sorry this cell is going to be expanding through different rows so it's like merged across three or four uh cells so how do we do that so just it will look nice it's nothing other than looks right now so using var let's call it r equal worksheet dot cells and we're gonna make it expand between a1 and c1 great and then inside this basically we're gonna make it merge so equal to true we are merging all of these cells together and then we're just gonna add in some styling so r dot font sorry arnot style dot font dot set color let's see the dot color dot set color and we're going to give it for example color dot green for example just the green yeah and let's give it as a background color so it will look nice so r dot style dot fill dot background color dot set color correct and then we're going to give it color let's give it background color of lavender for example i don't know what's the color of lavender but it should be nice okay now we have done that now let us start building our table inside the excel sheet so worksheet dot cells and we're gonna using the a4 one says we said we're gonna start at row five so we're gonna be filling the table header on the fourth row so a4 dot value and this is gonna be basically name and then we're gonna be worksheet dot cells it's going to be v4 dot value it's going to be the email and the last one is going to be worksheet.cells on c4 dot value and this is gonna be the phone perfect so now uh once we have done that uh let's give it a background color so it will be easily separated but this is the uh this is the table header so we're gonna be worksheet dot cells and we're gonna say it's gonna be from a4 to c4 and we're gonna give it that style dot let's say by dot color sorry dot fill dot pattern i think it's the background color correct dot color and we're gonna use color dot uh let's give it a color yellow for example okay that should be fine now once we have done that all we need to do is uh we're gonna be just looping through the information that we have and filling our information so as we have said the row is gonna be on five and then we're gonna make four each var user in i think users correct and then basically here we're gonna put worksheet dot cells and here we're gonna make the row dynamic so it's going to be row because basically we're going to be looking through it and we're going to give it the first cell and we're going to put the value and it's going to be equal user.name great and then similar let's take this copy let's make another two times and then this is going to be the second cell and this is going to be the third cell and this is going to be the email and this is going to be the phone perfect and all we need to do is because we are going to be incrementing the rows so it's going to be row plus plus great so basically row plus plus if you don't equal to rho equal to rho plus 1. so we're just adding increment of one once we have done this the rest is going to be very simple all we need to do is just give the title for this worksheet so we're going to be xlpackage.workbook.properties.title we're gonna call it user list uh let's give it the author name if you want excel package.workbook.properties.author and it's gonna put my name mohammed and then all we need to do is put xl.sensorpackage.save so basically what we are doing here we are saving to the memory of our application right now and the last thing we're going to be doing is we're going to be setting the stream position to zero which means that we're gonna start downloading it from zero because as you know a stream uh is like a an array so we need to set it on the beginning of those array and then all we need to do is return file and inside this file is going to be the array which is going to be the stream that we have and here basically it's going to be application application yeah forward slash vnd dot open xml formats for maths dash office document and all of these information basically are just uh you can google them up what is the mime type of an excel sheet and you can find them so office document i think it's not a spreadsheet uh let's see so let's see the cheat i think this is it and let's put let's call it users dot xlsx okay great uh this should be it now let's save it and now let's build it and run it it should take a few seconds great great great now let's go back to our web browser and let's refresh and now let's click on export to excel or we have an error i think the color is not right so let's fix that it is on line 46 okay so it says here that background color the set color well let's do it um i think it's going to be from rgb uh let's give it any random color c5593 i think that should fix it let's just try stop the application and run it again okay now it's running let us refresh and download it's the same um let's see so r dot fill the background dot set color okay i think we need to add the pattern type okay let's add that r dot style dot fill dot pattern type equal i see open sorry it's going to be office i think office open xml dot style correct yeah dot excel fill style excel fill style this is it that's solid let's see if this would help okay let's refresh hmm okay so now it's a different one on line 53. great uh i think it's going to be the same thing so let's copy this and that's updated here so it's gonna be basically similar to this so somewhere like this okay let's stop this and let's run it again let's refresh okay we can see it's downloaded let's click on it again and we can see okay perfect we can see it's downloaded correctly and now if we open it we can see here let's make this bigger so you guys can see it let's make this bigger let's make it smaller and let's zoom in and as we can see here we have the sample user export with the title that we have specified before as well we have the name and the emails as well as the phone number perfect so once we have done that all what's left is for us to create an import functionality so just let us do a quick recap on what we have done here it should be also very simple so the first thing that we done let's stop the application so we can go through it again so the first thing here is we got the users basically you will need to get this information from your database then we have created the memory stream that we're going to be downloading to the user utilize the excel package from the package that we have installed and then basically we defined a worksheet defined some styles and then basically we started on row 5 then we created the main say title of the sheet if you want so called sample user export and then basically we created all of the headers for the table name email with phone with some styling and then we looped through the information that we have and lastly we have downloaded to the user perfect so the next step after this is we're going to be creating the input functionality and this is also going to be very easy so that's in order for us to do that let us create a new action we'll add it here and this action basically is going to be http gut and we're gonna call it public i action result and we're gonna call it that user upload very simple and all right now all it's gonna do is return the view because basically here it's gonna take a excel sheet and process it and show the results perfect so the next step is basically uh what we're gonna be doing is we're gonna be creating the post for it right now so http post why is it not happy oh didn't that return apologize so after that it's going to be public uh let's add a token validation so validate anterior oh so validate anti-foreign key now let's put i action resolved and then here we're going to call it patch user upload as well and it's going to take an i form if i can type form file we're going to call it batch user great so let's fix this reference and now basically what we're going to be doing is uh first we're going to check if the model that we're going to sending back it's valid so if model state that is valid we're gonna be continuing else we're gonna return to view that's the first check that we're gonna be doing second what we're gonna do is we're going to check if this actually has your information inside of it so if that user dot length is bigger than 0 and let's let's check if this is null first maybe it's not maybe because we're not going to add right now client side validation so let's check the validation from here and right now what we're going to be doing is because this is going to be a file so we need to transform it from a file to a stream so we're going to put far stream equal batch user batch user let's make it users so it has the right pronunciations but users because it's going to be a couple of users and basically we're gonna utilize the up and read stream so basically we're converting the iphone file into a stream so convert to a stream great the next step right now is we're gonna be defining a list of users we're gonna be storing the information from that excel file into this list so we're gonna call it users equal new list of user perfect now let's create a try and catch because basically we're gonna be doing some io so it's always better to do it now try and catch exception ex and okay something went wrong let's do console dot writeline ex dot message and now let's fix the references great now so inside the try what we're going to be doing is we're going to be utilizing the excel package so again using var package equal new excel package and this is going to be a stream great and then from that point forward we're going to do is we're going to check the worksheet that we have so far worksheet equal package dot workbook dot worksheets and then we're gonna get the first one and for this we're gonna be utilizing link so let us add this reference perfect and now we're going to get the numbers of row so we're going to call it row count equal worksheet dot dimension the throws perfect great once we have done that the next right now all we need to do is to loop through the results that we have so for row because we don't want to start from the first row because it's gonna contain the title so we need to start from the second row so we're gonna make it equal to two and then if we're gonna put row is less or equal to row count and then we're gonna make row plus plus perfect and now all we need to do is we need to check the information one by one so how do we do that try and then let's put the catch because basically also we're gonna we're doing io reading so it's always better to do it in a try catch an exception exception ex as well console.writeline and this is going to be ex dot message great now inside the tri-cut we're gonna put var name equal worksheet dot cells we're gonna basically utilizing the row and we're going to take from the first value which is going to be a dot value basically dot to string great and basically let's copy this i will make it for the email and for the phone so copy and this is going to be the email and here needs to be the second one the second cell and this needs to be the third cell and this is going to be the phone perfect so now once we have done that always all what we need to do is basically var create a new user object equal a new user and let's fill this information here so it's going to be email equal email name name equal name and lastly phone equal phone perfect the next step is after this user has been created we need to add it to the main user list that we have created here so all we need to do is users dot add and we're gonna add user perfect so once we have done that all we need to do right now which is we're gonna be utilizing the indexed view so we're gonna put return view and basically we're just gonna pass the user list because it already it's gonna take a list of users uh sorry view and here's gonna be basically index great i think this is it uh all what's left is we need to create the view for the uh upload now so let's take the name of this action so inside here let's create a new file html great and inside of this let us create a div class equal row and then after that let's create a another div class called md-12 and then basically let's create the form and this form is gonna go to asp.action to batch user upload and asp.controller to users and then we're going to put the method equal post yep now let's close this uh and we need to add one more thing which i almost forgot which is the ink type i think it's called yeah and we're gonna make it form data so basically this for this line here inc type multipart form data tells this form that when you're gonna upload some files so the next is gonna be very simple it's gonna be a div class form dash group we're just gonna make it a a label yeah label define we'll call it upload file and then all we need to do is add an input list so input and we're gonna call it i think we called it here about users yeah so let's put the id batch users and password the name batch users and let's give it a class we don't really have a class but let's call it uh form control yeah that should be fine and i think the last thing we need to add here is we need to give it a file a type equal file and now all we need to do is add the buttons that's gonna be responsible to push this so we're gonna put button of type submit and basically here that's uh the class equal with the end with the un-dash uh success and anything else let's give it the name upload user okay let's see how this look so it's building it's running let's refresh this and click on import import to excel um what did we call the action here batch users we didn't call it import so let's update this okay and now let's stop it and run it again okay perfect now let's come here and click on back refresh and click on this one okay great now we see that it's taking a file and we have the upload button all we need to do right now is prepare our excel sheet so let's create a new excel sheet okay so this is the actual excel sheet that we're going to be creating so here's going to be name and here's going to be email and lastly it's going to be phone uh let's make this [Music] blue and basically name let's put muhammad one i think yeah that should be fine yeah and here let's put muhammad one at email.com i think it will take it automatically if we're gonna drag it no didn't we can update it might be that's fine so this should be muhammad to muhammad one muhammad two muhammad three three and lastly is gonna be muhammad for and as for the phone i think this will take it automatically also no two two two three three three four four four okay great once we have done that let us save it uh save it on the mac and here i'll save it inside the document uh save it inside the desktop youtube save it here we'll call it user list and save great now all we need to do is let's go back to our web browser and let's choose this file so desktop youtube user list and just click on upload user and as we can see here the import has worked and we have the rest the information already showing to us in the right order so we have muhammad1234 name hamad1234 and the phone one one two two three three four four great so basically what we have done here let's do a quick recap on the upload functionality or the import what we have done here first is basically we created an empty form and then we decided that we're gonna pass a iphone file of code batch users first we check if the model is valid then we check the length it actually contain any information then we converted that file into a stream we created an empty list where we're gonna store all of the information and then basically we utilize the excel package in order for us to utilize the excel files so basically what we did is we converted the stream into an excel package we looked into the first worksheet if we have multiple worksheets here we need to change to the different worksheet that we want to import then we check the number of rows that we have once we have done that all we have done basically is we have looped through it and saved it into the list and then we have expo gave it back to the view great thanks very much for watching please in the comments down below ask any questions that you might have or any inquiries i'll be more than happy to help please like share and subscribe if you find this video helpful and if you made it this far please type your favorite food at the end of this video great so i will know that that you have watched till the end and i will like the comment have a great day and thanks a lot

Channel: Mohamad Lawand

Views: 4,782

Keywords: dotnet, c#, beginners, step by step, dotnet 5, .net 5, aspnetcore, aspnet, rest api, excel, import excel, export excel

Id: 1y9GdiEoYxY

Length: 41min 37sec (2497 seconds)

Published: Mon Apr 26 2021