Exporting data from datagrid to Excel

Prasad Vempati

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pero16y ago
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
0
Prasad Vempati16y ago
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

--------------------------------------------------------------------------------------------------------------------------------------------------------
0
Nikhil Mahajan16y ago
for exporting gridview/datagrid u dont have have to create html table.. just simple use above code :)
0
Jitendra Patel16y ago
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
0

Nikhil Mahajan16y ago

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<=ds.Tables[0].Rows.Count - 1; i++)
{
  for(int j=0;j<=ds.Tables[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();