Create And Download Excel Using Closed XML In MVC5

Hello guys so this video is all about closed XML with dot net MVC 5 so I have

used Vizel Studio 17 to give you a quick demonstration of how to create an

Excel sheet using closed XML in MVC 5 in the following videos we will be

discussing much advanced features of closed XM and all but to start with

we'll take a quick look I have created just a sample project here

This project is dot basic template which comes with Visual Studio for MVC5

Within this, what you have to do is you have to go under tools, your NUCAT package manager

Within this package manager, we will look for closed XML here. Closed XML here

we will install closed XML within our project and once it is installed which I have

all you can see I have already installed we are good to go without programming so I

have already installed it within my system within my project now in terms of

coding what we have to do is this Excel workbook it comes under the namespace

closed XML dot Excel okay so So Excel Workbook is just like creating a simple Excel file

We will be mapping this code with a sample Excel so that we can understand what exactly we are doing and what output we can expect

So here once you write this line of code Excel Workbook is equal to new Excel Workbook That means you have open an Excel sheet Okay now what I doing is once you have open an actual sheet you need to add this thing this is your

worksheet which we some people call it as a workbook so you have to add a

worksheet into your complete actual sheet so that is being done here that we write

workbook dot worksheets dot add and then we have to give give name to our

worksheet which default comes with the name sheet one sheet two or something okay so

what I have given name here is pine tech so I will give the same name to this

worksheet here with p capital so till here what we have done is we have created

this worksheet now what we want to do is we want to write something right up here

okay so if I want to write something up right of here this each block is called

as sell in terms of x this is is an Excel language. So one who has done some trading on actual sheet, they will understand it

This is called his cell. So within the cell, we are naming row one and column one. If you go through

this, it's basically row number and column number. So row one and column one, that means this cell

we have to put some value into that place, that is that set value, and we have written

pie in actual sheet. We can write some, uh, what else we can write here

our demo excel sheet I think that's good enough now this is something which we have

already till here we have already created our our Excel sheet now we need to

save it so either you can save it directly onto your server that is easy busy

thing you can write worksheet dot save and everything or what you can do is you can download this thing because in MVC when you hit a query you need a response So basically what we want to do is we want to create an Excel sheet dynamically on the server and download it

So to download it, we need to save our Excel sheet into the memory stream

That's only way possible. And this memory stream is a class under system.io, that is dot net class

It has nothing to do with Excel sheet. using system.io. Okay, so we have used .net's memory stream

We have saved our Excel sheet, okay? This Excel sheet as memory stream and we have to seek position zero

This slide is very important. Without this line, your actual sheet will be corrupted and you will not be able to read any data

Basically, you are seeking position to zero. Somewhere you will see the seek syntax as well, which we will discuss later on

And then you are returning a file stream result of dot net

okay, where you are giving it the type, what exactly is the type of sheet

So this is an extension which comes for Excel X, Excel sheets

So, and here you are giving this entire Excel sheet some name

So what we will do here is we'll save our file with the name

save if you are saving it we'll save it somewhere on my desktop and my desktop only under hobby folder

within my whole hobby folder only there you go so we give this sheet a name as Axel 1 That is our first file which we are developing under this series of tutorial so i have saved this with excel 1 so i will give it the same name that is e xcel1 so this is the name of the

entire excel sheet so just to verify whatever i have said is right or wrong we'll just run it once we'll

what we have I have done is I have given this a simple Excel link here clicking

on this will take me to this method I'll put a breakpoint here so once I head it

simple Excel so this takes me here my workbook is create actually is created I

have I read my worksheet I have added some value then I have saved it into my memory

stream and then I say download it once I say download it you can see that

in my browser i already have a file downloaded as excel 1.xel so if i open it up

say you you can see the name is constant okay we cannot open to that means we have opened the last

one as well i'll close it i'll go to my download section

yes and i'll open it so yeah see our demo actual sheet this is not very formal it

formatted I mean this should have been formatted something like this that will learn later on

but for now is easy busy creating an actual sheet using closed xMr okay I hope you like this video

in the next tutorial we will focus on formatting this sheet properly adding image to it

and all other advanced features of closed xMrm thank you