Export gridview to excel in asp.net - Part 57

welcome to prism technologies I am venket this is part 57 of asp.net grid-view tutorial in this video we'll discuss about exporting grid-view data to Microsoft Excel and ferd we'll be using this table TBL employee for this demo now I want to retrieve this data from this table and then display that within the grid view control and then underneath the grid view control I want to have two buttons export to excel export to word so when I click this button export to excel then I want to export the data that is present in this grid view into an excel worksheet similarly when I click you know another button export to word which we don't have at the moment I want to export this data from the grid we control to a Microsoft Word document obviously to achieve this the first thing to do is to write the ad or dotnet code to retrieve data from this table and then display that within the grid view control so let's flip to visual studio let's drag and drop a grid view control onto this web form let's auto format this lets choose brown sugar screen now let's drag and drop a button control and let's the text on the button control to export to excel so go to the properties of the button control and change the text to export to excel now let's double click this button control to generate the event handler within the page load event now we need to write ad or dotnet code to retrieve data from the stable and then display that within the grid view control and that we discussed about ad or dotnet in a very great detail in radio dotnet video series so please watch those videos first to speed things up i have this code already implemented so let me copy and paste that here so within the page load event if not is post back meaning if this is the initial get request of the waveform then use the configuration manager class read the connection string from web config file and store that connection string in this variable and using that connection string we are building a signal connection object look at the sequel command select star from TBL employee we are passing the sequel command as a parameter to this equal data adapter object we are going to execute this command using this connection object and then look at this we are creating it a set object and then we are invoking the film method of the data adapter object to fill the data you know into this data set after we execute this command then we are setting the data set as the data source for the grid view control invoking data bind method okay so if we run this application at the moment you know the data will be retrieved from TBL employee table and then displayed in the grid view control now when we click this button export to excel we want to export the data that is present within the grid view control into an excel worksheet okay so anytime we want to send something to the client now here we want to send the grid view data to the client in in an excel sheet so we need to you know do that using the response object for example let's say I want to write cookie information on to a client computer which object do I use I use the response object okay similarly here we want to send at the excel I mean the grid view data in an Excel format to the client so I have to use the response object for that so let's flip to visual studio so within the button click here and I'm going to use the response object the first thing that I need to do is call the clear or clear content method you know the clear content method is going to clear any content from the response buffers no if there is any other content within the response buffer that will be cleared when you invoke this method and the next thing that I want to do is I want to append some information to the response header that's going from the server to the client okay so to the HTTP response header I want to add some information so what is that information going to be now I want to tell you know - the response object the data that I am going to send you know that has to be opened up you know using Microsoft Excel and you know I have to specify the name of the excel file now to specify the name of the excel file now I'm using a response header called content disposition so content - disposition so I'm using this HTTP response header to specify the name of the you know excel file that I'm going to send to the client and to do that I have to say it's an attachment semicolon and then file name is equal to whatever name that you want to give to your excel file so here we are you know storing employees information so I'm going to specify the name for my excel file s employees dot XLS Excel files have an extension of dot XLS so I'm using that extension here so that's response you know dot append header so to the header you know of the response object you know we are using this cont content-disposition header which is basically used to raise you know specify the file name for our excel file and the next thing to do is to specify the content type you know the mime type basically so using content type property so on the response object specify your content type our content type is nothing but application /xl so it's an excel data okay so those are the three things that we need to do on the response object after that I'm going to create instances of two classes here you may not understand why we are using those classes at the moment but don't worry that will be clearing just a bit so I'm going to use a class called string writer this class is present in system dot IO namespace so let's go ahead and import that system dot IO so its string right at last so let me create an instance of this class a string writer in a bit you'll understand how we are going to use this class and then I'm going to create another class HTML text writer class again in a bit we'll understand how we will be using this class so HTML text writer I'm going to call the instance as HTML text writer is equal to new HTML text writer and look at the constructor of this class it's expecting an object of type text writer but then look at what I'm going to pass to the constructor of this class I'm going to pass this string writer object but then you know I don't get any compilation errors look at this the constructor expects a text writer but I am passing string writer that's because if you look at the string writer object if I go to the definition string writer class inherits from text writer class so we can pass a string writer to this HTML text writer you know class as an inherited type in a bit we'll understand how we are going to use these two classes okay so those are the two objects I need and then the next thing we have to do here is where is our data present our data is present in grid view control and then what is the ID of this grid view control it's grid view one so the idea of the control is grid view one so now what I'm going to do is grid v1 dot I'm going to invoke a method called render control and look at the you know method it's expecting an object of type HTML text writer do we have an object of that type yes HTML text writer object I have here so I'm going to pass this object to this method now what is this going to do look at this render control method look at the intelligence it says outputs server control content to a provided you know system dot v dot u Y dot HTML text writer object so you know the content of this grid view control will be returned into this object okay so what is the content that is going to be present in this crib you control the employee data not only this employee data the grid control will also contain you know the HTML of the table cell table row etc ok so this HTML text writer object will have both the content and the controls HTML ok so that's why we have used this HTML text writer object so the entire content and the HTML will be returning to that object and now what I'm going to do I'm going to say response dot write and then I'm going to use the string writer object so response dot write string greater dot to string and then finally I'm going to say response dot and okay so that's all you know the code that we need to export data to excel so look at this the surrender control method is going to render the content and the HTML you know to this object HTML text writer and look at this HTML text writer it is actually taking in this string writer object as a parameter so you know now whatever content and HTML that we have in this object is now converted into a string format and stood in this object and then what I am doing with that object I am converting that to string representation using the to string method and then passing on that to the right method of the response object which is going to write that to the client and in the response header we specified that the name of the file is employee dot XLS so this data will be returned into the excel file and we are ending the response which means everything will be flushed out the response has ended okay so let's go ahead and run this and see if it works as expected so when the wave form loads as you might expect the data should be retrieved from TBL employee table and then displayed within the grid we control but let me click this button see what's going to happen huh look at the error we have an error stating control grid view one of type grid view must be placed inside a form tag with ronette is equal to server now let's actually go back and see if our grid view control is present inside a form tag yes it is look at this the grid we control is here and it's present inside this form tag with run it is equal to server attribute then why am I getting this error that's because of the surrender control method so this render control method you know causes the confusion it makes a dotnet think we are dynamically rendering a control outside of the form tag okay so obviously to fix this error all we need to do is we need to override a specific method called verify rendering in silver form so you don't have to remember that name by hard but then look at this the moment I say override and then I press space then it's going to show me all the methods that can that I can override so there is a method called verify rendering in silver form and then all I need to do is you know just override that method I don't have to provide any implementation here so this method confirms to.net runtime environment that though we are rendering controls dynamically they will be rendered as part of you know a server-side form okay so that's it let me go ahead and run this now and see if it's going to work as expected okay let's click them this report export to excel look at that I have this employee's dot axela XLS let's open that and we should have our employees data but then we're going to get a warning message here look at this the file you are trying to open employees start Excel XLS is in a different format than specified by the file extension verify that the file is not corrupted and it's from a trusted source before before opening the file do you want to open this file now now when I click on this warning yes look at that I have the file opened okay so why am I getting that error message I mean it's not an error message it's a warning message the warning message is a user notification function that was added to Microsoft Excel 2007 so the warning message can help prevent any unexpected problems that might occur because of possible incompatibility between the actual content of the file and the file name extension okay and if you want to resolve this error we need to edit the registry of our operating system now you can find those instructions here in this Microsoft article here so if you want to get rid of that error you need to edit the registry and to do that you can make use of this article here I'm not going to go into the details of editing registry right now but then if you look at you know the output here within the grid we control I mean we got the output but then look at the formatting you know it it is actually applying the background color to the entire row of the header as well as the data rows now don't want to you know I don't want the colors to be spread across the entire worksheet I only want those background colors to be present just you know where we have the data okay so to properly format this you know obviously you can apply styles to your grid view control as well loop through the header row apply whatever styles you want for these cells in the header row similarly do the same thing for you know the data rows as well and just to speed things up I have this already implemented so let me copy this and paste that within our you know code behind file here so look at the code what am i doing I'm saying grid v1 dot header row to that you know on the header row we are using the style property and we are adding a style of background color you know that's the style background color I am setting it to ffff meaning I am setting it to white color all red green and blue all the three colors I am setting it to FF FF & FF which means you know it's a white color and then I'm looping through each cell within the head header row and then for each table cell I am setting a background color of a five five one two nine that's nothing but I picked up that color number from the header row style here look at the header rows time that's what is the header row style when we applied you know that Auto formatting of brown sugar the header row color number is that one so that's what we are specifying here for the header row and then look at this for each data row for each data row I'm looping through each data row and each data row has caught cells within them so the first for each loop loops through each row and then the inner for each loop is going to loop through each row I mean each row cells and then for each cell within the data row we are setting a background color of F F F 7 e7 which is nothing but you know the roof style background color here so I just picked it up from the auto formatted HTML that we have here and that said so if I am going to run this now and then when we export it to excel this time it should be properly formatted the formatting should be applied only for the data part so let's open that so we get the warning again I open that look at that you know the colors are applied just to the grid we control all right fine now we have seen how to export this data to excel let's say how to export it to Microsoft Word to do that all I need to do is let's drag and drop another button control here and let's set the text on the button control to export to word and let's double click that regenerate the even handler method so what I'm going to do I'm going to copy this entire content I mean the code that we have in export to excel click event handler and paste that in this button to click even handler which is going to export the data to Microsoft Word ok now I'm going to make only two modifications the first modification here is instead of the file extension XLS I am going to change that to dot do say and then here the content type instead of Excel I'm going to say word that's it let's go ahead and run this now and see you know if it's going to export the data to Microsoft for a document all right so let's click on this button export to word look at that I have a word document there I click on the word document it opens up word and we should have this great view data within Microsoft Word look at that it works exactly as expected all right so in this video we have seen how to export data to excel as well as to Microsoft Word all right on this slide you can find resources for asp.net c-sharp and sequence of interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 77,421

Keywords: Export, gridview, excel, asp.net, c#, c#.net, exporting, data, grid view, content, tutorial, workbook, word, microsoft, document

Id: K8_YrQVKe58

Length: 17min 40sec (1060 seconds)

Published: Tue Apr 16 2013