Exporting data from datagrid to Excel

Question

Hi
I am trying to export data from datagrid to excel sheet. My datagrid is having 5 columns and while exporting i would like to see only 3 columns in excel sheet. How can I do that? I appreciate if any one give an idea.
Also while transfering the data from datagrid to excel, I would like display header and footer to the transferred excelsheet.
Thank you
Prasad

Pero · Answer

Hi Hi you can easily convert DataGrid to Excel with GemBox.Spreadsheet Excel C# library. Here is a code snippet how to do it in your ASP.NET Excel app: // Get DataTable that DataGrid is bound to. var dataTable = (DataTable)dataGrid.DataSource; // Create new ExcelFile. var ef = new ExcelFile(); // Add new worksheet to the file. var ws = ef.Worksheets.Add(dataTable.TableName); // Insert the data from DataTable to the worksheet starting at cell A1. ws.InsertDataTable(dataTable A1 true); // Stream file to browser. Response.Clear(); Response.ContentType = application/vnd.ms-excel; Response.AddHeader(Content-Disposition attachment; filename=Employee.xls); ef.SaveXls(Response.OutputStream); Response.End(); For live web demo see: http://www.gemboxsoftware.com/WebDemo/Index.aspx

Prasad Vempati · Answer

Mr.Nikhil Thanx for your reply. It was not the problem of exporting data to excel. It was not the problem of header either. Whatever the header the datagrid is having will be exported along with other data. Probably I should not say header in my question. After exporting into excel sheet I would like to have the company name and address phone and fax etc on top of the excel sheet. For example THE COMPANY NAME 1111 ANYWHERE ST. CITY STATE ZIPCODE PH:(111)111-1111 FAX:(111)111-1111 --------------------------------------------------------------------------------------------------------------------------------------------------------- Here comes the data from datagrid --------------------------------------------------------------------------------------------------------------------------------------------------------

Nikhil Mahajan · Answer

for exporting gridview/datagrid u dont have have to create html table.. just simple use above code :)

Jitendra Patel · Answer

Hi........... You have create HTML table in your code and your 3 column are bind in your html table. and it will transfer into EXcel. and use above code to exporting HTML table to excel. Thanks Jitendra Patel

Nikhil Mahajan · Answer

u can set datagrid/gridview header footer on..then it will show in excel code to export datagrid to excel Response.Clear(); Response.AddHeader(  content-disposition   attachment;filename=FileName.xls ); Response.Charset =   ; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType =  application/vnd.xls ; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); myDataGrid.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); Code To Export TO Word Response.Clear(); Response.AddHeader(  content-disposition   attachment;filename=FileName.doc ); Response.Charset =   ; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType =  application/vnd.word ; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); myDataGrid.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); Exporting To text file Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper selectCommandWrapper = db.GetStoredProcCommandWrapper(  sp_GetLatestArticles ); DataSet ds = db.ExecuteDataSet(selectCommandWrapper); StringBuilder str = new StringBuilder(); for ( int i=0;i 0 ].Rows.Count - 1 ; i++) { for ( int j=0;j 0 ].Columns.Count - 1 ; j++) { str.Append(ds.Tables[ 0 ].Rows[i][j].ToString()); } str.Append(   ); } Response.Clear(); Response.AddHeader(  content-disposition   attachment;filename=FileName.txt ); Response.Charset =   ; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType =  application/vnd.text ; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); Response.Write(str.ToString()); Response.End();